在預存程序中執行 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) 欄位。