MySQL:mysql存储emoji表情以及默认字符集修改
来源:互联网 发布:seo前沿者 编辑:程序博客网 时间:2024/05/16 01:21
mysql存储emoji表情有两种方法:base64和设置字段为utf8mb4
我之前的数据库是utf8,现在默认是3个字节,但emoji是4个字节,此时需要改为utf8mb4,为4个字节,utf8mb4是在mysql 5.6之后的版本支持的
下面是mysql官方文档的说明
The idea of UTF-8 is that various Unicode characters are encoded using byte sequences of different lengths:
--Basic Latin letters, digits, and punctuation signs use one byte.
--Most European and Middle East script letters fit into a 2-byte sequence: extended Latin letters (with tilde, macron, acute, grave and other accents), Cyrillic, Greek, Armenian, Hebrew, Arabic, Syriac, and others.
--Korean, Chinese, and Japanese ideographs use 3-byte or 4-byte sequences.
从上面看到一般中文是3byte,所以一般我们都用utf8,支持到3个字节,emoji是4个自己,所以为了兼容,目前支持4个自己的为 utf8mb4,只要我们设置这个就好了。
一.查看字符集,我们用 show variables 方法
mysql> show variables like "character%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
其实我们假如 把默认的数据库都改为 utf8字符集,因为我们大部分都要存储中文,把有emoji的那个表,改为utf8mb4
查看字符的collation
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
如上面所展示
二.查看现有的数据库,以及表字符集
查看数据库编码:
SHOW CREATE DATABASE db_name;
查看表编码:
SHOW CREATE TABLE tbl_name;
查看字段编码:
SHOW FULL COLUMNS FROM tbl_name;
三.修改字符集
1修改全局的默认的字符集
我们在show variables上面看到所有的默认的字符集,使用下面的命令行
set character_set_database = utf8
set character_set_server = utf8
这样 我们再执行一次 show variables
mysql> show variables like "character%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
此时默认的,数据库以及服务器都改为utf8了
2.修改某个数据库的字符集
ALTERDATABASEdb_nameDEFAULTCHARACTERSET character_namecollation;
修改某个 myfriend 数据库的话
alter database myfriend default character set utf8 utf8_general_ci
3.把表默认的字符集和所有字符列(CHAR,VARCHAR,TEXT)改为新的字符集:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name collation
4.只是修改表的默认字符集:
ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name collation
如:ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
5.修改字段的字符集:
ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_namecollation
如:ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;
参考
http://dev.mysql.com/doc/refman/5.6/en/charset-unicode-utf8.html
获取更多的文章,欢迎关注微信公众号
- MySQL:mysql存储emoji表情以及默认字符集修改
- MySQL utf8mb4 字符集,用于存储emoji表情
- MySQL utf8mb4 字符集,用于存储emoji表情
- MySQL utf8mb4 字符集,用于存储emoji表情
- mysql存储emoji表情
- Mysql存储emoji表情
- mysql存储emoji表情
- Mysql存储emoji表情
- Mysql 字符集,支持Emoji表情
- mysql Emoji表情字符集转换
- mysql存储emoji表情,utf8mb4
- mysql支持存储emoji表情
- php+mysql存储emoji表情
- Mysql数据库存储emoji表情
- mysql存储emoji表情出错
- Mysql支持emoji表情存储
- mysql存储emoji表情设置
- mysql数据库存储emoji表情
- centos6\7下hive1.2.1安装部署
- 传智播客骗局
- Poj 3067 Japan【树状数组】
- JNDI学习总结(三)——Tomcat下使用Druid配置JNDI数据源
- 只有 DBA 才能导入由其他 DBA 导出的文件
- MySQL:mysql存储emoji表情以及默认字符集修改
- Rails
- Spring Boot——开发新一代Spring Java应用
- 记一次给centOS磁盘扩容
- 洛谷[luogu] P1641 [SCOI2010]生成字符串(Catalan数(折线原理)+乘法逆元)
- Eclipse常用的快捷键
- 根据 url地址 下载图片
- Android Studio+ideasmali动态调试smali汇编
- git stash