MySQL - 如何删除列不包含某些值的行？

Question

我正在尝试删除特定列不包含特定字符串的所有行。像这样:

DELETE * FROM table
WHERE Disease NOT LIKE (Malaria, HIV, E. coli O157);

我得到这个错误:#1064 - 您的 SQL 语法有误； near '* FROM table WHERE Disease NOT LIKE (Malaria' at line 1

Answer 1

如果给出的描述是对疾病的准确描述，只需使用NOT IN(带适当的引号):

DELETE FROM table
WHERE Disease NOT IN ('Malaria', 'HIV', 'E. coli O157');

但是如果是子串，就得用多个LIKE:

DELETE FROM table
WHERE
  NOT (Disease LIKE '%Malaria%'
       or Disease LIKE '%HIV%'
       or Disease LIKE '%E. coli O157%');