AWS Aurora MySQL 5.7将MEDIUMTEXT改JSON类型时报Cannot CAST错误

1. 字符集不兼容：Aurora MySQL 5.7的JSON类型默认使用utf8mb4字符集进行存储和解析，你的场景存在两层字符集不匹配：
   • 实例默认字符集为latin1，执行读写操作时会默认将JSON值转换为latin1编码，编码转换过程中会出现乱码，导致后续JSON解析失败
   • 目标表默认字符集为utf8（即MySQL专属的utf8mb3，仅支持最多3字节的Unicode字符），无法存储emoji、生僻汉字等4字节Unicode字符，原MEDIUMTEXT字段中的这类字符写入JSON字段时会被截断损坏，后续读取解析时就会抛出Cannot CAST value to JSON错误
2. 转换操作不规范：你在更新JSON字段时仅做了JSON_VALID校验就直接赋值字符串，没有显式做字符集转换和JSON类型转换，依赖隐式转换的过程放大了字符集不兼容的问题，部分合法JSON字符串也会被错误识别为非法格式。

尚未完成字段转换的场景，按以下步骤执行正确转换 #

1. 先修改当前会话字符集为utf8mb4，避免会话层面的编码转换干扰：

SET NAMES utf8mb4;

2. 新增JSON类型的过渡字段，显式指定字符集为utf8mb4：

ALTER TABLE table_sample ADD COLUMN config2 JSON DEFAULT NULL CHARSET=utf8mb4;

3. 数据迁移时先将原MEDIUMTEXT字段转换为utf8mb4编码，校验合法后再显式转换为JSON类型写入：

UPDATE table_sample
SET config2 = CAST(CONVERT(config USING utf8mb4) AS JSON)
WHERE JSON_VALID(CONVERT(config USING utf8mb4)) = 1;

4. （推荐）修改目标表的默认字符集为utf8mb4，避免后续新写入数据出现编码问题：

ALTER TABLE table_sample DEFAULT CHARSET=utf8mb4;

5. 替换原有字段：

ALTER TABLE table_sample DROP COLUMN config;
ALTER TABLE table_sample CHANGE COLUMN config2 config JSON NOT NULL; -- 可根据业务需要调整非空约束

已完成错误转换的场景 #

首先从备份恢复原有存储MEDIUMTEXT类型config字段的表数据，再按照上面的正确步骤重新执行字段类型转换即可。

临时解决现有表的插入报错问题 #

如果暂时不想修改表结构和重跑数据，执行读写JSON字段的操作前，先修改当前会话字符集为utf8mb4，同时显式做JSON类型转换即可：

SET NAMES utf8mb4;
INSERT INTO table_sample (config)
SELECT CAST(config AS JSON) FROM table_sample WHERE id = 1;

内容的提问来源于stack exchange，提问作者Joseph Kane