ITPub博客

首页 > 数据库 > MySQL > mysql实现merge功能之DUPLICATE key UPDATE语法

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

原创 MySQL 作者:linxueguo 时间:2021-09-07 09:46:21 0 删除 编辑

知识点    

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

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

注意:

  • values(cola)语法仅能用于insert select语句中,这里也可以是你想要的任何合法的值

  • update后面跟所有需要更新的字段

  • 为了提高性能,建议在insert into tbname后面列出所有需要的字段名

  • 该语法对于单条记录执行更新操作,返回的影响条数为2,执行插入操作,返回的影响条数为1,不执行操作,返回影响条数为0


实验例子

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;





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31374736/viewspace-2790722/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2016-07-16

  • 博文量
    10
  • 访问量
    3357