MySQL学习日记（五）MySQL事务和字符集

程序员文章站 2022-03-11 16:29:13

MySQL事务和字符集1、事务当多个用户访问同一数据时，一个用户在更改数据的过程中可能有其它用户同时发起更改请求，为保证数据的一致性状态，MySQL 引入了事务。1.1 MySQL为什么需要事务多个用户访问同一个数据，一个用户修改数据的同时另一个用户同时发出更改要求。这个时候会导致数据可能发生异常。如银行转账业务，张三账号有1000块，李四有500块。假设张三转账给李四500块，而这个时候恰巧有另一个人王五给张三转账500，如果没有事务，而这两个请求同时发生。那么张三的账号上的余额是多少？是100...

文章目录

MySQL事务和字符集

1、事务

1.1 MySQL为什么需要事务
1.2 数据库中事务的概念和特性
1.3 执行事务的语法和流程
1.4 设置事务自动提交
1.5 事务的隔离级别

1.5.1 READ UNCOMITTED（读未提交、RU）
1.5.2 READ COMMITTED（读提交、RC）
1.5.3 REPEATABLE READ（可重复读、RR）
1.5.4 SERIALIZABLE（串行化、效率低）

1.6 查看和修改事务的级别

1.6.1 查看事务的级别
1.6.2 修改事务的隔离级别

2、字符集和校对规则

2.1、什么是字符集和校对规则
2.2 MySQL的字符集转换过程
2.3、查看字符集和校对规则
2.4 设置默认字符集和校对规则

2.4.1设置服务器的字符集和校对规则
2.4.2 表字符集和校对规则
2.4.3 列字符集和校对规则
2.4.4 连接字符集和校对规则

2.5 修改字符集过程学习
2.6 字符集如何选择？

MySQL事务和字符集

1、事务

当多个用户访问同一数据时，一个用户在更改数据的过程中可能有其它用户同时发起更改请求，为保证数据的一致性状态，MySQL 引入了事务。

1.1 MySQL为什么需要事务

多个用户访问同一个数据，一个用户修改数据的同时另一个用户同时发出更改要求。这个时候会导致数据可能发生异常。

如银行转账业务，张三账号有1000块，李四有500块。假设张三转账给李四500块，而这个时候恰巧有另一个人王五给张三转账500，如果没有事务，而这两个请求同时发生。那么张三的账号上的余额是多少？是1000-500还是1000+500。我们知道正确的结果是张三账号上余额还是1000元
MySQL 为了解决此类问题，提供了事务。事务可以将一系列的数据操作捆绑成一个整体进行统一管理，如果某一事务执行成功，则在该事务中进行的所有数据更改均会提交，成为数据库中的永久组成部分。如果事务执行时遇到错误，则就必须取消或回滚。取消或回滚后，数据将全部恢复到操作前的状态，所有数据的更改均被清除。

张三转账给别人是一个事务、接受别人转账又是一个事务、他们之间是独立的。当事务修改数据时，如果任何其他进程正在同时使用相同的数据，则直到该事务成功提交之后，对数据的修改才能生效。张三和李四之间的转账与王五和赵二之间的转账，永远是相互独立的。隔离性

1.2 数据库中事务的概念和特性

数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。

在数据库系统上执行并发操作时，事务是作为最小的控制单元来使用的，特别适用于多用户同时操作的数据库系统。例如，航空公司的订票系统、银行、保险公司以及证券交易系统等。

事务具有 4 个特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这 4 个特性通常简称为 ACID。

特性	描述
原子性	事务是一个完整的操作。事务的各元素是不可分的（原子的）。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。以银行转账事务为例，如果该事务提交了，则这两个账户的数据将会更新。如果由于某种原因，事务在成功更新这两个账户之前终止了，则不会更新这两个账户的余额，并且会撤销对任何账户余额的修改，事务不能部分提交。
一致性	当事务完成时，数据必须处于一致状态。也就是说，在事务开始之前，数据库中存储的数据处于一致状态。在正在进行的事务中. 数据可能处于不一致的状态，如数据可能有部分被修改。然而，当事务成功完成时，数据必须再次回到已知的一致状态。通过事务对数据所做的修改不能损坏数据，或者说事务不能使数据存储处于不稳定的状态。以银行转账事务事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。事务完成以后，账户余额的总额再次恢复到一致状态。
隔离性	对数据进行修改的所有并发事务是彼此隔离的，这表明`事务必须是独立的，它不应以任何方式依赖于或影响其他事务。`修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。
持久性	事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。一个事务成功完成之后，它对数据库所作的改变是永久性的，即使系统出现故障也是如此。也就是说，一旦事务被提交，事务对数据所做的任何变动都会被永久地保留在数据库中。

