MySQL怎么存文本不乱码?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

转自:http://database.51cto.com/art/201712/560537.htm

1、只修改字符集(使用默认校验集)

 
  1. yejr@imysql.com> alter table t1 convert to character set utf8mb4 

2、同时修改表字符集和校验集

 
  1. yejr@imysql.com> alter table t1 convert to  character set utf8mb4 collate utf8mb4_bin; 

3、只修改某列的字符集

 
  1. yejr@imysql.com> alter table t1 modify c1 varchar(20)  character set utf8mb4 not null default ‘’ 

4、同时修改某列的字符集和校验集

 
  1. yejr@imysql.com> alter table t1 modify c1 varchar(20)  character set utf8mb4  collate utf8mb4_unicode_ci  not null default ‘’ 

好了,有个字符集为UTF8MB4的表中想存储各类不同字符集的文本,有哪些注意事项亿避免乱码?

如果是通过WEB接口存储数据,则建议在browser端、server端全都采用UTF8字符集,MySQL Server端采用UTF8/UTF8MB4均可(针对大多数文本,其实UTF8字符集就足够存储的了)。

其中,MySQL端的字符集设置比较让人头大,涉及到的字符集有好几个:

  • character_set_server,server端默认字符集;
  • character_set_database,database默认字符集,若未设定,则和 character_set_server 的设定一样;database中的 数据表/stored procedure/stored function 也可以自行设定字符集,若未指定,则和 character_set_database 的设置一样;数据表中的字符类型列,也可以单独设定字符集,若未设定,则和该表指定的字符集一样;
  • character_set_client,客户端显示读取结果的字符集;
  • character_set_connection,客户端从server端读取数据时传输字符集;
  • character_set_results,server端将数据发送给客户端时的字符集;

可见,涉及到字符集的因素实在太多,因此我们强烈建议各个环节全部采用同一种字符集,避免出现意外状况。

MySQL采用UTF8MB4字符集时,存储文本实际消耗字节数是由文本内容的字节数决定的,并非总是需要4字节,列举几种情况:

  • 输入字符集任意,且存储ASCII字符时,每个字符需要1byte;
  • 输入字符集是GB2312,且存储的字符是汉字时,每个字符需要2bytes;
  • 输入字符集是UTF8/UTF8MB4,且存储的字符是低编码汉字时,每个字符需要3bytes;
  • 输入字符集是UTF8/UTF8MB4,且存储的字符是高编码汉字时,每个字符需要4bytes;
  • 输入字符集是binary,且存储的字符是高编码汉字时,每个字符需要4bytes;

总结建议

  1. 从前端到后端(浏览器=>WEB Server=>MySQL连接层=>Server层=>DB层>TABLE层),尽可能使用同一种字符集;
  2. 尽可能采用大字符集,也就是优先级:UTF8Mb4 > UTF8 > GBK > LATIN1;
  3. 采用逻辑备份数据时,切记要不定期进行恢复测试,我以前在这方面栽过一次,教训惨痛。

附1,关于编码简介

  • ASCII码,占7bit,由128个字符组成,包括大小写字母、数字0-9、标点符号、非打印字符(换行符、制表符等4个)以及控制字符(退格、响铃等)组成;
  • latin1,占1byte,在ASCII基础上,增加128 ~ 255区间的字符;
  • GB2312等CJK字符集,可变长字符集,最多占2bytes,用于存储常见的CJK字符;
  • UTF8,可变长字符集,最多占3bytes,可以囊括ASCII、CJK及其他绝大多数常用语言文字;这中间其实还有个UNICODE字符集,它也是2bytes的,也能囊括ASCII字符,但即便是ASCII字符也需要消耗2bytes,存在一定浪费,而用UTF8存储ASCII字符时,实际只需要1byte,更为节省存储空间;
  • UTF8MB4,可变长字符集,最多占4bytes,可以包含上面其他几种字符集;同样地,以UTF8MB4存储ASCII字符时,实际上也是只占用1bytes,存储一般的汉字占用3bytes,而存储个别汉字则需要4bytes,存储emoji也至少需要4bytes;

附2,字符集兼容在线测试

为了方便大家,我写了个简单的PHP接口供测试,可以提交一些不常见的汉字,或者emoji表情符,看看是否都能正常显示。

开发这个接口时,发现钉钉中的个别表情符是由2个4字节编码组成的,也就是说一个emoji表情符,其实是需要8个字节的。

这个接口最后输出的格式是:

字符串 : 字节数

比如 "a齒a : 5" ,表示 "a齒a" 这个字符串共消耗 5个字节,因为 "齒" 其实只需要3个字节来存储,虽然看起来挺大一坨的。

相应的代码如下:

 
  1. select vchar, length(vchar) as vcharlen 

测试接口链接: utf8mb4字符集兼容性测试接口

如果想要写入4字节的汉字,可以从龙泉寺提供的字库拷贝过来,或者插入emoji表情符。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
SQL JSON 关系型数据库
【SQL编程】MySQL 5.7.28 版本使用 SQL 直接解析 JSON 字符串(判断是否是合法JSON类型+文本深度+文本长度+值类型+keys获取+值获取+不同深度数据获取)
【SQL编程】MySQL 5.7.28 版本使用 SQL 直接解析 JSON 字符串(判断是否是合法JSON类型+文本深度+文本长度+值类型+keys获取+值获取+不同深度数据获取)
72 0
|
关系型数据库 MySQL
MYSQL 高级文本查询之regexp_like和REGEXP
在MySQL中,regexp_like和REGEXP都是用于执行正则表达式搜索的函数。虽然它们都可以完成相似的任务,但它们之间还是有一些区别的。在本篇博客中,我们将比较这两个函数的用法和示例,并解释它们之间的差异。
|
9月前
|
存储 关系型数据库 MySQL
MySQL长文本字段的选取
MySQL长文本字段的选取
79 0
|
12月前
|
存储 关系型数据库 MySQL
MySQL长文本字段的选
MySQL长文本字段的选
92 0
MySQL长文本字段的选
|
关系型数据库 MySQL
Mysql文本函数
Mysql文本函数
97 1
|
11月前
|
关系型数据库 MySQL
MySQL实战基础知识入门(5):命令行汉字中文输出乱码的解决方案
MySQL实战基础知识入门(5):命令行汉字中文输出乱码的解决方案
64 0
|
12月前
|
关系型数据库 MySQL 索引
mysql 布尔文本搜索
mysql 布尔文本搜索
101 0
|
JSON 关系型数据库 MySQL
【Python】【MySQL】Python将JSON数据以文本形式存放到MySQL的Text类型字段中
【Python】【MySQL】Python将JSON数据以文本形式存放到MySQL的Text类型字段中
158 0
|
存储 SQL JSON
不全?MySQL数据类型精讲,定点日期枚举文本字符串,json二进制,空间,选择建议,完整详细可收藏
不全?MySQL数据类型精讲,定点日期枚举文本字符串,json二进制,空间,选择建议,完整详细可收藏
290 1
不全?MySQL数据类型精讲,定点日期枚举文本字符串,json二进制,空间,选择建议,完整详细可收藏
|
缓存 关系型数据库 MySQL
关于mysql导入大文本txt文件
关于mysql导入大文本txt文件
227 0
关于mysql导入大文本txt文件

热门文章

最新文章