MySql 分配变量时出错

Question

我正在尝试编写一个脚本，该脚本将从大型父表创建较小的表。 (在这种情况下我别无选择。)然而，自从我不得不做任何严肃的 SQL 工作以来已经有一段时间了，我被可能是愚蠢的事情所困扰......这不会通过第一对夫妇线路不会爆炸。

DELIMITER $$
BEGIN
SET @I = 1; 
SET @CountCol = 'Item Number';
SET @StartPos = 0;
SET @EndPos = 24999;
SET @TotalCount = (SELECT Count(@CountCol) FROM `All_Info`);
SET @HowMany = CEILING(@TotalCount / 25000) + 1;

WHILE @I < @HowMany DO
    SET @query = CONCAT('CREATE TABLE All_Info_',@I,' AS (SELECT @n := @n + 1 `id`, * FROM All_Info LIMIT ',@StartPos,',',@EndPos,'),PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;)');
    PREPARE stmt from @query;
    EXECUTE stmt;

    SET @I = @I + 1;
    SET @StartPos = @EndPos;
    SET @EndPos = @EndPos + 25000;
END WHILE;
END$$

我得到的错误是:

[ERROR in query 1] 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 'SET @I = 1' at line 2

我感觉自己正在服用疯狂的药物。你知道我做错了什么吗？

(注意:使用 Sequel Pro 作为我的客户端，以防有某种相关性)

Answer 1

像往常一样，我的回答很明显:您不能在存储过程之外执行 WHILE 循环。哎呀。

将其放入过程中并调用它解决了我的问题。我还必须删除引擎和默认字符集。简化了一点。现在效果很好。

DROP PROCEDURE IF EXISTS TableSplitTemp;
DELIMITER ;;
create procedure TableSplitTemp ()
BEGIN
SET @I = 1;
SET @CountCol = 'Item Number';
SET @StartPos = 0;
SET @EndPos = 24999;
SET @TotalCount = (SELECT Count(@CountCol) FROM `All_Info`);
SET @HowMany = CEILING(@TotalCount / 25000) + 1;
WHILE @I < @HowMany DO
    SET @query = CONCAT('CREATE TABLE All_Info_',@I,' AS (SELECT * FROM All_Info LIMIT ',@StartPos,', ',@EndPos,');');
    PREPARE stmt from @query;
    EXECUTE stmt;

    SET @I = @I + 1;
    SET @StartPos = @EndPos;
    SET @EndPos = @EndPos + 25000;
END WHILE;
END;;
delimiter ;
/* call TableSplitTemp(); ta-daa it works! */