This is the 9th day of my participation in the Gwen Challenge

Sometimes we need to batch insert tens of thousands or even hundreds of thousands of data into the table, at this time it is always impossible to manually insert a row, which is estimated to be tiring, you can use MySQL functions and storage to achieve this requirement.

1. Establish the table required for testing

CREATE TABLE `dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
ceo INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`empno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Copy the code

2. Parameter Settings

MySQL has a parameter log_bin_trust_function_Creators, which is available when binary logging is enabled. This variable controls whether the creators of the storage function are trusted not to create the storage function that writes the events that cause binary logging insecurity. The default value is 0, indicating that storage functions are not allowed to be created or modified.

If you create a storage function with a variable value of 0, an error is reported

ERROR 1418 (HY000): This function has none of DETERMINISTIC.NO SQL.or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
Copy the code

So why does MySQL have this limitation? An important function of binary logs is for master/slave replication, and the storage function may cause data inconsistency between master and slave. So, when binary logging is enabled, the log_bin_trust_function_Creators parameter takes effect, limiting the creators, modifications, and calls of the storage function.

So for testing, we need to turn this parameter on, otherwise we can’t play. Note: If you run the command to enable it, it will become invalid after restart. To make it permanent, you need to modify my.cnf.

3. Write random functions

Two random functions are enough, one to produce a random number and one to produce a random string.

3.1 Generating random Strings
-- Define the delimiter as $$, otherwise encountered; Will be over
DELIMITER $$
The function name is rand_string, the input parameter is n, the type is int, the return value is vARCHar, the length is 255
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
-- Define a variable chars_str with a default value
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT ' ';
DECLARE i INT DEFAULT 0;
Generate a random string of length n and store it in return_str
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 $$
Copy the code
3.2 Generating random numbers
-- Randomly generate from_num to to_num
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 $$
Copy the code

To delete a function, use the following command

drop function function_name;
Copy the code

Create a stored procedure

4.1 Stored procedure for inserting data into an EMP table
DELIMITER $$
CREATE PROCEDURE insert_emp(START INT, max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
-- Turn autocommit off
set autocommit = 0;
REPEAT
SET i = i +1;
INSERT INTO emp(empno, NAME, age, deptid) VALUES((START + i), rand_string(6), rand_num(30.50), rand_num(1.10000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$

- delete
DELIMITER ;
drop PROCEDURE insert_emp;
Copy the code
4.2 Stored procedure for inserting data into dept table
DELIMITER $$
CREATE PROCEDURE insert_dept(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept(deptname, address, ceo) VALUES(rand_string(8), rand_string(10), rand_num(1.500000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$

- delete
DELIMITER ;
drop PROCEDURE insert_dept;
Copy the code

5. Call the stored procedure

5.1 Adding Data to the Department Table
Execute the stored procedure to add 1W data to the DEPT table
DELIMITER ;
CALL insert_dept(10000);
Copy the code
5.2 Adding data to the employee table
-- Execute stored procedure to add 200W data to EMP table
DELIMITER ;
CALL insert_emp(100000.2000000);
Copy the code

6. Drop all indexes on a table

6.1 Creating a Stored Procedure for Deleting an Index
DELIMITER $$
CREATE PROCEDURE proc_drop_index(dbname VARCHAR(200), tablename VARCHAR(200))
BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE ct INT DEFAULT 0;
	DECLARE _index VARCHAR(200) DEFAULT ' ';
	DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema = dbname AND table_name = tablename AND seq_in_index = 1 AND index_name <> 'PRIMARY';
	DECLARE CONTINUE HANDLER FOR NOT FOUND set done = 2;
	OPEN _cur;
	FETCH _cur INTO _index;
	WHILE _index <> ' ' DO
		SET @str = CONCAT("drop index ", _index, " on ", tablename);
		REPEAT sql_str FROM @str;
		EXECUTE sql_str;
		DEALLOCATE PREPARE sql_str;
		SET _index = ' ';
		FETCH _cur INTO _index;
	END WHILE;
CLOSE _cur;
END $$
Copy the code
6.2 Executing stored Procedures
CALL proc_drop_index("dbname", "tablename");
Copy the code