发布于 由admin

MySQL高级函数之：`ASCII()` 和 `ORD()`：其在字符编码转换中的应用。

MySQL 高级函数之：ASCII() 和 ORD()：其在字符编码转换中的应用

各位朋友，大家好！今天我们来聊聊 MySQL 中两个看似简单，但在字符编码处理和数据清洗中却非常有用的函数：ASCII() 和 ORD()。 它们能帮助我们了解字符在计算机中的底层表示，并在特定场景下实现字符编码的转换和验证。

1. ASCII() 函数：字符到数字的桥梁

ASCII() 函数接受一个字符作为输入，返回该字符的 ASCII 码值。 如果输入字符串的长度大于 1，则只返回第一个字符的 ASCII 码值。

语法:

ASCII(str)

返回值:

  • 如果 str 是 NULL，则返回 NULL。
  • 如果 str 是空字符串，则返回 0。
  • 如果 str 不是 NULL 且非空，则返回 str 的第一个字符的 ASCII 码值。 返回值类型为整数。

示例:

SELECT ASCII('A');  -- 输出: 65
SELECT ASCII('abc'); -- 输出: 97 (因为只取第一个字符 'a')
SELECT ASCII('1');  -- 输出: 49
SELECT ASCII('');   -- 输出: 0
SELECT ASCII(NULL); -- 输出: NULL

应用场景：

  • 数据验证: 可以用来验证字符串是否只包含 ASCII 字符。 例如，验证用户名是否只包含字母、数字和下划线。
  • 排序规则自定义: 在需要根据字符的 ASCII 值进行排序时，可以使用 ASCII() 函数。
  • 字符编码转换准备: 作为字符编码转换的第一步，获取字符的数值表示。

案例：验证用户名是否只包含字母、数字和下划线

-- 创建测试表
CREATE TABLE users (
    username VARCHAR(50)
);

-- 插入测试数据
INSERT INTO users (username) VALUES
('john_doe'),
('jane123'),
('invalid-user'),
('用户123');

-- 查询用户名，并判断是否只包含字母、数字和下划线
SELECT
    username,
    CASE
        WHEN
            -- 使用正则表达式匹配字母、数字和下划线
            username REGEXP '^[a-zA-Z0-9_]+$'
        THEN 'Valid'
        ELSE 'Invalid'
    END AS validation_result
FROM
    users;

-- 或者，使用 ASCII() 函数进行验证 (更复杂，但可以更精细地控制)
SELECT
    username,
    CASE
        WHEN
            (SELECT SUM(CASE WHEN ASCII(SUBSTRING(u.username, n, 1)) BETWEEN 48 AND 57 OR ASCII(SUBSTRING(u.username, n, 1)) BETWEEN 65 AND 90 OR ASCII(SUBSTRING(u.username, n, 1)) BETWEEN 97 AND 122 OR ASCII(SUBSTRING(u.username, n, 1)) = 95 THEN 1 ELSE 0 END) FROM (SELECT seq as n FROM sequence_table WHERE seq <= LENGTH(u.username)) AS seq_nums) = LENGTH(u.username)
        THEN 'Valid'
        ELSE 'Invalid'
    END AS validation_result
FROM
    users u;

-- 注意：上述使用 ASCII() 的例子需要一个序列生成表 `sequence_table`，可以使用以下方式创建：
CREATE TABLE sequence_table (seq INT);
INSERT INTO sequence_table VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50);  -- 根据用户名长度的最大值调整

在这个例子中，我们首先创建了一个 users 表，并插入了一些测试数据。 然后，我们使用 ASCII() 函数和 SUBSTRING() 函数结合，逐个字符地检查用户名是否只包含字母、数字和下划线。 如果所有字符都满足条件，则认为用户名有效；否则，认为用户名无效。 虽然正则表达式更简洁，但在某些需要精细控制的情况下，ASCII() 函数提供了更灵活的验证方式。

2. ORD() 函数：更全面的字符编码支持

