A practical tutorial on how to quickly insert tens of millions of records into MySQL

1. Create a database

2. Create a table

1. Create the dept table

CREATE TABLE `dept` (
 `id` int(11) NOT NULL,
 `deptno` mediumint(9) DEFAULT NULL,
 `dname` varchar(20) DEFAULT NULL,
 `loc` varchar(13) DEFAULT NULL,
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Create the emp table

CREATE TABLE `emp` (
 `id` int(11) NOT NULL,
 `empon` mediumint(9) DEFAULT NULL COMMENT 'Number',
 `ename` varchar(20) DEFAULT NULL,
 `job` varchar(9) DEFAULT NULL,
 `mgr` mediumint(9) DEFAULT NULL COMMENT 'Superior number',
 `hirdate` datetime DEFAULT NULL COMMENT 'Job start time',
 `sal` decimal(7,2) DEFAULT NULL COMMENT 'Salary',
 `comm` decimal(7,2) DEFAULT NULL COMMENT 'Dividend',
 `deptno` mediumint(9) DEFAULT NULL COMMENT 'Department number',
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. Set parameters

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

Off by default. Need to be set to 1. Because the mediumint field creation function is set in the table, an error may occur

SET GLOBAL log_bin_trust_function_creators=1;

4. Create a function

1. Generate a random string

DELIMITER $
CREATE FUNCTION RAND_STR(n INT) RETURNS VARCHAR(255)
BEGIN
 DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
	 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
	 WHILE i< n DO 
	 SET return_str = COUCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
	 SET i= i+1;
 END WHILE;
	 RETURN return_str;
END $

2. Randomly generate department numbers

DELIMITER $
CREATE FUNCTION RAND_num() RETURNS INT(5)
BEGIN
 DECLARE i INT DEFAULT 0; 
	 SET i= FLOOR(100+RAND()*10);
	 RETURN i;
END $

5. Create a stored procedure

1. emp table stored procedure

DELIMITER $
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
  DECLARE i INT DEFAULT 0;
		SET autocommit = 0;
		REPEAT #Repeat SET i = i + 1;
		INSERT INTO emp(empon,ename,job,mgr,hiredate,sal,comm,depton) VALUES ((START+i),RAND_STR(6),'SALESMAN',0001,CURDATE(),2000,400,RAND_num());
	 UNTIL i = max_num
 END REPEAT;
	 COMMIT;
END $

2.dept table stored procedure

DELIMITER $
CREATE PROCEDURE insert_dept(IN START INT(10), IN max_num INT(10))
BEGIN
  DECLARE i INT DEFAULT 0;
		SET autocommit = 0;
		REPEAT #Repeat SET i = i + 1;
		INSERT INTO dept(deptno,dname,loc) VALUES ((START+i),RAND_STR(10),RAND_STR(8));
	 UNTIL i = max_num
 END REPEAT;
	 COMMIT;
END $

6. Execution

1. Execute the ten rules first

This error is a small pit. Did you find it? I left it before. Check it according to the prompts.

Execution successful!

2. View the data

The highlight is here! Let's take a gamble and see if it will fail.

3. Perform one million inserts

CALL insert_dept(10001,1000000);

400s to run a million data, 2500 per second. My configuration is too bad. The previous test was 10,000 per second, and the best was 500,000 per run.

Oak Sleeping

Summarize

This is the end of this article about how to quickly insert tens of millions of data in Mysql. For more information about how to insert tens of millions of data in Mysql, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

Examples of 4 methods for inserting large amounts of data in MySQL
How to quickly insert millions of test data in MySQL
Implementation code for inserting data from one table into another table in MySql
Insert multiple records with one mysql statement
If the data does not exist in mysql, insert new data, otherwise update the implementation method
How to solve the problem of Chinese garbled characters when inserting table data into MySQL
Adjustment record of mysql database insertion speed and reading speed
MYSQL batch insert data implementation code
MySQL tips: Increase the speed of inserting data (adding records)
MySQL inserts multiple data into a table at a time

<<: Detailed explanation of CSS3 Flex elastic layout example code

>>: Detailed explanation of the process of building Prometheus+Grafana based on docker