批量提交
while 语句写法：
|
1
2
3
|
while '条件' do 循环体语句; end while; |
完整写法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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('t',v), substring(md5(rand()), 1, 10), 'good', adddate('1970-01-01', rand(v) * 10000)); set v = v + 1; end while; set AUTOCOMMIT = 1;end$$delimiter ; |
查看、删除存储过程：
|
1
2
3
|
mysql> show procedure status like 'test_insert';mysql> show create procedure test_insert\G;mysql> drop procedure if exists test_insert; |
创建表
|
1
2
3
4
5
6
7
8
9
10
|
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; |
插入100万条数据
|
1
2
|
mysql> call test_insert(1000000);Query OK, 0 rows affected (31.86 sec) |
单个提交
完整写法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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('t',v), substring(md5(rand()), 1, 10), 'good', adddate('1970-01-01', rand(v) * 10000)); set v = v + 1; end while;end$$delimiter ; |
插入1万条数据
|
1
2
|
mysql> call test_insert(10000);Query OK, 1 row affected (1 min 8.52 sec) |
打开另一个窗口查看
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
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) |
结论
批量提交100万条数据用了30秒，单个提交1万条数据用了1分钟，对比发现，批量提交的效率远大于单个提交的效率
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。
原文链接：https://liudada.blog.csdn.net/article/details/125104992
