CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL COMMENT '用戶名',
  `role_id` int(11) DEFAULT NULL COMMENT '角色id',
  `password` varchar(255) DEFAULT NULL COMMENT '密碼',
  `salt` varchar(255) DEFAULT NULL COMMENT '鹽',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(255) DEFAULT NULL COMMENT '角色名',
  `orders` int(11) DEFAULT NULL COMMENT '排序權(quán)重\r\n',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

SET GLOBAL log_bin_trust_function_creators=1; 

-- 隨機(jī)產(chǎn)生字符串
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 $$
-- 假如要?jiǎng)h除
-- drop function rand_string;

-- 用于隨機(jī)產(chǎn)生區(qū)間數(shù)字
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$$
-- 假如要?jiǎng)h除
-- drop function rand_num;

-- 插入角色數(shù)據(jù)
DELIMITER $$
CREATE PROCEDURE insert_role(max_num INT)
BEGIN  
	DECLARE i INT DEFAULT 0;   
	SET autocommit = 0;    
	REPEAT  
		SET i = i + 1;  
		INSERT INTO role ( role_name,orders ) VALUES (rand_string(8),rand_num(1,5000)); 
		UNTIL i = max_num  
	END REPEAT;  
	COMMIT;  
END$$
-- 刪除
-- DELIMITER ;
-- drop PROCEDURE insert_role;

-- 插入用戶數(shù)據(jù)
DELIMITER $$
CREATE PROCEDURE  insert_user(START INT, max_num INT)
BEGIN  
	DECLARE i INT DEFAULT 0;   
	SET autocommit = 0;    
	REPEAT  
		SET i = i + 1;  
		INSERT INTO user (username, role_id, password, salt ) VALUES (rand_string(8) ,rand_num(1,100000), rand_string(10), rand_string(10));  
		UNTIL i = max_num  
	END REPEAT;  
	COMMIT;  
END$$
-- 刪除
-- DELIMITER ;
-- drop PROCEDURE insert_user;

-- 執(zhí)行存儲(chǔ)過程，往dept表添加10萬條數(shù)據(jù)
CALL insert_role(100000); 
-- 執(zhí)行存儲(chǔ)過程，往emp表添加100萬條數(shù)據(jù)，編號(hào)從100000開始
CALL insert_user(100000,1100000);