如何在 MySQL 中判断中文字符？

骑猪看日落 2022-09-22 11:47 163阅读 0赞

# 如何在 MySQL 中判断中文字符？ #

http://blog.jobbole.com/94567/

**一 引子**

在生产环境中，经常会有这样的场景：获得中文数据。那问题就来了，怎么才能匹配出中文字符呢？

本文提供两种方法。

## 二 演示 ##

### 2.1 环境 ###

MySQL

### 2.2 创建测试表和插入测试数据 ###

MySQL

mysql -S /tmp/mysql\_5173.sock -uroot -proot

<table style=""> 
 <tbody> 
  <tr> 
   <td> 
    <div style="font-size:13px!important; line-height:15px!important"> 
     <div>
       1 
     </div> 
    </div> </td> 
   <td> 
    <div style=""> 
     <div>
       mysql 
      <span> </span>-S 
      <span> </span>/tmp 
      <span>/</span>mysql_5173.sock 
      <span> </span>-uroot 
      <span> </span>-proot 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

创建测试表和插入测试数据。

MySQL

mysql> USE test; Database changed mysql> CREATE TABLE user -> (name VARCHAR(20) -> ) DEFAULT CHARSET = utf8 ENGINE = INNODB; Query OK, 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 Data\_free: 0 Auto\_increment: NULL Create\_time: 2015-01-16 18:01:36 Update\_time: NULL Check\_time: NULL Collation: utf8\_general\_ci Checksum: NULL Create\_options: Comment: 1 row in set (0.00 sec) ERROR: 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 affected (0.00 sec)

<table style=""> 
 <tbody> 
  <tr> 
   <td> 
    <div style="font-size:13px!important; line-height:15px!important"> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
     <div>
       3 
     </div> 
     <div>
       4 
     </div> 
     <div>
       5 
     </div> 
     <div>
       6 
     </div> 
     <div>
       7 
     </div> 
     <div>
       8 
     </div> 
     <div>
       9 
     </div> 
     <div>
       10 
     </div> 
     <div>
       11 
     </div> 
     <div>
       12 
     </div> 
     <div>
       13 
     </div> 
     <div>
       14 
     </div> 
     <div>
       15 
     </div> 
     <div>
       16 
     </div> 
     <div>
       17 
     </div> 
     <div>
       18 
     </div> 
     <div>
       19 
     </div> 
     <div>
       20 
     </div> 
     <div>
       21 
     </div> 
     <div>
       22 
     </div> 
     <div>
       23 
     </div> 
     <div>
       24 
     </div> 
     <div>
       25 
     </div> 
     <div>
       26 
     </div> 
     <div>
       27 
     </div> 
     <div>
       28 
     </div> 
     <div>
       29 
     </div> 
     <div>
       30 
     </div> 
     <div>
       31 
     </div> 
     <div>
       32 
     </div> 
     <div>
       33 
     </div> 
     <div>
       34 
     </div> 
     <div>
       35 
     </div> 
     <div>
       36 
     </div> 
     <div>
       37 
     </div> 
     <div>
       38 
     </div> 
    </div> </td> 
   <td> 
    <div style=""> 
     <div>
       mysql 
      <span>&gt;</span> 
      <span> </span> 
      <span>USE</span> 
      <span> </span>test; 
     </div> 
     <div> 
      <span>Database</span> 
      <span> </span> 
      <span>changed</span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div>
       mysql 
      <span>&gt;</span> 
      <span> </span> 
      <span>CREATE</span> 
      <span> </span> 
      <span>TABLE</span> 
      <span> </span> 
      <span>user</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;</span>- 
      <span>&gt;</span> 
      <span> </span>(name 
      <span> </span> 
      <span>VARCHAR</span>(20) 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;</span>- 
      <span>&gt;</span> 
      <span>&nbsp;&nbsp;</span>) 
      <span> </span> 
      <span>DEFAULT</span> 
      <span> </span> 
      <span>CHARSET</span> 
      <span> </span>= 
      <span> </span>utf8 
      <span> </span> 
      <span>ENGINE</span> 
      <span> </span>= 
      <span> </span> 
      <span>INNODB</span>; 
     </div> 
     <div>
       Query 
      <span> </span>OK, 
      <span> </span>0 
      <span> </span> 
      <span>rows</span> 
      <span> </span>affected 
      <span> </span>(0.10 
      <span> </span> sec) 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div>
       mysql 
      <span>&gt;</span> 
      <span> </span> 
      <span>SHOW</span> 
      <span> </span> 
      <span>TABLE STATUS</span> 
      <span> </span> 
      <span>LIKE</span> 
      <span> </span> 
      <span>'user'</span> 
      <span> </span>G; 
     </div> 
     <div>
       *************************** 
      <span> </span>1. 
      <span> </span> 
      <span>row</span> 
      <span> </span>*************************** 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span>Name: 
      <span> </span> 
      <span>user</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span> 
      <span>Engine</span>: 
      <span> </span> 
      <span>InnoDB</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span> 
      <span>Version</span>: 
      <span> </span>10 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp; </span> 
      <span>Row_format</span>: 
      <span> </span> 
      <span>Compact</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span> 
      <span>Rows</span>: 
      <span> </span>2 
     </div> 
     <div> 
      <span></span> 
      <span>Avg_row_length</span>: 
      <span> </span>8192 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;</span>Data_length: 
      <span> </span>16384 
     </div> 
     <div>
       Max_data_length: 
      <span> </span>0 
     </div> 
     <div> 
      <span>&nbsp;&nbsp; </span>Index_length: 
      <span> </span>0 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>Data_free: 
      <span> </span>0 
     </div> 
     <div> 
      <span></span> 
      <span>Auto_increment</span>: 
      <span> </span> 
      <span>NULL</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;</span>Create_time: 
      <span> </span>2015 
      <span>-</span>01 
      <span>-</span>16 
      <span> </span>18:01:36 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;</span>Update_time: 
      <span> </span> 
      <span>NULL</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp; </span>Check_time: 
      <span> </span> 
      <span>NULL</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span> 
      <span>Collation</span>: 
      <span> </span>utf8_general_ci 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span> 
      <span>Checksum</span>: 
      <span> </span> 
      <span>NULL</span> 
     </div> 
     <div> 
      <span></span>Create_options: 
      <span> </span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span> 
      <span>Comment</span>: 
      <span> </span> 
     </div> 
     <div>
       1 
      <span> </span> 
      <span>row</span> 
      <span> </span> 
      <span>in</span> 
      <span> </span> 
      <span>set</span> 
      <span> </span>(0.00 
      <span> </span>sec) 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div>
       ERROR: 
      <span> </span> 
     </div> 
     <div> 
      <span>No</span> 
      <span> </span>query 
      <span> </span>specified 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div>
       mysql 
      <span>&gt;</span> 
      <span> </span> 
      <span>INSERT</span> 
      <span> </span> 
      <span>INTO</span> 
      <span> </span> 
      <span>user</span> 
      <span> </span> 
      <span>VALUES</span>( 
      <span>'robin'</span>); 
     </div> 
     <div>
       Query 
      <span> </span>OK, 
      <span> </span>1 
      <span> </span> 
      <span>row</span> 
      <span> </span>affected 
      <span> </span>(0.01 
      <span> </span> sec) 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div>
       mysql 
      <span>&gt;</span> 
      <span> </span> 
      <span>INSERT</span> 
      <span> </span> 
      <span>INTO</span> 
      <span> </span> 
      <span>user</span> 
      <span> </span> 
      <span>VALUES</span>( 
      <span>'温国兵'</span>); 
     </div> 
     <div>
       Query 
      <span> </span>OK, 
      <span> </span>1 
      <span> </span> 
      <span>row</span> 
      <span> </span>affected 
      <span> </span>(0.00 
      <span> </span>sec) 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

