原文出處： 溫國兵 歡迎分享原創到伯樂頭條
一 引子
在生產環境中，常會有這樣的場景：取得中文資料。那問題就來了，怎麼才能匹配出中文字元呢？
本文提供兩種方法。
二示範
2.1 環境
mysql> SHOW VARIABLES LIKE "%version%";
+----------------------- --+------------------------------+
| Variable_name | Value |
+------ -------------------+----------------------------- +
| protocol_version | 10 |
| version | 5.1.73 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine |386
|
+ -------------------------+------------------------ ------+
5 rows in set (0.00 sec)
2.2 建立測試表和插入測試資料
mysql -S /tmp/mysql_5173.sock -uroot -prootmysql -S /tmp/mysql_5173.sock -uroot -prootrootmysql數據。
mysql> USE test;
Database changed
mysql> CREATE TABLE user
-> (name VARCHAR(20)
. 0 rows affected ( 0.10 sec)
mysql> SHOW TABLE STATUS LIKE 'user' G;
*************************** 1. row ** *************************
Name: user
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
ate_time: 2015-01-16 18:01:36
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment: sec No query specified
mysql> INSERT INTO user VALUES( 'robin');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO user VALUES('溫國兵');
Query OK, 1 row affed (方法一正規表示式
mysql> SELECT * FROM user G;
*************************** 1. row *** ************************
name: robin
******************* ******** 2. row ***************************
name: 溫國兵
2 rows in set ( 0.00 sec)
mysql> SELECT name,
-> CASE name REGEXP "[u0391-uFFE5]"
->中文字元
-> END AS "判斷是否為中文字"
-> FROM user;
+-----------+------------------- ----------+
| name | 判斷是否為中文字元|
+-----------+------------- ----------------+
| robin | 不是中文字元|
| 溫國兵| 是中文字元|
+-----------+ -----------------------------+
2 rows in set (0.00 sec)
mysql> SELECT name FROM user WHERE NOT (name REGEXP "[u0391-uFFE5]");
+-----------+
| name |
+-----------+
|溫國兵|
+-----------+
1 rowinset(0.00 sec)
3.2 方法二length() 與char_length()
3.2 方法二length() 和char_length()
my> ), char_length(name) FROM user;
+-----------+--------------+----------- --------+
| name | length(name) | char_length(name) |
+-----------+----------- ---+-------------------+
| robin | 5 | 5 |
| 溫國兵| 20 | 9 |
+----- ------+--------------+-------------------+
2 rows in set (0.00 sec)
mysql> SELECT name FROM user WHERE length(name) char_length(name);
+-----------+
| name ----+
| 溫國兵|
+-----------+
1 rowinset(0.00 sec)
以上就是如何在 MySQL 中判斷中文字元？的內容，更多相關內容請關注PHP中文網（www.php.cn）！