事务的 ACID 原则保证了一个事务或者成功提交，或者失败回滚，二者必居其一。因此，它对事务的修改具有可恢复性。即当事务失败时，它对数据的修改都会恢复到该事务执行前的状态。

1.3 执行事务的语法和流程

MySQL 提供了多种存储引擎来支持事务。支持事务的存储引擎有 InnoDB 和 BDB，其中，InnoDB 存储引擎事务主要通过 UNDO 日志和 REDO 日志实现，MyISAM 存储引擎不支持事务。

任何一种数据库，都会拥有各种各样的日志，用来记录数据库的运行情况、日常操作、错误信息等，MySQL 也不例外。例如，当用户 root 登录到 MySQL 服务器，就会在日志文件里记录该用户的登录时间、执行操作等。

为了维护 MySQL 服务器，经常需要在 MySQL 数据库中进行日志操作：

UNDO 日志：复制事务执行前的数据，用于在事务发生异常时回滚数据。
REDO 日志：记录在事务执行中，每条对数据进行更新的操作，当事务提交时，该内容将被刷新到磁盘。

默认设置下，每条 SQL 语句就是一个事务，即执行 SQL 语句后自动提交。为了达到将几个操作做为一个整体的目的，需要使用 BEGIN 或 START TRANSACTION 开启一个事务，或者禁止当前会话的自动提交。

开始事务

BEGIN;
或
START TRANSACTION;

这个语句显式地标记一个事务的起始点。

提交事务

COMMIT ;

COMMIT 表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。

提交事务，意味着将事务开始以来所执行的所有数据都修改成为数据库的永久部分，因此也标志着一个事务的结束。一旦执行了该命令，将不能回滚事务。只有在所有修改都准备好提交给数据库时，才执行这一操作。

回滚

ROLLBACK;

ROLLBACK 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态。这里的操作指对数据库的更新操作。

当事务执行过程中遇到错误时，使用 ROLLBACK 语句使事务回滚到起点或指定的保持点处。同时，系统将清除自事务起点或到某个保存点所做的所有的数据修改，并且释放由事务控制的资源。因此，这条语句也标志着事务的结束。

小结

BEGIN 或 START TRANSACTION 语句后面的 SQL 语句对数据库数据的更新操作都将记录在事务日志中，直至遇到 ROLLBACK 语句或 COMMIT 语句。
如果事务中某一操作失败且执行了 ROLLBACK 语句，那么在开启事务语句之后所有更新的数据都能回滚到事务开始前的状态。
如果事务中的所有操作都全部正确完成，并且使用了 COMMIT 语句向数据库提交更新数据，则此时的数据又处在新的一致状态。

实战例子
模拟张三给李四转账500，提交和未提交时李四的账户余额情况。
MySQL学习日记（五）MySQL事务和字符集

张三的账户余额已经减少到 500 元，如果再转出 1000 元，将会出现余额为负数，因此需要回滚到原始状态。
MySQL学习日记（五）MySQL事务和字符集

注意事项
注意事项
MySQL 事务是一项非常消耗资源的功能，大家在使用过程中要注意以下几点。

事务尽可能简短
事务的开启到结束会在数据库管理系统中保留大量资源，以保证事务的原子性、一致性、隔离性和持久性。如果在多用户系统中，较大的事务将会占用系统的大量资源，使得系统不堪重负，会影响软件的运行性能，甚至导致系统崩溃。
事务中访问的数据量尽量最少
当并发执行事务处理时，事务操作的数据量越少，事务之间对相同数据的操作就越少。
查询数据时尽量不要使用事务
对数据进行浏览查询操作并不会更新数据库的数据，因此应尽量不使用事务查询数据，避免占用过量的系统资源。
在事务处理过程中尽量不要出现等待用户输入的操作
在处理事务的过程中，如果需要等待用户输入数据，那么事务会长时间地占用资源，有可能造成系统阻塞。

