MySQL instr()函式

2019-10-16 22:54:29

本教學將向您展示如何使用MySQL INSTR()函式返回字串第一次出現的位置。

MySQL INSTR函式簡介

有時,您想要在字串中查詢子字串或檢查字串中是否存在子字串。在這種情況下,您可以使用字串內建INSTR()函式。

INSTR()函式返回字串中子字串第一次出現的位置。如果在str中找不到子字串,則INSTR()函式返回零(0)。

下面說明了INSTR函式的語法。

INSTR(str,substr);

INSTR函式接受兩個引數:

  • str是要搜尋的字串。
  • substr是要搜尋的子字串。

INSTR()函式不區分大小寫。這意味著如果通過小寫,大寫,標題大小寫等,結果總是一樣的。

如果希望INSTR函式在非二進位制字串上以區分大小寫的方式執行搜尋,則可以使用BINARY運算子將INSTR函式的引數從非二進位制字串轉換為二進位制字串。

MySQL INSTR函式範例

以下語句返回MySQL INSTR字串中的子字串SQL的位置。

SELECT INSTR('MySQL INSTR', 'SQL');

執行上面查詢語句,得到以下結果 -

mysql> SELECT INSTR('MySQL INSTR', 'SQL');
+-----------------------------+
| INSTR('MySQL INSTR', 'SQL') |
+-----------------------------+
|                           3 |
+-----------------------------+
1 row in set

以下語句返回相同的結果,因為INSTR函式不區分大小寫。

SELECT INSTR('MySQL INSTR', 'sql');

執行上面查詢語句,得到以下結果 -

mysql> SELECT INSTR('MySQL INSTR', 'sql');
+-----------------------------+
| INSTR('MySQL INSTR', 'sql') |
+-----------------------------+
|                           3 |
+-----------------------------+
1 row in set

要強制INSTR函式根據以區分大小寫的方式進行搜尋,請按如下所示使用BINARY運算子:

SELECT INSTR('MySQL INSTR', BINARY 'sql');

執行上面查詢語句,得到以下結果 -

mysql> SELECT INSTR('MySQL INSTR', BINARY 'sql');
+------------------------------------+
| INSTR('MySQL INSTR', BINARY 'sql') |
+------------------------------------+
|                                  0 |
+------------------------------------+
1 row in set

結果是不同的,因為現在使用BINARY運算子,因為sqlSQL是不同的字串。

INSTR函式與LIKE運算子

我們將使用範例資料庫(yiibaidb)中的products表,其表結構如下 -

mysql> desc products;
+--------------------+---------------+------+-----+---------+------------------+
| Field              | Type          | Null | Key | Default | Extra            |
+--------------------+---------------+------+-----+---------+------------------+
| productCode        | varchar(15)   | NO   | PRI |         |                  |
| productName        | varchar(70)   | NO   | MUL | NULL    |                  |
| productLine        | varchar(50)   | NO   | MUL | NULL    |                  |
| productScale       | varchar(10)   | NO   |     | NULL    |                  |
| productVendor      | varchar(50)   | NO   |     | NULL    |                  |
| productDescription | text          | NO   |     | NULL    |                  |
| quantityInStock    | smallint(6)   | NO   |     | NULL    |                  |
| buyPrice           | decimal(10,2) | NO   |     | NULL    |                  |
| MSRP               | decimal(10,2) | NO   |     | NULL    |                  |
| stockValue         | double        | YES  |     | NULL    | STORED GENERATED |
+--------------------+---------------+------+-----+---------+------------------+
10 rows in set

假設要查詢名稱包含car關鍵字的產品,可以使用INSTR函式,如下所示:

SELECT 
    productName
FROM
    products
WHERE
    INSTR(productname,'Car') > 0;

執行上面查詢語句,得到以下結果 -

+----------------------------------------+
| productName                            |
+----------------------------------------+
| 1911 Ford Town Car                     |
| 1999 Indy 500 Monte Carlo SS           |
| 18th Century Vintage Horse Carriage    |
| 1917 Maxwell Touring Car               |
| 1950s Chicago Surface Lines Streetcar |
| 1962 City of Detroit Streetcar         |
+----------------------------------------+
6 rows in set

除了INSTR函式,可以使用LIKE運算子來匹配Car模式。

SELECT 
    productname
FROM
    products
WHERE
    productname LIKE '%Car%';

執行上面查詢語句,得到以下結果 -

+----------------------------------------+
| productname                            |
+----------------------------------------+
| 1911 Ford Town Car                     |
| 1999 Indy 500 Monte Carlo SS           |
| 18th Century Vintage Horse Carriage    |
| 1917 Maxwell Touring Car               |
| 1950s Chicago Surface Lines Streetcar |
| 1962 City of Detroit Streetcar         |
+----------------------------------------+
6 rows in set

兩個查詢返回相同的結果。那麼哪一個更快,INSTR還是LIKE操作符?

答案是它們是一樣的,它們都區分大小寫,並執行全表掃描。

讓我們在productname列建立一個索引。

CREATE INDEX idx_products_name ON products(productname);

如果您使用具有字首搜尋的LIKE運算子,則在此索引列上,LIKE運算子的執行速度要比INSTR函式快。

請參閱以下查詢語句 -

SELECT 
    productname
FROM
    products
WHERE
    productname LIKE '1900%';

執行上面查詢語句,得到以下結果 -

+-------------------------+
| productname             |
+-------------------------+
| 1900s Vintage Bi-Plane  |
| 1900s Vintage Tri-Plane |
+-------------------------+
2 rows in set

您可以使用EXPLAIN語句檢查它:

EXPLAIN SELECT 
    productname
FROM
    products
WHERE
    productname LIKE '1900%';

執行查詢分析,得到以下結果 -

下面使用INSTR函式執行查詢分析,以進行比較,參考以下語句 -

EXPLAIN SELECT 
    productname
FROM
    products
WHERE
    instr(productname,'1900');

執行查詢分析,得到以下結果 -

即使productname列具有索引,INSTR函式也執行表掃描。 這是因為MySQL不能對INSTR函式的語意做任何假設,MySQL可以利用其對LIKE運算子語意的理解。

測試字串中是否存在子字串的最快方法是使用全文索引。 但是,需要正確組態和維護索引。

在本教學中,您已經學習了如何使用INSTR函式來查詢字串中子字串第一次出現的位置。