Mysql进阶学习Ⅱ，使用数据库函数随机插入数据

Mysql进阶学习Ⅱ

我们使用Mysql中的函数来进行随机数据的插入，函数分为两种：一种是有返回值的，用它来生成一些随机的单个数据；另一种是无返回值的，用它执行一些复杂数据库操作，比如自动生成表数据，对多张表的关联增删改查等。这章我们以一个实例来学习。

• 使用的工具是Navicat for Mysql

1、建表

建立两张表：emp（员工表）和dept（部门表）

• emp表
  
• dept
  

2、开启数据库函数

在查询编辑器中执行下面的语句开始Mysql支持数据库函数。

• 显示状态：

show variables like 'log_bin_trust_function_creators'

• 设置状态：值为1开启（ON），值为0关闭（OFF）

set global log_bin_trust_function_creators=1

3、编写随机生成函数

1. 随机字符串函数

①在查询编辑器中执行下面的语句。

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 $$

②点击左侧函数刷新后得到下图的结果：

③右键rand_string运行结果：输入参数6，会随机生成一个6个字母的字符串，在本表中可用于各种名字。

2. 随机生成数字函数

①在查询编辑器中执行下面的语句。

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

②点击左侧函数刷新后得到下图的结果：

③右键rand_num运行结果：会随机生成100~119中的一个数,在本表中用于部门编号。

4、 编写存储函数

1. 随机存储部门表数据函数：

①在查询编辑器中执行下面的语句。

DELIMITER $$
CREATE PROCEDURE insert_dept(IN start_num 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_num+i),rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$

②点击左侧函数刷新后得到下图的结果：

③右键inset_dept运行结果：会向部门表里插入20条数据，deptno从101~120。

2. 随机存储员工表数据函数：

①在查询编辑器中执行下面的语句。

DELIMITER $$
CREATE PROCEDURE insert_emp(IN start_num INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT 
SET i = i + 1;
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (start_num+i,rand_string(6),'SALESMAN',0001,NOW(),rand_sal(),400,rand_num());
UNTIL i = max_num;
END REPEAT;
COMMIT;
END;

②点击左侧函数刷新后得到下图的结果：

其中rand_sal函数，参考rand_num，生成随机工资数。

③右键inset_emp运行结果：输入参数0,500000，会向部门表里插入50W条数据，我使用的是腾讯云数据库，耗时01:01:57。参数0代表从0开始，500000代表插入50W条数据。