SQL – Case Sensitive & Insensitive

在做資料搜尋,比對時,什麼情況會 區分大小寫 ( Case sensitive ) 或是 不區分大小寫 ( Case insensitive ).

預設建立資料庫時使用的是 latin1 ( 關於資料庫編碼請參考 – http://benjr.tw/102156 ) ,只有 Binary 的資料格式 (Data Type) 才有區分大小寫 ( Case sensitive ).

測試環境為 CentOS 8 x86_64 (虛擬機)

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

建立資料庫,並建立含有 Binary 欄位的資料表.

MariaDB [(none)]> CREATE DATABASE test1;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE test1;
Database changed
MariaDB [test1]> STATUS;
--------------
mysql  Ver 15.1 Distrib 10.3.17-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          10
Current database:       test1
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.3.17-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 9 min 29 sec

Threads: 7  Questions: 19  Slow queries: 0  Opens: 21  Flush tables: 1  Open tables: 15  Queries per second avg: 0.033
--------------

MariaDB [test1]> CREATE TABLE employee (Name VARCHAR(30) ,JobTitle BINARY(30) , Description TEXT);
Query OK, 0 rows affected (0.010 sec)

MariaDB [test1]> INSERT INTO employee VALUES ('Ben' , 'Engineer' , '5 years in Testing experience' ) ;
Query OK, 1 row affected (0.002 sec)

可以發現 VARCHAR 欄位(Name) 不區分大小寫 ( Case insensitive ).

MariaDB [test1]> SELECT * FROM employee WHERE Name LIKE 'Ben';
+------+--------------------------------+-------------------------------+
| Name | JobTitle                       | Description                   |
+------+--------------------------------+-------------------------------+
| Ben  | Engineer                       | 5 years in Testing experience |
+------+--------------------------------+-------------------------------+
1 row in set (0.001 sec)

MariaDB [test1]> SELECT * FROM employee WHERE Name LIKE 'ben';
+------+--------------------------------+-------------------------------+
| Name | JobTitle                       | Description                   |
+------+--------------------------------+-------------------------------+
| Ben  | Engineer                       | 5 years in Testing experience |
+------+--------------------------------+-------------------------------+
1 row in set (0.001 sec)

但 Binary 欄位(JobTitle )區分大小寫 ( Case sensitive )

MariaDB [test1]> SELECT * FROM employee WHERE JobTitle LIKE 'Engineer%';
+------+--------------------------------+-------------------------------+
| Name | JobTitle                       | Description                   |
+------+--------------------------------+-------------------------------+
| Ben  | Engineer                       | 5 years in Testing experience |
+------+--------------------------------+-------------------------------+
1 row in set (0.001 sec)

MariaDB [test1]> SELECT * FROM employee WHERE JobTitle LIKE 'engineer%';
Empty set (0.001 sec)

那要如何指定 區分大小寫 ( Case sensitive ) 或是 不區分大小寫 ( Case insensitive ).

LIKE BINARY

在 LIKE 後面指定為 BINARY ,這時候的搜尋就是 區分大小寫 ( Case sensitive ).

MariaDB [test1]> SELECT * FROM employee WHERE Name LIKE 'ben';
+------+--------------------------------+-------------------------------+
| Name | JobTitle                       | Description                   |
+------+--------------------------------+-------------------------------+
| Ben  | Engineer                       | 5 years in Testing experience |
+------+--------------------------------+-------------------------------+
1 row in set (0.001 sec)

MariaDB [test1]> SELECT * FROM employee WHERE Name LIKE BINARY 'ben';
Empty set (0.001 sec)

COLLATE latin1_bin

也可以在進行比較搜尋時變更 文字排序 ( Collations ) 為有支援區分大小寫 ( Case sensitive ) ,如 latin1_bin (須依據你實際使用的編碼選擇適當的 Collations )

MariaDB [test1]> SELECT * FROM employee WHERE Name LIKE 'ben';
+------+--------------------------------+-------------------------------+
| Name | JobTitle                       | Description                   |
+------+--------------------------------+-------------------------------+
| Ben  | Engineer                       | 5 years in Testing experience |
+------+--------------------------------+-------------------------------+
1 row in set (0.001 sec)

MariaDB [test1]> SELECT * FROM employee WHERE Name LIKE 'ben' COLLATE latin1_bin;
Empty set (0.001 sec)

(?i) 與 (?-i)

為增強正規表示式的性能 MariaDB 10.0.5 開始支援 PCRE (Perl Compatible Regular Expressions) flags.

  • (?i) 強制不區分大小寫.
    試不出來.
  • (?-i) 強制區分大小寫.
    MariaDB [test1]> SELECT * FROM employee WHERE Name LIKE 'ben';
    +------+--------------------------------+-------------------------------+
    | Name | JobTitle                       | Description                   |
    +------+--------------------------------+-------------------------------+
    | Ben  | Engineer                       | 5 years in Testing experience |
    +------+--------------------------------+-------------------------------+
    1 row in set (0.001 sec)
    
    MariaDB [test1]> SELECT * FROM employee WHERE Name LIKE '(?-i)ben';
    Empty set (0.001 sec)
    

DEFAULT CHARACTER SET & COLLATE

在資料庫編碼文字排序 ( Collations ) bin 代表是區分大小寫 ( Case sensitive ).

MariaDB [test1]> SHOW COLLATION WHERE Collation LIKE "latin1%";
+-------------------------+---------+------+---------+----------+---------+
| Collation               | Charset | Id   | Default | Compiled | Sortlen |
+-------------------------+---------+------+---------+----------+---------+
| latin1_german1_ci       | latin1  |    5 |         | Yes      |       1 |
| latin1_swedish_ci       | latin1  |    8 | Yes     | Yes      |       1 |
| latin1_danish_ci        | latin1  |   15 |         | Yes      |       1 |
| latin1_german2_ci       | latin1  |   31 |         | Yes      |       2 |
| latin1_bin              | latin1  |   47 |         | Yes      |       1 |
| latin1_general_ci       | latin1  |   48 |         | Yes      |       1 |
| latin1_general_cs       | latin1  |   49 |         | Yes      |       1 |
| latin1_spanish_ci       | latin1  |   94 |         | Yes      |       1 |
| latin1_swedish_nopad_ci | latin1  | 1032 |         | Yes      |       1 |
| latin1_nopad_bin        | latin1  | 1071 |         | Yes      |       1 |
+-------------------------+---------+------+---------+----------+---------+
10 rows in set (0.001 sec)

建立資料庫時 COLLATE 選擇有 bin 的 (如: latin1_bin).

MariaDB [test1]> CREATE DATABASE test2 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
Query OK, 1 row affected (0.001 sec)

MariaDB [test1]> USE test2;
Database changed

建立一樣的資料表.

MariaDB [test2]> CREATE TABLE employee (Name VARCHAR(30) ,JobTitle BINARY(30) , Description TEXT);
Query OK, 0 rows affected (0.006 sec)

MariaDB [test2]> INSERT INTO employee VALUES ('Ben' , 'Engineer' , '5 years in Testing experience' ) ;
Query OK, 1 row affected (0.002 sec)

現在會發現不管欄位是 Binary 還是 VARCHAR 都是區分大小寫 ( Case sensitive ) .

MariaDB [test2]> SELECT * FROM employee WHERE Name LIKE 'Ben';
+------+--------------------------------+-------------------------------+
| Name | JobTitle                       | Description                   |
+------+--------------------------------+-------------------------------+
| Ben  | Engineer                       | 5 years in Testing experience |
+------+--------------------------------+-------------------------------+
1 row in set (0.001 sec)

MariaDB [test2]> SELECT * FROM employee WHERE Name LIKE 'ben';
Empty set (0.001 sec)

MariaDB [test2]> SELECT * FROM employee WHERE JobTitle LIKE 'Engineer%';
+------+--------------------------------+-------------------------------+
| Name | JobTitle                       | Description                   |
+------+--------------------------------+-------------------------------+
| Ben  | Engineer                       | 5 years in Testing experience |
+------+--------------------------------+-------------------------------+
1 row in set (0.001 sec)

MariaDB [test2]> SELECT * FROM employee WHERE JobTitle LIKE 'engineer%';
Empty set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料