1.4 设置事务自动提交

MySQL 默认开启事务自动提交模式，即除非显式的开启事务（BEGIN 或 START TRANSACTION），否则每条 SOL 语句都会被当做一个单独的事务自动执行。但有些情况下，我们需要关闭事务自动提交来保证数据的一致性。
查看事务自动提交是否开启

show variables like 'autocommit';

设置事务自动提交的开启和关闭

set autocommit=0|1|on|off;

实例:
MySQL学习日记（五）MySQL事务和字符集

使用 BEGIN 或 START TRANSACTION 开启一个事务之后，自动提交将保持禁用状态，直到使用 COMMIT 或 ROLLBACK 结束事务。之后，自动提交模式会恢复到之前的状态，即如果 BEGIN 前 autocommit = 1，则完成本次事务后 autocommit 还是 1。如果 BEGIN 前 autocommit = 0，则完成本次事务后 autocommit 还是 0。

1.5 事务的隔离级别

事务的隔离性就是指当多个事务同时运行时，各事务之间相互隔离，不可互相干扰。如果事务没有隔离性，就容易出现脏读、不可重复读和幻读等情况。

脏读
脏读是指一个事务正在访问数据，并且对数据进行了修改，但是这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
不可重复读
不可重复读是指在一个事务内，多次读取同一个数据。
在这个事务还没有结束时，另外一个事务也访问了该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
幻读
幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

解决办法——标准 SQL 定义了 4 类事务隔离级别，用来指定事务中的哪些数据改变是可见的，哪些数据改变是不可见的。

隔离级别

读未提交（READ UNCOMITTED）
读提交（READ COMMITTED）
可重复读（REPEATABLE READ）
串行化（SERIALIZABLE）

隔离级别	脏读	不可重复读	幻读	隔离级别	描述
READ UNCOMITTED	√	√	√	最低	读未提交就是可以读到未提交的内容。
READ COMMITTED	×	√	√	次低	读提交就是只能读到已经提交了的内容。
REPEATABLE READ	×	×	√	高	专门针对不可重复读这种情况而制定的隔离级别，可以有效的避免不可重复读。
SERIALIZABLE	×	×	×	最高	SERIALIZABLE 是最高的事务隔离级别，主要通过强制事务排序来解决幻读问题

低级别的隔离级别可以支持更高的并发处理，同时占用的系统资源更少。

1.5.1 READ UNCOMITTED（读未提交、RU）

如果一个事务读取到了另一个未提交事务修改过的数据，那么这种隔离级别就称之为读未提交。在该隔离级别下，所有事务都可以看到其它未提交事务的执行结果。因为它的性能与其他隔离级别相比没有高多少，所以一般情况下，该隔离级别在实际应用中很少使用。

实例
第一步、将全局事务隔离级别设置为未提交，重启生效
MySQL学习日记（五）MySQL事务和字符集
新开一个窗口B，原来的窗口是A。B中查看事务隔离级别:

然后

由于 B 窗口中的事务回滚了，所以 A 事务出现了脏读现象。

1.5.2 READ COMMITTED（读提交、RC）

如果一个事务只能读取到另一个已提交事务修改过的数据，并且其它事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那么这种隔离级别就称之为读提交。

该隔离级别满足了隔离的简单定义：一个事务从开始到提交前所做的任何改变都是不可见的，事务只能读取到已经提交的事务所做的改变。

这是大多数数据库系统的默认事务隔离级别（例如 Oracle、SQL Server），但不是 MySQL 默认的。

可能导致情况：不可重复读

实例
先将事务隔离级别修改为读提交。
MySQL学习日记（五）MySQL事务和字符集
开启另一个窗口B，如图过程：

使用可重复读隔离级别可以解决实例中产生的不可重复读问题。

1.5.3 REPEATABLE READ（可重复读、RR）

