直接INSERT tablea( field1) select .. from tablea;这样就会指数级的增长。
示例: DELIMITER $$CREATE /*[DEFINER = { user | CURRENT_USER }]*/ PROCEDURE `zz`.`rr`() /*LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'*/ BEGIN declare i int; while i<=1000 do begin insert into tt select * from newtt; set i=@i+1; end; end while; END$$DELIMITER ;
在phpmyadmin 2.0.63执行后,报这个错, 我MYSQL是5.0.21 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE PROCEDURE `zz`.`rr`() BEGIN declare i i' at line 1
不会吧,我在MYSQL5.1+SQLYOG6.5下测试成功的,没有语法错误。 完整代码贴出来看看
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -ADatabase changed mysql> create table id (id int not null auto_increment primary key); Query OK, 0 rows affected (0.01 sec)mysql> insert into id values(NULL); Query OK, 1 row affected (0.05 sec)mysql> insert into id select NULL from id; Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id; Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id; Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id; Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id; Query OK, 16 rows affected (0.00 sec) Records: 16 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id; Query OK, 32 rows affected (0.00 sec) Records: 32 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id; Query OK, 64 rows affected (0.00 sec) Records: 64 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id; Query OK, 128 rows affected (0.01 sec) Records: 128 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id; Query OK, 256 rows affected (0.01 sec) Records: 256 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id; Query OK, 512 rows affected (0.00 sec) Records: 512 Duplicates: 0 Warnings: 0mysql> \q Bye
to WWWWA 我就是的你贴出来的那段在phpmyadmin里执行的 啊
你下载一个SQLYOG,试试上述代码
To: yueliangdao0608 我现有数据库主键ID不是自增长的,也不可以将原有的 我用的是先max(id)+1的方式 再insert请问你有什么高招; 在mysql下好像不能同时运行两个::的语句吧?
可是我为啥在phpmyadmin 里写出来的循环语句不能用啊?
直接INSERT tablea( field1) select .. from tablea;这样就会指数级的增长。
DELIMITER $$CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `zz`.`rr`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
declare i int;
while i<=1000 do
begin
insert into tt select * from newtt;
set i=@i+1;
end;
end while;
END$$DELIMITER ;
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE
PROCEDURE `zz`.`rr`()
BEGIN
declare i i' at line 1
完整代码贴出来看看
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> create table id (id int not null auto_increment primary key);
Query OK, 0 rows affected (0.01 sec)mysql> insert into id values(NULL);
Query OK, 1 row affected (0.05 sec)mysql> insert into id select NULL from id;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id;
Query OK, 32 rows affected (0.00 sec)
Records: 32 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id;
Query OK, 64 rows affected (0.00 sec)
Records: 64 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id;
Query OK, 128 rows affected (0.01 sec)
Records: 128 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id;
Query OK, 256 rows affected (0.01 sec)
Records: 256 Duplicates: 0 Warnings: 0mysql> insert into id select NULL from id;
Query OK, 512 rows affected (0.00 sec)
Records: 512 Duplicates: 0 Warnings: 0mysql> \q
Bye
我就是的你贴出来的那段在phpmyadmin里执行的 啊
我现有数据库主键ID不是自增长的,也不可以将原有的
我用的是先max(id)+1的方式
再insert请问你有什么高招; 在mysql下好像不能同时运行两个::的语句吧?
什么意思?
我想一次运行两个SQL语句 ,就像存储过程那种。可是用命令行一次只能执行一个语句。begin end脚本语法又不起作用;如果还是没理解我的意思,就来个非自动增长,又可以批量插入的方法。谢了 哥们儿
insert into tt select * from t1;
insert into tt select * from t1;用分号隔离
是在同行>MYSQL里,可以?
declare i int;
while i <=1000 do
begin
insert into tt select * from newtt;
set i=@i+1;
end;
end while;
当然可以,@var是SESSION级别的!只要你不退出,就不会清除!
一般情况下,如果只是插入数据的话,建议楼主不要试验了,没有意义。
在存储过程里用 insert into .... select 语句是最快的。(MyIsam表,不要索引)
但是,如果硬盘够大,一天也执行不完。呵呵,接分。