时间戳

首页 > 解决方案 > MySQL 存储过程失败并出现错误 1064 (42000)

mysql - MySQL 存储过程失败并出现错误 1064 (42000)

问题描述

我编写了一个 MySQL 存储过程，它将向现有表添加一个新分区：

DELIMITER //

DROP PROCEDURE IF EXISTS cr_par //

CREATE PROCEDURE cr_par (
    IN p_table VARCHAR(256),
    IN p_date DATE
) BEGIN

    DECLARE stmt      VARCHAR(1024);
    DECLARE ddl       VARCHAR(512);
    DECLARE par_name  VARCHAR(20) DEFAULT '';
    DECLARE par_no    INT DEFAULT 0;
    DECLARE lt_value  INT DEFAULT 0;
    
    SET par_no   = TO_DAYS(p_date) + 1;
    SET par_name = CONCAT('p', par_no);
    SET lt_value = par_no + 1;
    
    SET ddl = CONCAT('ALTER TABLE ', p_table, ' ADD PARTITION (PARTITION ', par_name, ' VALUES LESS THAN (', lt_value, '))');

    PREPARE stmt FROM @ddl;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SELECT ddl;

END //

DELIMITER ;

当我运行存储过程时，出现此错误：

mysql> CALL cr_par('test', '2021-09-13');
ERROR 1064 (42000): 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 'NULL' at line 1

如果我注释掉 PREPARE、EXECUTE 和 DEALLOCATE 语句并重新运行存储过程，我会得到这个，这是一个有效的 DDL 语句：

mysql> CALL cr_par('test', '2021-09-13');
+------------------------------------------------------------------------------+
| ddl                                                                          |
+------------------------------------------------------------------------------+
| ALTER TABLE test ADD PARTITION (PARTITION p738412 VALUES LESS THAN (738413)) |
+------------------------------------------------------------------------------+
1 row in set (0.01 sec)

我也尝试了这些变体并且都返回相同的错误：

    SET ddl = 'ALTER TABLE test ADD PARTITION (PARTITION p738412 VALUES LESS THAN (738413));';

    PREPARE stmt FROM @ddl;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

和这个...

    SET ddl = CONCAT('ALTER TABLE ? ADD PARTITION (PARTITION ? VALUES LESS THAN (?))');
    
    PREPARE stmt FROM @ddl;
    EXECUTE stmt USING @p_table, @par_no, @lt_value;
    DEALLOCATE PREPARE stmt;

我正在使用这个版本的 MySQL：

Server version: 8.0.25-15 Percona Server (GPL), Release 15, Revision a558ec2

有没有人有任何想法可能导致这种情况，我一定错过了一些简单的东西？

标签： mysqlstored-proceduresddldatabase-partitioning

解决方案

SET ddl = CONCAT('ALTER TABLE ', p_table, ' ADD PARTITION (PARTITION ', par_name, ' VALUES LESS THAN (', lt_value, '))');

PREPARE stmt FROM @ddl;

请注意ddl和@ddl是两个不同的变量。

您使用局部变量 DECLARE 语句声明的变量在一个存储例程的主体内具有一个范围。他们从来没有用@印记拼写。

带有sigil的用户定义变量@具有MySQL 会话的范围。您不需要声明这些类型的变量。只需将变量设置为一个值就会隐式创建变量。

您不能SET ddl = ...并且期望从@ddl变量中读取该字符串。反之亦然。

该PREPARE语句仅支持从用户定义的变量准备 SQL。这意味着您必须将@ddl变量设置为您的 SQL 语句：

SET @ddl = CONCAT('ALTER TABLE ', p_table, ' ADD PARTITION (PARTITION ', par_name, ' VALUES LESS THAN (', lt_value, '))');

然后你根本不需要DECLARE ddl，因为那个变量没有用。

推荐阅读