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);

注意：

• 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;