Shulou(Shulou.com)05/31 Report--

This article shows you how to batch insert data in MySQL, the content is concise and easy to understand, absolutely can make your eyes shine, through the detailed introduction of this article I hope you can gain something.

1. Create test sheets

mysql> show create table house\G

*************************** 1. row ***************************

Table: house

Create Table: CREATE TABLE `house` (

`unitid` int(11) DEFAULT NULL,

`housename` varchar(20) COLLATE utf8_bin DEFAULT NULL,

`status` int(11) DEFAULT NULL,

KEY `unitid` (`unitid`),

KEY `index_name` (`housename`),

KEY `index_status_housename` (`status`,`housename`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

1 row in set (0.00 sec)

2. Create a stored procedure for bulk insert data

mysql> delimiter //

mysql> create procedure myproc()

-> begin

-> declare num int;

-> set num=1;

-> while num

insert into house values(num,concat('''1-',num,''''),mod(num,2));

-> set num=num+1;

-> end while;

-> end

-> //

Query OK, 0 rows affected (0.00 sec)

3. Batch insertion of data

mysql> call myproc

-> //

Query OK, 1 row affected (52.33 sec)

4. validation data

mysql> select status,count(*) from house group by status;

-> //

+--------+----------+

| status | count(*) |

+--------+----------+

| 0 | 249999 |

| 1 | 250000 |

+--------+----------+

2 rows in set (0.16 sec)

The above content is how to insert data in batches in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.