56,678
社区成员
发帖
与我相关
我的任务
分享
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i<1000
DO
INSERT INTO author(name, email) VALUES('name', CONCAT('email@',i));
SET i=i+1;
END WHILE ;
END
CREATE TABLE IF NOT EXISTS `author` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11512 DEFAULT CHARSET=utf8;
mysql> use world;
Database changed
mysql> CREATE TABLE IF NOT EXISTS `author` ( `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL) ENGINE=InnoDB AUTO_INCREMENT=11512 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.99 sec)
mysql> delimiter $$
mysql> CREATE PROCEDURE `author_copy_self`(IN `nThousnd` INT)
-> NO SQL
-> BEGIN
->
-> DECLARE i INT DEFAULT 0;
-> SET i = 0;
-> WHILE i < nThousnd
-> DO
-> INSERT INTO
-> author(name,email)
-> SELECT name,email
-> FROM author
-> WHERE id >= 0 LIMIT 1000;
-> SET i = i + 1;
-> END WHILE ;
->
-> END$$
Query OK, 0 rows affected (0.12 sec)
mysql> show variables like 'autocommit%'$$
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.09 sec)
mysql> call author_copy_self(1000)$$
Query OK, 0 rows affected, 2 warnings (0.26 sec)
mysql> delimiter ;
mysql> select * from author;
Empty set (0.01 sec)
CREATE PROCEDURE `author_copy_self`(IN `nThousnd` INT)
NO SQL
BEGIN
DECLARE i INT DEFAULT 0;
SET i = 0;
WHILE i < nThousnd
DO
INSERT INTO
author(name,email)
SELECT name,email
FROM author
WHERE id >= 0 LIMIT 1000;
SET i = i + 1;
END WHILE ;
END