9. MySQL事务、字符集

2024-06-05 01:04
文章标签 mysql 事务 database 字符集

本文主要是介绍9. MySQL事务、字符集，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

  • 【 1. 事务 Transaction 】
    • 1.1 事务的基本原理
    • 1.2 MySQL 执行事务的语法和流程
      • 1.2.1 开始事务
      • 1.2.2 提交事务
      • 1.2.3 回滚（撤销）事务
      • 实例1：一致性
      • 实例2：原子性
  • 【 2. 字符集 和 校对规则 】
    • 2.1 基本原理
    • 2.2 查看字符集
      • 查看当前 MySQL 使用的字符集
      • 查看 MySQL 可用的字符集
    • 2.3 查看校对规则
      • 查看当前 MySQL 使用的校对规则
      • 查看 MySQL 中可用的校对规则
      • 实例：校对规则对字符比较的影响

【 1. 事务 Transaction 】

1.1 事务的基本原理

  • 问题背景
    当多个用户访问同一数据时，一个用户在更改数据的过程中可能有其它用户同时发起更改请求，为保证数据的一致性状态，MySQL 引入了事务。
  • 数据库的 事务（Transaction） 是 一种机制、一个操作序列，包含了一组数据库操作命令。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此 事务是一个不可分割的工作逻辑单元。
    在数据库系统上执行 并发操作时，事务是作为最小的控制单元 来使用的，特别适用于多用户同时操作的数据库系统。例如，航空公司的订票系统、银行、保险公司以及证券交易系统等。
  • 事务具有 4 个特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这 4 个特性通常简称为 ACID 。事务的 ACID 原则保证了一个事务或者成功提交，或者失败回滚，二者必居其一。因此，它对事务的修改具有可恢复性 ： 当事务失败时，它对数据的修改都会恢复到该事务执行前的状态。
    • 原子性（Atomicity）
      事务是一个完整的操作，事务的各元素是不可分的（原子的） 。事务中的所有元素必须作为一个整体提交或回滚，如果事务中的任何元素失败，则整个事务将失败。
      以银行转账事务为例，如果该事务提交了，则这两个账户的数据将会更新。如果由于某种原因，事务在成功更新这两个账户之前终止了，则不会更新这两个账户的余额，并且会撤销对任何账户余额的修改，事务不能部分提交。
    • 一致性（Consistency）
      当事务完成时，数据必须处于一致状态 。也就是说，在事务开始之前，数据库中存储的数据处于一致状态；在正在进行的事务中，数据可能处于不一致的状态，如数据可能有部分被修改；然而，当事务成功完成时，数据必须再次回到已知的一致状态。即 通过事务对数据所做的修改不能损坏数据，或者说事务不能使数据存储处于不稳定的状态。
      以银行转账事务事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改，此时，所有账户余额的总额处于不一致状态。当转账成功后，即另一个账户余额增加后，即事务完成以后，账户余额的总额再次恢复到一致状态。
    • 隔离性（Isolation）
      对数据进行修改的 所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
      • 修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。
      • 当事务修改数据时，如果任何其他进程正在同时使用相同的数据，则直到该事务成功提交之后，对数据的修改才能生效。
        例如，张三和李四之间的转账与王五和赵二之间的转账，永远是相互独立的。
    • 持久性（Durability）
      事务的持久性指 一个事务成功完成之后，它对数据库所作的改变是永久性的，即使系统出现故障也是如此。也就是说，一旦事务被提交，事务对数据所做的任何变动都会被永久地保留在数据库中。

