Mysql存储过程批量写入
通过MySQL存储过程、函数来批量插入记录
mysql> use test;
Database changed
mysql> SET global log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP FUNCTION IF EXISTS randString;
Query OK, 0 rows affected, 1 warning (0.00 sec)
函数用来取得随机字符串
mysql> delimiter $
mysql> CREATE FUNCTION randString(n INT)
-> RETURNS VARCHAR(255)
-> BEGIN
-> DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
-> DECLARE return_str varchar(255) DEFAULT '';
-> DECLARE i INT DEFAULT 0;
-> WHILE i < n DO
-> SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
-> SET i = i +1;
-> END WHILE;
-> RETURN return_str;
-> END ;$
Query OK, 0 rows affected (0.00 sec)
mysql> select randString(10) \G$
*************************** 1. row ***************************
randString(10): PLdDmKtYgf
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> CREATE TABLE `test` (
-> `id` int(4) NOT NULL auto_increment,
-> `tname` varchar(255) not null ,
-> `tstat` tinyint(1) NOT NULL default 0,
-> `tkey` varchar(32) NOT NULL,
-> `tsort` decimal(10,2) NOT NULL default 0.00,
-> sid tinyint(1) not null default 0,
-> `created_ts` timestamp NOT NULL default current_timestamp,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8$
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE PROCEDURE sp_test(num int, sid int)
-> begin
-> declare j int;
-> declare k int;
-> set j = 0;
-> while j < num do
-> set k = ceil(1 + rand()*32);
-> INSERT INTO test (tname,tstat,tkey,tsort, sid) VALUES(randString(k), round(rand()*5),randString(32), round(1 + rand()*100, 2), sid);
-> set j = j + 1;
-> end while;
-> end;$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call sp_test(1,1);
Query OK, 1 row affected (0.03 sec)
mysql> select * from test \G;
*************************** 1. row ***************************
id: 1
tname: zM3NDE
tstat: 0
tkey: NiXHrYpYtgKMuZhhq79fCaQwY8ydiEdY
tsort: 93.11
sid: 1
created_ts: 2011-12-15 10:11:25
1 row in set (0.00 sec)
开启10个命令行同时写入10000次
mysql> call test.sp_test(10000,1);
Query OK, 1 row affected, 1231 warnings (4 min 30.15 sec)
mysql> call test.sp_test(10000,2);
Query OK, 1 row affected, 1229 warnings (4 min 30.48 sec)
mysql> call test.sp_test(10000,3);
Query OK, 1 row affected, 1235 warnings (4 min 31.29 sec)
mysql> call test.sp_test(10000,4);
Query OK, 1 row affected, 1279 warnings (4 min 32.31 sec)
mysql> call test.sp_test(10000,7);
Query OK, 1 row affected, 1235 warnings (4 min 31.78 sec)
mysql> call test.sp_test(10000,6);
Query OK, 1 row affected, 1241 warnings (4 min 34.24 sec)
mysql> call test.sp_test(10000,5);
Query OK, 1 row affected, 1288 warnings (4 min 35.53 sec)
mysql> call test.sp_test(10000,9);
Query OK, 1 row affected, 1209 warnings (4 min 33.77 sec)
mysql> call test.sp_test(10000,8);
Query OK, 1 row affected, 1190 warnings (4 min 35.63 sec)
mysql> call test.sp_test(10000,10);
Query OK, 1 row affected, 1222 warnings (4 min 33.98 sec)
10w次
mysql> call test.sp_test(100000,7);
Query OK, 1 row affected, 12346 warnings (47 min 4.96 sec)
mysql> call test.sp_test(100000,4);
Query OK, 1 row affected, 12489 warnings (47 min 30.93 sec)
mysql> call test.sp_test(100000,3);
Query OK, 1 row affected, 12409 warnings (47 min 39.41 sec)
mysql> call test.sp_test(100000,1);
Query OK, 1 row affected, 12530 warnings (47 min 44.09 sec)
mysql> call test.sp_test(100000,5);
Query OK, 1 row affected, 12457 warnings (47 min 48.10 sec)
mysql> call test.sp_test(100000,2);
Query OK, 1 row affected, 12434 warnings (47 min 52.56 sec)
mysql> call test.sp_test(100000,6);
Query OK, 1 row affected, 12397 warnings (47 min 50.28 sec)
mysql> call test.sp_test(100000,8);
Query OK, 1 row affected, 12409 warnings (47 min 51.98 sec)
mysql> call test.sp_test(100000,9);
Query OK, 1 row affected, 12415 warnings (47 min 51.99 sec)
mysql> call test.sp_test(100000,10);
Query OK, 1 row affected, 12390 warnings (47 min 58.01 sec)
mysql> call test.sp_test(100000,7);
Query OK, 1 row affected, 12507 warnings (44 min 55.30 sec)
mysql> call test.sp_test(100000,4);
Query OK, 1 row affected, 12490 warnings (45 min 25.42 sec)
mysql> call test.sp_test(100000,2);
Query OK, 1 row affected, 12477 warnings (45 min 37.67 sec)
mysql> call test.sp_test(100000,3);
Query OK, 1 row affected, 12243 warnings (45 min 39.01 sec)
mysql> call test.sp_test(100000,6);
Query OK, 1 row affected, 12570 warnings (45 min 36.74 sec)
mysql> call test.sp_test(100000,1);
Query OK, 1 row affected, 12199 warnings (45 min 45.62 sec)
mysql> call test.sp_test(100000,5);
Query OK, 1 row affected, 12487 warnings (45 min 41.51 sec)
mysql> call test.sp_test(100000,8);
Query OK, 1 row affected, 12457 warnings (45 min 39.39 sec)
mysql> call test.sp_test(100000,10);
Query OK, 1 row affected, 12640 warnings (45 min 51.71 sec)
mysql> call test.sp_test(100000,9);
Query OK, 1 row affected, 12430 warnings (45 min 58.25 sec)
mysql> call test.sp_test(100000,7);
Query OK, 1 row affected, 12351 warnings (1 hour 37.62 sec)
mysql> call test.sp_test(100000,3);
Query OK, 1 row affected, 12418 warnings (1 hour 1 min 22.94 sec)
mysql> call test.sp_test(100000,4);
Query OK, 1 row affected, 12312 warnings (1 hour 1 min 25.64 sec)
mysql> call test.sp_test(100000,9);
Query OK, 1 row affected, 12326 warnings (1 hour 1 min 34.22 sec)
mysql> call test.sp_test(100000,5);
Query OK, 1 row affected, 12349 warnings (1 hour 1 min 29.68 sec)
mysql> call test.sp_test(100000,8);
Query OK, 1 row affected, 12339 warnings (1 hour 1 min 37.84 sec)
mysql> call test.sp_test(100000,1);
Query OK, 1 row affected, 12405 warnings (1 hour 1 min 29.02 sec)
mysql> call test.sp_test(100000,2);
Query OK, 1 row affected, 12222 warnings (1 hour 1 min 30.12 sec)
mysql> call test.sp_test(100000,6);
Query OK, 1 row affected, 12534 warnings (1 hour 1 min 46.50 sec)
posted on 2011-12-15 17:58 BobbyPeng 阅读(1744) 评论(0) 编辑 收藏 举报