全面了解mysql中utf8和utf8mb4的区别

一.简介

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。

二.内容描述

那上面说了既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面（BMP）。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情（Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上），和很多不常用的汉字，以及任何新增的 Unicode 字符等等。

三.问题根源

最初的 UTF-8 格式使用一至六个字节，最大能编码 31 位字符。最新的 UTF-8 规范只使用一到四个字节，最大能编码21位，正好能够表示所有的 17个 Unicode 平面。

utf8 是 Mysql 中的一种字符集，只支持最长三个字节的 UTF-8字符，也就是 Unicode 中的基本多文本平面。

Mysql 中的 utf8 为什么只支持持最长三个字节的 UTF-8字符呢？可能是因为 Mysql 刚开始开发那会，Unicode 还没有辅助平面这一说呢。那时候，Unicode 委员会还做着 “65535 个字符足够全世界用了”的美梦。Mysql 中的字符串长度算的是字符数而非字节数，对于 CHAR 数据类型来说，需要为字符串保留足够的长。当使用 utf8 字符集时，需要保留的长度就是 utf8 最长字符长度乘以字符串长度，所以这里理所当然的限制了 utf8 最大长度为 3，比如 CHAR(100) Mysql 会保留 300字节长度。至于后续的版本为什么不对 4 字节长度的 UTF-8 字符提供支持，我想一个是为了向后兼容性的考虑，还有就是基本多文种平面之外的字符确实很少用到。

要在 Mysql 中保存 4 字节长度的 UTF-8 字符，需要使用 utf8mb4 字符集，但只有 5.5.3 版本以后的才支持（查看版本： select version();）。我觉得，为了获取更好的兼容性，应该总是使用 utf8mb4 而非 utf8. 对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用 VARCHAR 替代 CHAR。

四.utf8升级utf8mb4问题

utf8mb4 字符集(4字节 UTF-8 Unicode 编码)

UTF-8字符集每个字符最多使用三个字节，并且只包含基本多语言面 (Basic Multilingual Plane,BMP)字符。

utf8mb4 字符集使用最多每字符四个字节支持补充字符：

对于 BMP字符 UTF8 和 utf8mb4 具有相同的存储特性：相同的代码值，相同的编码，相同的长度。

对于补充字符，UTF8不能储存所有的字符，而utf8mb4需要四个字节来存储它。因为UTF8不能存储所有的字符，你的 utf8 列中都没有补充字符，因此从旧版本的MySQL UTF8 升级数据时不用担心字符转换或丢失数据。

utf8mb4 是 utf8 的超集，所以像下面的连接字符串操作，其结果字符集是 utf8mb4 排序规则（一组规则，定义如何对字符串进行比较和排序）是 utf8mb4_col：

SELECT CONCAT (utf8_col, utf8mb4_col);

同样，下面的 WHERE 子句中的内容比较根据 utf8mb4_col 规则：

SELECT * FROM utf8_tbl, utf8mb4_tbl WHERE utf8_tbl.utf8_col = utf8mb4_tbl.utf8mb4_col;

如上面所说到的： 要使用 utf8mb4 节省空间，使用 VARCHAR 替换 CHAR。否则，MySQL必须为使用 utf8mb4字符集的列的每一个字符保留四字节的空间，因为其最大长度可能是四字节。例如，MySQL必须为一个使用 utf8mb4 字符集的 char（10）的列保留40字节空间。

五.utf8升级utf8mb4具体步骤

首先将我们数据库默认字符集由utf8 更改为utf8mb4，对应的表默认字符集也更改为utf8mb4 已经存储表情的字段默认字符集也做了相应的调整。

SQL 语句

# 修改数据库:

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# 修改表:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 修改表字段:

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

修改MySQL配置文件

新增如下参数：

default-character-set= utf8mb4default-character-set= utf8mb4character-set-client-handshake =FALSEcharacter-set-server= utf8mb4collation-server= utf8mb4_unicode_ci init_connect='SET NAMES utf8mb4'

检查环境变量和测试 SQL 如下：

SHOWVARIABLESWHEREVariable_nameLIKE'character\_set\_%'ORVariable_nameLIKE'collation%';

注意：MySQL版本必须为5.5.3以上版本，否则不支持字符集utf8mb4

六.建议

建议普通表使用utf8，如果这个表需要支持emoji就使用utf8mb4

