不带百分号且WHERE子句中等于(=)的SQL LIKE之间的差异 [英] Difference between SQL LIKE without percent signs and equal (=) in WHERE clause

问题描述

这两个查询的结果除性能之外是否还有其他差异?

Are there any differences in the results of these two queries other than performance?

SELECT * FROM pet WHERE name LIKE 'Spot';
SELECT * FROM pet WHERE name = 'Spot';

我问的原因是真实的脚本将类似于以下内容，并且API用户负责提出该模式.通常不会提供LIKE模式，但是总有可能仅提供一个字符串，从而导致SELECT * FROM pet WHERE name LIKE "Spot".

Reason I ask is the real script will be something like the following and the API user is responsible to come up with the pattern. More often that not, a LIKE pattern will be provided, but there is always a chance that just a string will be provided resulting in SELECT * FROM pet WHERE name LIKE "Spot".

$stmt = $this->pdo->prepare('SELECT * FROM pet WHERE name LIKE ?');
$stmt->execute([$_GET['name']]); //Spot
return [$stmt->fetchAll(),200];

推荐答案

在实践中，没有通配符的LIKE在功能上等同于=.但是，它们不一样！明显的区别是=不会以任何特殊方式处理\，%和_，但是LIKE会处理.

In practice, LIKE with no wildcards is functionally equivalent to =. However, they are not the same! The obvious difference is that = doesn't treat \, %, and _ in any special way, but LIKE does.

文档是对此很清楚:

根据SQL标准，LIKE根据每个字符执行匹配， 因此它可以产生与=比较运算符不同的结果:

Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:

除了排序规则差异外，尾随空格也很重要:

In addition to collation differences, trailing spaces matter:

尤其是尾随空格很重要，这不适用于 用=运算符进行的CHAR或VARCHAR比较:

In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator:

在实践中，被比较的字符串通常具有相同的排序规则，没有尾部空格，并且特殊字符被忽略，因此LIKE有时会代替=(尤其是因为LIKE模式开头的通配符也可以使用索引.)

In practice, the strings being compared usually have the same collation, don't have trailing spaces, and special characters are ignored, so LIKE is sometimes used as a replacement for = (especially because LIKE without wildcards at the beginning of the pattern can also make use of an index).

这篇关于不带百分号且WHERE子句中等于(=)的SQL LIKE之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！