How to determine Chinese characters in MySQL?

Original source: Wen Guobing Welcome to share the original to Bole Toutiao

1 Introduction

In the production environment, there is often such a scenario: obtaining Chinese data. Then the question arises, how can we match Chinese characters?

This article provides two methods.

Second Demo

2.1 Environment

mysql> SHOW VARIABLES LIKE "%version%";

+-------------------------- --+--------------------------------+

| Variable_name | Value |

+------ ------------------+--------------------------------- +

| protocol_version | 10 |

| version | 5.1.73 |

| version_comment | MySQL Community Server (GPL) |

| version_compile_machine | i386 |

| version_compile_os | apple-darwin10.3.0 |

+ --------------------------+------------------------ ------+

5 rows in set (0.00 sec)

2.2 Create test table and insert test data

mysql -S /tmp/mysql_5173.sock -uroot -proot

Create test table and insert test data.

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('Wen Guobing');

Query OK, 1 row affected (0.00 sec)

Three implementations

3.1 Method 1 regular expression

mysql> SELECT * FROM user G;

**************************** 1. row * ***************************

name: robin

********************** ********** 2. row ***************************

name: Wen Guobing

2 rows in set (0.00 sec)

mysql> SELECT name,

-> CASE name REGEXP "[u0391-uFFE5]"

-> WHEN 1 THEN "is not a Chinese character"

-> ELSE "is a Chinese character "

-> END AS "Determine whether it is a Chinese character"

-> FROM user;

+-----------+------------ ------------------+

| name | Determine whether it is a Chinese character|

+-----------+------ -----------------------+

| robin | is not a Chinese character |

| Wen Guobing| is a Chinese character|

+----- ------+--------------------------------+

2 rows in set (0.00 sec)

mysql> ; SELECT name FROM user WHERE NOT (name REGEXP "[u0391-uFFE5]");

+----------+

| name |

+-------- ---+

| Wen Guobing|

+-----------+

1 rowinset(0.00 sec)

3.2 Method 2 length() and char_length()

mysql> ; SELECT name, length(name), char_length(name) FROM user;

+-----------+--------------+---- ---------------+

| name | length(name) | char_length(name) |

+----------+---- ----------+-------------------+

| robin | 5 | 5 |

| Wen Guobing | 20 | 9 |

+-----------+---------------+-------------------+

2 rows in set (0.00 sec)

mysql> SELECT name FROM user WHERE length(name) char_length(name);

+----------+

| name |

+ -----------+

| Wen Guobing|

+-----------+

1 rowinset(0.00 sec)

The above is how to determine Chinese characters in MySQL? For more related content, please pay attention to the PHP Chinese website (www.php.cn)!