MySQL procedure存储过程循环插入数据和duplicate的使用

SQL语句循环插入数据

---

 

一、有时为了测试接口需要手动插入很多测试数据，为了节省时间避免做更多复杂无味的ob操作，写一个存储过程来批量插入多条数据；

# 删除test表
DROP TABLE IF EXISTS test;

# 创建test表
CREATE TABLE `test` (
	`id` VARCHAR (255) NOT NULL,
	`fileName` VARCHAR (255) DEFAULT NULL,
	`downloadCount` INT (11) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `idx_key` (
		`id`,
		`fileName`,
		`downloadCount`
	) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8;

# 删除dowhile存储过程
DROP PROCEDURE dowhile;

# 创建dowhile存储过程
CREATE PROCEDURE dowhile ()
BEGIN

DECLARE i INT;


SET i = 1;

START TRANSACTION;


WHILE i < 11 DO
	INSERT INTO test (
		`id`,
		`fileName`,
		`downloadCount`
	)
VALUES
	(
		CONCAT(i),
		CONCAT('文件', '_', i),
		1
	);


SET i = i + 1;


END
WHILE;

COMMIT;


END;

# 查看test表的所有存储过程
SHOW PROCEDURE STATUS WHERE db = 'test';

# 查看test表的全部存储过程
SELECT *FROM mysql.proc;

# 执行dowhile存储过程
CALL dowhile (); 

结果：

有则更新，无则新增

---

 

二、INSERT ... ON DUPLICATE KEY UPDATE ... 批量插入与更新(存在则更新，不存在则插入)

INSERT INTO test (
	`id`,
	`fileName`,
	`downloadCount`
)
VALUES
	('11', '我的文件', 1) ON DUPLICATE KEY UPDATE downloadCount = downloadCount + 1;

结果：

 另外，如果执行时含duplicate的SQL报错，请参考：

# 1024*1024代表1M, set global max_allowed_packet = 2*1024*1024*10;
SHOW VARIABLES LIKE '%max_allowed_packet%';