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)