MySQL 通过存储过程批量插入数据

今天我们来演示一下使用 MySQL 的存储过程来批量插入数据。

1. 新建数据库

create database bigData;
use bigData;

复制

1. 建立数据表 dept 和 emp

-- 建立 dept 表
CREATE TABLE dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
ioc VARCHAR(13) NOT NULL DEFAULT ""
)ENGINE=INNODB DEFAULT CHARSET=GBK;

-- 建立 emp 表
CREATE TABLE emp(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, *编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", *名字*/
job VARCHAR(9) NOT NULL DEFAULT "", *工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, *上级编号*/
hiredate DATE NOT NULL, *入职时间*/
sal DECIMAL(7,2) NOT NULL, *薪水*/
comm DECIMAL(7,2) NOT NULL, *红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 *部门编号*/
)ENGINE=INNODB DEFAULT CHARSET=GBK;


复制

1. 设置参数 log_bin_trust_function_creators

创建函数，假如报错：This function has none of DETERMINISTIC  ……
由于开启过慢查询日志，因为我们开启了 bin-log，我们就必须为我们的 function 指定一个参数。

# 查看是否开启了
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

# 临时开启（mysqld 重启之后，参数会失效）
SET GLOBAL log_bin_trust_function_creators = 1;

复制

永久开启方法：

• Windows 下 my.ini [mysqld] 下加上 log_bin_trust_function_creators=1
• Linux 下 etc/my.cnf [mysqld] 下加上 log_bin_trust_function_creators=1

1. 创建函数，保证每条数据都不同

-- 随机产生字符串的 MySQL 函数

delimiter $$$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
while i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END while;
RETURN return_str;
END $$$


-- 用于随机产生部门编号

delimiter $$$
CREATE FUNCTION rand_num()
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10);
RETURN i;
END $$$

-- 如果要删除函数的话
drop function rand_num;

复制

1. 建立存储过程

-- 为 emp 表插入数据建立方法

delimiter $$$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
# set autocommit = 0 把 autocommit 设置成 0
SET autocommit = 0;
repeat
SET i = i + 1;
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES ((START + i),rand_string(6),
'SALESMAN', 0001, CURDATE(), 2000, 400, rand_num());
until i = max_num
END repeat;
# 循环之后在提交
COMMIT; 
END $$$

-- 执行存储过程，往 dept 表添加随机数据

delimiter $$$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
repeat
SET i = i + 1;
INSERT INTO dept(deptno,dname,ioc) VALUES ((START + i),rand_string(10),rand_string(8));
until i = max_num
END repeat;
COMMIT;
END $$$

-- 如果要删除掉存储过程的话
drop procedure insert_dept;

复制

1. 调用存储过程

-- 先恢复以分号结尾 sql 语句
delimiter ;

-- 往 dept 表中插入数据
call insert_dept(100,10);

-- 往 emp 表中插入数据
call insert_emp(100001,500000);

复制

最后说个事
公号算法变了，为防止看不到我的更新
大家帮忙加个星标
点击上方的公众号卡片
再点右上角三个点
就能看到设为星标
算我跪下来求你们

往期精选：

微信，看看你的另一半跟谁聊天频繁！

让你的微信“拍一拍”有趣且不失风度

微信年度账单来了，不敢看！

张万林，下雪了……我用编程带你看场纷飞大雪

还在使用默认的微信图标？赶紧换个吧！

我的微信和你们的不一样！？