<ruby id="bdb3f"></ruby>

    <p id="bdb3f"><cite id="bdb3f"></cite></p>

      <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
        <p id="bdb3f"><cite id="bdb3f"></cite></p>

          <pre id="bdb3f"></pre>
          <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

          <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
          <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

          <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                <ruby id="bdb3f"></ruby>

                合規國際互聯網加速 OSASE為企業客戶提供高速穩定SD-WAN國際加速解決方案。 廣告
                #### 3.3.4.7 模式匹配 MySQL 提供了標準的 SQL 模式匹配, 以及一種基于擴展正則表達式的模式匹配形式, 類似于 Unix 的工具, 如 `vi`, `grep`, 和 `sed`. SQL 模式匹配允許使用 `_` 匹配任何單個字符, `%` 匹配任意數量的字符(包含零個字符). 在 MySQL 中, SQL 匹配默認不區分大小寫. 這里展示了一些示例. 當你使用 SQL 模式時不要使用 `=` 或者 `<>`. 而是使用 [`LIKE`](https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_like) 或者 [`NOT LIKE`](https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_not-like) 比較運算符. 找出 `b`開頭的 name: ```sql mysql> SELECT * FROM pet WHERE name LIKE 'b%'; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ ``` 找出 `fy` 結尾的 name: ```sql mysql> SELECT * FROM pet WHERE name LIKE '%fy'; +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ ``` 找出包含 `w` 的 name: ```sql mysql> SELECT * FROM pet WHERE name LIKE '%w%'; +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ ``` 找出恰好包含五個字符的 name, 使用五個 `_` 匹配字符集的實例: ```sql mysql> SELECT * FROM pet WHERE name LIKE '_____'; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ ``` MySQL 提供的另一種模式匹配是使用擴展正則表達式. 當你測試這種類型模式的匹配時, 使用 [`REGEXP_LIKE()`](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-like) 函數 (或者 [`REGEXP`](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#operator_regexp) 或者 [`RLIKE`](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#operator_regexp) 操作符, 它們是 [`REGEXP_LIKE()`](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-like) 的同義詞). 下列列表描述了正則表達式的一些特性: - `.` 匹配任何單個字符. - 字符類 `[...]` 匹配括號內的任何字符. 例如, `[abc]` 匹配 a, b, 或者 c. 要命名一個字符范圍, 請使用破折號. `[a-z]` 匹配任何字母, `[0-9]` 匹配任何數字. - `*` 匹配前一項的零個或者多個實例. 例如, `x*` 匹配任意數量的 `x` 字符, `[0-9]*` 匹配任意數量的數字, `.*` 匹配任意數量的任何字符. - 如果正則表達式模式匹配正在測試的值中的任何位置, 則該模式匹配成功. (這與 [`LIKE`](https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_like) 模式匹配不同, 如果模式匹配真個值時才會成功.) - 如果要匹配被測試值的開頭或者結束, 請在模式的開始添加 `^` 或者在結尾添加 `$`. 為了演示擴展正則表達式是如何工作的, 這里使用 [`REGEXP_LIKE()`](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-like) 重寫了前面的 [`LIKE`](https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_like) 查詢. 找出以 `b` 開頭的 name, 使用 `^` 匹配 name 的開頭: ```sql mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b'); +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 | +--------+--------+---------+------+------------+------------+ ``` To force a regular expression comparison to be case sensitive, use a case-sensitive collation, or use the [`BINARY`](https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#operator_binary) keyword to make one of the strings a binary string, or specify the `c` match-control character. Each of these queries matches only lowercase b at the beginning of a name: ```sql SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs); SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b'); SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c'); ``` To find names ending with `fy`, use `$` to match the end of the name: ```sql mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$'); +--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ ``` To find names containing a `w`, use this query: ```sql mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w'); +----------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+------------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | +----------+-------+---------+------+------------+------------+ ``` Because a regular expression pattern matches if it occurs anywhere in the value, it is not necessary in the previous query to put a wildcard on either side of the pattern to get it to match the entire value as would be true with an SQL pattern. To find names containing exactly five characters, use `^` and `$` to match the beginning and end of the name, and five instances of `.` in between: ```sql mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$'); +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ ``` You could also write the previous query using the `{n}` (“repeat-`n`-times”) operator: ```sql mysql> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$'); +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +-------+--------+---------+------+------------+-------+ ```` For more information about the syntax for regular expressions, see [Section 12.5.2, “Regular Expressions”](https://dev.mysql.com/doc/refman/8.0/en/regexp.html).
                  <ruby id="bdb3f"></ruby>

                  <p id="bdb3f"><cite id="bdb3f"></cite></p>

                    <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
                      <p id="bdb3f"><cite id="bdb3f"></cite></p>

                        <pre id="bdb3f"></pre>
                        <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

                        <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
                        <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

                        <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                              <ruby id="bdb3f"></ruby>

                              哎呀哎呀视频在线观看