MySQL INSERT INTO SELECT

/ 数据库 / 0 条评论 / 78浏览

初始化数据库

CREATE TABLE `xiaohaizi_second_table` (
  `c1` int unsigned NOT NULL AUTO_INCREMENT,
  `c2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `c3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`c1`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学习小孩子-insert-into-select测试';

查询结果插入数据

INSERT INTO table_name(c1, c2, c3) SELECT c1, c2, c3 FROM table_name2

查询结果,插入数据, 如果遇到 唯一约束, 则跳过

INSERT IGNORE INTO table_name(c1, c2, c3) SELECT c1, c2, c3 FROM table_name2

查询结果,插入数据,如果遇到 唯一约束, 则更新

冲突更新单列:

假设c1列具有唯一索引,插入数据(1, ‘xx’, ‘yy’),如果c1列已存在c1=1的数据,则将此列的c2字段更新为’hhh’

INSERT INTO table_name(c1, c2, c3) VALUES(1, ‘xx’, ‘yy’) ON DUPLICATE KEY UPDATE c2 = ‘hhh'

冲突更新多列:

假设c1列具有唯一索引,插入数据(1, ‘xx’, ‘yy’),如果c1列已存在c1=1的数据,则将此列的c2字段更新为待插入行中对应列的数据。

INSERT INTO table_name ( c1, c2, c3 )
VALUES
	( 1, "xx1", "yy1" ),
	( 2, "xxx2", "yyy2" ) 
	ON DUPLICATE KEY UPDATE c2 =
VALUES
	( c2 ),
	c3 =
VALUES
	( c3 );