MySQL匈牙利字符ORDER BY排序异常问题求助

问题背景 #

最近在处理店铺名称排序时遇到了个头疼的问题：执行SELECT name FROM shops ORDER BY name;后，返回结果是ABC shop ÁDE shop ALT shop，但按照匈牙利语的正确排序规则，应该是ABC shop ALT shop ÁDE shop——也就是A要排在Á前面，可现在两者被当成了等同字符。

我用的是MySQL 8.0.17版本，已经尝试把数据库的字符集和排序规则改成匈牙利语的：

ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_hu_0900_ai_ci;

而且查资料知道MySQL团队早在8.0.1版本就修复过类似的排序bug，但问题还是没解决。下面是我的shops表结构：

CREATE TABLE `shops` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `address_id` bigint(20) unsigned DEFAULT NULL,
  `shop_category_id` bigint(20) unsigned DEFAULT NULL,
  `shop_chain_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `partner_id` bigint(20) unsigned DEFAULT NULL,
  `location_id` bigint(20) unsigned DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `deputy_user_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `shops_address_id_foreign` (`address_id`),
  KEY `shops_shop_category_id_foreign` (`shop_category_id`),
  KEY `shops_shop_chain_id_foreign` (`shop_chain_id`),
  KEY `shops_partner_id_foreign` (`partner_id`),
  KEY `shops_location_id_foreign` (`location_id`),
  KEY `shops_user_id_foreign` (`user_id`),
  KEY `shops_deputy_user_id_foreign` (`deputy_user_id`),
  CONSTRAINT `shops_address_id_foreign` FOREIGN KEY (`address_id`) REFERENCES `addresses` (`id`) ON DELETE SET NULL,
  CONSTRAINT `shops_deputy_user_id_foreign` FOREIGN KEY (`deputy_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `shops_location_id_foreign` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE SET NULL,
  CONSTRAINT `shops_partner_id_foreign` FOREIGN KEY (`partner_id`) REFERENCES `partners` (`id`) ON DELETE CASCADE,
  CONSTRAINT `shops_shop_category_id_foreign` FOREIGN KEY (`shop_category_id`) REFERENCES `shop_categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `shops_shop_chain_id_foreign` FOREIGN KEY (`shop_chain_id`) REFERENCES `shop_chains` (`id`) ON DELETE SET NULL,
  CONSTRAINT `shops_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=564 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_hu_0900_ai_ci;

问题排查与解决步骤 #

1. 先搞清楚：数据库级修改不影响已有表

你修改了数据库的排序规则，但这个设置只对之后新建的表生效，已经存在的shops表（以及它的列）还是用的旧规则。所以第一步先检查name列实际用的排序规则：

SELECT column_name, collation_name 
FROM information_schema.columns 
WHERE table_schema = 'mydb' 
  AND table_name = 'shops' 
  AND column_name = 'name';

如果返回的collation_name不是utf8mb4_hu_0900_ai_ci，那就是列的规则没更新，这就是问题所在。

2. 关键坑点：ai_ci不区分重音！

你用的utf8mb4_hu_0900_ai_ci里的ai是**Accent Insensitive（不区分重音）**的意思，所以A和Á会被视为等同字符。而我们需要的是区分重音的排序，应该用utf8mb4_hu_0900_as_ci（as是Accent Sensitive）。

3. 执行正确的修改操作

先修改表的排序规则（会同步更新所有字符型列）：

ALTER TABLE shops CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_hu_0900_as_ci;

或者单独修改name列（更精准）：

ALTER TABLE shops MODIFY COLUMN name varchar(255) NOT NULL COLLATE utf8mb4_hu_0900_as_ci;

如果需要后续新建的表都用这个规则，再修改数据库：

ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_hu_0900_as_ci;

4. 验证结果

修改完成后，重新执行查询：

SELECT name FROM shops ORDER BY name;

这次应该就能得到期望的ABC shop ALT shop ÁDE shop排序结果了。

内容的提问来源于stack exchange，提问作者Peter Kota