MySQL把A表的数据插入到B表

背景

web 开发中，我们经常需要将一个表的数据插入到另外一个表，有时还需要指定导入字段，设置只需要导入目标表中不存在的记录，虽然这些都可以在程序中拆分成简单 sql 来实现，但是用一个 sql 的话，会节省大量代码

A 表的数据插入到 B 表中

数据准备

-- 来源表
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_a` (`a`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4;


-- 目标表
CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `d` varchar(11) DEFAULT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4;


--  创建存储过程，向 t1 表插入 100 条数据
CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`(INOUT `i` int)
BEGIN
	DECLARE
		i INT;
	SET i = 1;
	WHILE
		( i <= 100 ) 
		DO INSERT INTO t2 VALUES (i, i, i);
	SET i = i + 1;
	END WHILE;
END

t1 表的 1 ~ 100 条数据如下

两张表的字段一致

-- 插入 A 表的全部数据
INSERT INTO 目标表 (SELECT * FROM 来源表)

INSERT INTO insertTest (SELECT * FROM insertTest2)

-- 插入 A 表的部分数据
INSERT INTO 目标表 (SELECT * FROM 来源表 过滤条件)

INSERT INTO insertTest (SELECT * FROM insertTest2 WHERE id >= 100)

两张表的字段一致或不一致时，插入指定字段

INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表 (这里的话字段必须保持一致)

INSERT INTO t3 ( id, b ) ( SELECT t1.id, t1.b FROM t1 WHERE t1.id <= 5 )

测试 t3 表的数据如下

只插入 B 表中不存在的记录

INSERT INTO 目标表 (字段1, 字段2, ...)  SELECT 字段1, 字段2, ...  FROM 来源表  
WHERE not exists 
	(select * from 目标表 where 目标表.比较字段 = 来源表.比较字段);


INSERT INTO t3 ( id, b ) (
	SELECT
		t1.id,
		t1.b 
	FROM
		t1 
	WHERE
		NOT EXISTS ( SELECT * FROM t3 WHERE t3.b = t1.b ) 
)

再次测试 t3 表的数据如下

插入 B 表中不存在的部分记录

INSERT INTO insertTest2 (id,name) (SELECT id, name FROM insertTest
	WHERE NOT EXISTS 
		(SELECT * FROM insertTest2 WHERE insertTest2.id = insertTest.id));

插入 B 表中不存在的一条记录

INSERT INTO insertTest (id, name) (SELECT 100, 'liudehua' FROM  dual    
	WHERE NOT EXISTS 
		(SELECT * FROM insertTest WHERE insertTest.id = 100));