mysql insert截取_[mysql]查询截取分析-批量插入数据脚本

批量插入数据脚本

建表SQL

CREATE TABLE `dept` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

`deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',

`dname` varchar(20) NOT NULL DEFAULT '' COMMENT '部门名字',

`loc` varchar(13) NOT NULL DEFAULT '' COMMENT '部门地址',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表'

CREATE TABLE `emp` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

`empno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '员工编号',

`ename` varchar(20) NOT NULL DEFAULT '' COMMENT '员工名字',

`job` varchar(9) NOT NULL DEFAULT '' COMMENT '职位',

`mgr` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '上级编号',

`hiredata` date NOT NULL COMMENT '入职时间',

`sal` decimal(7,2) NOT NULL COMMENT '薪水',

`comm` decimal(7,2) NOT NULL COMMENT '分红',

`deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'

设置参数log_bin_trust_function_creators

创建函数，假如曝错：This function has none of DETERMINSTIC。。。。

由于开启过慢查询日志，因为我们开启了bin-log，我们必须为我们的function制定一个参数。

mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| log_bin_trust_function_creators | OFF |

+---------------------------------+-------+

1 row in set (0.00 sec)

mysql> SET GLOBAL log_bin_trust_function_creators=1;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| log_bin_trust_function_creators | ON |

+---------------------------------+-------+

这样添加了参数后，如果重启mysql，上面的参数又会消失，永久方法：

[mysqld]

log_bin_trust_function_creators=ON

创建函数

1、随机产生字符串

# 1随机产生字符串

DELIMITER $$ #使用两个$$表示语句结束，sql完了后 ; 不会出结果，需要 $$ 才行

CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)

BEGIN

DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

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

2、随机产生部门编号

DELIMITER $$

CREATE FUNCTION rand_num() RETURNS INT(5)

BEGIN

DECLARE i INT DEFAULT 0;

SET i = FLOOR(100 + RAND() * 10);

RETURN i;

END $$

创建存储过程

#向dept表批量插入

DELIMITER $$

CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))

BEGIN

DECLARE i INT DEFAULT 0;

#设置autocommit = 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 $$

#向emp表批量插入

DELIMITER $$

CREATE PROCEDURE insert_emp(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 emp(empno,ename,job,mgr,hiredata,sal,comm,deptno) VALUES((START + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());

UNTIL i = max_num

END REPEAT;

COMMIT;

调用存储过程

#恢复出厂默认 :结尾

DELIMITER ;

# 调用存储过程向dept表插入10个部门。

CALL insert_dept(100,10);

# 调用存储过程向emp表插入50万条数据。

mysql> CALL insert_emp(100001,500000);

Query OK, 0 rows affected (30.32 sec)