MySQL高级函数之：`LENGTH()` 和 `CHAR_LENGTH()`：其在不同编码下计算长度的区别。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL高级函数之：`LENGTH()` 和 `CHAR_LENGTH()`：不同编码下计算长度的区别

大家好，今天我们要深入探讨MySQL中两个常用的字符串长度函数：LENGTH()和CHAR_LENGTH()。虽然它们都用于获取字符串的长度，但在处理不同字符编码时，它们的行为却有所不同。理解这些差异对于编写健壮且能正确处理各种语言数据的MySQL应用程序至关重要。

1. 函数定义与基本用法

LENGTH(str): 返回字符串 str 的字节长度。也就是说，它计算的是字符串占用的字节数，而不是字符数。
CHAR_LENGTH(str) 或 CHARACTER_LENGTH(str): 返回字符串 str 的字符长度。它计算的是字符串中包含的字符数，无论这些字符占用多少字节。

让我们通过一些简单的例子来理解它们的基本用法：

SELECT LENGTH('hello');       -- 输出: 5
SELECT CHAR_LENGTH('hello');  -- 输出: 5

SELECT LENGTH('你好');       -- 输出: 6 (假设数据库默认编码是UTF-8)
SELECT CHAR_LENGTH('你好');  -- 输出: 2

在上面的例子中，对于纯ASCII字符串 "hello"，LENGTH() 和 CHAR_LENGTH() 的结果相同，都是 5。然而，对于包含中文的字符串 "你好"，由于中文在 UTF-8 编码中通常占用 3 个字节，LENGTH() 返回 6，而 CHAR_LENGTH() 返回 2，表示字符串包含 2 个字符。

2. 字符编码的影响

LENGTH() 和 CHAR_LENGTH() 差异的关键在于字符编码。 MySQL 数据库，表，列以及字符串字面量都具有字符集属性。这个字符集决定了字符串的存储方式和字节长度。

以下是一些常见的字符集及其特性：

字符集	描述	字符长度 (字节)	适用场景
`ascii`	仅包含 128 个基本 ASCII 字符。	1	英文环境，对存储空间要求严格。
`latin1`	也称为 ISO-8859-1，包含 ASCII 字符以及一些西欧字符。	1	西欧语言环境。
`gbk`	简体中文编码，使用 1 或 2 个字节表示一个字符。	1-2	简体中文环境，兼容 ASCII。
`utf8mb3`	MySQL 中 `utf8` 的别名，也被称为 `utf8`，每个字符最多使用 3 个字节。	1-3	支持大部分常用字符，包括中文，但不完全支持所有 Unicode 字符。
`utf8mb4`	完整的 UTF-8 编码，每个字符最多使用 4 个字节。	1-4	支持所有 Unicode 字符，包括表情符号等。
`ucs2`	使用 2 个字节表示 Unicode 字符。	2	已经被 `utf8mb4` 取代。
`utf16`	使用 2 或 4 个字节表示 Unicode 字符。	2-4	较少使用。
`utf32`	使用 4 个字节表示 Unicode 字符。	4	存储空间占用大，但字符处理简单。

重要提示: 在 MySQL 8.0 之前，utf8 实际上是 utf8mb3 的别名，这意味着它只能存储最多 3 字节的 UTF-8 字符。如果你的数据库版本较旧，并且需要存储所有 Unicode 字符（例如，包含表情符号），请务必使用 utf8mb4。

3. 实践案例分析

为了更清楚地展示 LENGTH() 和 CHAR_LENGTH() 的区别，我们来创建一些示例表，并使用不同的字符集：

案例 1: latin1 字符集

CREATE TABLE latin1_test (
    id INT PRIMARY KEY,
    name VARCHAR(20) CHARACTER SET latin1
);

INSERT INTO latin1_test (id, name) VALUES (1, 'hello');
INSERT INTO latin1_test (id, name) VALUES (2, 'André'); -- 包含特殊字符

