How to use while to batch insert data in mysql storage-Mysql Tutorial-php.cn

How to use while to batch insert data in mysql storage

WBOY

Release： 2023-05-27 10:07:45

forward

1394 people have browsed it

##Batch submission

while statement writing method:

    while &#39;条件&#39; do
            循环体语句;
    end while;

Copy after login

Complete writing method

drop procedure if exists test_insert;
delimiter $$
create procedure test_insert(n int)
    begin
    declare v int default 0;
    set AUTOCOMMIT = 0;
    while v < n
        do
                insert into test(second_key, text, field_4,status, create_date)
                values ((v*10),
                concat(&#39;t&#39;,v),
                substring(md5(rand()), 1, 10),
                &#39;good&#39;,
                adddate(&#39;1970-01-01&#39;, rand(v) * 10000));
        set v = v + 1;
     end while;
      set AUTOCOMMIT = 1;
end$$
delimiter ;

Copy after login

View and delete stored procedures:

mysql> show procedure status like &#39;test_insert&#39;;
mysql> show create procedure test_insert\G;
mysql> drop procedure if exists test_insert;

Copy after login

Create table

CREATE TABLE test (
id INT NOT NULL AUTO_INCREMENT,
second_key INT,
text VARCHAR(20),
field_4 VARCHAR(20),
status VARCHAR(10),
create_date date,
PRIMARY KEY (id),
KEY idx_second_key (second_key)
) Engine=InnoDB CHARSET=utf8;

Copy after login

Insert 1 million pieces of data

mysql> call test_insert(1000000);
Query OK, 0 rows affected (31.86 sec)

Copy after login

Single submission

Complete writing method

drop procedure if exists test_insert;
delimiter $$
create procedure test_insert(n int)
    begin
    declare v int default 0;
    while v < n
        do
                insert into test(second_key, text, field_4,status, create_date)
                values ((v*10),
                concat(&#39;t&#39;,v),
                substring(md5(rand()), 1, 10),
                &#39;good&#39;,
                adddate(&#39;1970-01-01&#39;, rand(v) * 10000));
        set v = v + 1;
     end while;
end$$
delimiter ;

Copy after login

Insert 10,000 pieces of data

mysql> call test_insert(10000);
Query OK, 1 row affected (1 min 8.52 sec)

Copy after login

Open another window to view

mysql> select count(*) from test.test;
+----------+
| count(*) |
+----------+
|     1428 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from test.test;
+----------+
| count(*) |
+----------+
|     1598 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from test.test;
+----------+
| count(*) |
+----------+
|     1721 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from test.test;
+----------+
| count(*) |
+----------+
|     1983 |
+----------+
1 row in set (0.00 sec)

Copy after login

The above is the detailed content of How to use while to batch insert data in mysql storage. For more information, please follow other related articles on the PHP Chinese website!