mysql - SQL LIKE 是如何工作的

Question

例如当我有这样一个字符串时:

ABBBCSLAK**JDK**ASAAAAFJKDSKJFSDF

当我像这样使用 SQL 时:

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

当服务器访问 JDK 时究竟发生了什么？它是停止并执行 SQL 还是遍历字符串的其余部分然后执行 SQL？

另外，当我的 SQL 语句中有多个 LIKE 子句与 OR 连接时会发生什么情况？当它是肯定的时候，它会在第一个 LIKE 子句处停止吗？

编辑:我有这样的SQL。这可能有点矫枉过正，但很好......每个变量都包含一个表列的 LIKE 子句循环。他们之间有“或”。无论我将这些“AND”更改为“AND”还是“OR”都没有任何区别。

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

Answer 1

MySQL 尝试 improve the speed of LIKE when a B-Tree index is available通过预过滤行:

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%';

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.

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;

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

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 -connected terms，查询优化将尝试找到最佳查询路径，并可能重新排序条件，甚至决定一一处理。参见 this question了解详情。