SELECT id, name, LENGTH(name), CHAR_LENGTH(name) FROM latin1_test;

结果：

id	name	LENGTH(name)	CHAR_LENGTH(name)
1	hello	5	5
2	André	5	5

在这个例子中，由于 latin1 使用单字节编码，即使字符串包含特殊字符 ‘é’，LENGTH() 和 CHAR_LENGTH() 的结果仍然相同。

案例 2: utf8mb4 字符集

CREATE TABLE utf8mb4_test (
    id INT PRIMARY KEY,
    name VARCHAR(20) CHARACTER SET utf8mb4
);

INSERT INTO utf8mb4_test (id, name) VALUES (1, 'hello');
INSERT INTO utf8mb4_test (id, name) VALUES (2, '你好');
INSERT INTO utf8mb4_test (id, name) VALUES (3, '😃'); -- 表情符号

SELECT id, name, LENGTH(name), CHAR_LENGTH(name) FROM utf8mb4_test;

结果：

id	name	LENGTH(name)	CHAR_LENGTH(name)
1	hello	5	5
2	你好	6	2
3	😃	4	1

在这个例子中，utf8mb4 字符集能够正确处理中文字符和表情符号。对于 "你好"，LENGTH() 返回 6 (每个汉字占用 3 个字节)，而 CHAR_LENGTH() 返回 2。对于表情符号 "😃"，LENGTH() 返回 4 (表情符号占用 4 个字节)，而 CHAR_LENGTH() 返回 1。

案例 3: 混合使用 utf8mb4 和 latin1

CREATE TABLE mixed_test (
    id INT PRIMARY KEY,
    name_utf8mb4 VARCHAR(20) CHARACTER SET utf8mb4,
    name_latin1 VARCHAR(20) CHARACTER SET latin1
);

INSERT INTO mixed_test (id, name_utf8mb4, name_latin1) VALUES (1, '你好', '你好'); -- 注意这里'你好'会被隐式转换为latin1

SELECT
    id,
    LENGTH(name_utf8mb4),
    CHAR_LENGTH(name_utf8mb4),
    LENGTH(name_latin1),
    CHAR_LENGTH(name_latin1)
FROM mixed_test;

结果：

id	LENGTH(name_utf8mb4)	CHAR_LENGTH(name_utf8mb4)	LENGTH(name_latin1)	CHAR_LENGTH(name_latin1)
1	6	2	2	2

这个例子展示了在同一张表中，不同列使用不同的字符集时，LENGTH() 和 CHAR_LENGTH() 的结果差异。因为 name_latin1 使用 latin1 编码，而 latin1 并不支持中文，所以在插入数据时， MySQL 会尝试将 ‘你好’ 转换为 latin1。由于无法完全转换，通常会用 ‘??’ 代替，每个 ‘?’ 占用一个字节。因此，LENGTH(name_latin1) 返回 2，CHAR_LENGTH(name_latin1) 返回 2。而 name_utf8mb4 正确存储了中文，所以 LENGTH(name_utf8mb4) 和 CHAR_LENGTH(name_utf8mb4) 的结果如前所述。

4. 常见应用场景

理解 LENGTH() 和 CHAR_LENGTH() 的区别，可以帮助我们解决以下实际问题：

数据验证: 限制用户输入的字符串长度，例如用户名或评论。如果需要限制字符数 (例如，微博字数限制)，应该使用 CHAR_LENGTH()。如果需要限制字节数 (例如，数据库字段长度限制)，应该使用 LENGTH()。
字符串截取: 在截取字符串时，需要考虑字符编码。 LEFT(), RIGHT(), SUBSTRING() 等函数在处理多字节字符时，可能需要配合 CHAR_LENGTH() 来确保截取的字符数正确。
排序和比较: 字符编码会影响字符串的排序和比较。如果需要按照字符顺序排序，需要确保使用正确的字符集和排序规则 (collation)。
计算存储空间: 了解字符串的字节长度，可以帮助我们评估数据库的存储空间需求。