新建mysql库或者表的时候还有一个排序规则

utf8_unicode_ci比较准确，utf8_general_ci速度比较快。通常情况下 utf8_general_ci的准确性就够我们用的了，在我看过很多程序源码后，发现它们大多数也用的是utf8_general_ci，所以新建数据库时一般选用utf8_general_ci就可以了

如果是utf8mb4那么对应的就是 utf8mb4_general_ci utf8mb4_unicode_ci

七.utf8_unicode_ci与utf8_general_ci的区别

当前，utf8_unicode_ci校对规则仅部分支持Unicode校对规则算法。一些字符还是不能支持。并且，不能完全支持组合的记号。这主要影响越南和俄罗斯的一些少数民族语言，如：Udmurt 、Tatar、Bashkir和Mari。

utf8_unicode_ci的最主要的特色是支持扩展，即当把一个字母看作与其它字母组合相等时。例如，在德语和一些其它语言中‘ß’等于‘ss’。

utf8_general_ci是一个遗留的校对规则，不支持扩展。它仅能够在字符之间进行逐个比较。这意味着utf8_general_ci校对规则进行的比较速度很快，但是与使用utf8_unicode_ci的校对规则相比，比较正确性较差）。

例如，使用utf8_general_ci和utf8_unicode_ci两种校对规则下面的比较相等：

Ä = A

Ö = O

Ü = U

两种校对规则之间的区别是，对于utf8_general_ci下面的等式成立：

ß = s

但是，对于utf8_unicode_ci下面等式成立：

ß = ss

对于一种语言仅当使用utf8_unicode_ci排序做的不好时，才执行与具体语言相关的utf8字符集校对规则。例如，对于德语和法语，utf8_unicode_ci工作的很好，因此不再需要为这两种语言创建特殊的utf8校对规则。

utf8_general_ci也适用与德语和法语，除了‘ß’等于‘s’，而不是‘ss’之外。如果你的应用能够接受这些，那么应该使用utf8_general_ci，因为它速度快。否则，使用utf8_unicode_ci，因为它比较准确。

八.案例