1.2 MySQL 执行事务的语法和流程

  • 任何一种数据库，都会拥有各种各样的 日志 ，用来记录数据库的运行情况、日常操作、错误信息等，MySQL 也不例外。
    例如，当用户 root 登录到 MySQL 服务器，就会在日志文件里记录该用户的登录时间、执行操作等。
  • 为了维护 MySQL 服务器，经常需要在 MySQL 数据库中进行日志操作：
    • UNDO 日志 （中文译为取消）：复制事务执行前的数据，用于在事务发生异常时回滚数据。
    • REDO 日志 （中文译为重做）：记录在事务执行中，每条对数据进行更新的操作，当事务提交时，该内容将被刷新到磁盘。
  • MySQL 提供了多种存储引擎来支持事务 。支持事务的存储引擎有 InnoDB 和 BDB，InnoDB 存储引擎事务主要通过 UNDO 日志和 REDO 日志实现 ，MyISAM 存储引擎不支持事务。

1.2.1 开始事务

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

1.2.2 提交事务

  • 基本语法
    • COMMIT 表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束，这体现了事务的持久性。
    • 提交事务，意味着将事务开始以来所 执行的所有数据都修改成为数据库的永久部分，因此也 标志着一个事务的结束。一旦执行了该命令，将不能回滚事务。只有在所有修改都准备好提交给数据库时，才执行这一操作。
COMMIT;

1.2.3 回滚（撤销）事务

  • 基本语法
    • ROLLBACK 表示撤销事务，即在 事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态，并且释放由事务控制的资源。这里的操作指对数据库的更新操作。 ROLLBACK 语句也标志着事务的结束。
ROLLBACK;
  • 总结：
    BEGIN 或 START TRANSACTION 语句后面的 SQL 语句对数据库数据的更新操作都将记录在事务日志中，直至遇到 ROLLBACK 语句或 COMMIT 语句。
    • 如果事务中某一操作失败且执行了 ROLLBACK 语句，那么在开启事务语句之后所有更新的数据都能回滚到事务开始前的状态。
    • 如果事务中的所有操作都全部正确完成，并且使用了 COMMIT 语句向数据库提交更新数据，则此时的数据又处在新的一致状态。

实例1：一致性

  • 下面模拟在张三的账户减少 500 元后，李四的账户还未增加 500 时，有其他会话访问数据表的场景。由于代码需要在两个窗口中执行，为了方便阅读，这里我们称为 A 窗口和 B 窗口。
  1. 创建数据表 bank。
    CREATE TABLE bank(
    customerName VARCHAR(25),
    currentMoney float
    );
  2. 插入数据。
    INSERT INTO bank VALUES("张三",500),("李四",500);
  3. 在 A 窗口中开启一个事务，并更新 bank 数据表的数据：让张三的currentMoney - 500。
    BEGIN;
    UPDATE bank SET currentMoney = currentMoney-500 WHERE customerName='张三';
  4. 在 B 窗口中查询 bank 数据表中的数据。
    从结果可以看出，虽然 A 窗口中的事务已经更改了 bank 表中的数据，但没有立即更新数据这是因为还没有提交事务，这时其他会话读取到的仍然是更新前的数据。
    SELECT * FROM bank;
    在这里插入图片描述
  5. 在 A 窗口中继续执行事务：让李四的 currentMoney+500，最后提交事务。
    UPDATE bank SET currentMoney = currentMoney+500 WHERE customerName='李四';
    COMMIT;
  6. 在 B 窗口中再次查询 bank 数据表的数据。
    结果可以看出张三和李四的账户余额总和与转账前保持一致，这样数据从一个一致性状态更新到另一个一致性状态。
    SELECT * FROM bank;
    在这里插入图片描述

实例2：原子性

  • 在例 1 中，张三的账户余额已经减少到 0 元，如果再转出 100 元，将会出现余额为负数，因此需要回滚到原始状态。
  1. 将张三的账户余额减少 1000 元。
    BEGIN;
    UPDATE bank SET currentMoney = currentMoney-1000 WHERE customerName='张三';
  2. 让事务回滚
    ROLLBACK;
  3. 查看结果。
    SELECT * FROM bank;
    在这里插入图片描述

【 2. 字符集 和 校对规则 】

