SQL LIKE实际如何工作

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

ABBBCSLAK**JDK**ASAAAAFJKDSKJFSDF

当我这样使用SQL时:

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

服务器到达JDK时会发生什么?它会停止并执行SQL,还是遍历字符串的其余部分然后执行SQL?

当我的SQL语句中有多个与OR连接的LIKE子句时,还会发生什么?当它为正数时,它会在第一个LIKE子句处停止吗?

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

                     WHERE
                        ($countrySQL)
                            AND
                        ($schools_typeSQL)
                            AND
                        $schoolsSQL
                            AND
                        $schools_facultiesSQL
                            AND
                        $schools_classesSQL
                    ORDER BY
评论
  • 发霉的情书
    发霉的情书 回复

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

    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.

    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.

  • 小梨涡
    小梨涡 回复

    当然,这取决于数据库管理系统(DMBS)的实现。

    但是,大多数DBMS都针对性能进行了优化,因此它们使用短路。即如果(a OR B)中的一个参数为true,则它将停止评估其余参数,因为它已经为true。对于(A AND B)来说,如果A为假,则相同,它不需要检查B,因为整个表达式不再是真实的。

    同样也适用于LIKE运营商。如果已经找到匹配项,则无需继续检查字符串。

    除此之外: SQL是声明性的“语言”,因此,您不知道DBMS是否已将其更改/优化为更好的语言(例如,首先检查另一列,因为DBMS对此列具有索引)。