SQL LIKE实际如何工作 [英] How does SQL LIKE actually work

问题描述

例如，当我有这样的字符串时:

For example when I have a string like this:

ABBBCSLAK**JDK**ASAAAAFJKDSKJFSDF

当我这样使用SQL时:

And when I use SQL like this:

SELECT * FROM table WHERE column LIKE '%JDK%'

当服务器使用JDK时会发生什么?是停止并执行SQL，还是遍历字符串的其余部分然后执行SQL?

What exactly happens when server gets to JDK? Does it stop and execute the SQL or does it go through the rest of the string and then execute the SQL?

当我在与OR连接的SQL语句中有多个LIKE子句时，还会发生什么情况?它可能时会在第一个LIKE子句处停止吗?

Also what happends when I have multiple LIKE clauses in my SQL statement connected with OR? Does it stop at first LIKE clause when it is possitive?

我有这样的SQL.这可能是矫kill过正，但值得一提...每个变量都包含一个表列的LIKE子句循环.它们之间有或".我是否将这些"AND"更改为"AND"或"OR"都没有任何区别.

I have SQL like this. It is probably overkill but wel... Every variable contains loop of LIKE clauses for one table column. They have "OR" in between of them. Whether I change these "ANDs" to "AND" or "OR" does not make any difference.

                     WHERE
                        ($countrySQL)
                            AND
                        ($schools_typeSQL)
                            AND
                        $schoolsSQL
                            AND
                        $schools_facultiesSQL
                            AND
                        $schools_classesSQL
                    ORDER BY

推荐答案

MySQL尝试

MySQL tries to improve the speed of LIKE when a B-Tree index is available by pre-filtering the rows:

B树索引特征

B树索引可用于使用=，>，>=，<，<=或BETWEEN运算符的表达式中的列比较.如果LIKE的参数是一个不以通配符开头的常量字符串，则该索引也可以用于LIKE比较.例如，以下SELECT语句使用索引:

B-Tree Index Characteristics

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character. For example, the following SELECT statements use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

在第一条语句中，只有'Patrick'< = key_col<考虑"Patricl".在第二条语句中，只有'Pat'< = key_col<考虑了波城".

In the first statement, only rows with 'Patrick' <= key_col < 'Patricl' are considered. In the second statement, only rows with 'Pat' <= key_col < 'Pau' are considered.

以下SELECT语句不使用索引:

The following SELECT statements do not use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

在第一条语句中，LIKE值以通配符开头.在第二条语句中，LIKE值不是常数.

In the first statement, the LIKE value begins with a wildcard character. In the second statement, the LIKE value is not a constant.

如果您使用... LIKE '%string%'并且字符串长度超过三个字符，则MySQL使用 Turbo Boyer-Moore算法初始化字符串的模式，然后使用此模式执行搜索更快.

If you use ... LIKE '%string%' and string is longer than three characters, MySQL uses the Turbo Boyer-Moore algorithm to initialize the pattern for the string and then uses this pattern to perform the search more quickly.

如果您有多个与OR连接的术语，查询优化将尝试查找最佳查询路径，并可能重新排列条件，甚至决定逐一处理它们.有关详细信息，请参见此问题.

If you have multiple OR-connected terms, query optimization will try to find the optimal query path and may reorder the conditions or even decide to handle them one by one. See this question for details.

这篇关于SQL LIKE实际如何工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！