5. 注意事项和最佳实践

始终指定字符集: 在创建数据库、表和列时，始终明确指定字符集。推荐使用 utf8mb4 字符集，以支持所有 Unicode 字符。
保持字符集一致: 尽量保持数据库、表、列以及应用程序使用的字符集一致，避免字符集转换带来的问题。
使用正确的函数: 根据实际需求选择 LENGTH() 或 CHAR_LENGTH()。如果需要处理字符数，应该使用 CHAR_LENGTH()。
测试不同字符: 在开发和测试过程中，应该使用包含各种语言字符的字符串进行测试，以确保应用程序能够正确处理多语言数据。
了解 collation: collation 定义了字符串的排序和比较规则。选择合适的 collation 可以确保字符串按照预期的顺序排序。例如，utf8mb4_unicode_ci 不区分大小写，而 utf8mb4_bin 区分大小写。

6. 示例：字符串截取

假设我们需要从一个包含中英文混合的字符串中截取前 5 个字符。直接使用 LEFT() 函数可能会导致截取结果不正确：

SELECT LEFT('你好world', 5); --  如果数据库编码是utf8mb4，结果可能是 '你好w'， 包含了 3个汉字 + 1个英文字符

为了确保截取的是 5 个字符，可以使用 CHAR_LENGTH() 和 LEFT() 函数结合：

SELECT LEFT('你好world', 5);  -- 结果是 '你好wor' （3个汉字 + 3个英文字符）

虽然结果看起来还是不对，但是已经避免了截断汉字的情况，要完全正确的截取，需要自定义函数或存储过程，实现基于字符的截取功能。这里展示一种使用存储过程实现的方法:

DELIMITER //
CREATE PROCEDURE substring_by_char(
    IN input_string VARCHAR(255),
    IN length INT,
    OUT result VARCHAR(255)
)
BEGIN
    SET @i = 1;
    SET @result = '';
    WHILE @i <= length DO
        SET @result = CONCAT(@result, SUBSTRING(input_string, @i, 1));
        SET @i = @i + 1;
    END WHILE;
    SET result = @result;
END //
DELIMITER ;

-- 调用存储过程
CALL substring_by_char('你好world', 5, @output);
SELECT @output; -- 输出: 你好wor

7. 示例：验证字符串长度

假设我们需要验证用户输入的用户名长度是否在 6 到 20 个字符之间。应该使用 CHAR_LENGTH() 函数：

SELECT CHAR_LENGTH('testuser');  -- 输出: 8

-- 验证用户名长度
SELECT IF(CHAR_LENGTH('testuser') BETWEEN 6 AND 20, 'Valid', 'Invalid'); -- 输出: Valid

如果使用 LENGTH() 函数，对于包含中文的用户名，可能会导致验证结果不正确。

8. 关于不同MySQL版本的影响

如前所述，MySQL 8.0 之前的 utf8 实际上是 utf8mb3。这意味着它无法存储所有的 Unicode 字符。如果你的应用程序需要存储表情符号或其他 4 字节的 Unicode 字符，并且你使用的是 MySQL 8.0 之前的版本，你需要升级到 MySQL 8.0 或更高版本，并将字符集更改为 utf8mb4。

9. 总结：字符编码，函数选择，应用场景

理解 LENGTH() 和 CHAR_LENGTH() 在不同字符编码下的区别至关重要。选择正确的函数，并始终明确指定字符集，才能确保应用程序能够正确处理多语言数据，并避免潜在的问题。在进行字符串长度验证、截取和排序等操作时，务必考虑到字符编码的影响。

MySQL高级函数之：LENGTH() 和 CHAR_LENGTH()：不同编码下计算长度的区别

发表回复 取消回复

MySQL高级函数之：`LENGTH()` 和 `CHAR_LENGTH()`：不同编码下计算长度的区别

发表回复取消回复