2.1 基本原理

  • 字符（Character） 是计算机中字母、数字、符号的统称，一个字符可以是一个中文汉字、一个英文字母、一个阿拉伯数字、一个标点符号等。
  • 计算机是以二进制的形式来存储数据的。平时我们在显示器上看到的数字、英文、标点符号、汉字等 字符都是二进制数转换之后的结果。
  • 字符集（Character set） 定义了 字符和二进制的对应关系，为字符分配了唯一的编号 。常见的字符集有 ASCII、GBK、IOS-8859-1 等。
  • 字符编码/字集码（Character encoding） 规定了 如何将字符的编号存储到计算机中 。
    • 大部分字符集都只对应一种字符编码，例如：ASCII、IOS-8859-1、GB2312、GBK，都是既表示了字符集又表示了对应的字符编码，所以一般情况下，可以将字符集和字符编码视为同义词。
    • 而 Unicode 有三种编码方案：UTF-8、UTF-16 和 UTF-32。最为常用的是 UTF-8 编码。
  • 校对规则/排序规则（Collation） 是指 在同一个字符集内字符之间的比较规则。字符集和校对规则是一对多的关系，每个字符集都有一个默认的校对规则。字符集和校对规则相辅相成，相互依赖关联。
    简单来说，字符集用来定义存储字符串的方式，校对规则用来定义 比较字符串的方式。
    • 有些数据库并没有清晰的区分开字符集和校对规则。例如，在 SQL Server 中创建数据库时，选择字符集就相当于选定了字符集和校对规则。
    • 而在 MySQL 中，字符集和校对规则是区分开的，必须设置字符集和校对规则。一般情况下，没有特殊需求，只设置其一即可。只设置字符集时，MySQL 会将校对规则设置为字符集中对应的默认校对规则。
  • MySQL字符集的转换过程
    1. 在命令提示符窗口（cmd 命令行）中执行 MySQL 命令或 sql 语句时，这些命令或语句从 命令提示符窗口字符集 转换为 character_set_client MySQL 客户端使用的字符集。
    2. 使用命令提示符窗口成功连接 MySQL 服务器后，就建立了一条“数据通信链路”，MySQL 命令或 sql 语句沿着“数据链路”传向 MySQL 服务器，由 character_set_client 定义的字符集转换为 character_set_connection 连接数据库时使用的字符集。
    3. MySQL 服务实例收到数据通信链路中的 MySQL 命令或 sql 语句后，将 MySQL 命令或 sql 语句从 character_set_connection 定义的字符集转换为 character_set_server MySQL 服务器使用的字符集。
    4. 若 MySQL 命令或 sql 语句针对于某个数据库进行操作，此时将 MySQL 命令或 sql 语句从 character_set_server 定义的字符集转换为 character_set_database 创建数据库使用的字符集。
    5. MySQL 命令或 sql 语句执行结束后，将执行结果设置为 character_set_results 数据库给客户端返回数据时使用的字符集。
    6. 执行结果沿着打开的数据通信链路原路返回，将执行结果从 character_set_results 定义的字符集转换为 character_set_client MySQL 客户端使用的字符集，最终转换为命令提示符窗口字符集，显示到命令提示符窗口中。
      在这里插入图片描述

2.2 查看字符集

查看当前 MySQL 使用的字符集

  • 基本语法：
SHOW VARIABLES LIKE 'character%';

在这里插入图片描述
乱码时，不需要关心 character_set_filesystem、character_set_system 和 character_sets_dir 这 3 个系统变量，它们不会影响乱码 。

名称说明
character_set_clientMySQL 客户端使用的字符集
character_set_connection连接数据库时使用的字符集
character_set_database创建数据库使用的字符集
character_set_filesystemMySQL 服务器文件系统使用的字符集，默认值为 binary，不做任何转换
character_set_results数据库给客户端返回数据时使用的字符集
character_set_serverMySQL 服务器使用的字符集，建议由系统自己管理，不要人为定义
character_set_system数据库系统使用的字符集，默认值为 utf8，不需要设置
character_sets_dir字符集的安装目录

查看 MySQL 可用的字符集

  • 基本语法：
