MySQL生成千萬測試資料以及遇到的問題

2022-08-03 14:04:14

1、建立基礎表結構

CREATE TABLE `t_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `c_user_id` varchar(36) NOT NULL DEFAULT '',
 `c_name` varchar(22) NOT NULL DEFAULT '',
 `c_province_id` int(11) NOT NULL,
 `c_city_id` int(11) NOT NULL,
 `create_time` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_user_id` (`c_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2、建立記憶體表

直接往實表新增資料比較慢，所以我們先插入記憶體表，然後再同步到實表。

CREATE TABLE `t_user_memory` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `c_user_id` varchar(36) NOT NULL DEFAULT '',
 `c_name` varchar(22) NOT NULL DEFAULT '',
 `c_province_id` int(11) NOT NULL,
 `c_city_id` int(11) NOT NULL,
 `create_time` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_user_id` (`c_user_id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

3、建立儲存過程和函數

# 建立隨機字串
delimiter $$
CREATE DEFINER = `root` @`%` FUNCTION `randStr` ( n INT ) RETURNS VARCHAR ( 255 ) CHARSET utf8mb4 DETERMINISTIC BEGIN
	DECLARE
		chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
	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() * 62 ), 1 ));
		
		SET i = i + 1;
		
	END WHILE;
	RETURN return_str;
	
	END $$ 
# 建立隨機時間的函數，sd和ed兩個入參代表生成的時間是這個時間範圍內的。sd開始時間，ed截止時間。	
CREATE DEFINER = `root` @`%` FUNCTION `randDataTime` ( sd DATETIME, ed DATETIME ) RETURNS datetime DETERMINISTIC BEGIN
	DECLARE
		sub INT DEFAULT 0;
	DECLARE
		ret DATETIME;
	
	SET sub = ABS(
	UNIX_TIMESTAMP( ed )- UNIX_TIMESTAMP( sd ));
	
	SET ret = DATE_ADD( sd, INTERVAL FLOOR( 1+RAND ()*( sub - 1 )) SECOND );
	RETURN ret;

END $$ 

# 建立插入資料儲存過程
CREATE DEFINER = `root` @`%` PROCEDURE `add_t_user_memory` ( IN n INT ) BEGIN
	DECLARE
		i INT DEFAULT 1;
	WHILE
			( i <= n ) DO
			INSERT INTO t_user_memory ( c_user_id, c_name, c_province_id, c_city_id, create_time )
		VALUES
			(
				uuid(),
				randStr ( 20 ),
				FLOOR( RAND() * 1000 ),
				FLOOR( RAND() * 100 ),
				randDataTime ( "2020-01-01", "2021-01-01" ));
		

		SET i = i + 1;
		
	END WHILE;

END $$
delimiter ;

4、執行儲存過程

儲存過程當中的數位就是要生成的數量，自行填寫。

CALL add_t_user_memory(10);

100萬大概需要8分鐘！

5、遇到的問題

建立儲存過程和執行的時候可能會出現以下兩種問題：

5.1、1449錯誤

在建立儲存過程的時候可能會出現1449:錯誤：

mysql 1449 ： The user specified as a definer (‘root’@‘%’) does not exist

經查詢是許可權問題，解決辦法：

執行sql：

grant all privileges on *.* to 'root'@'%' identified by ".";
flush privileges;

5.2、1114錯誤

當生成數量大的時候就可能會報這個錯誤：

解決方法：在my.cnf中修改max_heap_table_size = 256M tmp_table_size = 256M,重啟MySQL服務（my.cnf在mysql安裝路徑），如果還不夠用根據自己電腦自行修改。如果是線上伺服器，最好不要自行修改，還是跟運維多溝通溝通，避免出現問題。

show VARIABLES like '%TABLE_size%';

改完可以在這進行檢視：

6、同步資料

INSERT INTO t_user SELECT * FROM t_user_memory;

總結

到此這篇關於MySQL生成千萬測試資料以及遇到的問題的文章就介紹到這了,更多相關MySQL生成千萬測試資料內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！