MySQL stored procedure inserts data into a table in bulk

Business needs, inserting data into a table in bulk, using stored procedures to insert

First, to create a MySQL table, indicating that for Phone_number, three fields, id self-increment, number is the table to be inserted, is_used means that it is already used, the default value is 0, not used

CREATE TABLE' Phone_number ' (' ID ')int(8) not NULLauto_increment, ' Number`varchar( A) not NULL, ' is_used 'tinyint(1)DEFAULT '0'COMMENT'whether already used 1 already used, 0 unused',  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=Bayi DEFAULTCHARSET=UTF8;

In the new stored procedure, the stored procedure name is Batch_insert, passing in 2 parameters, the first one representing the starting number, and the number as an integer. The second parameter is how many records are inserted in a loop, and each insertion is incremented by 1 and then inserted.

DELIMITER $$ Use' db_xxxx ' $$DROP PROCEDURE IF EXISTS' Batch_insert ' $$CREATEDefiner=' Root ' @ '%`PROCEDURE' Batch_insert ' (inch' Start_number 'BIGINT,inch' Counts 'BIGINT)BEGIN     DECLAREP_numberBIGINT DEFAULTStart_number; DECLAREStop_numberBIGINT DEFAULTStart_number; SETStop_number=Start_number+counts;  whileP_number<Stop_number DoINSERT  intoPhone_number ( Number)VALUES(P_number); SETP_number=P_number+1; END  while ; COMMIT; END$ $DELIMITER;

If the lower band 2 parameters, indicating 13535561906 start inserting, insert 40 record after stop inserting

Call Batch_insert (13535561906,+);

Execution results

MySQL stored procedure inserts data into a table in bulk