MySQL的`LENGTH()`与`CHAR_LENGTH()`：在多字节字符集下的区别 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL中 LENGTH() 与 CHAR_LENGTH() 在多字节字符集下的差异详解

各位朋友，大家好！今天我们来深入探讨MySQL中两个常用的字符串函数：LENGTH() 和 CHAR_LENGTH()。这两个函数在处理单字节字符集（例如latin1）时，通常表现一致，但在多字节字符集（例如utf8, utf8mb4, gbk）下，差异就显现出来了。理解这些差异对于编写健壮且能正确处理各种字符数据的SQL语句至关重要。

1. 函数定义及基本用法

首先，让我们明确一下这两个函数的定义：

LENGTH(str): 返回字符串 str 的长度，单位是字节。
CHAR_LENGTH(str) (或 CHARACTER_LENGTH(str)): 返回字符串 str 的长度，单位是字符。

这里，“字节”和“字符”是关键区别。在单字节字符集中，一个字符占用一个字节，因此两个函数的结果相同。但在多字节字符集中，一个字符可能占用多个字节，这导致了两者返回值的差异。

示例（使用 latin1 字符集）：

SET NAMES latin1;  -- 设置字符集为latin1

SELECT LENGTH('Hello'), CHAR_LENGTH('Hello');
-- 输出： LENGTH('Hello') = 5, CHAR_LENGTH('Hello') = 5

SELECT LENGTH('你好'), CHAR_LENGTH('你好');
-- 输出： LENGTH('你好') = 4, CHAR_LENGTH('你好') = 2 (因为latin1无法正确存储中文，存储的是问号等替代字符，每个占一个字节)

在上面的例子中，'Hello' 由5个单字节字符组成，因此 LENGTH() 和 CHAR_LENGTH() 都返回 5。但是，当尝试存储中文字符串'你好'时，由于latin1字符集不支持中文，它会将每个中文字符转换为一个或多个替代字符（通常是问号），每个替代字符占用一个字节。因此，LENGTH('你好') 返回 4，而 CHAR_LENGTH('你好') 返回 2 (尽管显示结果可能不正确，因为字符集不匹配)。

2. 多字节字符集的关键：编码方式

理解 LENGTH() 和 CHAR_LENGTH() 的差异，必须了解多字节字符集的编码方式。以常用的 UTF-8 为例，它是一种变长编码：

ASCII字符（A-Z, a-z, 0-9等）占用 1 个字节。
西欧字符占用 1 或 2 个字节。
东亚字符（包括中文）通常占用 3 个字节。
一些不常用的字符可能占用 4 个字节。

其他多字节字符集，例如 GBK，也采用类似的变长编码机制。GBK中，ASCII字符占用 1 个字节，汉字占用 2 个字节。

示例（使用 utf8 字符集）：

SET NAMES utf8;  -- 设置字符集为utf8

SELECT LENGTH('Hello'), CHAR_LENGTH('Hello');
-- 输出： LENGTH('Hello') = 5, CHAR_LENGTH('Hello') = 5

SELECT LENGTH('你好'), CHAR_LENGTH('你好');
-- 输出： LENGTH('你好') = 6, CHAR_LENGTH('你好') = 2 (因为utf8中，每个中文字符占用3个字节)

在这个例子中，LENGTH('你好') 返回 6，因为每个中文字符在 UTF-8 中占用 3 个字节 (3 * 2 = 6)。CHAR_LENGTH('你好') 仍然返回 2，因为它计算的是字符的个数。

3. 实际应用场景分析

接下来，我们通过几个实际的应用场景，来进一步说明 LENGTH() 和 CHAR_LENGTH() 的用法和区别。

场景 1：限制用户输入长度

假设我们有一个用户注册表单，其中包含一个“用户名”字段，我们希望限制用户名的长度在 20 个字符以内。

错误的做法： 使用 LENGTH() 来限制。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50)
);

-- 错误示例：使用LENGTH()
INSERT INTO users (username) VALUES ('你好世界你好世界你好世界你好世界'); -- 假设每个中文3字节，则6*4=24字节
-- 如果 VARCHAR(50) 使用的是 utf8，并且用户输入的是纯中文，那么即使字符数超过 20，也能成功插入，因为字节数没有超过 50。

-- 更好的做法：使用CHAR_LENGTH()进行限制
INSERT INTO users (username) VALUES (LEFT('你好世界你好世界你好世界你好世界', 20)); -- 使用LEFT截取前20个字符

在这种情况下，使用 LENGTH() 是不正确的，因为它限制的是字节数，而不是字符数。如果用户输入的是纯中文，那么即使字符数超过 20，也能成功插入，因为字节数可能没有超过 50 (VARCHAR(50) 的含义是最多存储 50 个字节)。正确的做法是使用 CHAR_LENGTH() 来判断字符数，并使用 LEFT() 函数截取前 20 个字符。

正确的做法： 使用 CHAR_LENGTH() 来限制，并结合 LEFT() 函数截取。

CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF CHAR_LENGTH(NEW.username) > 20 THEN
        SET NEW.username = LEFT(NEW.username, 20);
    END IF;
END;

上面的触发器会在插入数据之前检查 username 的字符数，如果超过 20，则截取前 20 个字符。

场景 2：计算字符串的实际显示宽度

在某些情况下，我们需要计算字符串在屏幕上的实际显示宽度。例如，在网页设计中，我们需要根据字符串的长度来调整元素的宽度。

如果字符串中包含中文等宽字符，那么仅仅使用 LENGTH() 或 CHAR_LENGTH() 都是不够的。我们需要考虑每个字符的实际宽度。一般来说，一个英文字符的宽度大约是中文的一半。

