在存储过程中执行 CONCAT 语句不起作用

Question

我有两个表，一个基表包含有关值“tconst”（也是主键）的各种信息，另一个表在“titleId”名称下从“nconst”链接到多个“tconst”值'.---基表'titlebasics'tconst标题类型...tt0000009电影...tt0000147电影............---额外信息表'knownfortitles'idnconst标题订单标题ID1nm00000011tt00

P粉798343415 · Answer

感谢 P. Salmon，我了解到问题是通过准备好的语句运行 IF ... THEN 语句。这不可能。经过一番修改后，我想出了以下程序，它的工作原理与我想要的完全一样。我希望我可以帮助一些与我遇到类似问题的人。

DELIMITER $$
USE ``$$
CREATE DEFINER=`Setupinfolab`@`%` PROCEDURE `CheckValueExistsInBaseTable`(
    IN checkedTable VARCHAR(100),
    IN referencedBaseTable VARCHAR(100),
    IN checkedCol VARCHAR(100),
    IN referencedCol VARCHAR(100)
    )
BEGIN
    SET @checkedTable = checkedTable;
    SET @referencedBaseTable = referencedBaseTable;
    SET @checkedCol = checkedCol;
    SET @referencedCol = referencedCol;
    SET @new_column_name  = CONCAT('is_in_', referencedBaseTable);
    
    -- Check if there are indeed values in checkedCol that are not present in referencedCol
    SET @query1 = CONCAT('
        SELECT CASE WHEN EXISTS(
            SELECT 1 
            FROM ',@checkedTable,'
            WHERE ',@checkedCol,' NOT IN (SELECT ',@referencedCol,' FROM ',@referencedBaseTable,')) 
            THEN 1 ELSE 0 END 
            INTO @proceed');
        
    -- Adding the boolean column
    SET @query2 = CONCAT('
        ALTER TABLE ',@checkedTable,' ADD ',@new_column_name,' BOOL;');
    
    -- Inserting boolean values in new column according to presence in referencedCol
    SET @query3 = CONCAT('
        UPDATE ',@checkedTable,' SET ',@new_column_name,' = (
            CASE WHEN EXISTS(
                SELECT * 
                FROM ',@referencedBaseTable,' 
                WHERE ',@referencedBaseTable,'.',@referencedCol,' = ',@checkedTable,'.',@checkedCol,')
            THEN true ELSE false END
            ) WHERE id<>0;');
    
    PREPARE stmt1 FROM @query1;
    EXECUTE stmt1;
    
    IF @proceed = 1
    THEN 
        PREPARE stmt2 FROM @query2;
        EXECUTE stmt2;
        PREPARE stmt3 FROM @query3;
        EXECUTE stmt3;
        SELECT CONCAT(@new_column_name,' column added to table ', @checkedTable,'.') 
            AS 'Message: Done';
        DEALLOCATE PREPARE stmt2;
        DEALLOCATE PREPARE stmt3;
    ELSE 
        SELECT 'All values are present in base table. Adding column is thus unnecessary.' 
            AS 'Message: UNNECESSARY';
    END IF;
    
    DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;
;

但是，我想指出的是，我还没有添加 TRY ... CATCH 或任何阻止程序抛出错误的内容。这只是使其发挥作用的最低限度。

调用过程

CALL CheckValueExistsInBaseTable(
    'knownfortitles',
    'titlebasics',
    'titleId',
    'tconst'
    );

返回消息确认：

或消息警告：

仅在第一种情况下，才会根据需要添加 TINYINT(1) 列。