This is the sixth day of my participation in the August Text Challenge.More challenges in August

3. Batch insert data scripts

Build table

CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `dname` varchar(40) NOT NULL DEFAULT ' ',
  `loc` varchar(40) NOT NULL DEFAULT ' '.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `emp` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `empno` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT 'number',
  `enmae` varchar(20) NOT NULL DEFAULT ' ',
  `job` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'work',
  `mar` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT 'Leader Number',
  `hiredate` date NOT NULL COMMENT 'Entry Time',
  `sal` decimal(7.2) NOT NULL COMMENT 'salaries',
  `comm` decimal(7.2) NOT NULL COMMENT 'bonus',
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT 'Department No.'.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

If bin_log is enabled, the MySQL database is restarted

# see bin_logSHOW VARIABLES LIKE"%log_bin_trust_function_creators%" # enable bin_logSET GLOBAL log_bin_trust_function_creators = 1;
Copy the code

Create functions that make sure each piece of data is different

A function that randomly generates strings

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
	DECLARE char_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT ' ';
	DECLARE i INT DEFAULT 0;
	WHILE i < n DO
	SET return_str = CONCAT(return_str,SUBSTRING(char_str,FLOOR(1+RAND()*52),1));
	SET i = i+1;
	END WHILE;
	RETURN return_str;
END $$
Copy the code

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 $$
Copy the code

Creating a stored procedure

A stored procedure for inserting data into an EMP table

DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0; # turn autocommit off REPEATSET i = i + 1;
	INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES((START+i),rand_string(6),'SALESMAN'.0001,CURDATE(),2000.400,rand_num());
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END $$
Copy the code

Stored procedure to insert data into the DEPT table

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

Calling a stored procedure

Insert into dept table10The dataCALL insert_dept(100.10); Insert into emP table50W dataCALL insert_emp(100001.500000);
Copy the code

4. Use Show Profile for SQL analysis

Profiling steps

# Check the profiling statusSHOW VARIABLES LIKE"%profiling%" # enable profilingSET profiling = on; # check executionSQLThe record ofSHOWPROFILES; # diagnosisSQLThere are more parameters that can be added or removed, but the usual parameters are used here.SHOW PROFILE cpu,block io forQuery Indicates the ID of the record queried in the previous stepCopy the code

Note the following fields