MySQL查询：排除order_name含'Spanish copywriter'的所有记录

需求 #

从orders表中获取所有记录，但排除order_name列包含'Spanish copywriter'的记录。

尝试的错误方案及原因 #

• NOT EXISTS子查询语法错误

  SELECT * FROM `orders` where order_name NOT EXISTS
  (SELECT * from orders where order_name LIKE '%Spanish copywriter%')
  

  执行报错，原因是NOT EXISTS用于判断子查询是否返回结果，不能直接与列名关联，语法逻辑完全错误。

• !=运算符无法处理包含场景

  SELECT * FROM `orders` WHERE order_name != 'Spanish copywriter' ORDER BY `orders`.`order_name` DESC
  

  仍返回包含目标字符串的记录，因为!=是精确匹配，仅排除order_name完全等于'Spanish copywriter'的记录，无法处理"包含该字符串"的场景（比如示例中大小写不同的'Spanish Copywriter'）。

• <>运算符同样无效

  SELECT * FROM `orders` WHERE order_name <> 'Spanish copywriter'
  

  和!=逻辑一致，属于精确匹配，无法过滤包含指定字符串的记录，达不到预期效果。

示例数据 #

INSERT INTO `orders` (`id`, `publisher_id`, `client_id`, `order_number`, `order_name`, `order_date`, `sub_total`, `discount`, `discount_type`, `total`, `status`, `currency_id`, `show_shipping_address`, `redactor`, `external_order_id`, `note`, `internal_note`, `editor_note`, `client_note`, `reason_incidence`, `post_url`, `added_by`, `last_updated_by`, `billing_address`, `company_address_id`, `created_at`, `updated_at`) VALUES
(500, NULL, 29, '20429', 'Spanish Copywriter', '2024-01-29', 0, 0, 'percent', 0, 'publicada', 0, 'no', '0', '0', '', NULL, '', '', '', NULL, 29, NULL, '0', 1, '2024-01-29 12:22:54', '2024-01-29 14:11:29'),
(501, NULL, 91, '20430', 'Brazilian Copywriter', '2024-01-29', 0, 0, 'percent', 0, 'publicada', 0, 'no', '0', '0', '', NULL, '', '', '', NULL, 0, NULL, '', 1, '2024-01-29 12:41:30', '2024-02-05 07:26:34'),
(502, NULL, 29, '20431', 'Italian Copywriter', '2024-01-29', 0, 0, 'percent', 0, 'publicada', 0, 'no', '5', '0', '', NULL, '', '', '', NULL, 29, NULL, '0', 1, '2024-01-29 15:45:07', '2024-01-30 14:07:41'),
(503, NULL, 91, '20432', 'Italian Copywriter', '2024-01-29', 0, 0, 'percent', 0, 'publicada', 0, 'no', '5', '0', '', NULL, '0', '', '', NULL, 91, NULL, '', 1, '2024-01-29 15:49:26', '2024-01-30 14:13:27'),
(504, NULL, 29, '20433', 'English Copywriter', '2024-01-30', 0, 0, 'percent', 0, 'publicada', 0, 'no', '4', '0', '', NULL, '', '', '', NULL, 29, NULL, '0', 1, '2024-01-30 05:50:06', '2024-02-01 10:02:34'),
(520, NULL, 35, '20449', '0', '2024-01-30', 0, 0, 'percent', 0, 'publicada', 3, 'no', NULL, NULL, '', NULL, '', '', '', '0', 35, NULL, '0', 1, '2024-01-30 10:23:00', '2024-02-06 16:23:14'),
(521, NULL, 35, '20450', '0', '2024-01-30', 0, 10, 'percent', 0, 'publicada', 0, 'no', '5', NULL, '', NULL, '0', '0', 1, '2024-01-30 10:32:35', '2024-02-28 13:59:52'),

预期结果 #

仅返回id为520、521的记录。

正确解决方案 #

使用NOT LIKE运算符，通过通配符%匹配任意包含指定字符串的记录，实现排除效果：

SELECT * FROM `orders` WHERE order_name NOT LIKE '%Spanish copywriter%'

注：如果需要严格区分大小写，可添加BINARY关键字：

SELECT * FROM `orders` WHERE order_name NOT LIKE BINARY '%Spanish copywriter%'

内容的提问来源于stack exchange，提问作者scorpions77