在一些场景中，一个事务只能读取到另一个已提交事务修改过的数据，但是第一次读过某条记录后，即使其它事务修改了该记录的值并且提交，之后该事务再读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据。那么这种隔离级别就称之为可重复读。

可重复读是 MySQL 的默认事务隔离级别，它能确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。在该隔离级别下，如果有事务正在读取数据，就不允许有其它事务进行修改操作，这样就解决了可重复读问题。

实例
第一步：
MySQL学习日记（五）MySQL事务和字符集
第二步：

1.5.4 SERIALIZABLE（串行化、效率低）

如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。那么这种隔离级别就称之为串行化。

SERIALIZABLE 是最高的事务隔离级别，主要通过强制事务排序来解决幻读问题。简单来说，就是在每个读取的数据行上加上共享锁实现，这样就避免了脏读、不可重复读和幻读等问题。但是该事务隔离级别执行效率低下，且性能开销也最大，所以一般情况下不推荐使用。

1.6 查看和修改事务的级别

1.6.1 查看事务的级别

MySQL 8.0.3版本以前使用：

show variables like '%tx_isolation%';
select @@tx_isolation;
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;

8.0.3版本起：tx_isolation 变量被 transaction_isolation 变量替换了。
MySQL学习日记（五）MySQL事务和字符集

1.6.2 修改事务的隔离级别

MySQL 提供了 SET TRANSACTION 语句，该语句可以改变单个会话或全局的事务隔离级别。语法格式如下：

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

其中，SESSION 和 GLOBAL 关键字用来指定修改的事务隔离级别的范围。注意:

SESSION：表示修改的事务隔离级别将应用于当前 session（当前 cmd 窗口）内的所有事务；
GLOBAL：表示修改的事务隔离级别将应用于所有 session（全局）中的所有事务，且当前已经存在的 session 不受影响；
如果省略 SESSION 和 GLOBAL，表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。
任何用户都能改变会话的事务隔离级别，但是只有拥有 SUPER 权限的用户才能改变全局的事务隔离级别。

2、字符集和校对规则

2.1、什么是字符集和校对规则

字符（Character）
是计算机中字母、数字、符号的统称，一个字符可以是一个中文汉字、一个英文字母、一个阿拉伯数字、一个标点符号等。计算机是以二进制的形式来存储数据的。平时我们在显示器上看到的数字、英文、标点符号、汉字等字符都是二进制数转换之后的结果。

字符集（Character set）
定义了字符和二进制的对应关系，为字符分配了唯一的编号。常见的字符集有 ASCII、GBK、IOS-8859-1 等。

字符编码（Character encoding）
也可以称为字集码，规定了如何将字符的编号存储到计算机中。
校对规则（Collation）
也可以称为排序规则，是指在同一个字符集内字符之间的比较规则。字符集和校对规则是一对多的关系，每个字符集都有一个默认的校对规则。字符集和校对规则相辅相成，相互依赖关联。

简单来说，字符集用来定义 MySQL 存储字符串的方式，校对规则用来定义 MySQL 比较
字符串的方式。

程序员必备：彻底弄懂常见的7种中文字符编码

有些数据库并没有清晰的区分开字符集和校对规则。例如，在 SQL Server 中创建数据库时，选择字符集就相当于选定了字符集和校对规则。

而在 MySQL 中，字符集和校对规则是区分开的，必须设置字符集和校对规则。一般情况下，没有特殊需求，只设置其一即可。只设置字符集时，MySQL 会将校对规则设置为字符集中对应的默认校对规则。

MySQL学习日记（五）MySQL事务和字符集

2.2 MySQL的字符集转换过程

MySQL 中字符集的转换过程如下：

