欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL优化篇:数据准备

程序员文章站 2024-03-19 19:06:10
...


为了后续学习MySQL相关的优化内容,需要准备大量相关的演示数据,为此学习如何通过批量的方式向MySQL数据库中插入数据。

1、建表语句

#创建部门表的语句
CREATE TABLE `dept` (
	`id` INT(11) NOT NULL AUTO_INCREMENT, # 部门ID 非空、自增
	`deptName` VARCHAR(30) DEFAULT NULL, #部门名称 默认为空
	`address` VARCHAR(40) DEFAULT NULL,  #部门地址 默认为空
	ceo INT NULL ,  # 部门领导
	PRIMARY KEY (`id`) #创建主键
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

#创建员工表的语句
CREATE TABLE `emp` (
	`id` INT(11) NOT NULL AUTO_INCREMENT, # 员工ID 非空、自增
	`empno` INT NOT NULL , # 员工编号 非空
	`name` VARCHAR(20) DEFAULT NULL, # 员工姓名 默认为空
	`age` INT(3) DEFAULT NULL,  # 年龄 默认为空
	`deptId` INT(11) DEFAULT NULL,  # 所在部门ID 默认为空
	PRIMARY KEY (`id`) #创建主键
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2、设置参数

在执行创建函数之前,首先要保证log_bin_trust_function_creators参数为1,即on开启状态,否则会出现以下错误:

MySQL优化篇:数据准备

MySQL优化篇:数据准备

永久生效

以上设置只存在于当前操作,想要永久生效,需要写入到配置文件中:

在[mysqld]中加上log_bin_trust_function_creators=1

3、编写随机函数

创建函数的目的是保证每条数据都不相同。

3.1 随机产生字符串

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
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 $$

3.2 随机产生部门编号

#用于随机产生多少到多少的编号
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;
RETURN i;
END$$

删除函数

如果要删除相应的函数,可执行以下语句

  • drop function rand_string;
  • drop function rand_num;

4、创建存储过程

4.1 创建往emp表中插入数据的存储过程

DELIMITER $$
CREATE PROCEDURE insert_emp( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把 autocommit 设置成 0
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6) , rand_num(30,50),rand_num(1,10000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;

4.2 创建往dept表中插入数据的存储过程

#执行存储过程,往 dept 表添加随机数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;

5、调用存储过程

5.1 添加数据到部门表

#执行存储过程,往 dept 表添加 1 万条数据
DELIMITER ;
CALL insert_dept(10000);

5.2 添加数据到员工表

#执行存储过程,往 emp 表添加 50 万条数据
DELIMITER ;
CALL insert_emp(100000,500000);