MySQL 批量插入并返回批量自增ID的方法

在MySQL数据库表中插入大量记录时，若逐条插入时，性能非常性，也比较耗，因此MySQL提供批量插入记录的方法,具体语法为：

Insert into myTable(Field1,Field2,...Fieldn) values
(V11,V12,…V1n),
(V21,V22,…V2n),
……，
(Vn1,Vn2,…Vnn)

上述语句，可以一次性插入大量的数据，具体记录的数量只受系统变量max_allowed_packet的限制，同时提高高数据插入的速度；但有一个问题，就是插入数据后，无法得知插入记录的自增ID，如果需要逐条去查询，那个效率会低得让人无法忍受！

这里就有一种解决办法，可以获取新插入记录的ID，具体思路是：

1. 锁表，并获取当前表的自增ID；
2. 执行插入操作；
3. 获取当前表的自增ID；
4. 表解锁

其中步骤2，3可以放在存储过程中执行(MySQL不支持在存储过程中锁表)；这样的话,前后两次获得的自增ID区间内的值，就是新插入记录的自增ID，可以依据插入顺序为其赋值，

测试表如下：

CREATE TABLE `device`  (
  `ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `Name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `IP` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `Port` int(8) NULL DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

对测试表作批量插入并取得自增ID的语句如下：

LOCK tables `device` WRITE; 	-- 锁表
	-- 取得插入前的自增ID
	SELECT `AUTO_INCREMENT` into @MaxID1 FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'MyDataBase' AND  TABLE_NAME  = 'device'; 
	INSERT INTO `device`(`ID`, `Name`, `IP`, `Port`) VALUES 
	(0, 'Dev1', '192.168.2.1', 80),
	(0, 'Dev2', '192.168.2.2', 80),
	(0, 'Dev3', '192.168.2.3', 80),
	(0, 'Dev4', '192.168.2.4', 80),
	(0, 'Dev5', '192.168.2.5', 80);
	-- 取得插入后的自增ID
	SELECT `AUTO_INCREMENT` into @MaxID2 FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'MyDataBase' AND  TABLE_NAME  = 'device';
	SELECT  @MaxID1,@MaxID2;		-- 输出自增ID
	UNLOCK table;		-- 解除所有表的锁定

在输入记录中，@MaxID1和@MaxID2区间的ID，就是新增记录的ID，而且是按顺序连续递增的，可以依次分配给插入前的数据；

注意：在实际应用过程中，以上语句可能会存在锁表的隐患，因为插入数据过程中可能会发生异常，导致无法正常执行Unlock talbe，因此建议把步骤2，3放到存储过程中，将在存储过程作异常捕捉，以防止异常影响到Unlock talbe的执行；