在命令提示符窗口（cmd 命令行）中执行 MySQL 命令或 sql 语句时，这些命令或语句从“命令提示符窗口字符集”转换为“character_set_client”定义的字符集。
使用命令提示符窗口成功连接 MySQL 服务器后，就建立了一条“数据通信链路”，MySQL 命令或 sql 语句沿着“数据链路”传向 MySQL 服务器，由 character_set_client 定义的字符集转换为 character_set_connection 定义的字符集。
MySQL 服务实例收到数据通信链路中的 MySQL 命令或 sql 语句后，将 MySQL 命令或 sql 语句从 character_set_connection 定义的字符集转换为 character_set_server 定义的字符集。
若 MySQL 命令或 sql 语句针对于某个数据库进行操作，此时将 MySQL 命令或 sql 语句从 character_set_server 定义的字符集转换为 character_set_database 定义的字符集。
MySQL 命令或 sql 语句执行结束后，将执行结果设置为 character_set_results 定义的字符集。
执行结果沿着打开的数据通信链路原路返回，将执行结果从 character_set_results 定义的字符集转换为 character_set_client 定义的字符集，最终转换为命令提示符窗口字符集，显示到命令提示符窗口中。

2.3、查看字符集和校对规则

1、查看支持的字符集

show character set;
或者
SELECT * FROM information_schema.character_sets;

MySQL学习日记（五）MySQL事务和字符集
2、查看校对规则

 SHOW COLLATION LIKE '校对规则';
 或者
 SELECT * FROM information_schema.COLLATIONS;

在mysql8.0.21版本，使用方式2查出的校对规则有272行记录。
MySQL学习日记（五）MySQL事务和字符集

实例

mysql> SELECT CASE WHEN 'A' COLLATE gbk_chinese_ci = 'a' COLLATE gbk_chinese_ci then 1
    -> else 0 end;
+-------------------------------------------------------------------------------------+
| CASE WHEN 'A' COLLATE gbk_chinese_ci = 'a' COLLATE gbk_chinese_ci then 1
else 0 end |
+-------------------------------------------------------------------------------------+
|                                                                                   1 |
+-------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT CASE WHEN 'A' COLLATE gbk_bin = 'a' COLLATE gbk_bin then 1
    -> else 0 end;
+-----------------------------------------------------------------------+
| CASE WHEN 'A' COLLATE gbk_bin = 'a' COLLATE gbk_bin then 1
else 0 end |
+-----------------------------------------------------------------------+
|                                                                     0 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

gbk_chinese_ci 校对规则忽略大小写，所以认为两个“A“和“a”是相同的。 gbk_bin 校对规则不忽略大小写，则认为两个字符是不同的。

2.4 设置默认字符集和校对规则

2.4.1设置服务器的字符集和校对规则

修改服务器默认字符集和校对规则的方法如下。

1）可以在 my.ini 配置文件中设置服务器字符集和校对规则，添加内容如下：

[mysqld]
character-set-server=字符集名称

2）连接 MySQL 服务器时指定字符集：

mysql --default-character-set=字符集名称 -h 主机IP地址 -u 用户名 -p 密码

如果没有指定服务器字符集，MySQL 会默认使用 latin1 作为服务器字符集。如果只指定了字符集，没有指定校对规则，MySQL 会使用该字符集对应的默认校对规则。如果要使用字符集的非默认校对规则，需要在指定字符集的同时指定校对规则。

查看自己当前数据库字符集、服务器字符集和校对规则
MySQL学习日记（五）MySQL事务和字符集

2.4.2 表字符集和校对规则

表的字符集和校对规则在创建表的时候指定，也可以在创建完表后通过 ALTER TABLE 命令进行修改。
同样，如果表中已有记录，修改字符集后，原有的记录不会按照新的字符集重新存放。表的字段仍然使用原来的字符集。设置表的字符集规则和设置数据库字符集的规则基本类似：

如果指定了字符集和校对规则，使用指定的字符集和校对规则；
如果指定了字符集没有指定校对规则，使用指定字符集的默认校对规则；
如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集；
如果没有指定字符集和校对规则，使用数据库字符集和校对规则作为表的字符集和校对规则。

为了避免受到默认值的影响，推荐在创建表的时候指定字符集和校对规则

查看表的字符集和校对规则
MySQL学习日记（五）MySQL事务和字符集

2.4.3 列字符集和校对规则

MySQL 可以定义列级别的字符集和校对规则，主要是针对相同表的不同字段需要使用不同字符集的情况。一般遇到这种情况的几率比较小，这只是 MySQL 提供给我们一个灵活设置的手段。

