Introduction

MySQL exposes two similarly named string functions that solve different problems. LENGTH() returns the number of bytes in a string, while CHAR_LENGTH() returns the number of characters. That distinction is easy to miss with plain ASCII text and very important once your data contains multibyte characters.

What LENGTH() Measures

LENGTH() tells you how many bytes a value occupies in its current encoding. For ASCII text, one character is usually one byte, so the result looks intuitive:

SELECT LENGTH('hello') AS byte_count;

The result is 5. If you only test with English letters and digits, it is tempting to conclude that LENGTH() means "string length" in the everyday sense. That is the trap.

Byte count is useful when the storage or transport limit is byte-based. Examples include:

• checking payload sizes before exporting data
• estimating row or message size
• debugging encoding issues

In those situations, LENGTH() is the correct tool because the system cares about bytes, not what a human sees on screen.

What CHAR_LENGTH() Measures

CHAR_LENGTH() answers a different question: how many characters are in the string. It is sometimes easier to think of it as the user-facing text length.

SELECT CHAR_LENGTH('hello') AS character_count;

The result is also 5, which again makes both functions look interchangeable. They are not interchangeable once the character set allows multibyte data.

This function is the right choice when the rule is written in characters, such as:

• usernames up to 20 characters
• titles up to 100 characters
• validation based on visible text length

If the business rule is about what the user typed, CHAR_LENGTH() is usually what you want.

Where the Difference Appears

The difference becomes obvious with utf8mb4, where a single character may use more than one byte. Accented characters and emoji are common examples.

1SELECT
2  LENGTH(_utf8mb4 'é') AS bytes_for_e_acute,
3  CHAR_LENGTH(_utf8mb4 'é') AS chars_for_e_acute,
4  LENGTH(_utf8mb4 '😀') AS bytes_for_emoji,
5  CHAR_LENGTH(_utf8mb4 '😀') AS chars_for_emoji;

On a multibyte character set, the accented letter and the emoji each count as one character, but their byte counts are larger. That is exactly why code that uses LENGTH() for user-facing validation tends to reject multilingual input too early.

Another practical example is a table with utf8mb4 names:

1CREATE TABLE users (
2  id INT PRIMARY KEY AUTO_INCREMENT,
3  display_name VARCHAR(50) CHARACTER SET utf8mb4
4);
5
6INSERT INTO users (display_name)
7VALUES ('Ana'), ('José'), ('李雷'), ('😀dev');
8
9SELECT
10  display_name,
11  LENGTH(display_name) AS bytes_used,
12  CHAR_LENGTH(display_name) AS characters_used
13FROM users;

The result shows the same visible text can occupy different numbers of bytes depending on the characters involved.

Choose the Function Based on the Rule

A clean way to decide is to ask what is being constrained.

If the requirement says "maximum 30 characters", use CHAR_LENGTH():

SELECT display_name
FROM users
WHERE CHAR_LENGTH(display_name) > 30;

If the requirement says "must fit in a 64-byte external field", use LENGTH():

SELECT display_name
FROM users
WHERE LENGTH(display_name) > 64;

Those queries may return different rows, and that is correct. One checks text length from a human perspective, and the other checks storage size.

Character Set and Collation Still Matter

The byte result from LENGTH() depends on the string's character set. The same visible character can occupy different byte counts under different encodings. That means byte-based logic is only meaningful if you also know the encoding being used by the column or literal.

For modern MySQL applications, utf8mb4 is the usual default assumption. Under that character set, it is normal for byte count and character count to diverge. If you are validating multilingual input, test with real sample data rather than only ASCII examples.

You should also be careful when writing documentation or helper functions for teammates. A helper named get_length without explaining whether it means bytes or characters is an invitation for bugs.

Common Pitfalls

The most common error is using LENGTH() to enforce a character limit because the name sounds like the obvious choice. Another is testing with only ASCII data and missing the difference until real users enter accented text, CJK characters, or emoji. Teams also sometimes use CHAR_LENGTH() when the real downstream limit is byte-based, which can produce export or protocol failures later. A final problem is ignoring the column character set, because the byte count from LENGTH() has to be interpreted in that encoding context.

Summary

• 'LENGTH() returns the number of bytes in the string.'
• 'CHAR_LENGTH() returns the number of characters in the string.'
• The two functions often match for ASCII and diverge for multibyte text such as utf8mb4.
• Use CHAR_LENGTH() for user-facing text validation.
• Use LENGTH() for byte-based storage, protocol, or payload limits.