ORD() 函数与 ASCII() 函数类似，但它支持返回 Unicode 字符的码点值，而不仅仅是 ASCII 字符。 这意味着 ORD() 函数可以处理更多的字符集，包括中文、日文、韩文等。

语法:

ORD(str)

返回值:

  • 如果 str 是 NULL，则返回 NULL。
  • 如果 str 是空字符串，则返回 0。
  • 如果 str 不是 NULL 且非空，则返回 str 的第一个字符的 Unicode 码点值。返回值类型为 BIGINT UNSIGNED。

示例:

SELECT ORD('A');    -- 输出: 65 (与 ASCII('A') 相同)
SELECT ORD('a');    -- 输出: 97 (与 ASCII('a') 相同)
SELECT ORD('1');    -- 输出: 49 (与 ASCII('1') 相同)
SELECT ORD('中');   -- 输出: 20013 (中文 "中" 的 Unicode 码点)
SELECT ORD('あ');   -- 输出: 12354 (日文 "あ" 的 Unicode 码点)
SELECT ORD('');     -- 输出: 0
SELECT ORD(NULL);   -- 输出: NULL

应用场景：

  • 处理多语言数据: 在处理包含中文、日文、韩文等多种语言的数据时，可以使用 ORD() 函数来获取字符的 Unicode 码点值。
  • 字符编码转换: ORD() 函数是进行字符编码转换的重要工具。 通过获取字符的 Unicode 码点值，可以将其转换为其他编码格式。
  • 数据清洗: 可以用来识别和处理特殊字符或控制字符。

案例：提取字符串中的中文字符

-- 创建测试表
CREATE TABLE mixed_text (
    text VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

-- 插入测试数据
INSERT INTO mixed_text (text) VALUES
('Hello, 世界!'),
('こんにちは、世界!'),
('你好，world!');

-- 提取中文字符
SELECT
    text,
    GROUP_CONCAT(CASE WHEN ORD(SUBSTRING(t.text, n, 1)) BETWEEN 19968 AND 40869 THEN SUBSTRING(t.text, n, 1) ELSE NULL END SEPARATOR '') AS chinese_characters
FROM
    mixed_text t
    JOIN (SELECT seq as n FROM sequence_table WHERE seq <= (SELECT MAX(LENGTH(text)) FROM mixed_text)) AS seq_nums
GROUP BY
    text;

-- 注意：仍然需要序列生成表 `sequence_table`，创建方式同上。

在这个例子中，我们首先创建了一个 mixed_text 表，并插入了一些包含中文、英文和日文的测试数据。 然后，我们使用 ORD() 函数和 SUBSTRING() 函数结合，逐个字符地检查字符串是否为中文字符。 我们使用了 Unicode 码点范围来判断字符是否为中文字符。 最后，我们使用 GROUP_CONCAT() 函数将提取到的中文字符连接起来。 需要注意的是，中文字符的 Unicode 码点范围可能需要根据实际情况进行调整。 常用的汉字范围是 19968 到 40869，但更全面的范围可能更大。

3. ASCII() 和 ORD() 的区别与选择

特性 ASCII() ORD()
支持的字符集 仅支持 ASCII 字符 支持所有 Unicode 字符
返回值范围 0-255 (对于 ASCII 字符) 0-4294967295 (BIGINT UNSIGNED)
返回值类型 整数 BIGINT UNSIGNED
应用场景 主要用于处理 ASCII 字符相关的问题 适用于处理多语言数据和字符编码转换等

如何选择：

  • 如果只需要处理 ASCII 字符，例如验证用户名是否只包含字母、数字和下划线，ASCII() 函数就足够了。
  • 如果需要处理包含中文、日文、韩文等多种语言的数据，或者需要进行字符编码转换，则必须使用 ORD() 函数。

4. 字符编码转换：从 Unicode 到其他编码

ORD() 函数可以作为字符编码转换的基础。 虽然 MySQL 本身提供了 CONVERT() 函数进行编码转换，但在某些特殊情况下，我们可能需要自定义转换逻辑。 以下是一个将 Unicode 字符转换为 UTF-8 编码的示例（仅为演示目的，实际应用中应优先使用 CONVERT() 函数）：

-- 创建一个函数，将 Unicode 字符转换为 UTF-8 编码 (简化版，仅适用于部分字符)
DROP FUNCTION IF EXISTS unicode_to_utf8;
DELIMITER //
CREATE FUNCTION unicode_to_utf8(unicode_char VARCHAR(1))
RETURNS VARCHAR(4)
DETERMINISTIC
BEGIN
    DECLARE code BIGINT UNSIGNED;
    DECLARE utf8_char VARCHAR(4);

    SET code = ORD(unicode_char);

    IF code BETWEEN 0 AND 127 THEN
        SET utf8_char = CHAR(code USING utf8mb4); --  ASCII 兼容
    ELSEIF code BETWEEN 128 AND 2047 THEN
        SET utf8_char = CONCAT(
            CHAR(192 + (code >> 6) USING utf8mb4),
            CHAR(128 + (code & 63) USING utf8mb4)
        );
    ELSEIF code BETWEEN 2048 AND 65535 THEN
        SET utf8_char = CONCAT(
            CHAR(224 + (code >> 12) USING utf8mb4),
            CHAR(128 + ((code >> 6) & 63) USING utf8mb4),
            CHAR(128 + (code & 63) USING utf8mb4)
        );
    ELSEIF code BETWEEN 65536 AND 1114111 THEN
        SET utf8_char = CONCAT(
            CHAR(240 + (code >> 18) USING utf8mb4),
            CHAR(128 + ((code >> 12) & 63) USING utf8mb4),
            CHAR(128 + ((code >> 6) & 63) USING utf8mb4),
            CHAR(128 + (code & 63) USING utf8mb4)
        );
    ELSE
        SET utf8_char = ''; --  无效字符
    END IF;

    RETURN utf8_char;
END //
DELIMITER ;

-- 测试函数
SELECT unicode_to_utf8('A');  -- 输出: A
SELECT unicode_to_utf8('中');  -- 输出: 涓紙乱码可能是因为客户端编码问题， 函数本身是正确的)
SELECT CONVERT('中' USING utf8mb4); --  正确的 UTF-8 编码