查询：CREATETABLEtest_session ( sessionIdvarchar(255)NOTNULL, userIdint(10)unsignedDEFAULTNULL, createAt datetimeDEFAULTNULL,...错误代码：1071Specifiedkeywas toolong; max key length is 767 bytes

如上，报错,当使用utf8mb4编码后，主键id的长度设置255，太长，只能设置小于191的

其中：

max key length is 767 bytes

utf8: 767/3=255.6666666666667

utf8mb4: 767/4=191.75

九.深入Mysql字符集设置

基本概念

• 字符(Character)是指人类语言中最小的表义符号。例如’A'、’B'等；

• 给定一系列字符，对每个字符赋予一个数值，用数值来代表对应的字符，这一数值就是字符的编码(Encoding)。例如，我们给字符’A'赋予数值0，给字符’B'赋予数值1，则0就是字符’A'的编码；

• 给定一系列字符并赋予对应的编码后，所有这些字符和编码对组成的集合就是字符集(Character Set)。例如，给定字符列表为{‘A’,'B’}时，{‘A’=>0, ‘B’=>1}就是一个字符集；

• 字符序(Collation)是指在同一字符集内字符之间的比较规则；

• 确定字符序后，才能在一个字符集上定义什么是等价的字符，以及字符之间的大小关系；

• 每个字符序唯一对应一种字符集，但一个字符集可以对应多种字符序，其中有一个是默认字符序(Default Collation)；

• MySQL中的字符序名称遵从命名惯例：以字符序对应的字符集名称开头；以_ci(表示大小写不敏感)、_cs(表示大小写敏感)或_bin(表示按编码值比较)结尾。例如：在字符序“utf8_general_ci”下，字符“a”和“A”是等价的；

MySQL字符集设置

• 系统变量：

– character_set_server：默认的内部操作字符集

– character_set_client：客户端来源数据使用的字符集

– character_set_connection：连接层字符集

– character_set_results：查询结果字符集

– character_set_database：当前选中数据库的默认字符集

– character_set_system：系统元数据(字段名等)字符集

– 还有以collation_开头的同上面对应的变量，用来描述字符序。

• 用introducer指定文本字符串的字符集：

– 格式为：[_charset] ‘string’ [COLLATE collation]

– 例如：

• SELECT _latin1 ‘string’;

• SELECT _utf8 ‘你好’ COLLATE utf8_general_ci;

– 由introducer修饰的文本字符串在请求过程中不经过多余的转码，直接转换为内部字符集处理。

MySQL中的字符集转换过程

1. MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection；

2. 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下：

• 使用每个数据字段的CHARACTER SET设定值；

• 若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)；

• 若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；

• 若上述值不存在，则使用character_set_server设定值。

3. 将操作结果从内部操作字符集转换为character_set_results。

常见问题解析

• 向默认字符集为utf8的数据表插入utf8编码的数据前没有设置连接字符集，查询时设置连接字符集为utf8

– 插入时根据MySQL服务器的默认设置，character_set_client、character_set_connection和character_set_results均为latin1；

– 插入操作的数据将经过latin1=>latin1=>utf8的字符集转换过程，这一过程中每个插入的汉字都会从原始的3个字节变成6个字节保存；

– 查询时的结果将经过utf8=>utf8的字符集转换过程，将保存的6个字节原封不动返回，产生乱码……

• 向默认字符集为latin1的数据表插入utf8编码的数据前设置了连接字符集为utf8

– 插入时根据连接字符集设置，character_set_client、character_set_connection和character_set_results均为utf8；

– 插入数据将经过utf8=>utf8=>latin1的字符集转换，若原始数据中含有\u0000~\u00ff范围以外的Unicode字符，会因为无法在latin1字符集中表示而被转换为“?”(0x3F)符号，以后查询时不管连接字符集设置如何都无法恢复其内容了。

检测字符集问题的一些手段

• SHOW CHARACTER SET;

• SHOW COLLATION;

• SHOW VARIABLES LIKE ‘character%’;

• SHOW VARIABLES LIKE ‘collation%’;

• SQL函数HEX、LENGTH、CHAR_LENGTH

• SQL函数CHARSET、COLLATION

使用MySQL字符集时的建议

• 建立数据库/表和进行数据库操作时尽量显式指出使用的字符集，而不是依赖于MySQL的默认设置，否则MySQL升级时可能带来很大困扰；

• 数据库和连接字符集都使用latin1时虽然大部分情况下都可以解决乱码问题，但缺点是无法以字符为单位来进行SQL操作，一般情况下将数据库和连接字符集都置为utf8是较好的选择；

• 使用mysql C API时，初始化数据库句柄后马上用mysql_options设定MYSQL_SET_CHARSET_NAME属性为utf8，这样就不用显式地用 SET NAMES语句指定连接字符集，且用mysql_ping重连断开的长连接时也会把连接字符集重置为utf8；

• 对于mysql PHP API，一般页面级的PHP程序总运行时间较短，在连接到数据库以后显式用SET NAMES语句设置一次连接字符集即可；但当使用长连接时，请注意保持连接通畅并在断开重连后用SET NAMES语句显式重置连接字符集。

其他注意事项

• my.cnf中的default_character_set设置只影响mysql命令连接服务器时的连接字符集，不会对使用libmysqlclient库的应用程序产生任何作用！

• 对字段进行的SQL函数操作通常都是以内部操作字符集进行的，不受连接字符集设置的影响。

• SQL语句中的裸字符串会受到连接字符集或introducer设置的影响，对于比较之类的操作可能产生完全不同的结果，需要小心！

全面了解mysql中utf8和utf8mb4的区别 - 简书 (jianshu.com)

【SQL】delete 与 truncate 命令的区别_truncate和delete的区别_JJJiker的博客-CSDN博客

delete 与 truncate 命令的区别

1. 相同点

二者都能删除表中的数据
一般情况下，主流的关系型数据库都支持这两个命令

使用 Oracle 数据库作为演示

SQL> select * from emp;

      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----------- ---------- --------- ----- ----------- --------- --------- ------
       7369 SMITH      CLERK      7902 1980-12-17     800.00               20
       7499 ALLEN      SALESMAN   7698 1981-02-20    1600.00    300.00     30
       7521 WARD       SALESMAN   7698 1981-02-22    1250.00    500.00     30
       7566 JONES      MANAGER    7839 1981-04-02    2975.00               20
       7654 MARTIN     SALESMAN   7698 1981-09-28    1250.00   1400.00     30
       7698 BLAKE      MANAGER    7839 1981-05-01    2850.00               30
       7782 CLARK      MANAGER    7839 1981-06-09    2450.00               10
       7788 SCOTT      ANALYST    7566 1987-04-19    3000.00               20
       7839 KING       PRESIDENT       1981-11-17    5000.00               10
       7844 TURNER     SALESMAN   7698 1981-09-08    1500.00      0.00     30
       7876 ADAMS      CLERK      7788 1987-05-23    1100.00               20
       7900 JAMES      CLERK      7698 1981-12-03     950.00               30
       7902 FORD       ANALYST    7566 1981-12-03    3000.00               20
       7934 MILLER     CLERK      7782 1982-01-23    1300.00               10

14 rows selected

delete 命令

SQL> delete from emp;

14 rows deleted

SQL> commit;

Commit complete

truncate 命令

SQL>  @d:\oracle.sql;  --重新导入数据

SQL> truncate table emp;

Table truncated

2. 区别

2.1 SQL 性质的不同

delete：属于 DML(数据操作语言) 范畴
truncate：属于 DDL(数据定义语言) 范畴

2.2 操作对象不同

delete：能删除表中或基于真实表创建的视图中的数据
truncate：只能删除表中的数据，无法应用在视图上

创建测试视图

create or replace view v_emp
as
select * from emp
where sal > 2000;

delete 测试：删除成功

SQL> delete from v_emp;
6 rows deleted

SQL> commit;
Commit complete

truncate 测试：

SQL> truncate table v_emp;
truncate table v_emp

ORA-01702: 视图不适用于此处

2.3 条件限制区别

delete：删除表中数据时，可以通过 where 关键字，进行选择性s删除
truncate：不能使用 where 关键字进行选择性删除

delete 测试：

SQL> delete from emp where sal > 2000;
6 rows deleted

SQL> commit;
Commit complete

truncate 测试：

SQL> truncate table emp where sal > 2000;
truncate table emp where sal > 2000

ORA-03291: 无效的截断选项 - 缺失 STORAGE 关键字

2.4 事务

delete：删除表中数据时，涉及到事务处理：回退（缓存）、提交、撤退
truncate：删除缓存时，不涉及缓存事务处理

delete 测试：

SQL> delete from emp;
14 rows deleted

SQL> select count(1) from emp;
  COUNT(1)
----------
         0

此时，在本图形化工具中，显示 14 条信息已被删除，同时查看员工表，得到 0 条数据
之后，若不执行 commit 命令，通过另一个进程再连接上数据库
在这里插入图片描述
此时检索员工表，依旧是 14 条数据，验证了事务不提交后的数据的不完整性（oracle数据库是手动 commit）
再在原窗口执行 commit 命令

事务得到提交，保证了数据的完整性

truncate 测试：

SQL> truncate table emp;
Table truncated

SQL> select count(1) from emp;
  COUNT(1)
----------
         0

在这里插入图片描述
若使用 rollback 命令回退：

SQL> rollback;
Rollback complete

SQL> select count(1) from emp;
  COUNT(1)
----------
         0

发现：虽然提示 Rollback complete ，但再次检索数据依旧是 0 。发现 truncate 命令不涉及到事务处理

2.5 删除效率

delete：由于 delete 删除数据时要考虑事务管理（缓存、回退机制、日志记录），所以当删除大批量数据时，速度慢，效率低，甚至达不到删除的目的
truncate：删除大批量数据时，速度快、效率高、但无法撤销

测试：

--创建测试表
create table jiker
as select * from emp;

--快速生成数据（表的自动复制）
insert into jiker
select * from jiker;
commit;

最后生成 1400 万条数据 
SQL> 

14680064 rows inserted


Commit complete

使用 delete命令，已不能在短时间内删除，甚至达不到删除的目的
使用 truncate 命令，可快速删除大批量数据

SQL> truncate table jiker;

Table truncated

2.6 激活触发器

delete：在进行删除操作时，可能激活触发器的处理
truncate：不存在激活触发器处理

delete 测试：

--测试表，复制员工表表结构
create table emp_tmp
as select * 
from emp
where 1 = 2;

--创建触发器
create or replace trigger t_emp
       before delete on emp
       for each row
declare

begin
  --删除数据前出发：将数据备份到临时表 emp_tmp 中
  insert into emp_tmp
  values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:olddeptno)
