How to query the character length in mysql

Method: 1. Use the length() function to query. The unit is byte. A Chinese character is three bytes. Arrays and letters are one byte. The syntax is "length (character)"; 2. Use "char_length()" function query, the unit is character, Chinese characters, numbers and letters are all one character unit.

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

How to query the character length in mysql

First understand the difference between length() and char_length() in Mysql.

　1), length(): The length() function in mysql is a built-in function used to obtain the length of a string.
　2), char_length(): Another function to check the length of a string in the mysql built-in function is char_length().
　3) The difference between these two functions is:

a), length(): The unit is bytes. Under utf8 encoding, one Chinese character is three bytes, and one number or letter is one byte. . Under gbk encoding, one Chinese character is two bytes, and one number or letter is one byte.
b), char_length(): The unit is a character, whether it is a Chinese character, a number or a letter, it is considered a character.

Expand on the changes in the varchar type after MySQL 5.0.3 version.

　1), Before MySQL 5.0.3: 0--255 bytes, such as: 20 in varchar(20) represents the number of bytes. If the utf-8 encoding is stored, only 6 Chinese characters can be stored. varchar(n), where n represents the number of bytes.
After MySQL 5.0.3: 0--65535 bytes, varchar(20) represents the number of characters, no matter what encoding, 20 Chinese characters can be stored. But it occupies up to 65532 bytes (two bytes to store the length, if it is less than 255 bytes, use 1 byte to store the length). The n here in varchar(n) represents the number of characters, such as varchar (200), whether it is English or Chinese. Can store 200 pieces. For other specific differences, you can check online.

4. length()char_length() can be used to check whether it contains Chinese characters.

To determine that a field is in full English in utf-8 encoding, length (field) = char_length (field) can be used.

Recommended learning: mysql video tutorial

The above is the detailed content of How to query the character length in mysql. For more information, please follow other related articles on the PHP Chinese website!