mysql实现merge功能之DUPLICATE key UPDATE语法

知识点    

    mysql提供了DUPLICATE key UPDATE语法可以实现类似oracle中的merge功能，当表中存在数据的时候（根据主键判断），就更新记录，不存在的时候则插入数据，相关语法如下：

insert into tb1（col1,col2） select cola,colb from tb2 on DUPLICATE key UPDATE cola=values(cola);

注意：

实验例子

person表结构和记录如下

CREATE TABLE `PERSON` (
  `id` int(8) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '姓名',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `sex` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '性别',
  `height` double(6,2) DEFAULT NULL COMMENT '身高（cm）',
  `weight` double(6,2) DEFAULT NULL COMMENT '体重（kg）',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

person_temp

CREATE TABLE `PERSON_TEMP` (
  `id` int(8) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '姓名',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `sex` varchar(8) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '性别',
  `height` double(6,2) DEFAULT NULL COMMENT '身高（cm）',
  `weight` double(6,2) DEFAULT NULL COMMENT '体重（kg）'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

现在需要用person_temp表中的数据去更新person表的数据，根据上面的数据分析可知，person表的主键为id，person——temp的两条记录一条对应执行update，一条执行insert，返回影响条数为3。下面我们验证一下。执行语句：

INSERT into PERSON select * from PERSON_TEMP on DUPLICATE key UPDATE 
id=values(id),
name=values(name),
age=values(age),
sex=values(sex),
height=values(height),
weight=values(weight);

返回结果如下： 执行上述语句后person表的记录如下： 跟先前的数据比对，我们发现id=1的记录被更新了，插入了一条id=6的记录，执行结果返回的影响条数为3，与我们设想的一样。

自动生成语句

实际环境中，我们需要处理的表字段数可能是几十个上百个的，这时候手动写insert select on DUPLUCATEE KEY UPDATE是极其麻烦的，我根据person以及person_temp表的关系，写了自动生成 on DUPLUCATEE KEY UPDATE语句的语法，大家有需要可以借鉴参考一下：

----information_schema用户下执行语句
SELECT
	CONCAT(
		'INSERT into ',
		UPPER( TABLE_NAME ),
		' select * from ',
		UPPER( TABLE_NAME ),
		'_TEMP on DUPLICATE key UPDATE ',
	GROUP_CONCAT( a.colmap ORDER BY a.ORDINAL_POSITION )) 
FROM
	(
	SELECT
		CONCAT( COLUMN_NAME, '=values(', COLUMN_NAME, ')' ) colmap,
		TABLE_NAME,
		ORDINAL_POSITION 
	FROM
		`COLUMNS` 
	WHERE
		TABLE_SCHEMA = 'testdb' 
		AND table_name = 'PERSON' 
	ORDER BY
	ORDINAL_POSITION 
	) AS a;

生成的结果就是我们上面执行的语句

INSERT into PERSON select * from PERSON_TEMP on DUPLICATE key UPDATE 
id=values(id),
name=values(name),
age=values(age),
sex=values(sex),
height=values(height),
weight=values(weight)

注意：mysql默认 group_concat()返回的长度限制为1024，在上述语句中，当字段数较多的时候，会返回不完整的语句，要根据需要设置参数： 永久方法： 在my.cnf加上参数 group_concat_max_len = 102400 临时方法： SET GLOBAL group_concat_max_len = 102400; SET SESSION group_concat_max_len = 102400;