## 三 实现 ##

### 3.1 方法一 正则表达式 ###

MySQL

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\]" -> WHEN 1 THEN "不是中文字符" -> ELSE "是中文字符" -> 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 row in set (0.00 sec)

<table style=""> 
 <tbody> 
  <tr> 
   <td> 
    <div style="font-size:13px!important; line-height:15px!important"> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
     <div>
       3 
     </div> 
     <div>
       4 
     </div> 
     <div>
       5 
     </div> 
     <div>
       6 
     </div> 
     <div>
       7 
     </div> 
     <div>
       8 
     </div> 
     <div>
       9 
     </div> 
     <div>
       10 
     </div> 
     <div>
       11 
     </div> 
     <div>
       12 
     </div> 
     <div>
       13 
     </div> 
     <div>
       14 
     </div> 
     <div>
       15 
     </div> 
     <div>
       16 
     </div> 
     <div>
       17 
     </div> 
     <div>
       18 
     </div> 
     <div>
       19 
     </div> 
     <div>
       20 
     </div> 
     <div>
       21 
     </div> 
     <div>
       22 
     </div> 
     <div>
       23 
     </div> 
     <div>
       24 
     </div> 
     <div>
       25 
     </div> 
     <div>
       26 
     </div> 
     <div>
       27 
     </div> 
     <div>
       28 
     </div> 
    </div> </td> 
   <td> 
    <div style=""> 
     <div>
       mysql 
      <span>&gt;</span> 
      <span> </span> 
      <span>SELECT</span> 
      <span> </span>* 
      <span> </span> 
      <span>FROM</span> 
      <span> </span> 
      <span>user</span> 
      <span> </span>G; 
     </div> 
     <div>
       *************************** 
      <span> </span>1. 
      <span> </span> 
      <span>row</span> 
      <span> </span>*************************** 
     </div> 
     <div>
       name: 
      <span> </span>robin 
     </div> 
     <div>
       *************************** 
      <span> </span>2. 
      <span> </span> 
      <span>row</span> 
      <span> </span>*************************** 
     </div> 
     <div>
       name: 
      <span> </span>温国兵 
     </div> 
     <div>
       2 
      <span> </span> 
      <span>rows</span> 
      <span> </span> 
      <span>in</span> 
      <span> </span> 
      <span>set</span> 
      <span> </span>(0.00 
      <span> </span>sec) 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div>
       mysql 
      <span>&gt;</span> 
      <span> </span> 
      <span>SELECT</span> 
      <span> </span>name, 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;</span>- 
      <span>&gt;</span> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp; </span> 
      <span>CASE</span> 
      <span> </span>name 
      <span> </span> 
      <span>REGEXP</span> 
      <span> </span> 
      <span>"[u0391-uFFE5]"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;</span>- 
      <span>&gt;</span> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span> 
      <span>WHEN</span> 
      <span> </span>1 
      <span> </span> 
      <span>THEN</span> 
      <span> </span> 
      <span>"不是中文字符"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;</span>- 
      <span>&gt;</span> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span> 
      <span>ELSE</span> 
      <span> </span> 
      <span>"是中文字符"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;</span>- 
      <span>&gt;</span> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp; </span> 
      <span>END</span> 
      <span> </span> 
      <span>AS</span> 
      <span> </span> 
      <span>"判断是否是中文字符"</span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;</span>- 
      <span>&gt;</span> 
      <span> </span> 
      <span>FROM</span> 
      <span> </span> 
      <span>user</span>; 
     </div> 
     <div>
       +-----------+-----------------------------+ 
     </div> 
     <div>
       | 
      <span> </span>name 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>| 
      <span> </span>判断是否是中文字符 
      <span> </span>| 
     </div> 
     <div>
       +-----------+-----------------------------+ 
     </div> 
     <div>
       | 
      <span> </span>robin 
      <span>&nbsp;&nbsp;&nbsp;&nbsp; </span>| 
      <span> </span>不是中文字符 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>| 
     </div> 
     <div>
       | 
      <span> </span>温国兵 
      <span> </span>| 
      <span> </span>是中文字符 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span>| 
     </div> 
     <div>
       +-----------+-----------------------------+ 
     </div> 
     <div>
       2 
      <span> </span> 
      <span>rows</span> 
      <span> </span> 
      <span>in</span> 
      <span> </span> 
      <span>set</span> 
      <span> </span>(0.00 
      <span> </span>sec) 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div>
       mysql 
      <span>&gt;</span> 
      <span> </span> 
      <span>SELECT</span> 
      <span> </span>name 
      <span> </span> 
      <span>FROM</span> 
      <span> </span> 
      <span>user</span> 
      <span> </span> 
      <span>WHERE</span> 
      <span> </span> 
      <span>NOT</span> 
      <span> </span>(name 
      <span> </span> 
      <span>REGEXP</span> 
      <span> </span> 
      <span>"[u0391-uFFE5]"</span>); 
     </div> 
     <div>
       +-----------+ 
     </div> 
     <div>
       | 
      <span> </span>name 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>| 
     </div> 
     <div>
       +-----------+ 
     </div> 
     <div>
       | 
      <span> </span>温国兵 
      <span> </span>| 
     </div> 
     <div>
       +-----------+ 
     </div> 
     <div>
       1 
      <span> </span> 
      <span>row</span> 
      <span> </span> 
      <span>in</span> 
      <span> </span> 
      <span>set</span> 
      <span> </span>(0.00 
      <span> </span>sec) 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

### 3.2 方法二 length() 和 char\_length() ###

MySQL

mysql> SELECT name, length(name), 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 row in set (0.00 sec)

## 四 总结 ##

方法一中，`[u0391-uFFE5]` 匹配中文以外的字符。

方法二中，当字符集为UTF-8，并且字符为中文时，`length()` 和 `char_length()` 两个方法返回的结果不相同。

参考官方文档：

> **LENGTH()**  
> Return the length of a string in bytes  
> Returns the length of the string str, measured in bytes. A multibyte character counts as multiple bytes. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR\_LENGTH() returns 5.

> **CHAR\_LENGTH()**  
> Return number of characters in argument  
> Returns the length of the string str, measured in characters. A multibyte character counts as a single character. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR\_LENGTH() returns 5.

## 五 Ref ##

*  [12.5 String Functions][]

[12.5 String Functions]: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html