使用mysql 5.7,执行此过程时:
DELIMITER $$
CREATE PROCEDURE tres()
BEGIN
DECLARE maxid INT;
DECLARE x INT;
SET maxid = 655;
SET x=1;
WHILE x<= maxid DO
INSERT INTO `forum_topicresponder` (topic_id, username, reply_id) SELECT p.topic_id, p.creator_name, p.id FROM forum_post AS p WHERE p.topic_id=x;
SET x = x + 1;
END WHILE;
END$$
我得到
mysql>调用tres();
查询正常,0行受影响(3.49秒)
但是,当我为循环范围内的特定值执行插入时:
INSERT INTO `forum_topicresponder` (topic_id, username, reply_id) SELECT p.topic_id, p.creator_name, p.id FROM forum_post AS p WHERE p.topic_id=42;
我看到插入的结果没有问题。
表架构:
CREATE TABLE `forum_topicresponder` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`topic_id` int(10) NOT NULL DEFAULT '0',
`username` varchar(300) CHARACTER SET utf8mb4 NOT NULL DEFAULT 'na',
`reply_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=94 DEFAULT CHARSET=latin1;
和
CREATE TABLE `forum_post` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`body` varchar(16000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`topic_id` int(11) NOT NULL DEFAULT '0',
`creator_id` int(11) NOT NULL DEFAULT '0',
`creator_name` varchar(30) NOT NULL DEFAULT 'NA',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6264595 DEFAULT CHARSET=utf8;
那么这里有什么问题,我该怎么解决呢?