mysql 修改字符集为utf8mb4

Wesley13
• 阅读 288

一般情况下,我们会设置MySQL默认的字符编码为utf8,但是近些年来,emoji表情的火爆使用,给数据库带来了意外的错误,就是emoji的字符集已经超出了utf8的编码范畴😄

令人抓狂的字符编码问题

谈到字符编码问题,会让很多人感到头疼,这里不在深究各个字符编码的特点和理论,这里只说下Unicode和utf8字符编码的关系

1234567

Unicode是编码字符集,而UTF-8就是字符编码,即Unicode规则字库的一种实现形式。随着互联网的发展,对同一字库集的要求越来越迫切,Unicode标准也就自然而然的出现。它几乎涵盖了各个国家语言可能出现的符号和文字,并将为他们编号。详见:Unicode on Wikipedia。Unicode的编号从0000开始一直到10FFFF共分为16个Plane,每个Plane中有65536个字符。而UTF-8则只实现了第一个Plane,可见UTF-8虽然是一个当今接受度最广的字符集编码,但是它并没有涵盖整个Unicode的字库,这也造成了它在某些场景下对于特殊字符的处理困难

简单的说在计算机内存中,统一使用Unicode编码,当需要保存到硬盘或者需要传输的时候,就转换为UTF-8编码

用记事本编辑的时候,从文件读取的UTF-8字符被转换为Unicode字符到内存里,编辑完成后,保存的时候再把Unicode转换为UTF-8保存到文件

mysql 修改字符集为utf8mb4

emoji是Unicode编码,在MySQL中使用utf8编码无法正常显示emoji的表情,为了解决这个问题,MySQL在5.5.3版本之后,引进了新的字符编码utf8mb4,本篇文章主要介绍如何将已经是utf8的database切换到utf8mb4字符编码

什么是utf8mb4

utf8mb4最明显的好处是解决了苹果挖的坑-推广了emoji表情。utf8mb4解决了MySQL数据库存储emoji表情的问题

utf8mb4是utf8的超集,理论上由utf8升级到utf8mb4字符编码没有任何兼容问题

升级utf8到utf8mb4

1. 备份

安全第一,备份所有需要升级字符编码的数据库

  • 可以将库dump出来
  • 如果是虚拟机,可以给整个主机做快照

2. 升级

utf8mb4是MySQL5.5.3版本之后支持的字符集,so,如果你需要使用这个字符集,前提条件是你的MySQL版本必须 >= 5.5.3

3. 修改

在MySQL中,可以为一个database设置字符编码,可以为一张表设置字符编码,甚至可以为某一个字段设置字符编码

  • 查看当前系统默认的字符集设置

    SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

  • 查看database的字符编码

        show create database polarsnow;

  • 查看table的字符编码

        show create table ps;

  • 查看column的字符编码

        show full columns from ps;

修改database默认的字符集

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci

虽然修改了database的字符集为utf8mb4,但是实际只是修改了database新创建的表,默认使用utf8mb4,原来已经存在的表,字符集并没有跟着改变,需要手动为每张表设置字符集

修改table的字符集

  • 只修改表默认的字符集 ALTER TABLE table_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • 修改表默认的字符集和所有字符列的字符集 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

单独修改column默认的字符集

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

注:VARCHAR(191) 根据字段实例的类型填写

4. 检查字段的最大长度和索引列

  • 字段长度

由于从utf8升级到了utf8mb4,一个字符所占用的空间也由3个字节增长到4个字节,但是我们当初创建表时,设置的字段类型以及最大的长度没有改变。例如,你在utf8下设置某一字段的类型为TINYTEXT, 这中字段类型最大可以容纳255字节,三个字节一个字符的情况下可以容纳85个字符,四个字节一个字符的情况下只能容纳63个字符,如果原表中的这个字段的值有一个或多个超过了63个字符,那么转换成utf8mb4字符编码时将转换失败,你必须先将TINYTEXT更改为TEXT等更高容量的类型之后才能继续转换字符编码

  • 索引

在InnoDB引擎中,最大的索引长度为767字节,三个字节一个字符的情况下,索引列的字符长度最大可以达到255,四个字节一个字符的情况下,索引的字符长度最大只能到191。如果你已经存在的表中的索引列的类型为VARCHAR(255)那么转换utf8mb4时同样会转换失败。你需要先将VARCHAR(255)更改为VARCHAR(191)才能继续转换字符编码

5. 修改配置文件

SET NAMES utf8 COLLATE utf8_unicode_ci becomes SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci

123456789101112131415

> vim /etc/my.cnf# 对本地的mysql客户端的配置[client]default-character-set = utf8mb4# 对其他远程连接的mysql客户端的配置[mysql]default-character-set = utf8mb4# 本地mysql服务的配置[mysqld]character-set-client-handshake = FALSEcharacter-set-server = utf8mb4collation-server = utf8mb4_unicode_ci> service mysqld restart

检查修改

