在預存程序中執行 CONCAT 語句不起作用

Question

<p>我有兩個表，一個基表包含有關值“tconst”（也是主鍵）的各種信息，另一個表在“titleId”名稱下從“nconst”鏈接到多個“tconst”值' .</p> <p>---基表'titlebasics'</p> <table class="s-table"> <thead> <tr> <th>tconst</th> <th>標題類型</th> <th>...</th> </tr> </thead> <tbody> <tr> <td>tt0000009</td> <td>電影</td> <td>...</td> </tr> <tr> <td>tt0000147</td> <td>電影</td> <td>...</td> </tr> <tr> <td>...</td> <td>...</td> <td>...</td> </tr> </tbody> </table> <p>---額外資訊表'knownfortitles'</p> <table class="s-table"> <thead> <tr> <th>id</th> <th>nconst</th> <th>標題訂單</th> <th>標題ID</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>nm0000001</td> <td>1</td> <td>tt0050419</td> </tr> <tr> <td>2</td> <td>nm0000001</td> <td>2</td> <td>tt0053137</td> </tr> <tr> <td>...</td> <td>...</td> <td>...</td> <td>...</td> </tr> </tbody> </table> <p>「問題」是 <code>knownfortitles.titleId</code> 中的某些值在 <code>titlebasics.tconst</code> 中不存在。我想建立一個預存過程，在其中可以將兩個表和兩個對應列的名稱作為參數傳遞。此程序將首先檢查第二個表中是否確實存在第一個表中不存在的值，如果是，則向第二個表添加一個名為<code>is_in_<base_table_name></code> 的列。然後，它隨後將使用第二個表中每一行的布林值更新此列。我希望在預存過程中完成此操作，因為我有很多表都存在這個問題，我希望能夠使用此過程，而不是使用不同的值一遍又一遍地編寫相同的程式碼。但是，當我嘗試調用我的程式時遇到錯誤，但我似乎無法修復該錯誤。 </p> <p>作為一個儲存過程，這就是我陷入困境的地方。</p> <pre class="brush:php;toolbar:false;">建立程序`CheckValueExistsInBaseTable`( 在 checkTable VARCHAR(100) 中， IN 引用的基底表 VARCHAR(100)， 在 checkCol VARCHAR(100) 中， IN ReferencedCol VARCHAR(100) ） 開始 DECLARE new_column_name VARCHAR(100) DEFAULT 'is_in_baseTable'; DECLARE sql_statement1 VARCHAR(1000) DEFAULT 'SELECT NULL;'; DECLARE sql_statement2 VARCHAR(1000) DEFAULT 'SELECT NULL;'; SET @new_column_name = CONCAT('is_in_',referencedBaseTable); -- 如果檢查表不存在，則新增列 SET @sql_statement1 = CONCAT('IF (選擇存在的情況( 選擇1 FROM ', 已檢查表, ' WHERE '、checkedCol、' NOT IN (SELECT '、referencedCol、' FROM '、referencedBaseTable、')) 然後 1 否則 0 結束 ) = 1 然後 ALTER TABLE ', checkTable, ' ADD ', @new_column_name, ' BOOL; 別的 選擇空； 萬一'）; 從 @sql_statement1 準備 stmt1； 執行stmt1； 解除分配準備stmt1； -- 更新檢查表中的 is_in_referencedBaseTable 列 SET @sql_statement2 = CONCAT('UPDATE',checkedTable,'SET', @new_column_name, ' = CASE WHEN EXISTS(SELECT * FROM ', ReferencedBaseTable，'WHERE'，referencedBaseTable，'.'， referencedCol, ' = ', checkTable, '.', checkCol, ') THEN 1 ELSE 0 END'); 從 @sql_statement2 準備 stmt2； 執行stmt2； 解除分配準備stmt2； 結束</pre> <p>無論我嘗試更改什麼，這都會給我帶來以下錯誤之一。</p> <區塊引用> <p>錯誤代碼：1064。您的 SQL 語法有錯誤；檢查與您的 MySQL 伺服器版本相對應的手冊，以了解在第 1 行「NULL」附近使用的正確語法</p> </區塊引用> <p>或</p> <區塊引用> <p>錯誤代碼：1064。您的 SQL 語法有錯誤；檢查與您的 MySQL 伺服器版本相對應的手冊，以了解在 'IF (SELECT CASE WHEN EXISTS( 選擇1 來自知名作品 WHERE titleId NOT' 在第 1 行</p> </區塊引用> <p>我還創建了測試程序來檢查哪些部分可能會出現問題，但兩者都工作得很好，這讓我更加困惑。第一個只返回我在 <code>CONCAT</code>放入的內容中，以查看其中是否存在任何語法錯誤。</p> <pre class="brush:php;toolbar:false;">建立流程`test`( 在 checkTable VARCHAR(100) 中， IN 引用的基底表 VARCHAR(100)， 在 checkCol VARCHAR(100) 中， IN ReferencedCol VARCHAR(100), IN new_column_name VARCHAR (100) ） 開始 -- 宣告變數並賦值 聲明 myvar VARCHAR(1000); DECLARE new_column_name VARCHAR(100) DEFAULT 'is_in_baseTable'; SET @new_column_name = CONCAT('is_in_',referencedBaseTable); SET myvar = CONCAT('IF (選擇存在的情況( 選擇1 FROM ', 已檢查表, ' WHERE '、checkedCol、' NOT IN (SELECT '、referencedCol、' FROM '、referencedBaseTable、')) 然後 1 否則 0 結束 ) = 1 然後 ALTER TABLE ', checkTable, ' ADD ', @new_column_name, ' BOOL; 別的 選擇空； 萬一'）; -- 將值列印到控制台 SELECT concat(myvar) AS 變數； 結束</pre> <p>此過程給出了以下結果：</p>

IF (SELECT CASE WHEN EXISTS(
    選擇1
    來自知名作品
    WHERE titleId NOT IN（從 titlebasics 中選擇 tconst））
    然後 1 否則 0 結束
 ) = 1
 然後
    ALTER TABLEknownfortitles ADD is_in_titlebasics BOOL;
 別的
    選擇空；
 END IF</pre>
<p>可能的程式碼是正確的，我知道這一點是因為我使用了下面的第二個過程，該過程利用了這個惡意的程式碼區塊。</p>
<pre class="brush:php;toolbar:false;">建立流程`test2`()
開始
    IF（存在時選擇案例（
        選擇1
        來自知名作品
        WHERE titleId NOT IN（從 titlebasics 中選擇 tconst））
        然後 1 否則 0 結束
    ) = 1
    然後
        ALTER TABLEknownfortitles ADD is_in_titlebasics BOOL;
    別的
        選擇空；
    萬一;
結束</pre>
此程序將列is_in_titlebasics加到表knownfortitles中，這就是我想要發生的事情，所以這很好。此時，我完全迷失了，不知道為什麼我的實際存儲過程不起作用，因為它基本上是最後兩個過程的組合。我暫時忽略了我希望儲存過程執行的第二部分，因為我遇到的錯誤似乎將第一個<code>CONCAT</code>語句視為問題。</p>
<p>我希望這個問題非常明顯，但我只是忽略了。歡迎任何幫助，提前致謝！</p>            

Answer 1

感謝 P. Salmon，我了解到問題是透過準備好的語句來運行 IF ... THEN 語句。這不可能。經過一番修改後，我想出了以下程序，它的工作原理與我想要的完全一樣。我希望我可以幫助一些與我遇到類似問題的人。

DELIMITER $$
USE `<schema>`$$
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) 欄位。