php - 如何在 SQL LIKE ... ESCAPE '\' 中使用 '\'

Question

我正在尝试删除描述中没有快照聊天用户名的所有缓存项目。在尝试转义破折号字符时，我一直遇到语法问题？

查询:

$cacheItems = CacheItem::whereRaw("description NOT LIKE '%'")
      ->whereRaw("description NOT LIKE '%👻%'")
      ->whereRaw("description NOT LIKE '%𝑺𝒄 %'")
      ->whereRaw("description NOT LIKE '%𝑺𝒄 :%'")
      ->whereRaw("description NOT LIKE '%𝚜𝚗𝚊𝚙𝚌𝚑𝚊𝚝%'")
      ->whereRaw("description NOT LIKE '%sc\'%'")
      ->whereRaw("description NOT LIKE '%sc\_%' ESCAPE '\'")
      ->whereRaw("description NOT LIKE '%sc \- %' ESCAPE '\'")
      ->whereRaw("description NOT LIKE '%sc %'")
      ->whereRaw("description NOT LIKE '%sc:%'")
      ->whereRaw("description NOT LIKE '%scm %'")
      ->whereRaw("description NOT LIKE '%scm;%'")
      ->whereRaw("description NOT LIKE '%sc- %'")
      ->whereRaw("description NOT LIKE '%sc.%'")
      ->whereRaw("description NOT LIKE '%sc-%'")
      ->whereRaw("description NOT LIKE '%SC -  %'")
      ->whereRaw("description NOT LIKE '%Sc ~%'")
      ->whereRaw("description NOT LIKE '%sc ~%'")
      ->whereRaw("description NOT LIKE '%sc;%'")
      ->whereRaw("description NOT LIKE '%sc~%'")
      ->whereRaw("description NOT LIKE '%sc/%'")
      ->whereRaw("description NOT LIKE '%snapchat:%'")
      ->whereRaw("description NOT LIKE '%snapchat - %'")
      ->whereRaw("description NOT LIKE '%snapchat-%'")
      ->whereRaw("description NOT LIKE '%snap~%'")
      ->whereRaw("description NOT LIKE '%snap =%'")
      ->whereRaw("description NOT LIKE '%snap•%'")
      ->whereRaw("description NOT LIKE '%snap%'")
      ->whereRaw("description NOT LIKE '%snap~%'")
      ->whereRaw("description NOT LIKE '%snapchat%'")
      ->whereRaw("description NOT LIKE '%snapchat ~ %'")
      ->whereRaw("description NOT LIKE '%snap:%'")
      ->whereRaw("description NOT LIKE '%add my sc%'")
      ->whereRaw("description NOT LIKE '%add me on sc%'")
      ->whereRaw("description NOT LIKE '%add me sc%'")
      ->whereRaw("description NOT LIKE 'snap %'")
->get();

错误:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%sc \- %' ESCAPE '\' and description NOT LIKE '%sc %' and description NOT LIKE '' at line 1 (SQL: select * from `cache_items` where description NOT LIKE '%' and description NOT LIKE '%👻%' and description NOT LIKE '%𝑺𝒄 %' and description NOT LIKE '%𝑺𝒄 :%' and description NOT LIKE '%𝚜𝚗𝚊𝚙𝚌𝚑𝚊𝚝%' and description NOT LIKE '%sc\'%' and description NOT LIKE '%sc\_%' ESCAPE '\' and description NOT LIKE '%sc \- %' ESCAPE '\' and description NOT LIKE '%sc %' and description NOT LIKE '%sc:%' and description NOT LIKE '%scm %' and description NOT LIKE '%scm;%' and description NOT LIKE '%sc- %' and description NOT LIKE '%sc.%' and description NOT LIKE '%sc-%' and description NOT LIKE '%SC -  %' and description NOT LIKE '%Sc ~%' and description NOT LIKE '%sc ~%' and description NOT LIKE '%sc;%' and description NOT LIKE '%sc~%' and description NOT LIKE '%sc/%' and description NOT LIKE '%snapchat:%' and description NOT LIKE '%snapchat - %' and description NOT LIKE '%snapchat-%' and description NOT LIKE '%snap~%' and description NOT LIKE '%snap =%' and description NOT LIKE '%snap•%' and description NOT LIKE '%snap%' and description NOT LIKE '%snap~%' and description NOT LIKE '%snapchat%' and description NOT LIKE '%snapchat ~ %' and description NOT LIKE '%snap:%' and description NOT LIKE '%add my sc%' and description NOT LIKE '%add me on sc%' and description NOT LIKE '%add me sc%' and description NOT LIKE 'snap %')

Answer 1

您使用 \ 作为您的 ESCAPE 字符:

  description NOT LIKE '%sc\_%' ESCAPE '\'

这是一个有问题的选择，因为 \ 本身在 MySQL's string-literal level 处被用作转义字符。 ，以及各种客户端软件包中。

如果这是我的代码，我在这种情况下根本不会信任 \ (因为我很懒，我不想弄清楚它在哪里以及如何用作转义)字符)。

最好选择像这样的其他转义字符(本例中为 |)

  description NOT LIKE '%sc|_%' ESCAPE '|'

或者将 sql 的 ESCAPE 部分中的 \ 加倍，如下所示

  description NOT LIKE '%sc\_%' ESCAPE '\\'

(并且请记住，column LIKE '%whatever' 是一种臭名昭著的查询性能反模式，因为它不能在 column 上使用任何索引。)