MySQL instr()函式
本教學將向您展示如何使用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運算子，因為sql與SQL是不同的字串。
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函式來查詢字串中子字串第一次出現的位置。