mysql - 调试mysql sproc， 'NULL'附近语法正确

Question

我编写了一个存储过程，它将在向表中插入新记录时触发。

调用时会返回错误 1064 并指出:

'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1'

我遇到的问题是我无法调试内部查询来找出“NULL”在哪里。或者为什么它会出现。

如果有人能建议我如何调试这个，或者发现我的代码出了什么问题，我将不胜感激。注意:我不相信我可以在存储过程之外测试动态查询。

DROP PROCEDURE IF EXISTS SetUniqueCodeCustomLength;

DELIMITER $$
CREATE PROCEDURE SetUniqueCodeCustomLength (
    IN TableName VARCHAR(255), 
    IN FieldName VARCHAR(255), 
    IN PKName VARCHAR(250), 
    IN PKID INT, 
    IN CodeLength INT)
BEGIN

    SET @pass = '';
    SET @loop = 0;
    SET @matchCount = 1;
    SET @sSQL = '';

    WHILE @matchCount > 0 DO

        SET @loop = 0;
        WHILE @loop < CodeLength DO
            -- random number from x-uy
            SET @chr = SUBSTRING('abcdefghjkpqrstuvwxyz23456789', CAST( ROUND(RAND()*(29-1)+1) AS INT), 1);         
            SET @pass = @pass + @chr;
            SET @loop = @loop + 1;
        END WHILE;

        -- Check for unique-ness.
        SET @sSQL = CONCAT('SELECT COUNT(*) INTO @matchCount FROM ' + @TableName + ' WHERE ' + @FieldName + ' = ''' + @pass + '''');
        PREPARE stmt FROM @sSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END WHILE;


    SELECT @pass;
    -- perform the update
    SET @sSQL = CONCAT('UPDATE ' + @TableName + ' SET ' + @FieldName + ' = ''' + @pass + ''' WHERE ' + @PKName + ' = ' + @PKID);
    PREPARE stmt FROM @sSQL;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

调用方式:

CALL SetUniqueCodeCustomLength('Posts', 'PostCode', 'PostID', 7, 6);

Answer 1

一些观察:

• 存储过程的参数 ( 13.1.16 CREATE PROCEDURE and CREATE FUNCTION Syntax ) 与用户定义变量 ( 9.4 User-Defined Variables ) 不同，例如:IN TableName != @TableName。
• 检查CONCAT()语法。
• 小心准备好的语句(安全性)和可能的 SQL 注入(inject)。

mysql> DROP PROCEDURE IF EXISTS `SetUniqueCodeCustomLength`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DELIMITER $$

mysql> CREATE PROCEDURE `SetUniqueCodeCustomLength` (
    ->   IN `TableName` VARCHAR(255)
    -> )
    -> BEGIN
    ->   SET @`sSQL` := CONCAT('SELECT ', @`TableName`); -- NULL
    ->   SELECT @`sSQL`;
    -> 
    ->   SET @`sSQL` := CONCAT('SELECT ', `TableName`); -- SELECT Posts
    ->   SELECT @`sSQL`;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `SetUniqueCodeCustomLength`('Posts');
+---------+
| @`sSQL` |
+---------+
| NULL    |
+---------+
1 row in set (0.00 sec)

+--------------+
| @`sSQL`      |
+--------------+
| SELECT Posts |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)