<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;">CREATE PROCEDURE `CheckValueExistsInBaseTable`( IN checkedTable VARCHAR(100), IN referencedBaseTable VARCHAR(100), IN checkedCol VARCHAR(100), IN referencedCol VARCHAR(100) ) BEGIN 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); -- Add new column to checked table if it doesn't exist SET @sql_statement1 = CONCAT('IF (SELECT CASE WHEN EXISTS( 选择1 FROM ', checkedTable, ' WHERE ', checkedCol, ' NOT IN (SELECT ', referencedCol, ' FROM ', referencedBaseTable, ')) THEN 1 ELSE 0 END ) = 1 THEN ALTER TABLE ', checkedTable, ' ADD ', @new_column_name, ' BOOL; ELSE SELECT NULL; END IF'); PREPARE stmt1 FROM @sql_statement1; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; -- Update is_in_referencedBaseTable column in checked table SET @sql_statement2 = CONCAT('UPDATE ', checkedTable, ' SET ', @new_column_name, ' = CASE WHEN EXISTS(SELECT * FROM ', referencedBaseTable, ' WHERE ', referencedBaseTable, '.', referencedCol, ' = ', checkedTable, '.', checkedCol, ') THEN 1 ELSE 0 END'); PREPARE stmt2 FROM @sql_statement2; EXECUTE stmt2; DEALLOCATE PREPARE stmt2; END</pre> <p>无论我尝试更改什么，这都会给我带来以下错误之一。</p> <blockquote> <p>错误代码：1064。您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行“NULL”附近使用的正确语法</p> </blockquote> <p>或</p> <blockquote> <p>错误代码：1064。您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 'IF (SELECT CASE WHEN EXISTS( 选择1 来自知名作品 WHERE titleId NOT' 在第 1 行</p> </blockquote> <p>我还创建了测试程序来检查哪些部分可能出现问题，但两者都工作得很好，这让我更加困惑。第一个仅返回我在 <code>CONCAT</code> 中放入的内容，以查看其中是否存在任何语法错误。</p> <pre class="brush:php;toolbar:false;">CREATE PROCEDURE `test`( IN checkedTable VARCHAR(100), IN referencedBaseTable VARCHAR(100), IN checkedCol VARCHAR(100), IN referencedCol VARCHAR(100), IN new_column_name VARCHAR (100) ) BEGIN -- Declaring the variable and assigning the value declare 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 (SELECT CASE WHEN EXISTS( 选择1 FROM ', checkedTable, ' WHERE ', checkedCol, ' NOT IN (SELECT ', referencedCol, ' FROM ', referencedBaseTable, ')) THEN 1 ELSE 0 END ) = 1 THEN ALTER TABLE ', checkedTable, ' ADD ', @new_column_name, ' BOOL; ELSE SELECT NULL; END IF'); -- Printing the value to the console SELECT concat(myvar) AS Variable; END</pre> <p>此过程给出下一个结果：</p> <pre class="brush:php;toolbar:false;">IF (SELECT CASE WHEN EXISTS( 选择1 来自知名作品 WHERE titleId NOT IN (SELECT tconst FROM titlebasics)) THEN 1 ELSE 0 END ) = 1 THEN ALTER TABLE knownfortitles ADD is_in_titlebasics BOOL; ELSE SELECT NULL; END IF</pre> <p>这段代码是正确的，我知道这一点是因为我使用了下面的第二个过程，该过程利用了这个确切的代码块。</p> <pre class="brush:php;toolbar:false;">CREATE PROCEDURE `test2`() BEGIN IF (SELECT CASE WHEN EXISTS( 选择1 来自知名作品 WHERE titleId NOT IN (SELECT tconst FROM titlebasics)) THEN 1 ELSE 0 END ) = 1 THEN ALTER TABLE knownfortitles ADD is_in_titlebasics BOOL; ELSE SELECT NULL; END IF; END</pre> <p>此过程将列 <code>is_in_titlebasics</code> 添加到表 <code>knownfortitles</code> 中，这就是我想要发生的事情，所以这很好。此时，我完全迷失了，不知道为什么我的实际存储过程不起作用，因为它基本上是最后两个过程的组合。我暂时忽略了我希望存储过程执行的第二部分，因为我遇到的错误似乎将第一个 <code>CONCAT</code> 语句视为问题。</p> <p>我希望这个问题是非常明显的，但我只是忽略了。欢迎任何帮助，提前致谢！</p>

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 ;
;

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