在做資料搜尋,比對時,什麼情況會 區分大小寫 ( Case sensitive ) 或是 不區分大小寫 ( Case insensitive ).
預設建立資料庫時使用的是 latin1 ( 關於資料庫編碼請參考 – https://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)
沒有解決問題,試試搜尋本站其他內容