12345678910111213141516

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';+--------------------------+--------------------+| Variable_name            | Value              |+--------------------------+--------------------+| character_set_client | utf8mb4 || character_set_connection | utf8mb4 || character_set_database | utf8mb4 || character_set_filesystem | binary || character_set_results | utf8mb4 || character_set_server | utf8mb4 || character_set_system | utf8 || collation_connection | utf8mb4_unicode_ci || collation_database | utf8mb4_unicode_ci || collation_server | utf8mb4_unicode_ci |+--------------------------+--------------------+10 rows in set (0.00 sec)

注:character_set_system 一直都会是 utf8,不能被更改

6. 修复&优化所有数据表

1

> mysqlcheck -u root -p --auto-repair --optimize --all-databases

总结

不要在MySQL上使用utf8字符编码,推荐使用utf8mb4,至于为什么,引用国外友人的一段话:

Never use utf8 in MySQL — always use utf8mb4 instead. Updating your databases and code might take some time, but it’s definitely worth the effort. Why would you arbitrarily limit the set of symbols that can be used in your database? Why would you lose data every time a user enters an astral symbol as part of a comment or message or whatever it is you store in your database? There’s no reason not to strive for full Unicode support everywhere. Do the right thing, and use utf8mb4. 🍻

点赞
收藏
评论区
推荐文章
秃头王路飞 秃头王路飞
2个月前
webpack5手撸vue2脚手架
webpack5手撸vue相信工作个12年的小伙伴们在面试的时候多多少少怕被问到关于webpack方面的知识,本菜鸟最近闲来无事,就尝试了手撸了下vue2的脚手架,第一次发帖实在是没有经验,望海涵。 language JavaScript "name": "vuecliversion2", "version": "1.0.0", "desc
光头强的博客 光头强的博客
2个月前
Java面向对象试题
1、 请创建一个Animal动物类,要求有方法eat()方法,方法输出一条语句“吃东西”。 创建一个接口A,接口里有一个抽象方法fly()。创建一个Bird类继承Animal类并实现 接口A里的方法输出一条有语句“鸟儿飞翔”,重写eat()方法输出一条语句“鸟儿 吃虫”。在Test类中向上转型创建b对象,调用eat方法。然后向下转型调用eat()方
刚刚好 刚刚好
2个月前
css问题
1、 在IOS中图片不显示(给图片加了圆角或者img没有父级) <div<img src""/</div div {width: 20px; height: 20px; borderradius: 20px; overflow: h
blmius blmius
1年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录 问题 用navicat导入数据时,报错: 原因这是因为当前的MySQL不支持datetime为0的情况。 解决修改sql\mode: sql\mode:SQL Mode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。 全局s
小森森 小森森
2个月前
校园表白墙微信小程序V1.0 SayLove -基于微信云开发-一键快速搭建,开箱即用
后续会继续更新,敬请期待2.0全新版本 欢迎添加左边的微信一起探讨!项目地址:](https://www.aliyun.com/activity/daily/bestoffer?userCodesskuuw5n) \2. Bug修复更新日历 2. 情侣脸功能大家不要使用了,现在阿里云的接口已经要收费了(土豪请随意), \ \ 和 注意
Stella981 Stella981
1年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置 1、virsh edit centos7 找到“memory”和“vcpu”标签,将 <name>centos7</name> <uuid>2220a6d1-a36a-4fbb-8523-e078b3dfe795</uuid>
Wesley13 Wesley13
1年前
MySQL查询按照指定规则排序
1.按照指定(单个)字段排序 select * from table_name order id desc; 2.按照指定(多个)字段排序 select * from table_name order id desc,status desc; 3.按照指定字段和规则排序 selec
Wesley13 Wesley13
1年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
#### 背景描述 # Time: 2019-01-24T00:08:14.705724+08:00 # User@Host: **[**] @ [**] Id: ** # Schema: sentrymeta Last_errno: 0 Killed: 0 # Query_time: 0.315758 Lock_
3A网络 3A网络
2个月前
理解 virt、res、shr 之间的关系(linux 系统篇)
# 理解 virt、res、shr 之间的关系(linux 系统篇) **前言** 想必在 linux 上写过程序的同学都有分析进程占用多少内存的经历,或者被问到这样的问题 —— 你的程序在运行时占用了多少内存(物理内存)? 通常我们可以通过 t
3A网络 3A网络
2个月前
开发一个不需要重写成 Hive QL 的大数据 SQL 引擎
# 开发一个不需要重写成 Hive QL 的大数据 SQL 引擎 学习大数据技术的核心原理,掌握一些高效的思考和思维方式,构建自己的技术知识体系。明白了原理,有时甚至不需要学习,顺着原理就可以推导出各种实现细节。 各种知识表象看杂乱无章,若只是学习
helloworld_34035044 helloworld_34035044
4个月前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。 uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid() 或 uuid(sep)参数说明:sep 布尔值,生成的uuid中是否包含分隔符'',缺省为