最近在做数据分析,使用了JSON字段,在数据转换的时候老是出问题(本地使用了MYSQL8的版本),后来发现是MYSQL8默认使用utf8mb4编码格式的问题,生产环境是用的MYSQL7版本。这里把格式转换规则贴出来,以供参考
类型 | 其他类型(varchar\text\blob等)转为JSON | JSON转为其他类型(varchar\text\blob等) |
JSON | 无变化 | 无变化 |
utf8 编码格式 | String 会转换成JSON | JSON 会序列化为 utf8mb4格式的 string. |
其他编码格式 | 会直接转换成utf8mb4 并按按utf8字符类型处理。 | The JSON值会首先转换为 utf8mb4 字符串, 然后再转换成其他列下. 最终结果可能是无意义的 |
NULL | 值为NULL的JSON类型 | 不支持 |
Geometry 类型 | geometry类型的值会通过函数 ST_AsGeoJSON(). 转成JSON 文档 | 报操作异常,可以通过以下两个函数转换CAST(json_val AS CHAR) to ST_GeomFromGeoJSON(). |
所有其他类型 | 生成由单个标量值组成的JSON文档。 | 如果JSON文档由目标类型的单个标量值组成,并且该标量值可以强制转换为目标类型,则会成功。否则,返回NULL并生成警告。 |
JSON值的ORDER BY和GROUP BY遵循以下原则:
- 标量JSON值的排序使用与前面讨论中相同的规则。
- 对于升序排序,SQL NULL在所有JSON值之前排序,包括JSON NULL文本;对于降序排序,SQL NULL在所有JSON值之后排序,包括JSON NULL文本。
- JSON值的排序键由max_Sort_length系统变量的值绑定,因此仅在第一个max_Sort_length字节之后就算有不同的键,比较结果也是相等。
- 当前不支持对非标量值进行排序,并出现警告。
对于排序,将JSON标量强制转换为其他本地MySQL类型是有益的。例如,如果名为jdoc的列包含JSON对象,该对象的成员由id键和非负值组成,请使用此表达式按id值排序:
ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)
如果碰巧有一个生成的列被定义为使用与ORDER BY中相同的表达式,MySQL优化器就会识别出这一点,并考虑将索引用于查询执行计划。