各位早上好,我编写了如下语句
create procedure p6()
begin
declare i int;
set i=2;
while i<68 do
insert into jos_content_2and3categoryid(2categoryid,3categoryid,content_id) value(0,0,i);
set i=i+1;
end while;
end;
call p6();那个while想循环60多次的,但是一次都没有循环,也没有语法错误,不知道为何?
create procedure p6()
begin
declare i int;
set i=2;
while i<68 do
insert into jos_content_2and3categoryid(2categoryid,3categoryid,content_id) value(0,0,i);
set i=i+1;
end while;
end;
call p6();那个while想循环60多次的,但是一次都没有循环,也没有语法错误,不知道为何?
delimiter //
create procedure p6()
begin
declare i int;
set i=2;
while i<68 do
insert into jos_content_2and3categoryid(2categoryid,3categoryid,content_id) value(0,0,i);
set i=i+1;
end while;
end;
//
call p6(); //
这样子试试
show create table jos_content_2and3categoryid;
show create procedure p6;
BEGIN
DECLARE i INT;
SET i=2;
WHILE i<68 DO
INSERT INTO jos_content_2and3categoryid(2categoryid,3categoryid,content_id) VALUES(0,0,i);
SET i=i+1;
END WHILE;
END$$DELIMITER ;
测试没有问题,你的表是否有主键、外键、约束等等?
CREATE TABLE `jos_content_2and3categoryid` (
`2and3categoryid` int(11) NOT NULL AUTO_INCREMENT,
`2categoryid` int(11) DEFAULT NULL,
`3categoryid` int(11) DEFAULT NULL,
`content_id` int(11) NOT NULL,
PRIMARY KEY (`2and3categoryid`)
) ENGINE=MyISAM AUTO_INCREMENT=390 DEFAULT CHARSET=latin1CREATE DEFINER=`root`@`localhost` PROCEDURE `p6`()
begin
declare i int;
set i=2;
while i<68 do
insert into jos_content_2and3categoryid(2categoryid,3categoryid,content_id) value(0,0,i);
set i=i+1;
end while;
end
你能做相同的测试吗?贴出来看看。
mysql> CREATE TABLE `jos_content_2and3categoryid` (
-> `2and3categoryid` int(11) NOT NULL AUTO_INCREMENT,
-> `2categoryid` int(11) DEFAULT NULL,
-> `3categoryid` int(11) DEFAULT NULL,
-> `content_id` int(11) NOT NULL,
-> PRIMARY KEY (`2and3categoryid`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.09 sec)mysql> delimiter //
mysql> CREATE PROCEDURE `p6`()
-> begin
-> declare i int;
-> set i=2;
-> while i<68 do
-> insert into jos_content_2and3categoryid(2categoryid,3categoryid,content_i
d) value(0,0,i);
-> set i=i+1;
-> end while;
-> end
-> //
Query OK, 0 rows affected (0.42 sec)mysql> delimiter ;
mysql> call p6();
Query OK, 1 row affected (0.06 sec)mysql> select count(*) from jos_content_2and3categoryid;
+----------+
| count(*) |
+----------+
| 66 |
+----------+
1 row in set (0.05 sec)mysql>
`2and3categoryid` INT(11) NOT NULL AUTO_INCREMENT,
`2categoryid` INT(11) DEFAULT NULL,
`3categoryid` INT(11) DEFAULT NULL,
`content_id` INT(11) NOT NULL,
PRIMARY KEY (`2and3categoryid`)
) ENGINE=MYISAM AUTO_INCREMENT=390 DEFAULT CHARSET=latin1;
CALL p6()USE `testa`$$DROP PROCEDURE IF EXISTS `p6`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `p6`()
BEGIN
DECLARE i INT;
SET i=2;
WHILE i<68 DO
INSERT INTO jos_content_2and3categoryid(2categoryid,3categoryid,content_id) VALUES(0,0,i);
SET i=i+1;
END WHILE;
END$$DELIMITER ;mysql> SELECT * FROM `jos_content_2and3categoryid`;
+-----------------+-------------+-------------+------------+
| 2and3categoryid | 2categoryid | 3categoryid | content_id |
+-----------------+-------------+-------------+------------+
| 390 | 0 | 0 | 2 |
| 391 | 0 | 0 | 3 |
| 392 | 0 | 0 | 4 |
| 393 | 0 | 0 | 5 |
| 394 | 0 | 0 | 6 |
| 395 | 0 | 0 | 7 |
| 396 | 0 | 0 | 8 |
| 397 | 0 | 0 | 9 |
| 398 | 0 | 0 | 10 |
| 399 | 0 | 0 | 11 |
| 400 | 0 | 0 | 12 |
| 401 | 0 | 0 | 13 |
| 402 | 0 | 0 | 14 |
| 403 | 0 | 0 | 15 |
| 404 | 0 | 0 | 16 |
| 405 | 0 | 0 | 17 |
| 406 | 0 | 0 | 18 |
| 407 | 0 | 0 | 19 |
| 408 | 0 | 0 | 20 |
| 409 | 0 | 0 | 21 |
| 410 | 0 | 0 | 22 |
| 411 | 0 | 0 | 23 |
| 412 | 0 | 0 | 24 |
| 413 | 0 | 0 | 25 |
| 414 | 0 | 0 | 26 |
| 415 | 0 | 0 | 27 |
| 416 | 0 | 0 | 28 |
| 417 | 0 | 0 | 29 |
| 418 | 0 | 0 | 30 |
| 419 | 0 | 0 | 31 |
| 420 | 0 | 0 | 32 |
| 421 | 0 | 0 | 33 |
| 422 | 0 | 0 | 34 |
| 423 | 0 | 0 | 35 |
| 424 | 0 | 0 | 36 |
| 425 | 0 | 0 | 37 |
| 426 | 0 | 0 | 38 |
| 427 | 0 | 0 | 39 |
| 428 | 0 | 0 | 40 |
| 429 | 0 | 0 | 41 |
| 430 | 0 | 0 | 42 |
| 431 | 0 | 0 | 43 |
| 432 | 0 | 0 | 44 |
| 433 | 0 | 0 | 45 |
| 434 | 0 | 0 | 46 |
| 435 | 0 | 0 | 47 |
| 436 | 0 | 0 | 48 |
| 437 | 0 | 0 | 49 |
| 438 | 0 | 0 | 50 |
| 439 | 0 | 0 | 51 |
| 440 | 0 | 0 | 52 |
| 441 | 0 | 0 | 53 |
| 442 | 0 | 0 | 54 |
| 443 | 0 | 0 | 55 |
| 444 | 0 | 0 | 56 |
| 445 | 0 | 0 | 57 |
| 446 | 0 | 0 | 58 |
| 447 | 0 | 0 | 59 |
| 448 | 0 | 0 | 60 |
| 449 | 0 | 0 | 61 |
| 450 | 0 | 0 | 62 |
| 451 | 0 | 0 | 63 |
| 452 | 0 | 0 | 64 |
| 453 | 0 | 0 | 65 |
| 454 | 0 | 0 | 66 |
| 455 | 0 | 0 | 67 |
+-----------------+-------------+-------------+------------+
66 rows in set (0.00 sec)mysql>
DROP PROCEDURE IF EXISTS p6;
DELIMITER //
CREATE PROCEDURE p6()
BEGIN
DECLARE i INT DEFAULT 2;
WHILE i < 68 DO
SET i=i+1;
INSERT INTO MyTable(id) values(i);
end while;
end //
DELIMITER ;call p6()
select * from mytable