先创建一个数据库创建两张表.
# 新建库
create database mysql-senior;
use mysql-senior;
#1 建表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=GBK ;
#2 建表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 ;
设置参数log_bin_trust_function_creators
为什么要设置这个参数?如果不设置这个参数创建存储过程或者函数就会报错.
当开启二进制日志后(可以执行show variables like 'log_bin'查看是否开启),
如果变量log_bin_trust_function_creators为OFF,那么创建或修改存储函数就会报
“ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)”这样的错误
【解决方法】
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
默认是off
# 这样添加了参数以后,如果mysqld重启,上述参数又会消失,永久方法:
windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下 编辑 /etc/my.cnf 加上log_bin_trust_function_creators=1
存储函数和存储过程的区别就是,存储函数有返回值,存储过程无返回值.
下面这个是创建一个存储函数作用是随机产生字符串
每行注释:
DELIMITER $$ :声明接收符号为$$,因为在创建的时候,里面的具体内容已经使用;结尾了,如果不设置一下的话,mysql以为到;就结束了就会执行这个操作.所以得自己改变一下结束符号.
如下图,当设置delimiter $$后,使用;结束符号就不好使了,必须得使用$$为结束符号.
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
CREATE FUNCTION就是声明创建一个存储函数,和创建表,索引那些意思一样
rand_string 它为函数名称
(n INT) 它为参数,只不过在mysql中参数类型写在后面,变量在前面
RETURNS VARCHAR(255) 它为返回一个varchar类型的字符串.
begin为函数体开始 end为函数体结束
DECLARE chars_str VARCHAR(100) DEFAULT 'asfasf' 这个是声明一个变量池
while i < n do 这个就是java的do while 循环语句
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
上面这句的意思就是,每次循环,把return_str后面拼接一个52个字母中随机一位字母.
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 $$
#假如要删除
#drop function rand_string
创建一个随机部门编号的存储函数 rand()*10 是生成一个 1-10的随机数
#用于随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num( )
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10);
RETURN i;
END $$
创建存储过程
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
CREATE PROCEDURE 声明创建一个存储过程.
IN START INT(10) : in 表示外界输入进来的意思 , start 变量名 int(10) 参数类型
SET autocommit = 0; 这个是把自动提交设置为手动提交,因为这个存储过程是要一次性插入多少条数据.
REPEAT END REPEAT; 这两个合一块相当于是while循环的意思,当到达一定条件就不循环了.
UNTIL i = max_num 它的意思是直到 i=max_num的时候就跳出循环
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 $$
#删除
# drop PROCEDURE insert_emp;
批量插入dept表的存储过程
#执行存储过程,往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 $$
执行完后,这就有了,就可以进行调用了.
调用存储过程使用call关键字
插入员工表五十万条记录