自定义函数计算显示宽度（简化版）：

DELIMITER //
CREATE FUNCTION display_width(str VARCHAR(255))
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE total_width INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    DECLARE char_code INT;

    WHILE i <= CHAR_LENGTH(str) DO
        SET char_code = ASCII(SUBSTRING(str, i, 1));

        IF char_code > 127 THEN  -- 假设大于127的字符为宽字符（中文等）
            SET total_width = total_width + 2; -- 宽字符宽度为2
        ELSE
            SET total_width = total_width + 1; -- 非宽字符宽度为1
        END IF;

        SET i = i + 1;
    END WHILE;

    RETURN total_width;
END //
DELIMITER ;

SELECT display_width('Hello, 世界');
-- 输出： 14 (Hello,  = 7, 世界 = 4, 空格 = 1, 总共 12)

这个自定义函数 display_width() 遍历字符串中的每个字符，并根据字符的 ASCII 码来判断其宽度。如果字符的 ASCII 码大于 127，则认为是宽字符（例如中文），宽度为 2；否则认为是窄字符（例如英文），宽度为 1。

注意： 这只是一个简化的示例。实际的显示宽度计算可能更复杂，需要考虑字体、字号、字符间距等因素。

场景 3：字符串截取

当我们需要截取字符串的一部分时，LENGTH() 和 CHAR_LENGTH() 的选择也会影响结果。

LEFT(str, len): 从字符串 str 的左边截取 len 个字符。这里的 len 是字符数。
SUBSTRING(str, pos, len): 从字符串 str 的位置 pos 开始截取 len 个字符。这里的 len 也是字符数。
SUBSTRING(str, pos, byte_len): 从字符串 str 的位置 pos 开始截取 byte_len 个字节。

SET NAMES utf8;

SELECT LEFT('你好世界', 2);  -- 输出： 你好
SELECT SUBSTRING('你好世界', 1, 2); -- 输出： 你好
SELECT SUBSTRING('你好世界', 1, 4); -- 输出： 你 (因为utf8每个中文3字节，4个字节只能截取到“你”字的一部分)

可以看到，LEFT() 和 SUBSTRING(str, pos, len) 都是基于字符数进行截取的。而 SUBSTRING(str, pos, byte_len) 是基于字节数进行截取的，这在处理多字节字符时需要特别小心，因为它可能截断一个字符，导致乱码。

4. 如何选择使用哪个函数？

总结一下，如何选择使用 LENGTH() 还是 CHAR_LENGTH()？

函数	返回值	适用场景
`LENGTH()`	字节数	1. 需要知道字符串占用的存储空间大小。 2. 在单字节字符集下，可以等同于 `CHAR_LENGTH()` 使用。 3. 某些底层操作，例如文件操作，可能需要字节数。
`CHAR_LENGTH()`	字符数	1. 需要知道字符串包含的字符个数。 2. 限制用户输入长度（例如用户名、密码等）。 3. 字符串截取（使用 `LEFT()`、`SUBSTRING()` 等函数时）。 4. 计算字符串的显示宽度（需要结合具体情况进行调整）。 5. 任何需要考虑字符语义的场景。

一般来说，在处理多字节字符集时，强烈建议使用 CHAR_LENGTH()，因为它更符合我们对字符串长度的直观理解。 除非你明确需要知道字符串占用的字节数，否则应该避免使用 LENGTH()。

5. 字符集设置的重要性

字符集设置是正确处理字符串的关键。MySQL 中，字符集可以在多个层次进行设置：

服务器级别： 影响所有数据库的默认字符集。
数据库级别： 影响数据库中所有表的默认字符集。
表级别： 影响表中所有列的默认字符集。
列级别： 影响单个列的字符集。
连接级别： 影响客户端与服务器之间的通信字符集。

使用 SHOW VARIABLES LIKE 'character_set_%'; 可以查看当前的字符集设置。

SHOW VARIABLES LIKE 'character_set_%';

确保所有层次的字符集设置一致，并且与你的应用程序所使用的字符集匹配，是避免乱码问题的关键。特别是连接字符集，需要与客户端的字符集保持一致。例如，如果你的应用程序使用 UTF-8 编码，那么应该将连接字符集设置为 utf8 或 utf8mb4。

6. utf8 与 utf8mb4 的选择

MySQL 中，utf8 和 utf8mb4 都是 UTF-8 字符集的实现。但是，utf8 实际上只支持 1-3 字节的 UTF-8 编码，而 utf8mb4 支持完整的 4 字节 UTF-8 编码。

这意味着，utf8 无法存储某些 Unicode 字符，例如 Emoji 表情。如果你的应用程序需要存储这些字符，那么必须使用 utf8mb4。

一般来说，建议使用 utf8mb4 作为默认字符集，因为它更完整，可以存储更多的字符。

7. 总结：选择合适的函数，关注字符集

今天我们详细讨论了MySQL中 LENGTH() 和 CHAR_LENGTH() 在多字节字符集下的差异。关键在于理解字节和字符的区别，以及不同字符集（例如 UTF-8, GBK）的编码方式。在实际应用中，选择合适的函数，并正确设置字符集，才能确保字符串处理的正确性。希望今天的讲解对大家有所帮助！

字符集选择、函数使用与数据准确性

理解字符集设置的各个层面，以及LENGTH()和CHAR_LENGTH()的区别，对于正确存储和处理字符串数据至关重要。务必根据应用需求选择合适的函数和字符集，避免潜在的乱码和数据截断问题。

MySQL中 LENGTH() 与 CHAR_LENGTH() 在多字节字符集下的差异详解

发表回复 取消回复

发表回复取消回复