【 TiDB 使用环境】测试
【 TiDB 版本】7.5.3
【复现路径】做过哪些操作出现的问题 4.0.12 升级到 7.5.3
【遇到的问题：问题现象及影响】
原4.0.12版本可以正常修改索引字段的排序规则
mysql> select version();
+---------------------+
| version() |
+---------------------+
| 5.7.25-TiDB-v4.0.12 |
+---------------------+
1 row in set (0.01 sec)
mysql> show create table nds_file_info_test\G
*************************** 1. row ***************************
Table: nds_file_info_test
Create Table: CREATE TABLE `nds_file_info_test` (
`id` varchar(40) NOT NULL COMMENT '主键',
...
`file_name` varchar(512) NOT NULL COMMENT 'file_name',
...
PRIMARY KEY (`id`),
...
KEY `idx_nds_file_info_parent_folder_id_file_name` (`parent_folder_id`,`file_name`),
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='xxx'
1 row in set (0.00 sec)
mysql> ALTER TABLE nds_file_info_test MODIFY COLUMN file_name varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'file_name';
Query OK, 0 rows affected (2.05 sec)
mysql> show create table nds_file_info_test\G
*************************** 1. row ***************************
Table: nds_file_info_test
Create Table: CREATE TABLE `nds_file_info_test` (
`id` varchar(40) NOT NULL COMMENT '主键',
...
`file_name` varchar(512) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'file_name',
...
PRIMARY KEY (`id`),
...
KEY `idx_nds_file_info_parent_folder_id_file_name` (`parent_folder_id`,`file_name`),
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='租户文件信息表'
1 row in set (0.00 sec)
但是升级到 7.5.3 版本后 执行同样的操作会报错 ：
ERROR 8200 (HY000): Unsupported modifying collation of column ‘file_name’ from ‘utf8mb4_general_ci’ to ‘utf8mb4_bin’ when index is defined on it.
mysql> select version();
+--------------------+
| version() |
+--------------------+
| 8.0.11-TiDB-v7.5.3 |
+--------------------+
1 row in set (0.00 sec)
mysql> show create table nds_file_info_test\G
*************************** 1. row ***************************
Table: nds_file_info_test
Create Table: CREATE TABLE `nds_file_info_test` (
`id` varchar(40) NOT NULL COMMENT '主键',
...
`file_name` varchar(512) COLLATE utf8mb4_general_ci NOT NULL COMMENT '文件名',
...
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
...
KEY `idx_nds_file_info_parent_folder_id_file_name` (`parent_folder_id`,`file_name`),
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='租户文件信息表'
1 row in set (0.00 sec)
mysql> ALTER TABLE nds_file_info_test MODIFY COLUMN file_name varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'file_name';
ERROR 8200 (HY000): Unsupported modifying collation of column 'file_name' from 'utf8mb4_general_ci' to 'utf8mb4_bin' when index is defined on it.
4.0.12 后面的版本是有做了什么修改吗？ 针对这个问题除了 删除索引后修改排序规则外还有没有更好的解决方法？
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】