基于 utf8mb4_general_ci 排序规则的详细分析

基于 utf8mb4_general_ci 排序规则的详细分析

根据您提供的系统信息，我们深入分析utf8mb4_general_ci排序规则下的排序行为。

1. utf8mb4_general_ci 的特性

字符归类（权重）规则：

-- 查看字符的权重SELECT'a'ASchar,WEIGHT_STRING('a'COLLATEutf8mb4_general_ci)ASweight_hexUNIONALLSELECT'A',WEIGHT_STRING('A'COLLATEutf8mb4_general_ci)UNIONALLSELECT'À',WEIGHT_STRING('À'COLLATEutf8mb4_general_ci)UNIONALLSELECT'á',WEIGHT_STRING('á'COLLATEutf8mb4_general_ci);-- 结果示例：-- a -> 0x0041-- A -> 0x0041-- À -> 0x0041-- á -> 0x0041-- 说明：a, A, À, á 在 general_ci 中权重相同

2. 实际排序行为测试

2.1 创建测试表

-- 使用系统默认校对规则CREATETABLEsort_demo_ci(idINTAUTO_INCREMENTPRIMARYKEY,char_valVARCHAR(10));INSERTINTOsort_demo_ci(char_val)VALUES('a'),('A'),('B'),('b'),('1'),('2'),('10'),('20'),('!'),('@'),('#'),('$'),('apple'),('Apple'),('APPLE'),('ä'),('Ä'),('À'),('à'),('test'),('Test'),('TEST'),('中'),('国'),('文'),('字');-- 查询实际排序SELECTchar_val,HEX(WEIGHT_STRING(char_valCOLLATEutf8mb4_general_ci))asweightFROMsort_demo_ciORDERBYchar_valLIMIT20;

2.2 排序结果分析

在utf8mb4_general_ci规则下，排序顺序大致为：

1. 特殊符号：! @ # $ % ^ & * ( ) 等 2. 数字：0-9（按字符串比较，非数值） 3. 字母：A-Z 和 a-z（不区分大小写，但有特定顺序） 4. 扩展拉丁字符：ä, Ä, à, À 等（被视为等于基本字母） 5. 中文字符：按Unicode码点排序

3. 与 ASCII 排序的差异对比

-- 创建二进制排序的对比表CREATETABLEsort_demo_bin(char_valVARCHAR(10))COLLATEutf8mb4_bin;INSERTINTOsort_demo_binVALUES('a'),('A'),('B'),('b'),('1'),('2'),('!'),('apple'),('Apple'),('APPLE');-- 对比不同规则的排序SET@test_str:='aAbB1!';-- 生成每个字符的对比SELECTc.char,ASCII(c.char)asascii_val,c.general_order,c.bin_orderFROM(SELECTSUBSTRING(@test_str,n,1)aschar,FIND_IN_SET(SUBSTRING(@test_str,n,1),GROUP_CONCAT(DISTINCTSUBSTRING(@test_str,seq.n,1)ORDERBYSUBSTRING(@test_str,seq.n,1)COLLATEutf8mb4_general_ci SEPARATOR','))asgeneral_order,FIND_IN_SET(SUBSTRING(@test_str,n,1),GROUP_CONCAT(DISTINCTSUBSTRING(@test_str,seq.n,1)ORDERBYSUBSTRING(@test_str,seq.n,1)COLLATEutf8mb4_bin SEPARATOR','))asbin_orderFROM(SELECT1asnUNIONSELECT2UNIONSELECT3UNIONSELECT4UNIONSELECT5UNIONSELECT6)asseqCROSSJOIN(SELECT@test_str)astWHEREn<=LENGTH(@test_str)GROUPBYn)ascORDERBYc.general_order;

4. 常见字符排序测试

测试1：大小写混合

SELECT'Test Results for Case Sensitivity:'astest;SELECT'A'aschar1,'a'aschar2,'A'='a'COLLATEutf8mb4_general_ciasequal_in_general,'A'='a'COLLATEutf8mb4_binasequal_in_binUNIONALLSELECT'Ä','ä','Ä'='ä'COLLATEutf8mb4_general_ci,'Ä'='ä'COLLATEutf8mb4_bin;

测试2：数字与字符串

-- 数字字符串排序（按字符，不按数值）SELECTvalFROM(SELECT'1'asvalUNIONALLSELECT'2'UNIONALLSELECT'10'UNIONALLSELECT'20'UNIONALLSELECT'100')tORDERBYvalCOLLATEutf8mb4_general_ci;-- 结果：1, 10, 100, 2, 20

测试3：扩展字符处理

-- 查看扩展字符的权重SELECTchar_val,ASCII(char_val)asascii_val,ORD(char_val)asunicode_val,HEX(char_val)ashex_valFROM(SELECT'a'aschar_valUNIONALLSELECT'á'UNIONALLSELECT'à'UNIONALLSELECT'ä'UNIONALLSELECT'â')charsORDERBYchar_valCOLLATEutf8mb4_general_ci;

5. 中文字符排序测试