end;

--查看测试表中是否有数据
SQL> select * from emp_tmp;

      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----------- ---------- --------- ----- ----------- --------- --------- ------

--删除员工表中的数据
SQL> delete from emp;
14 rows deleted

SQL> select * from emp_tmp;

      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----------- ---------- --------- ----- ----------- --------- --------- ------
       7369 SMITH      CLERK      7902 1980-12-17     800.00               20
       7499 ALLEN      SALESMAN   7698 1981-02-20    1600.00    300.00     30
       7521 WARD       SALESMAN   7698 1981-02-22    1250.00    500.00     30
       7566 JONES      MANAGER    7839 1981-04-02    2975.00               20
       7654 MARTIN     SALESMAN   7698 1981-09-28    1250.00   1400.00     30
       7698 BLAKE      MANAGER    7839 1981-05-01    2850.00               30
       7782 CLARK      MANAGER    7839 1981-06-09    2450.00               10
       7788 SCOTT      ANALYST    7566 1987-04-19    3000.00               20
       7839 KING       PRESIDENT       1981-11-17    5000.00               10
       7844 TURNER     SALESMAN   7698 1981-09-08    1500.00      0.00     30
       7876 ADAMS      CLERK      7788 1987-05-23    1100.00               20
       7900 JAMES      CLERK      7698 1981-12-03     950.00               30
       7902 FORD       ANALYST    7566 1981-12-03    3000.00               20
       7934 MILLER     CLERK      7782 1982-01-23    1300.00               10