SHOW CHARACTER set;# 或者通过查询 information_schema.character_set 表中的记录，来查看 MySQL 支持的字符集：
SELECT * FROM information_schema.character_sets;

- 第一列（Charset）为字符集名称；
- 第二列（Description）为字符集描述；
- 第三列（Default collation）为字符集的默认校对规则；
- 第四列（Maxlen）表示字符集中一个字符占用的最大字节数。

在这里插入图片描述

2.3 查看校对规则

查看当前 MySQL 使用的校对规则

  • 基本语法
    • collation_connection：连接数据库时使用的校对规则
    • collation_database：创建数据库时使用的校对规则
    • collation_server：MySQL 服务器使用的校对规则
    • 校对规则命令约定如下：
      • 以校对规则所对应的字符集名开头
      • 以国家名居中（或以 general 居中）
      • 以 ci、cs 或 bin 结尾，ci 表示大小写不敏感，cs 表示大小写敏感，bin 表示按二进制编码值比较。
SHOW VARIABLES LIKE '字符集名称';

在这里插入图片描述

  • 或者 使用 SHOW COLLATION LIKE 'gbk%';
    下面运行结果为 GBK 字符集所对应的校对规则，其中 gbk_chinese_ci 是默认的校对规则，对大小写不敏感。而 gbk_bin 按照二进制编码的值进行比较，对大小写敏感。
    在这里插入图片描述

查看 MySQL 中可用的校对规则

  • 通过查询 information_schema.COLLATIONS 表中的记录，来查看 MySQL 中可用的校对规则。
    基本语法：
SELECT * FROM information_schema.COLLATIONS;

在这里插入图片描述

实例：校对规则对字符比较的影响

注意此时不要用 unicode 的命令行。

  • 实例1
    指定“A”和“a”按照 gbk_bin 校对规则进行比较。
    gbk_bin 校对规则不忽略大小写，则认为两个字符是不同的，最终输出结果是0。
SELECT CASE WHEN 
'A' COLLATE gbk_bin = 'a' COLLATE gbk_binthen 1 else 0 
end;

在这里插入图片描述

  • 实例2
    指定“A”和“a”按照 gbk_chinese_ci 校对规则进行比较。
    由于 gbk_chinese_ci 校对规则忽略大小写，所以认为两个“A“和“a”是相同的，最终输出结果是1。
SELECT CASE WHEN 
'A' COLLATE gbk_chinese_ci = 'a' COLLATE gbk_chinese_ci then 1 else 0 
end;

在这里插入图片描述

这篇关于9. MySQL事务、字符集的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1031630。

相关文章

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》：本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1：以跳过权限验证模式启动 mysql 并重置密码方法 2：使用 my.ini 文件的临时配置在 Wi
阅读更多...
MySQL重复数据处理的七种高效方法

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼：明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案：使用异常捕获3.
阅读更多...
SQL中redo log 刷⼊磁盘的常见方法

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例（伪代码）在数据库系统中，r
阅读更多...
mysql中的group by高级用法

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使
阅读更多...
Mysql用户授权(GRANT)语法及示例解读

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》：本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权（GRANT）语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT
阅读更多...
Mysql如何解决死锁问题

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》：本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1
阅读更多...
SQL BETWEEN 的常见用法小结

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中，BETWEEN是一个操作符，用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用
阅读更多...
MySQL索引的优化之LIKE模糊查询功能实现

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》：本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分
阅读更多...
MySql match against工具详细用法

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引（Full-Textindex）的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七
阅读更多...
数据库面试必备之MySQL中的乐观锁与悲观锁

数据库面试必备之MySQL中的乐观锁与悲观锁

《数据库面试必备之MySQL中的乐观锁与悲观锁》：本文主要介绍数据库面试必备之MySQL中乐观锁与悲观锁的相关资料,乐观锁适用于读多写少的场景,通过版本号检查避免冲突,而悲观锁适用于写多读少且对数... 目录一、引言二、乐观锁（一）原理（二）应用场景（三）示例代码三、悲观锁（一）原理（二）应用场景（三）示例
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2