msql批量插入数据

# 函数：CREATE FUNCTION创建并且有返回值
# 存储过程：CREATE PROCEDURE ，没有返回值


# 新建bigData数据库
CREATE DATABASE bigData;
USE bigData;


# 创建dept表
CREATE TABLE dept(
	id		INT		UNSIGNED 	PRIMARY KEY 	AUTO_INCREMENT,
	deptno		MEDIUMINT	UNSIGNED 	NOT NULL 	DEFAULT 0,
	dname		VARCHAR(20)	NOT NULL	DEFAULT '',
	loc		VARCHAR(13)	NOT NULL	DEFAULT ''
)ENGINE=INNODB DEFAULT CHARSET=UTF8;


# 新建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=UTF8;


# 查询数据库中所有表
SHOW TABLES;


# =============================================================================
# 创建函数,假如报错：This function has none of DETERMINISTIC……
# 是因为开启了过慢查询日志,我们开启了bin-log,我们就必须为function指定一个参数。
# =============================================================================
# 通过以下SQL语句,查询到log_bin_trust_function_creators 参数值为：OFF
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
# 通过以下SQL语句,设置 log_bin_trust_function_creators 参数值为：ON
SET GLOBAL log_bin_trust_function_creators = 1;
# 再次通过查询, 发现 log_bin_trust_function_creators 参数值为：ON
# ====》上述添加了参数以后,如果MySQL重启,上述参数又将会丢失, 永久方法如下：
# =============================================================================
# windows下my.ini配置中[mysqld]标签下加上
# log_bin_trust_function_creators = 1
# linux下/etc/my.cnf配置[mysqld]标签下加上
# log_bin_trust_function_creators = 1
# =============================================================================


# 查询emp表结构,希望ename和deptno是随机产生的随机数
DESC emp;


# now()就是一个函数
SELECT NOW() FROM DUAL;


# =============================================================================
# DELIMITER $$ 
# 因为SQL是以分号结束，当SQL语句使用了分号时就执行语句，但是因为创建的一个函数
# 需要使用到多个SQL语句，而语句又有以分号结尾，所以要修改结束符号。
# 使用了后，要想执行SQL语句要输入$$，表示执行SQL语句。结束符$$可自己定义。
# 比如说定义成三个$$$或四个$$$$不等
# 
# 编写函数：随机产生一个字符串
# =============================================================================
DELIMITER $$
# CREATE FUNCTION，创建一个函数，
# rand_string(n INT)，函数名rand_string自行定义，并且有一个参数为整数
# RETURNS VARCHAR(255)，返回一个定长255字节的字符串
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
# 函数开始
BEGIN
# DECLARE chars_str，声明一个变量，变量名为chars_str，
# VARCHAR(100)，变量类型
# DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'，返回值
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';#单条语句结束
# DECLARE return_str VARCHAR(255) DEFAULT ''，定义变量return_str，变更为VARCHAR(255)，默认值DEFAULT ''
DECLARE return_str VARCHAR(255) DEFAULT '';
# 定义循环变量i，从0开始
DECLARE i INT DEFAULT 0;
# WHILE循环
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 $$


# 假如要删除
# DROP FUNCTION rand_string;


# 再次使用以下语句查询时，需要加上$$方表示语句结束。(在CMD命令行形式操作需要加上$$)
SELECT NOW() FROM DUAL;$$


# 随机产生部门编号
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;


# 创建存储过程
# =============================================================================
# 编写存储过程：往emp表中插入数据的存储过程：
# 比如要插入1000W条数据，但真正生产时会分批次插入，如先插入50W，分20次插入
# =============================================================================
DELIMITER $$
# CREATE PROCEDURE insert_emp创建存储过程，没有返回值
# 有两个参数从start开始，结束于max_num，数据类型均为INT(10)型
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
# SET autocommit = 0把自动提交设置为0，因为你使用insert into语句时会自动调用autocommit
# 当你插入50W条时，每一条都自动调用自动提交，数据库性能很Low。所以设置为0先关闭自动提交
# 等50W条记录插入完成后，再一次提交。
SET autocommit = 0;
# REPEAT，重复执行
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,loc) VALUES((START + i),rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$


# 删除存储过程的语句
# DROP PROCEDURE insert_dept;


# =============================================================================
# 之前为避免在创建函数或存储过程时(可能有多条分号结束的语句)，使用分号导致语句
# 结束而引起创建函数或存储过程失败，使用了有别于分号的$$自定义符号
# 现在重新定义分号为结束符号
# =============================================================================
DELIMITER ;


SELECT * FROM dept;
# 调用存储过程
# 使用CALL 存储过程调用名
# 从100开始，插入10记录
CALL insert_dept(100,10);
SELECT * FROM emp;
CALL insert_emp(100001,500000);


# 查看存储过程
SELECT `name` FROM mysql.`proc` WHERE db = 'bigdata' AND `type` = 'procedure';
SHOW PROCEDURE STATUS;
# 查看函数
SELECT `name` FROM mysql.`proc` WHERE db = 'bigdata' AND `type` = 'function';
SHOW FUNCTION STATUS;
# 查看存储过程或函数的创建代码
SHOW CREATE PROCEDURE insert_dept;
SHOW CREATE FUNCTION rand_num;

DROP TABLE dept;