列字符集和校对规则的定义可以在创建表时指定，或者在修改表时调整。语法格式如下：

ALTER TABLE 表名 MODIFY 列名  数据类型 CHARACTER SET 字符集名;

如果在创建列的时候没有特别指定字符集和校对规则，默认使用表的字符集和校对规则。

2.4.4 连接字符集和校对规则

上面所讲的设置方式，确定的都是数据保存的字符集和校对规则。实际应用中，还需要设置客户端和服务器之间交互的字符集和校对规则。

对于客户端和服务器的交互操作，MySQL 提供了 3 个不同的参数：character_set_client、character_set_connection 和 character_set_results，分别代表客户端、连接和返回结果的字符集。通常情况下，这 3 个字符集是相同的，这样可以确保正确读出用户写入的数据，尤其是中文字符。字符集不同时，容易导致写入的记录不能正确读出。

设置客户端和服务器连接的字符集和校对规则有以下几种方法：

1）在 my.ini 配置文件中，设置以下语句：

[mysql]
default-character-set=gbk

这样服务器启动后，所有连接默认使用 GBK 字符集进行连接。

2）可以通过以下命令来设置连接的字符集和校对规则，这个命令可以同时修改以上 3 个参数（character_set_client、character_set_connection 和 character_set_results）的值。

SET NAMES gbk;

使用这个方法可以“临时一次性地”修改客户端和服务器连接时的字符集为 gbk。

3）MySQL 还提供了下列 MySQL 命令“临时地”修改 MySQL“当前会话的”字符集和校对规则。

set character_set_client = gbk;
set character_set_connection = gbk;
set character_set_database = gbk;
set character_set_results = gbk;
set character_set_server = gbk;
set collation_connection = gbk_chinese_ci;
set collation_database = gbk_chinese_ci;
set collation_server = gbk_chinese_ci;

2.5 修改字符集过程学习

在实际应用中，如果一开始没有正确的设置字符集，在运行一段时间以后，才发现当前字符集不能满足要求，需要进行调整，但又不想丢弃这段时间的数据，这个时候就需要修改字符集。

做法描述
ALTER DATABASE 或 ALTER TABLE 命令对已经存在的数据没有作用，只对新创建的表或记录生效。如果想修改已存在数据的字符集，需要先将数据导出，经过适当的调整后，再重新导入。

下面演示：将原来的表字符集为gb2312改为gbk

第一步：创建字符集为gb2312的表，并插入数据；
第二步：导出表结构，如图所示；

mysqldump -uroot -p --default-character-set=gbk -d test testset> D:\testset.sql

MySQL学习日记（五）MySQL事务和字符集
第三步，打开导出的表结构，将其字符集改为gbk，保存，可能需要用到管理员权限。

第四步，确保表中的记录不再更新，导出所有记录。导出表数据；

mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=gb2312 test testset> D:\testdata.sql

–quick：该选项用于存储记录多的表。它强制 mysqldump 从服务器一次一行地查询表中的行，而不是查询所有行，并在输出前将它缓存到内存中。
–extended-insert：使用 INSERT 插入多行数据语法。可以使文件更小，导入文件时加速插入。
–no-create-info：不导出表的 CREATE TABLE 语句。
–default-character-set=gb2312：按照原有的字符集导出所有数据。这样导出的文件中，所有中文都是可见的，不会保存成乱码。

第五步，打开 testdata.sql，将 SET NAMES gb2312 修改成 SET NAMES gbk，如下图所示。
MySQL学习日记（五）MySQL事务和字符集
第六步，创建一个数据库，默认字符集为gbk；

CREATE DATABASE test2 DEFAULT CHARSET gbk;

第七步，导入表结构、导入表数据。

mysql -uroot -p test2 < D:\testset.sql
mysql -uroot -p test2 < D:\testdata.sql

第八步，查看表结构、表数据，竟然没有变为gbk字符集，纳闷。
MySQL学习日记（五）MySQL事务和字符集

选择目标字符集的时候，要注意最好的是原字符集的超集，或者确定比原字符集的字库更大，否则如果目标字符集的字库小于原字符集的字库，那么目标字符集中不支持的字符导入后会变成乱码，丢失一部分数据。

