MySQL實現批量插入測試數據的方法

今天小編給大家分享的是MySQL實現批量插入測試數據的方法，相信很多人都不太了解，為了讓大家更加了解，所以給大家總結了以下內容，一起往下看吧。一定會有所收獲的哦。

前言

在開發過程中我們不管是用來測試性能還是在生產環境中頁面展示好看一點, 又或者學習驗證某一知識點經常需要一些測試數據, 這個時候如果手敲的話, 十行二十行還好, 多了就很死亡了, 接下來介紹兩種常用的MySQL測試數據批量生成方式

• 存儲方式+函數

• Navicat的數據生成

表

準備了兩張表 角色表:

• id: 自增長

• role_name: 隨機字符串, 不允許重復

• orders: 1-1000任意數字

用戶表:

• id: 自增長

• username: 隨機字符串, 不允許重復

• password: 隨機字符串, 允許重復

• role_id: 1-10w之間的任意數字

建表語句:

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 '排序權重\r\n',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

使用函數生成

通過存儲過程快速插入, 通過函數保證數據不重復

設置允許創建函數

查看 MySQL是否允許創建函數

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

結果如圖所示, 我們使用以下命令將創建函數功能打開（global-所有session都生效）

SET GLOBAL 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;

創建存儲過程

插入角色表

-- 插入角色數據
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;

插入用戶表

-- 插入用戶數據
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;

執行存儲過程

-- 執行存儲過程，往dept表添加10萬條數據
CALL insert_role(100000); 
-- 執行存儲過程，往emp表添加100萬條數據，編號從100000開始
CALL insert_user(100000,1100000);

總結

執行用時 10w數據差不多半分鐘, 100w數據超過了20分鐘, 同時 user的存儲還卡死很久...

最后都成功新增, 但是自動遞增值和行數不一致, 這個我也不知道因為啥...

數據展示

role表

user表

使用 Navicat自帶的數據生成

接下來我們使用 Navicat的數據生成

直接下一步, 然后選擇對應的兩張表生成行數和對應的生成規則, 基于之前的執行速度, 這次 role生成 1w數據, user生成 10w數據

對于字符串類型的字段, 我們可以設置他的隨機數據生成器, 根據需要進行選擇

例如角色名稱, 選擇了 職位名稱 還可以進行是否包含 null 的選擇等

但是如果是 姓名 那么就會讓你選擇是否唯一

數字的話會讓你選擇范圍, 默認值等

等確定好了, 我們就可以點擊右下角進行生成隨機測試數據

關于MySQL實現批量插入測試數據的方法就分享到這里了，希望以上內容可以對大家有一定的參考價值，可以學以致用。如果喜歡本篇文章，不妨把它分享出去讓更多的人看到。