-- 中文排序测试CREATETABLEchinese_sort(chinese_charVARCHAR(10))COLLATEutf8mb4_general_ci;INSERTINTOchinese_sortVALUES('中'),('国'),('人'),('民'),('共'),('和'),('a'),('b'),('1'),('2'),('A'),('B');-- 观察排序结果SELECTchinese_char,HEX(chinese_char)ashex_code,CONV(HEX(chinese_char),16,10)asdecimal_codeFROMchinese_sortORDERBYchinese_char;

在utf8mb4_general_ci中，中文字符按照Unicode码点排序，大致顺序：

1. 基本拉丁字符（ASCII）
2. 拉丁扩展字符
3. 中文字符（按Unicode顺序，通常是按部首笔画）

6. 性能与准确性对比

6.1 排序规则比较

-- 创建测试数据CREATETABLEperformance_testASSELECTUUID()asrandom_stringFROMinformation_schema.columnsc1,information_schema.columnsc2LIMIT10000;-- 测试不同排序规则的性能SELECT'utf8mb4_general_ci'ascollation_name,COUNT(*)ascountFROMperformance_testORDERBYrandom_stringCOLLATEutf8mb4_general_ci;SELECT'utf8mb4_bin'ascollation_name,COUNT(*)ascountFROMperformance_testORDERBYrandom_stringCOLLATEutf8mb4_bin;

6.2 准确性测试

-- 检查哪些字符在general_ci中被视为相同SELECTc1.char_valaschar1,c2.char_valaschar2,CASEWHENc1.char_val=c2.char_valCOLLATEutf8mb4_general_ciTHEN'Equal'ELSE'Different'ENDascomparisonFROM(SELECT'cafe'aschar_valUNIONSELECT'café')c1CROSSJOIN(SELECT'cafe'aschar_valUNIONSELECT'café')c2WHEREc1.char_val<c2.char_val;

7. 实际应用建议

7.1 何时使用 general_ci

-- 适合场景：不区分大小写的搜索SELECT*FROMusersWHEREusername='john_doe'ORusername='John_Doe'ORusername='JOHN_DOE';-- 在 general_ci 下，以上都会匹配

7.2 何时使用 binary/binary_ci

-- 适合场景：区分大小写，如密码、验证码ALTERTABLEusersMODIFYCOLUMNpassword_hashVARCHAR(255)COLLATEutf8mb4_bin;-- 或者创建表时指定CREATETABLEsensitive_data(api_keyVARCHAR(100)COLLATEutf8mb4_bin,tokenVARCHAR(255)COLLATEutf8mb4_bin);

7.3 混合使用

-- 在同一个查询中使用不同排序规则SELECTusername,email,CASEWHENusername='admin'COLLATEutf8mb4_binTHEN'Administrator'ELSE'User'ENDasroleFROMusersORDERBYusernameCOLLATEutf8mb4_general_ci,-- 不区分大小写排序emailCOLLATEutf8mb4_bin;-- 区分大小写排序

8. 验证系统默认行为

-- 查看系统当前设置的影响SHOWVARIABLESWHEREVariable_nameLIKE'collation%';-- 查看当前连接的设置SELECT@@character_set_connectionaschar_set,@@collation_connectionascollation;-- 测试当前连接的默认排序SELECT'a'='A'asequality_test;-- 在 general_ci 下返回 1 (true)

9. 自定义排序顺序

-- 如果需要特定的排序顺序SELECTproduct_nameFROMproductsORDERBYCASEWHENproduct_nameLIKE'A%'THEN1WHENproduct_nameLIKE'B%'THEN2ELSE3END,product_nameCOLLATEutf8mb4_general_ci;-- 或者创建自定义排序规则SELECTitem,FIELD(item,'Special','High','Medium','Low')aspriorityFROMtasksORDERBYFIELD(item,'Special','High','Medium','Low'),item;

10. 总结：您的系统排序规则行为

基于您的系统设置（utf8mb4_general_ci）：

ORDER BY ASC 的默认行为：

1. 大小写不敏感：‘a’ = ‘A’ = ‘À’ = ‘á’
2. 基本ASCII顺序：特殊符号 < 数字 < 字母
3. 扩展字符处理：大部分重音字符被视为基本字母
4. 数字按字符串比较：‘10’ < ‘2’
5. 中文按Unicode码点：非拼音顺序
6. 性能优先：比utf8mb4_unicode_ci快，但准确性稍低

ASCII 码 vs utf8mb4_general_ci 对比表：

重要结论：

1. 不是纯ASCII排序，而是基于权重（weight）的排序
2. 大小写字母权重相同，但内部有特定顺序
3. 对非ASCII字符的处理简化，不严格按Unicode标准
4. 在您的MariaDB系统中，ORDER BY ASC使用这种规则

建议调整：

如果您需要：

• 区分大小写：使用COLLATE utf8mb4_bin
• 更准确的多语言排序：使用COLLATE utf8mb4_unicode_ci
• 中文拼音排序：考虑使用utf8mb4_zh_0900_as_cs（如可用）

-- 修改表的排序规则ALTERTABLEyour_tableCONVERTTOCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;

在您的系统中，默认的ORDER BY ASC是基于utf8mb4_general_ci权重系统的排序，不是纯ASCII码排序，但ASCII字符基本保持ASCII顺序（除了大小写不敏感）。