Summary: in this tutorial, you will learn about MySQL string length functions that allow you to get the length of strings measured in bytes and in characters.

MySQL supports various character sets such as latin1, utf8, etc. You use the SHOW CHARACTER SET statement to get all character sets supported by MySQL database server.

SHOW CHARACTER SET;

The Maxlen column stores the number of bytes for character sets. In MySQL, a string can be in any character set. If a string contains 1-byte characters, its length measured in characters and its length measured in bytes are equal. However, if a string contains multi-byte characters, its length in bytes is typically greater than its length in characters.

To get the length of a string measured in bytes, you use the LENGTH function as follows:

LENGTH(str);

You use the CHAR_LENGTH function to get the length of a string measured in characters as follows:

CHAR_LENGTH(str);

Examples of LENGTH and CHAR_LENGTH functions

Let’s take a look at the following statements:

SET @s = CONVERT('MySQL String Length' USING ucs2); SELECT CHAR_LENGTH(@s), LENGTH(@s);

How it works.

• First, we convert the MySQL String Length string into ucs2 character set, which is UCS-2 Unicode that holds 2-byte characters.

• Second, we use the CHAR_LENGTH and LENGTH functions to get the length of the @s string in bytes and in characters. Because the @s string contains 2-byte characters, its length in character is 19, while its length in bytes is 38.

The following statements demonstrate how the LENGTH and CHAR_LENGTH functions that work with 1-byte characters:

SET @s = CONVERT('MySQL string length' USING latin1); SELECT LENGTH(@s), CHAR_LENGTH(@s);

We use latin1 character set for the @s string. The latin1 character set contains 1-byte characters; therefore, its length in bytes and its length in character are equal.

Notice that some character sets hold characters whose number of bytes can be varies e.g., for the utf8 character set:

SET @s = CONVERT('MySQL String Length' USING utf8); SELECT CHAR_LENGTH(@s), LENGTH(@s);

The CHAR_LENGTH and LENGTH returns the same result. However, if a string has special characters, the result is different. See the following example:

SET @s = CONVERT('á' USING utf8); SELECT CHAR_LENGTH(@s), LENGTH(@s);

An application of MySQL string length functions

Suppose we have a posts table that stores blog posts with four columns postid, title, excerpt and content. (We make the posts table as simple as possible for the demonstration purpose).

First, we create the posts table by using the CREATE TABLE statement:

CREATE TABLE posts(   postid int auto_increment primary key,   title varchar(255) NOT NULL,   excerpt varchar(255) NOT NULL,   content text,   pubdate datetime )Engine=InnoDB;

Second, we insert some blog posts into the posts table by using the INSERT statement:

INSERT INTO posts(title,excerpt,content) VALUES('MySQL Length','MySQL string length function tutorial','dummy'),       ('Second blog post','Second blog post','dummy');

We can use the CHAR_LENGTH function to check if the except has more than 20 characters, we append an ellipsis (…) to the excerpt as the following query:

SELECT postid,        title,        IF(CHAR_LENGTH(excerpt) > 20,           CONCAT(LEFT(excerpt,20), '...'),           excerpt) summary FROM posts;

In the SELECT statement, we use the IF function to check if the length of the excerpt column is greater than 20, we concatenate the excerpt with the ellipsis (…) by using the CONCAT statement, otherwise we just get the excerpt.

In this tutorial, we have shown you how use MySQL string length functions to get the length of a string in bytes and in characters.

Reference

• http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_length - MySQL LENGTH function

• http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_char-length - MySQL CHAR_LENGTH function

原文链接：http://outofmemory.cn/mysql/function/mysql-string-length