MYSQL用法(十) 将某字段的值全部+5

将某字段的值全部+5 但这个字段的类型是varchar;

语法:

UPDATE tablename
SET columnName = columnName + 5
WHERE id='xxx';

如:mysql>

UPDATE bd_lodop_temp_detail
SET ITEM_TOP = ITEM_TOP + 5
WHERE
	TEMPLATE_UID = '2c2b218995f04b1f876a3667e144b002'

如果该字段中记录了两个用逗号隔开的整型数据;
语法: 
UPDATE tablename
SET columnName = concat(
	SUBSTRING_INDEX(columnName, ',', 1) + 5,
	substr(
		columnName,
		LOCATE(',', columnName)
	)
)
WHERE ID ='xxx'
如:mysql>
UPDATE bd_lodop_temp_detail
SET ITEM_TOP = concat(
	SUBSTRING_INDEX(ITEM_TOP, ',', 1)+5,
	substr(
		ITEM_TOP,
		LOCATE(',', ITEM_TOP)
	)
)
WHERE
	TEMPLATE_UID = '2c2b218995f04b1f876a3667e144b002'