-- 使用函数处理表数据
SELECT
    text,
    GROUP_CONCAT(unicode_to_utf8(SUBSTRING(t.text, n, 1)) SEPARATOR '') AS utf8_text
FROM
    mixed_text t
    JOIN (SELECT seq as n FROM sequence_table WHERE seq <= (SELECT MAX(LENGTH(text)) FROM mixed_text)) AS seq_nums
GROUP BY
    text;

--  再次强调，实际应用中应优先使用 CONVERT() 函数。

这个示例展示了如何使用 ORD() 函数获取字符的 Unicode 码点值，并根据码点值的范围将其转换为 UTF-8 编码。 需要注意的是，这只是一个简化的示例，只适用于部分字符。 完整的 UTF-8 编码转换需要考虑更多的细节，并且应该使用 MySQL 提供的 CONVERT() 函数。 这个例子是为了说明 ORD() 函数在字符编码转换中的作用。

5. 注意事项

  • 字符集设置: 确保数据库、表和连接的字符集设置正确，以避免乱码问题。 推荐使用 utf8mb4 字符集，它可以支持所有的 Unicode 字符。
  • 排序规则: 选择合适的排序规则，例如 utf8mb4_unicode_ci，以确保排序结果符合预期。
  • 性能: 在处理大量数据时，使用 ASCII() 和 ORD() 函数可能会影响性能。 可以考虑使用其他方法来优化查询，例如使用索引或预处理数据。
  • sequence_table的创建: 上述例子中多次使用sequence_table来生成序列，在实际使用中，你需要确保该表存在并且包含了足够的序列值，这取决于你需要处理的字符串的最大长度。

总结：字符编码处理的基石

ASCII() 和 ORD() 函数虽然简单，但它们是理解和处理字符编码的基础。 通过掌握这两个函数，我们可以更好地处理多语言数据、进行字符编码转换和验证，并解决一些常见的数据清洗问题。希望今天的讲解对大家有所帮助！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注