mysql - 相同的 where 条件但错误导致 mysql delete 和 select

Question

DELETE
FROM TB_VALIDATE_PARAM
WHERE UPPER(CONCAT(c_object_id, c_column_code)) NOT IN (
    SELECT UPPER(CONCAT(COL.TABLE_NAME, COL.COLUMN_NAME))
    FROM information_schema.COLUMNS AS COL
    WHERE COL.TABLE_SCHEMA = 'jlink'
);

SELECT * 
FROM TB_VALIDATE_PARAM
WHERE UPPER(CONCAT(c_object_id, c_column_code)) NOT IN (
    SELECT UPPER(CONCAT(COL.TABLE_NAME, COL.COLUMN_NAME))
    FROM information_schema.COLUMNS AS COL
    WHERE COL.TABLE_SCHEMA = 'jlink'
);

如你所见，数据库是MYSQL。DELETE和SELECT的条件相同。但是当SELECT时无法获取任何行，DELETE 删除表中的所有数据。我确定表 TB_VALIDATE_PARAM 有这么多行。Mysql 分析条件是否与 SELECT 和 DELETE 不同？

Answer 1

试试这是否适合你。

DELETE FROM TB_VALIDATE_PARAM 
USING TB_VALIDATE_PARAM AS TP INNER JOIN information_schema.COLUMNS AS COL
WHERE UPPER(CONCAT(TP.c_object_id, TP.c_column_code)) <> UPPER(CONCAT(COL.TABLE_NAME, COL.COLUMN_NAME))
AND COL.TABLE_SCHEMA = 'jlink'