创建函数，假如报错：This function has none of DETERMINISTIC……
# 由于开启过慢查询日志，因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
show variables like ‘log_bin_trust_function_creators’;
set global log_bin_trust_function_creators=1;
# 这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：
windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=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 $$

假如要删除
#drop function rand_string;

随机产生部门编号 :

#用于随机产生多少到多少的编号
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_num;

创建往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;

执行存储过程，往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;

调用存储过程

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

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