14 rows selected

数据复制保存到了 emp_tmp 临时表中，触发器生效

truncate 测试：

SQL> truncate table emp;

Table truncated


SQL> select * from emp_tmp;

      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----------- ---------- --------- ----- ----------- --------- --------- ------

触发器未被触发

2.7 外键约束

delete：可以运用 SQL 语句、按照业务逻辑、按照一定的先后顺序可以删除相关表中的数据
truncate：不能删除对于由 foreign key 约束引用的表，不能删除该表中的数据

delete 测试：

直接删除 dept 部门表中的数据

SQL> delete from dept;
delete from dept

ORA-02292: 违反完整约束条件 (SCOTT.FK_DEPTNO) - 已找到子记录

先删除员工表，再删除部门表，成功！

SQL> delete from emp;
14 rows deleted

SQL> commit;
Commit complete

SQL> delete from dept;
4 rows deleted

truncate 测试：

直接删除部门表

SQL> truncate table dept;
truncate table dept

ORA-02266: 表中的唯一/主键被启用的外键引用

先删除员工表，再删除部门表，

SQL> truncate table emp;

Table truncated

SQL> truncate table dept;
truncate table dept

ORA-02266: 表中的唯一/主键被启用的外键引用

依旧不能被删除

3. 应用场景

需要根据一定的业务条件删除数据时、且数据量、性能可控的情况下，可以考虑使用 delete from xxx where 业务条件
需要考虑事务提交、撤销的管理机制时，可以使用 delete
当需要删除大批量数据时，同时要求速度快，效率高并且w无需撤销时，可以使用 truncate
另外，在企业级开发中，实际上都是进行逻辑删除（将数据进行删除标识处理）、而并不进行物理上的删除
在实际生产环境中，一般情况下删除业务处理（过渡表）中的数据
在实际企业开发、维护过程中，不管使用 delete 还是 truncate 命令前，都要考虑数据的备份

posted @ 2023-07-06 11:24 CharyGao 阅读(635) 评论(0) 编辑收藏举报

刷新页面返回顶部

硅基文明

代码改变不了世界，但是改变世界需要代码。

全面了解mysql中utf8和utf8mb4的区别

一.简介

二.内容描述

三.问题根源

四.utf8升级utf8mb4问题

五.utf8升级utf8mb4具体步骤

六.建议

七.utf8_unicode_ci与utf8_general_ci的区别

八.案例

九.深入Mysql字符集设置

delete 与 truncate 命令的区别

1. 相同点

2. 区别

2.1 SQL 性质的不同

2.2 操作对象不同

2.3 条件限制区别

2.4 事务

2.5 删除效率

2.6 激活触发器

2.7 外键约束

3. 应用场景