2.6 字符集如何选择？

由于数据库中存储的数据大部分都是各种文字，所以字符集对数据库的存储、处理性能，以及日后系统的移植、推广都会有影响。对数据库来说，字符集非常重要。不论是在 MySQL 数据库还是其它数据库，都存在字符集的选择问题。

如果在创建数据库时没有正确选择字符集，在后期就可能需要更换字符集，而更换字符集是代价比较高的操作，也存在一定的风险。所以推荐在应用开始阶段，就按照实际需求，正确的选择合适的字符集，避免后期不必要的调整。

在选择数据库字符集时，可以根据应用的需求，结合字符集的特点来权衡，主要考虑以下几方面的因素。

满足应用支持语言的需求。如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，就应该选择 Unicode 字符集。对 MySQL 来说，目前就是 UTF-8。
如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。假如已有数据的字符集是 GBK，如果选择 GB 2312-80 为数据库字符集，就很可能出现某些文字无法正确导入。
如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如 GBK。
因为，相对于 UTF-8 而言，GBK 比较“小”，每个汉字只占 2 个字节，而 UTF-8 汉字编码需要 3 个字节，这样可以减少磁盘 I/O、数据库 Cache 以及网络传输的时间，从而提高性能。相反，如果应用主要处理英文字符，仅有少量汉字数据，那么选择 UTF-8 更好，因为 GBK、UCS-2、UTF-16 的西文字符编码都是 2 个字节，会造成很多不必要的开销。
如果数据库需要做大量的字符运算，如比较、排序等，那么选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快。
如果所有客户端程序都支持相同的字符集，则应该优先选择该字符集作为数据库字符集。这样可以避免因字符集转换带来的性能开销和数据损失。
在多种字符集都能够满足应用的前提下，应尽量使用小的字符集。因为更小的字符集意味着能够节省空间、减少网络传输字节数，同时由于存储空间的较小间接的提高了系统的性能。

有很多字符集都可以保存汉字，比如 UTF-8、GB2312、GBK、Latin1 等等。但是常用的是 GB2312 和 GBK。因为 GB2312 字库比 GBK 字库小，有些偏僻字（例如：洺）不能保存，因此在选择字符集的时候一定要权衡这些偏僻字出现的几率，一般情况下，最好选用 GBK。

本文地址：https://blog.csdn.net/qq_44861675/article/details/107645352

MySQL学习日记（五）MySQL事务和字符集

文章目录

MySQL事务和字符集

1、事务

1.1 MySQL为什么需要事务

1.2 数据库中事务的概念和特性

1.3 执行事务的语法和流程

1.4 设置事务自动提交

1.5 事务的隔离级别

1.5.1 READ UNCOMITTED（读未提交、RU）

1.5.2 READ COMMITTED（读提交、RC）

1.5.3 REPEATABLE READ（可重复读、RR）

1.5.4 SERIALIZABLE（串行化、效率低）

1.6 查看和修改事务的级别

1.6.1 查看事务的级别

1.6.2 修改事务的隔离级别

2、字符集和校对规则

2.1、什么是字符集和校对规则

2.2 MySQL的字符集转换过程

2.3、查看字符集和校对规则

2.4 设置默认字符集和校对规则

2.4.1设置服务器的字符集和校对规则

2.4.2 表字符集和校对规则

2.4.3 列字符集和校对规则

2.4.4 连接字符集和校对规则

2.5 修改字符集过程学习

2.6 字符集如何选择？

MongoDB系列教程（五）：mongo语法和mysql语法对比学习

mysql的学习（八）-储存过程和事务和导入导出

MySQL学习日记（五）MySQL事务和字符集

MYSQL入门学习之五：MYSQL的字符集_MySQL

深入浅出MongoDB（五）mongo语法和mysql语法对比学习

MySQL和Oracle对比学习之事务

跟我一起学习MySQL技术内幕（第五版）：（第三章学习日记3）

mysql学习3外键、事务和索引

跟我一起学习MySQL技术内幕（第五版）：（第三章学习日记2上）

MongoDB系列教程（五）：mongo语法和mysql语法对比学习