在我们的项目开发过程中，经常需要将时间戳或日期时间字段转换为特定的格式，以满足特定的业务需求。MySQL作为广泛使用的关系型数据库管理系统，提供了丰富的日期和时间函数。本文将介绍如何在MySQL中将时间戳或日期时间字段转换为年月日的格式。一、MySQL中的日期和时间类型在MySQL中，日期和时间相关的数据类型主要有以下集中：DATE：仅包含日期部分，格式为’YYYY-MM-DD’TIME：仅包含时间部分，格式为’HH:MM:SS’DATETIME：包含日期和时间部分，格式为’YYYY-MM-DD HH:MM:SS’TIMESTAMP：与DATETIME类似，但范围较小，且与时区相关二、使用DATE_FORMAT函数进行转换在MySQL中，我们可以使用DATE_FORMAT函数将日期时间字段转换为特定的格式。DATE_FORMAT函数的语法如下：1DATE_FORMAT(date, format)其中，date 是要格式化的日期或时间值，format 是指定的格式字符串。要将日期时间字段转换为年月日的格式，我们可以使用以下查询：12SELECT DATE_FORMAT(your_datetime_column, '%Y-%m-%d') AS formatted_date   FROM your_table;在这个例子中，your_datetime_column是包含日期时间值的列名，your_table是表名。%Y 代表四位数的年份，%m代表两位数的月份，%d 代表两位数的日期。查询结果将返回一个名为formatted_date的列，其中包含按照指定格式转换后的日期。

在MySQL中，我们可以通过WITH AS方法创建临时结果集，这些结果集可以在后续的SELECT、DELETE和UPDATE语句中被使用。通过使用WITH AS，我们可以将复杂的语句和功能分解为更小的、更易于管理的部分，从而提高SQL语句的可读性和可维护性。一、WITH AS 方法的基本语法WITH AS的基本语法如下：WITH cte_name (column1, column2, ...) AS (       -- CTE 的定义，即一个 SELECT 语句       SELECT column1, column2, ...       FROM table_name       WHERE condition       -- 其他可能的 SQL 语句，如 JOIN、GROUP BY 等   )   SELECT * FROM cte_name; 在这个语法中，cte_name是你为临时结果集定义的名称，而括号内的部分则是用于生成这个结果集的SQL查询。这个查询可以是任何有效的SELECT语句，包括JOIN、GROUP BY、HAVING等子句。二、使用 WITH AS 创建临时表的案例假设我们有一个销售数据库，其中包含一个名为orders的表，记录了所有的订单信息。现在，我们想要找出每个客户的总订单金额，并按金额降序排列。我们可以使用WITH AS方法来实现这个需求，将计算总订单金额的逻辑封装成一个临时结果集。WITH CustomerTotals AS (       SELECT customer_id, SUM(order_amount) AS total_amount       FROM orders       GROUP BY customer_id   )   SELECT customer_id, total_amount   FROM CustomerTotals   ORDER BY total_amount DESC; 在这个例子中，我们首先使用WITH AS创建了一个名为CustomerTotals的临时结果集，它包含了每个客户的总订单金额。然后，我们在主查询中从这个临时结果集中选择数据，并按总金额降序排列。通过使用WITH AS，我们将复杂的查询逻辑分解为了两个更简单的部分：一个是计算总订单金额，另一个是基于这个计算结果进行排序。这使得查询更加清晰，也更容易理解和维护。三、WITH AS的优势提高可读性：通过将复杂的查询分解为多个简单的部分，WITH AS使得查询逻辑更加清晰，提高了代码的可读性。可重用性：临时结果集（CTE）可以在后续的查询中被多次引用，避免了重复编写相同的SQL逻辑。模块化：通过将查询分解为多个CTE，可以更容易地对每个部分进行单独测试和优化，实现查询的模块化。简化嵌套查询：对于包含多层嵌套子查询的复杂查询，使用WITH AS可以将其扁平化，使得查询结构更加直观。

MySQL建立分区的条件是什么是MySQL分区？MySQL分区是将一张表分割成独立的子表的技术。每个子表被称为分区，它们有着相同的结构和字段，但存储着不同的数据。这项技术可以提高查询速度，减少日志文件和磁盘空间的使用。建立分区的条件要建立MySQL分区，需要满足以下几个条件：1.所需的MySQL版本：MySQL 5.1.5及以上版本支持分区，但仅限于使用InnoDB和MyISAM存储引擎的表。2.分区字段：必须定义一个或多个分区字段来确定如何将数据行分配到各个分区中。分区字段必须是表的主键或唯一索引之一。3.分区类型：MySQL提供了多种分区类型，包括范围分区、哈希分区和列表分区。你需要根据数据特点和查询需求选择合适的分区类型。4.分区数量：决定分区数量需要考虑表的大小、查询的复杂度、硬件资源等因素。建议根据具体情况选取合适的分区数量，一般不宜超过1000个。MySQL分区技术可以大大提高查询效率和管理的便利性，但在实际使用中需要根据具体情况选择合适的分区条件和数量，避免性能瓶颈和资源浪费。分区表介绍MySQL 数据库中的数据是以文件的形势存在磁盘上的，默认放在 /var/lib/mysql/ 目录下面，我们可以通过 show variables like '%datadir%'; 命令来查看：在 MySQL 中，如果存储引擎是 MyISAM，那么在 data 目录下会看到 3 类文件：.frm、.myi、.myd，如下：*.frm：这个是表定义，是描述表结构的文件。*.myd：这个是数据信息文件，是表的数据文件。*.myi：这个是索引信息文件。如果存储引擎是 InnoDB, 那么在 data 目录下会看到两类文件：.frm、.ibd，如下：*.frm：表结构文件。*.ibd：表数据和索引的文件。无论是哪种存储引擎，只要一张表的数据量过大，就会导致 *.myd、*.myi 以及 *.ibd 文件过大，数据的查找就会变的很慢。为了解决这个问题，我们可以利用 MySQL 的分区功能，在物理上将这一张表对应的文件，分割成许多小块，如此，当我们查找一条数据时，就不用在某一个文件中进行整个遍历了，我们只需要知道这条数据位于哪一个数据块，然后在那一个数据块上查找就行了；另一方面，如果一张表的数据量太大，可能一个磁盘放不下，这个时候，通过表分区我们就可以把数据分配到不同的磁盘里面去。通俗地讲表分区是将一大表，根据条件分割成若干个小表。如：某用户表的记录超过了600万条，那么就可以根据入库日期将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。MySQL 从 5.1 开始添加了对分区的支持，分区的过程是将一个表或索引分解为多个更小、更可管理的部分。对于开发者而言，分区后的表使用方式和不分区基本上还是一模一样，只不过在物理存储上，原本该表只有一个数据文件，现在变成了多个，每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。需要注意的是，分区功能并不是在存储引擎层完成的，常见的存储引擎如 InnoDB、MyISAM、NDB 等都支持分区。但并不是所有的存储引擎都支持，如 CSV、FEDORATED、MERGE 等就不支持分区，因此在使用此分区功能前，应该对选择的存储引擎对分区的支持有所了解。表分区的优缺点和限制MySQL分区有优点也有一些缺点，如下：优点：查询性能提升：分区可以将大表划分为更小的部分，查询时只需扫描特定的分区，而不是整个表，从而提高查询性能。特别是在处理大量数据或高并发负载时，分区可以显著减少查询的响应时间。管理和维护的简化：使用分区可以更轻松地管理和维护数据。可以针对特定的分区执行维护操作，如备份、恢复、优化和数据清理，而不必处理整个表。这简化了维护任务并减少了操作的复杂性。数据管理灵活性：通过分区，可以根据业务需求轻松地添加或删除分区，而无需影响整个表。这使得数据的增长和变化更具弹性，可以根据需求进行动态调整。改善数据安全性和可用性：可以将不同分区的数据分布在不同的存储设备上，从而提高数据的安全性和可用性。例如，可以将热数据放在高速存储设备上，而将冷数据放在廉价存储设备上，以实现更高的性能和成本效益。缺点：复杂性增加：分区引入了额外的复杂性，包括分区策略的选择、表结构的设计和维护、查询逻辑的调整等。正确地设置和管理分区需要一定的经验和专业知识。索引效率下降：对于某些查询，特别是涉及跨分区的查询，可能会导致索引效率下降。由于查询需要在多个分区之间进行扫描，可能无法充分利用索引优势，从而影响查询性能。存储空间需求增加：使用分区会导致一定程度的存储空间浪费。每个分区都需要占用一定的存储空间，包括分区元数据和一些额外的开销。因此，对于分区键的选择和分区粒度的设置需要权衡存储空间和性能之间的关系。功能限制：在某些情况下，分区可能会限制某些MySQL的功能和特性的使用。例如，某些类型的索引可能无法在分区表上使用，或者某些DDL操作可能需要更复杂的处理。在考虑使用分区时，需要综合考虑业务需求、查询模式、数据规模和硬件资源等因素，并权衡分区带来的优势和缺点。对于特定的应用和数据场景，分区可能是一个有效的解决方案，但并不适用于所有情况。同时分区表也存在一些限制，如下：限制：在mysql5.6.7之前的版本，一个表最多有1024个分区；从5.6.7开始，一个表最多可以有8192个分区。分区表无法使用外键约束。NULL值会使分区过滤无效。所有分区必须使用相同的存储引擎。分区适用场景分区表在以下情况下可以发挥其优势，适用于以下几种使用场景：大型表处理：当面对非常大的表时，分区表可以提高查询性能。通过将表分割为更小的分区，查询操作只需要处理特定的分区，从而减少扫描的数据量，提高查询效率。这在处理日志数据、历史数据或其他需要大量存储和高性能查询的场景中非常有用。时间范围查询：对于按时间排序的数据，分区表可以按照时间范围进行分区，每个分区包含特定时间段内的数据。这使得按时间范围进行查询变得更高效，例如在某个时间段内检索数据、生成报表或执行时间段的聚合操作。数据归档和数据保留：分区表可用于数据归档和数据保留的需求。旧数据可以归档到单独的分区中，并将其存储在低成本的存储介质上。同时，可以保留较新数据在高性能的存储介质上，以便快速查询和操作。并行查询和负载均衡：通过哈希分区或键分区，可以将数据均匀地分布在多个分区中，从而实现并行查询和负载均衡。查询可以同时在多个分区上进行，并在最终合并结果，提高查询性能和系统吞吐量。数据删除和维护：使用分区表，可以更轻松地删除或清理不再需要的数据。通过删除整个分区，可以更快速地删除大量数据，而不会影响整个表的操作。此外，可以针对特定分区执行维护任务，如重新构建索引、备份和优化，以减少对整个表的影响。分区表并非适用于所有情况。在选择使用分区表时，需要综合考虑数据量、查询模式、存储资源和硬件能力等因素，并评估分区对性能和管理的影响。分区方式分区有2种方式，水平切分和垂直切分。MySQL 数据库支持的分区类型为水平分区，它不支持垂直分区。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。分区策略RANGE分区RANGE分区是MySQL中的一种分区策略，根据某一列的范围值将数据分布到不同的分区。每个分区包含特定的范围。下面是RANGE分区的定义方式、特点以及代码示例。定义方式：指定分区键：选择作为分区依据的列作为分区键，通常是日期、数值等具有范围特性的列。分区函数：通过PARTITION BY RANGE指定使用RANGE分区策略。定义分区范围：使用VALUES LESS THAN子句定义每个分区的范围。RANGE分区的特点：范围划分：根据指定列的范围进行分区，适用于需要按范围进行查询和管理的情况。灵活的范围定义：可以定义任意数量的分区，并且每个分区可以具有不同的范围。高效查询：根据查询条件的范围，MySQL能够快速定位到特定的分区，提高查询效率。动态管理：可以根据业务需求轻松添加或删除分区，适应数据增长或变更的需求。在上述示例中，我们创建了名为sales的表，使用RANGE分区策略。根据sales_date列的年份范围将数据分布到不同的分区。PARTITION BY RANGE (YEAR(sales_date))：指定使用RANGE分区，基于sales_date列的年份进行分区。PARTITION p1 VALUES LESS THAN (2020)：定义名为p1的分区，包含年份小于2020的数据。PARTITION p2 VALUES LESS THAN (2021)：定义名为p2的分区，包含年份小于2021的数据。PARTITION p3 VALUES LESS THAN (2022)：定义名为p3的分区，包含年份小于2022的数据。PARTITION p4 VALUES LESS THAN MAXVALUE：定义名为p4的分区，包含超出定义范围的数据。RANGE分区允许根据列值的范围将数据分散到不同的分区中，适用于按范围进行查询和管理的情况。它提供了更灵活的数据管理和查询效率的提升。LIST分区LIST分区是根据某一列的离散值将数据分布到不同的分区。每个分区包含特定的列值列表。下面是LIST分区的定义方式、特点以及代码示例。定义方式：指定分区键：选择作为分区依据的列作为分区键，通常是具有离散值的列，如地区、类别等。分区函数：通过PARTITION BY LIST指定使用LIST分区策略。定义分区列表：使用VALUES IN子句定义每个分区包含的列值列表。LIST分区的特点：列值离散：根据指定列的具体取值进行分区，适用于具有离散值的列。灵活的分区定义：可以定义任意数量的分区，并且每个分区可以具有不同的列值列表。高效查询：根据查询条件的列值直接定位到特定分区，提高查询效率。动态管理：可以根据业务需求轻松添加或删除分区，适应数据增长或变更的需求。在上述示例中，我们创建了名为users的表，使用LIST分区策略。根据region列的具体取值将数据分布到不同的分区。PARTITION BY LIST (region)：指定使用LIST分区，基于region列的值进行分区。PARTITION p_east VALUES IN ('New York', 'Boston')：定义名为p_east的分区，包含值为’New York’和’Boston’的region列的数据。PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco')：定义名为p_west的分区，包含值为’Los Angeles’和’San Francisco’的region列的数据。PARTITION p_other VALUES IN (DEFAULT)：定义名为p_other的分区，包含其他region列值的数据。HASH分区是使用哈希算法将数据均匀地分布到多个分区中。下面是HASH分区的定义方式、特点以及代码示例。定义方式：指定分区键：选择作为分区依据的列作为分区键。分区函数：通过PARTITION BY HASH指定使用HASH分区策略。定义分区数量：使用PARTITIONS关键字指定分区的数量。HASH分区的特点：数据均匀分布：HASH分区使用哈希算法将数据均匀地分布到不同的分区中，确保数据在各个分区之间平衡。并行查询性能：通过将数据分散到多个分区，HASH分区可以提高并行查询的性能，多个查询可以同时在不同分区上执行。简化管理：HASH分区使得数据管理更加灵活，可以轻松地添加或删除分区，以适应数据增长或变更的需求。我们创建了名为sensor_data的表，使用HASH分区策略。根据id列的哈希值将数据分布到4个分区中。PARTITION BY HASH (id)：指定使用HASH分区，基于id列的哈希值进行分区。PARTITIONS 4：指定创建4个分区。KEY分区KEY分区是根据某一列的哈希值将数据分布到不同的分区。不同于HASH分区，KEY分区使用的是列值的哈希值而不是哈希函数。下面是KEY分区的定义方式、特点以及代码示例。定义方式：指定分区键：选择作为分区依据的列作为分区键。分区函数：通过PARTITION BY KEY指定使用KEY分区策略。定义分区数量：使用PARTITIONS关键字指定分区的数量。KEY分区的特点：哈希分布：KEY分区使用列值的哈希值将数据分布到不同的分区中，与哈希函数不同，它使用的是列值的哈希值。高度自定义：KEY分区允许根据业务需求自定义分区逻辑，可以灵活地选择分区键和分区数量。并行查询性能：通过将数据分散到多个分区，KEY分区可以提高并行查询的性能，多个查询可以同时在不同分区上执行。简化管理：KEY分区使得数据管理更加灵活，可以轻松地添加或删除分区，以适应数据增长或变更的需求。HASH分区

随着数据量的不断增长，数据库的性能和扩展性面临越来越大的挑战。为了解决这些问题，MySQL提供了多种数据分割方案，其中最常见的是分表和分区分表。虽然这两种方法都是为了提高数据库性能和管理效率，但它们在实现原理、应用场景和操作方式上存在显著差异。一、什么是分表？分表（Sharding）是将一个大型表的数据按某种规则拆分到多个独立的表中。分表的目的是将数据分散到多个存储单元中，以减轻单表的数据量和访问压力，从而提高数据库的性能和可扩展性。1.1 分表的实现方式分表可以在应用层或者通过数据库中间件来实现。常见的分表策略有：水平分表（Horizontal Sharding）：根据某个字段的值（如用户ID、订单ID等）将数据划分到多个表中，每个表结构相同但存储不同的数据。垂直分表（Vertical Sharding）：根据业务功能或数据模块将表的列拆分到多个表中，每个表存储不同的列，但所有表的主键相同。1.2 分表的示例假设有一个用户表 users，包含大量用户数据，可以按用户ID进行水平分表： CREATE TABLE users_0 (     id INT PRIMARY KEY,     name VARCHAR(50),     email VARCHAR(50) );  CREATE TABLE users_1 (     id INT PRIMARY KEY,     name VARCHAR(50),     email VARCHAR(50) );  -- 应用程序中实现分表逻辑 public String getTableName(int userId) {     int tableIndex = userId % 2;     return "users_" + tableIndex; } 二、什么是分区分表？分区分表（Partitioning）是将一个表的数据按某种规则划分成多个分区，每个分区存储一部分数据。分区分表的目的是优化查询性能和管理效率，特别是在处理大数据量时。2.1 分区分表的类型MySQL支持多种分区类型，常见的有：范围分区（Range Partitioning）：按数值或日期范围划分数据。列表分区（List Partitioning）：按离散的值列表划分数据。哈希分区（Hash Partitioning）：按哈希函数的结果划分数据。键分区（Key Partitioning）：类似于哈希分区，但使用MySQL内置的函数。2.2 分区分表的示例假设有一个订单表 orders，可以按订单日期进行范围分区：CREATE TABLE orders (     id INT PRIMARY KEY,     order_date DATE,     amount DECIMAL(10, 2) ) PARTITION BY RANGE (YEAR(order_date)) (     PARTITION p2021 VALUES LESS THAN (2022),     PARTITION p2022 VALUES LESS THAN (2023),     PARTITION p2023 VALUES LESS THAN (2024) ); 三、分表与分区分表的区别3.1 数据存储结构分表：将数据拆分到多个独立的表中，这些表可以分布在同一个数据库或不同的数据库实例上。每个表都是独立的存储单元。分区分表：将数据划分成多个分区，所有分区仍然属于同一个表和同一个数据库实例。分区是表的逻辑部分，每个分区存储一部分数据。3.2 实现方式分表：通常在应用层或通过数据库中间件实现，需要编写代码逻辑或使用中间件配置来确定数据的存储位置。分区分表：在数据库层实现，通过SQL语句定义分区规则，数据库系统自动管理分区的数据存储和访问。3.3 管理和维护分表：需要手动管理各个分表，包括表的创建、数据迁移和备份恢复等操作。跨表查询需要应用程序处理或使用中间件支持。分区分表：数据库系统自动管理分区，支持自动分区裁剪和优化。跨分区查询由数据库系统处理，不需要额外的应用程序逻辑。3.4 性能与扩展性分表：适合大规模数据的分布式存储和高并发访问，可以通过增加数据库实例来扩展系统的存储和处理能力。但分表后的数据一致性和事务管理变得复杂。分区分表：适合中等规模的数据优化，主要提升查询性能和管理效率。受限于单个数据库实例的资源，扩展性相对较弱。3.5 使用场景分表：适用于数据量特别大、需要分布式存储和高并发访问的场景，如大型电商平台、社交网络等。分区分表：适用于大数据量的查询优化和管理，如日志数据、历史记录等。四、分表和分区分表的优缺点4.1 分表的优缺点优点：提高系统的可扩展性和高可用性。分散数据和负载，减轻单表压力。适用于大规模数据和高并发场景。缺点：实现和维护复杂，增加开发和运维成本。跨表查询复杂，可能需要中间件支持。数据一致性和事务管理变得困难。4.2 分区分表的优缺点优点：简化数据管理，支持自动分区裁剪和优化。提升查询性能，特别是按分区键查询时。管理和维护相对简单，减少开发和运维成本。缺点：受限于单个数据库实例的资源，扩展性有限。不适合数据量特别大的场景。跨分区查询仍需考虑性能问题。五、总结MySQL分表和分区分表是两种常见的数据分割方案，各有优缺点和适用场景。分表适用于大规模数据和高并发访问场景，通过分散数据和负载，提升系统的可扩展性和高可用性。但其实现和维护复杂，跨表查询和数据一致性管理困难。分区分表则主要用于中等规模的数据优化，通过数据库系统自动管理分区，提升查询性能和管理效率，但扩展性相对较弱。

基本建表语句CREATE TABLE table_name (     column_name1 data_type(size) [column_constraints],     column_name2 data_type(size) [column_constraints],     ...     [table_constraints] ) [table_options];table_name: 新建表的名称。column_name1, column_name2, ...: 表中各列的名称。data_type: 列的数据类型，如 INT, VARCHAR, TEXT, DATE, TIMESTAMP 等。size: 数据类型的长度或大小（对于某些数据类型适用）。[column_constraints]: 列级约束，例如 NOT NULL, AUTO_INCREMENT, DEFAULT, PRIMARY KEY, UNIQUE, COMMENT 等。[table_constraints]: 表级约束，如 PRIMARY KEY, FOREIGN KEY, UNIQUE 等。[table_options]: 表的其他选项，如 ENGINE, AUTO_INCREMENT, CHARSET, COMMENT 等。数据类型  MySQL 支持多种数据类型，以下是一些常见的数据类型：INT: 整数类型。VARCHAR(size): 可变长度的字符串，size 表示最大字符数。CHAR(size): 固定长度的字符串。TEXT: 长文本数据。DATE: 日期，格式为 YYYY-MM-DD。DATETIME: 日期和时间，格式为 YYYY-MM-DD HH:MM:SS。TIMESTAMP: 时间戳，记录数据变更的日期和时间。FLOAT: 浮点数。DOUBLE: 双精度浮点数。DECIMAL(M, D): 定点数，M 是总位数，D 是小数点后的位数。列级约束NOT NULL: 该列不能有 NULL 值。AUTO_INCREMENT: 用于整数类型，自动递增。DEFAULT value: 为列指定默认值。PRIMARY KEY: 将列设置为表的主键。UNIQUE: 保证列中的每个值都是唯一的。COMMENT 'string': 为列添加注释。表级约束PRIMARY KEY (column1, column2, ...): 指定一个或多个列作为主键。UNIQUE KEY (column1, column2, ...): 指定一个或多个列作为唯一键。FOREIGN KEY (column) REFERENCES parent_table(column): 指定一个外键，创建与另一个表的引用关系。INDEX (column1, column2, ...): 创建一个或多个列的索引。表选项ENGINE=storage_engine: 指定存储引擎，如 InnoDB（默认）、MyISAM 等。AUTO_INCREMENT=value: 为 AUTO_INCREMENT 的列指定初始值。CHARSET=character_set: 指定表的默认字符集。COMMENT 'string': 为表添加注释。

在数据库设计中，约束（Constraints）是确保数据完整性和一致性的关键工具。MySQL 作为流行的关系型数据库管理系统，提供了多种约束类型来维护数据的准确性和可靠性。本文将详细探讨 MySQL 的各种表约束，包括它们的定义、用法、注意事项以及最佳实践。1. 什么是表约束？表约束是应用于数据库表的规则，用于限制表中的数据，以确保数据的完整性和有效性。约束有助于防止不正确的数据进入数据库，从而保证数据的一致性和准确性。2. 常见的 MySQL 表约束类型2.1 NOT NULL 约束NOT NULL 约束用于确保某列不能有 NULL 值。这对于必须包含数据的字段（如用户名、电子邮件地址等）非常重要。示例：CREATE TABLE Users (     id INT AUTO_INCREMENT PRIMARY KEY,     username VARCHAR(50) NOT NULL,     email VARCHAR(100) NOT NULL ); 在此示例中，username 和 email 列被设置为 NOT NULL，意味着每条记录必须包含这两个字段的值。2.2 UNIQUE 约束UNIQUE 约束用于确保一列或多列的值在表中是唯一的。它防止重复的值出现在指定列中。示例：CREATE TABLE Users (     id INT AUTO_INCREMENT PRIMARY KEY,     username VARCHAR(50) NOT NULL UNIQUE,     email VARCHAR(100) NOT NULL UNIQUE ); 在此示例中，username 和 email 列被设置为 UNIQUE，确保每个用户都有唯一的用户名和电子邮件地址。2.3 PRIMARY KEY 约束PRIMARY KEY 约束用于唯一标识表中的每条记录。一个表只能有一个主键，但主键可以由多列组合而成。示例：CREATE TABLE Users (     id INT AUTO_INCREMENT PRIMARY KEY,     username VARCHAR(50) NOT NULL,     email VARCHAR(100) NOT NULL ); 2.4 FOREIGN KEY 约束FOREIGN KEY 约束用于确保数据的一致性和完整性，通过引用另一表的主键来建立表之间的关系。它确保引用的值在父表中存在，从而保持数据的参照完整性。示例：CREATE TABLE Orders (     order_id INT AUTO_INCREMENT PRIMARY KEY,     user_id INT,     order_date DATE,     FOREIGN KEY (user_id) REFERENCES Users(id) ); 

在实际生产环境中，应按照软件安全设计的「最小特权原则」设置MySQL的文件权限。MySQL「安装目录」的属主和属组需要设置成mysql用户；MySQL的「历史操作文件」、「历史命令文件」、「数据物理存储文件」只给属主用户读写权限；MySQL的「配置文件」只给属主用户读写权限，属组和其他用户给只读权限。依次执行下列命令，检查权限是否符合要求：ll ~/.mysql_history ~/.bash_history 权限600ll /etc/my.cnf 权限644find / -name *.ibd | xargs ls -al 权限600find / -name *.MYD | xargs ls -al 权限600find / -name *.MYI| xargs ls -al 权限600find / -name *.frm| xargs ls -al 权限600接下来给大家解释一下这些文件都是干嘛的。1、数据库配置文件/etc/my.cnf 是MySQL数据库「配置文件」，为了防止未授权篡改，应设置权限为 644。ll /etc/my.cnf 检查配置文件权限：/etc/my.cnf 默认有以下字段：datadir：数据库目录socket：MySQL客户端程序与服务端通信的套接字文件log-error：日志位置pid-file：存放MySQL进程id的文件2、数据存储文件MySQL每创建一个「表」，都会在数据库目录下创建一个「二进制文件」，用来存储表中的「数据」。下图中可以看到，除了information_schema 和 performance_schema ，每个数据库都对应一个目录，目录下存放这个数据库的表文件。MySQL8.0以前，数据存储文件统一用 .frm 扩展名。MySQL8.0以后，不同的数据库引擎，保存文件的扩展名不一样。InnoDB：独享表空间用 .idb，一个表对应一个文件；共享表空间用 .ibdata，多个表公用一个文件。MyISAM：表的数据用 .MYD；表的索引用 .MYI。Archive： .arcCSV： .csv查看支持的引擎 show engines;，default表示默认，正在使用的引擎。为了防止未授权访问和篡改，数据存储文件的权限应配置为 600。检查数据库文件的权限：find / -name *.ibd | xargs ls -alfind / -name *.MYD | xargs ls -alfind / -name *.MYI| xargs ls -alfind / -name *.frm| xargs ls -al3、历史操作文件~/.mysql_history 和 ~/.bash_history 分别存储MySQL「历史操作命令」和「系统历史命令」。为了防止未授权访问和篡改，应将文件权限配置为 600。1ll ~/.mysql_history ~/.bash_history

在 SQL 中，WITH RECURSIVE 是一个用于创建递归查询的语句。它允许你定义一个 Common Table Expression (CTE)，该 CTE 可以引用自身的输出。递归 CTE 非常适合于查询具有层次结构或树状结构的数据，例如组织结构、文件系统或任何其他具有自引用关系的数据。一、基本语法WITH RECURSIVE cte_name (column1, column2, ...) AS (-- 非递归的初始部分，定义了 CTE 的起点SELECT ...FROM ...UNION ALL-- 递归部分，可以引用 CTE 的别名SELECT ...FROM cte_nameWHERE ...)-- 最后的 SELECT 或其他 DML 语句，使用递归 CTESELECT * FROM cte_name;二、示例假设我们有一个表示组织结构的表 employees，其中包含 id, manager_id 和 name 字段。manager_id 是员工的上级经理的 id，如果 manager_id 是 NULL，则表示该员工是 CEO 或顶层经理。我们想要查询整个组织结构中的所有员工及其上级经理。WITH RECURSIVE employee_hierarchy (id, name, manager_id, path) AS (-- 非递归的初始部分：查找顶层经理（没有经理的员工）SELECTid,name,manager_id,CONCAT(name, '/') AS path -- 使用 CONCAT 创建初始路径FROM employeesWHERE manager_id IS NULLUNION ALL-- 递归部分：查找所有下属SELECTe.id,e.name,e.manager_id,CONCAT(e.name, '/', eh.path) AS path -- 将当前员工添加到路径中FROM employees eINNER JOIN employee_hierarchy eh ON e.manager_id = eh.id)SELECT * FROM employee_hierarchy;在这个例子中：WITH RECURSIVE 开始定义一个递归 CTE employee_hierarchy。CTE 中的 column1, column2, … 是你想要在结果中选择的列。初始查询部分（在 UNION ALL 之前）定义了递归的起点，通常是顶级节点或者查询的基本情况。递归查询部分（在 UNION ALL 之后）使用 CTE 的别名来引用自身的输出，以便能够递归地查询下属或子节点。UNION ALL 用于合并初始查询和递归查询的结果，它允许重复的行，这是递归查询的关键部分。最后的 SELECT * FROM employee_hierarchy; 是最终的查询，它将返回 CTE 的全部结果。递归 CTE 是 SQL 中处理分层数据的强大工具，但它们也可能很复杂，需要仔细设计以避免无限递归或不正确的结果。

数据可用性：正确性、完整性、一致性。这是我们进行数据备份时的要求，如果无法保证备份数据的可用性那么备份数据也就失去了意义。前两个性质很好理解，但是一致性具体是什么呢？ 一、什么是一致性读 1.一致性的定义 **数据的一致性：**指相关联的数据之间的逻辑关系是否正确。 **数据库的一致性：**指数据库从一个一致性状态变成另一个状态。这期间数据可能会发生变化但是状态不会改变。  2.对一致性的分析 关于数据的一致性，举个例子当前时间中午11点，商城一台电脑价值5000元我的账户下刚好有5000元我是买得起的但是我没有买，中午12点我从账户取出1000元买其他东西此时余额为4000元已经无法购买电脑了。可以说现在买不起这台电脑但是不能说一直买不起，因为在12点的时候是有足够的钱的，也就是说不能拿我现在的余额放到11点时的逻辑关系中这是不一样的，11点时的逻辑关系中相对应的是11点时我的余额这才保证了数据的一致性。 关于数据库的一致性，这个就比较好理解了还是一家银行，这个银行只有A，B两人，A存了2w，B存3w。A给B转了1w，此时A余额为1w，B余额为4w。这就是两个一致性状态的切换。因为对银行而言总额是不变的一直是5w。虽然里面AB的数据是有变化的。  二、MySQL怎样保证数据的一致性 数据的一致性在数据库备份中有比较明显的体现。我们在一个时间点开始备份怎样能保证所有数据在这个时间点后都不会发生改变呢？ **加锁：**针对备份策略，将所有涉及的表都加上锁，保证在备份结束之前所有的表都不会被修改。这样不管这次备份持续多长时间，都可以确保数据始终一致在备份开始的时候。但是这种缺点也很明显，锁表对数据库影响比较大，这期间不能对数据库做任何写操作，只能读。 **快照：**在备份开始时对目标数据做一个快照，因为快照记录了那个时刻所有数据的样子，所以在这个快照范围内所有的数据都具有一致性。如果存储引擎为innodb那么利用事务的隔离性就可以保证数据的一致性。也就实现了快照功能。事务可重读隔离级别可以实现数据的一致性，虽然可重读可能因为更新数据导致幻读，但是数据备份是个只读操作。所以只要保证备份操作放在一个单独的事务中即可，其他对数据库的操作是别的事务中的因为隔离性这个特点并不会影响其他事务。这样就不会出现幻读了也保证了在备份过程中，保证了数据的一致性读。  三、可重读隔离级别的一致性读 上面说的一致性读也被称为快照读。具体就是上面快照的含义。接下来我们来模拟一下这个场景。  四、模拟测试 1）需要将两个数据库会话中的事务隔离级别都设置为可重读，然后在两个会话中都开启两个事务。 首先关闭两个会话的自动提交，设置会话隔离模式为可重读并开启一个事务。  mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)  mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit    | OFF   | +---------------+-------+  mysql> set session transaction isolation level repeatable read; Query OK, 0 rows affected (0.00 sec)  mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ         | +-------------------------+ 1 row in set (0.00 sec)  mysql> start transaction; Query OK, 0 rows affected (0.00 sec) 2）两个会话分别查看测试表信息如下： 3）在会话二中进行数据更新插入一条数据并提交： 4）在会话一中查看数据情况，可以看到此时数据并没有变化，在会话一中插入数据提交后，看到会话二新插入的数据发生了幻读。  解释：可以看到在会话二中做完插入操作查看这个测试表已经4条数据了，但是在会话一中看还是只有3条数据。这时我们在会话一做更新数据的操作，也就是插入数据然后提交却发现多出两条数据，这就是可重读级别下的幻读。 （插入操作并不会起到更新全部数据的效果，只会更新自己插入的数据，起到作用的是commit，将全部数据更新出来包括会话二插入的数据。这里可以适用update做更新数据的操作。update语句并没有指定任何条件，相当于更新表中的所有行的对应字段，如果你指定了条件，并且没有更新到"隐藏"的行，那么可能无法看到幻读现象。）  在备份操作中，我们所有的操作都是读操作并不涉及到更新数据，所以当我们把所有的备份操作都放到一个单独的‘事务’中，并且此事务将事务隔离级别设置为可重读，是不会出现幻读问题的，也就是达到了在某一时间点读取数据数据一致性的目的。  那么是不是只要在可重读隔离模式下，启动一个事务就相当于给当时的数据库打了一个快照？答案是否定的，具体测试如下：  1）仍然使用上面实验，如果是新打开的会话需要重新设置隔离模式以及关闭自动提交。查看此时两个会话查询测试表的信息如下： 2）在会话一中启动一个事务，其余什么操作也不做，用来观察 3）在会话二中做数据插入操作并提交。数据成功插入。  4）在会话一中进行查询，可以看到数据被更新了，并不是我们想的那样被打了快照。  那么这是为什么呢？第一个模拟实验是成功实现的，第二个却出现问题。而二者的差别在哪里？其实第一个实验中在启动会话后进行了一次查询也就是那个时候快照才真正的开始，而第二次实验我们并没有在会话开启后进行查询，所以启动会话并不是直接打快照。也就是说不是以start transaction语句开始的时间点作为"快照"建立的时间点。官方文档有这样的说明： If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries. 也就是说，当事务处于"可重读"隔离级别时，并不是事务开始时就代表快照建立，而是事务中的第一个查询语句执行时，快照点才会被建立。  五、结论   那么针对上述现象应该怎样才能在指定的时间点开启一个快照呢？难道要每次开启一个事务后都进行一次查询才能实现吗？MySQL已经为我们提供了另一种选择，在启动会话时指定参数即可。START TRANSACTION WITH consistent snapshot   使用start transaction with consistent snapshot;命令启动事务，就表示启动事务的同时就建立快照，也就是说，只要事务开始，就能保证"一致性读" ————————————————        原文链接：https://blog.csdn.net/qq_43250333/article/details/107786561 

一、幻读的定义         根据MySQL官网的描述，幻读是“相同的查询在不同时间返回了不同的结果”          The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times.           同时官网还举例说明了，如：两次查询中，后一次多出来的行就是所谓的“幻影行”           For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.          了解Innodb的同学应该十分眼熟下面这张图，图里介绍了各个隔离级别下的一致性问题。  图片来源：数据库系统原理         得益于MVCC机制，可重复读级别（RR）下依赖一份不更新的Read View使之后提交事务的修改对当前事务不可见，解决了脏读和不可重复读问题。  read view形如 [m_up_limit_id, m_low_limit_id] 的数组，记录了当前活跃的事务         不知你是否会和我有一样的疑问：          “既然RR实现了可重复读，按理已经屏蔽了其他事务的修改。但为什么还是会受其他事务影响产生幻读的问题？”          “RR下幻读是否真实存在？”          “幻读到底长什么样？”          如果你也和我一样有上述疑问，很好！接下来我们将一起探寻幻读的真相。  二、寻找幻读         秉持着“先问是不是，再问为什么”的理念，我们得先证明幻读在RR下是存在的。          为了制造幻读，先简单准备了一张'test_lock'表：  SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;   -- ---------------------------- -- Table structure for test_lock -- ---------------------------- DROP TABLE IF EXISTS `test_lock`; CREATE TABLE `test_lock`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `a` int(11) NOT NULL,   `b` int(11) NOT NULL,   PRIMARY KEY (`id`) USING BTREE,   INDEX `a`(`a`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 26 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;   -- ---------------------------- -- Records of test_lock -- ---------------------------- INSERT INTO `test_lock` VALUES (1, 1, 1); INSERT INTO `test_lock` VALUES (5, 5, 5); INSERT INTO `test_lock` VALUES (10, 10, 10); INSERT INTO `test_lock` VALUES (15, 15, 15);   SET FOREIGN_KEY_CHECKS = 1;  test_lock         确认RR隔离级别后，开始编写事务， 着手制造幻读：  事务A    事务B 1    begin;     2    SELECT * FROM `test_lock` WHERE a<10;     3        begin; 4        INSERT INTO test_lock VALUES(6,6,6); 5         commit;  6    （待输入）             在“待输入”处应该要执行什么语句才能复现幻读呢？尝试执行select for update：  SELECT * FROM `test_lock` WHERE a<10 for UPDATE;          使用当前读（Locking Read）确实看到了刚刚插入的 (6, 6, 6) ，但这是幻读吗？          根据官方的定义，幻读发生在相同的查询，返回不同的结果。          实验中 select 和 select for update ，一个是快照读（Consistent Nonlocking Reads），一个是当前读（Locking Read），明显不符合“same query”的要求。          那既然select for update已经能看到插入了，在后面再执行一遍原来的快照读，是否就能符合要求了呢？  事务A    事务B 1    begin;     2    SELECT * FROM `test_lock` WHERE a<10;     3        begin; 4        INSERT INTO test_lock VALUES(6,6,6); 5         commit;  6    SELECT * FROM `test_lock` WHERE a<10 for UPDATE;     7    SELECT * FROM `test_lock` WHERE a<10;      select for update  select         神奇的现象发生了，在select for update中可见的 (6, 6, 6) 又不见了。          这样一来，虽然满足了“same query”的要求，但又不满足“different sets of rows”了。          要想理解刚刚这种现象，需要回到RR的本质————“不更新的Read View”  前后两次快照读，因为Read View没有更新，所以没有任何差别  当前读使用了最新的Read View，看见了插入，但并没有更新事务里的read view副本。          至此，这种忽隐忽现的“伪幻读”已经解释清楚了。  三、发现幻读         既然RR下的Read View是不更新的，那事务A要如何看到事务B的插入呢？          进行两次当前读？很明显不行，由于间隙锁（Gap Lock）的存在，事务B无法在事务A锁定的区间进行插入。插入都被阻塞了，还谈什么返回结果不同。          那还有别的办法吗？有！         这次我们成功地看到了幻读的发生，同时符合相同查询和相同结果两个定义。          根据MySQL执行修改的流程，事务A在执行修改时，先使用当前读将数据读入缓冲池（Buffer Pool），再将修改应用到内存。  图片来源：update在MySQL中是怎样执行的，一张图牢记          正是这样的先加载后更新的操作，让事务A看到自身更新的同时，也看到了事务B的插入，导致幻读发生。  四、解决幻读         幻读发生的条件较为苛刻，多数情况下是触发不了的。          但如果发生，我们可以使用当前读对区间上间隙锁，阻塞插入的发生，从而规避幻读。          还记得刚刚讨论方案时说的吗，用的就是这种方法：          既然RR下的Read View是不更新的，那事务A要如何看到事务B的插入呢？          进行两次当前读？很明显不行，由于间隙锁（Gap Lock）的存在，事务B无法在事务A锁定的区间进行插入。插入都被阻塞了，还谈什么返回结果不同。          具体上锁的方式分为两种：  SELECT ... FOR SHARE SELECT ... FOR UPDATE         根据检索条件和具体行数据的不同，间隙锁可能与行锁（Record Lock）结合，生成临键锁（Next Key Lock）。与间隙锁一样，生成的临键锁也可阻塞其他事务的修改。三者的关系为：  行锁：对唯一索引进行等值查询且命中 间隙锁：进行等值查询未命中 临键锁：（剩余查询条件）         需要注意的是，间隙锁是种特殊的锁，相同的间隙锁是共享的，并不是互斥的。           这种共享将可能导致死锁的发生，如：  事务A    事务B 1    begin;     2    SELECT * FROM `test_lock` WHERE a=3 FOR UPDATE;     3    # 锁定区间(1, 5)     4        begin; 5        SELECT * FROM `test_lock` WHERE a=4 FOR UPDATE; 6        # 锁定区间(1, 5) 7    INSERT INTO test_lock VALUES(3, 3, 3);     8    # 发生阻塞，等待事务B释放间隙锁     9        INSERT INTO test_lock VALUES(4, 4, 4); 10        # 发生死锁  死锁事务B自动中断并报错 五、总结         至此，我们给出了幻读的定义、重现了幻读、提供了解决方案、讨论了死锁的条件。          RR下存在幻读，可以使用间隙锁避免。 ———————————————— 原文链接：https://blog.csdn.net/shichimiyasatone/article/details/131594657 

首先幻读是什么？ 根据MySQL文档上面的定义  The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row. 幻读指的是在一个事务内，同一SELECT语句在不同时间执行，得到不同的结果集时，就会发生所谓的幻读问题。  可以看看下面的例子：  这是网上找的一张图（事务的务字写错了，不过不影响我们理解）  假设这个例子中的MySQL的隔离级别是提交读，也就是一个事务内可以读到其他事务提交后的结果。  那么事务1第一次查询dept表中所有部门时,结果是没有"研发部"，但是由于隔离级别是提交读，在事务2插入“研发部”这一行数据后，并且提交后，事务1是可以读取到的，所以第二次查询时，结果集中会有“研发部”。这就是幻读。 SELECT语句分类 首先我们的SELECT查询分为快照读和实时读，快照读通过MVCC（并发多版本控制）来解决幻读问题，实时读通过行锁来解决幻读问题。  快照读 1.1 快照读是什么？  因为MySQL默认的隔离级别是可重复读，这种隔离级别下，我们普通的SELECT语句都是快照读，也就是在一个事务内，多次执行SELECT语句，查询到的数据都是事务开始时那个状态的数据（这样就不会受其他事务修改数据的影响），这样就解决了幻读的问题。  1.2 那么innodb是怎么解决快照读的幻读问题的？  快照读就是每一行数据中额外保存两个隐藏的列，插入这个数据行时的版本号，删除这个数据行时的版本号（可能为空），滚动指针(指向undo log中用于事务回滚的日志记录)。  事务在对数据修改后，进行保存时，如果数据行的当前版本号与事务开始取得数据的版本号一致就保存成功，否则保存失败。  当我们不显式使用BEGIN来开启事务时，我们执行的每一条语句就是一个事务，每次开始事务时，会对系统版本号+1作为当前事务的ID。  1.2.1 插入操作  插入一行数据时，将事务的ID作为数据行的创建版本号。  1.2.2 删除操作  执行删除操作时，会将原数据行的删除版本号设置为当前事务的ID，然后根据原数据行生成一条INSERT语句，写入undo log，用于事务执行失败时回滚。delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。但是会将数据行的删除版本号设置为当前的事务的ID，这样后面的事务B即便查到这行数据由于事务B的ID>删除版本号，也会忽略这条数据。  1.2.3 更新操作  更新时可以简单的认为是先将旧数据删除，然后插入一条新数据。  所以执行更新操作时，其实是会将原数据行的删除版本号设置为当前事务的ID，生成一条INSERT语句，写入undo log，用于事务执行失败时回滚。插入一条新的数据，将事务的ID作为数据行的的创建版本号。  1.2.4 查询操作  数据行要被查询出来必须满足两个条件，  数据行删除版本号为空或者>当前事务版本号的数据（否则数据已经被标记删除了) 创建版本号<=当前事务版本号的数据（否则数据是后面的事务创建出来的） 简单来说，就是查询时，  如果该行数据没有被加行锁中的X锁（也就是没有其他事务对这行数据进行修改），那么直接读取数据（前提是数据的版本号<=当前事务版本号的数据,不然不会放到查询结果集里面）。 该行数据被加了行锁X锁（也就是现在有其他事务对这行数据进行修改），那么读数据的事务不会进行等待，而是回去undo log端里面读之前版本的数据（这里存储的数据本身是用于回滚的），在可重复读的隔离级别下，从undo log中读取的数据总是事务开始时的快照数据(也就是版本号小于当前事务ID的数据)，在提交读的隔离级别下，从undo log中读取的总是最新的快照数据。 1.3 补充资料：undo log段是什么？  undo_log是一种逻辑日志，是旧数据的备份。有两个作用，用于事务回滚和为MVCC提供老版本的数据。  可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。  1.3.1 用于事务回滚  当事务执行失败，回退时，会读取这行数据的滚动指针(指向undo log中用于事务回滚的日志记录)，就可以在undo log中找到相应的逻辑记录，读取到相应的回滚语句，执行进行回滚。  1.3.2 为MVCC提供老版本的数据  当读取的某一行被其他事务锁定时（也就是有其他事务正在改这行数据），它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户进行快照读。在可重复读的隔离级别下，从undo log中读取的数据总是事务开始时的快照数据(也就是版本号小于当前事务ID的数据)，在提交读的隔离级别下，从undo log中读取的总是最新的快照数据（也就是比正在修改这行数据的事务ID修改前的数据。）。  实时读 2.1 实时读是什么？  如果说快照读总是读取事务开始时那个状态的数据，实时读就是查询时总是执行这个查询时数据库中的数据。  一般使用以下这两种查询语句进行查询时就是实时读。  SELECT *** FOR UPDATE 在查询时会先申请X锁SELECT *** IN SHARE MODE 在查询时会先申请S锁 首先看一个实时读产生幻读的案例：  这是《MySQL技术内幕++InnoDB存储引擎++第2版》里面的一张图，就是先将隔离级别设置为提交读，这样第一次执行 SELECT…FOR UPDATE查询出来的数据是a:4，事务B插入了一条新的数据，再次执行 SELECT…FOR UPDATE语句时，查询出来就是a:4,a:5两条数据，这就是幻读的问题。 2.1 那么innodb是怎么解决实时读的幻读问题的？  如果我们不在一开始将将隔离级别设置为提交读，其实是不会产生幻读问题的，因为MySQL的默认隔离级别是可重复读，在这种情况下，我们执行第一次 SELECT…FOR UPDATE查询语句是，其实是会先申请行锁，因为一开始数据库就只有a:4一行数据，那么加锁区间其实是(负无穷，4](4,正无穷) 我们查询条件是a>2，上面两个加锁区间都会可能有数据满足条件，所以会申请行锁中的next-key lock，是会对上面这两个区间都加锁，这样其他事务不能往这两个区间插入数据，事务B会执行插入时会一直等待获取锁，直到事务A提交，释放行锁，事务B才有可能申请到锁，然后进行插入。这样就解决了幻读问题。 ————————————————     原文链接：https://blog.csdn.net/u013994536/article/details/129600055 

幻读（phantom read） ********，是指在一个事务中前后两次相同的查询产生不同的结果集，后一次查询看到了前一次查询没有看到的记录行。 MySQL InnoDB默认的事务隔离级别是可重复读，可重复读的要旨在于同一数据行记录在一个事务内无论何时查询结果都是一样的。  从定义可以知道，可重复读解决的问题和幻读问题有实质性的区别，一个针对同一行记录，一个说的是数据行数，那么，MySQL又是怎么解决幻读问题的呢，今天就来一探究竟，先上一个目录： 一、MySQL如何解决幻读 1.1 快照读和当前读 1.2 快照读如何解决幻读 1.3 当前读如何解决幻读 二、可重复读完全解决幻读了么？ 2.1 鲜为人知的幻读  三、结语 一、MySQL如何解决幻读 首先，我们的前提是在MySQL数据库内，使用的引擎是InnoDB引擎，且事务的隔离级别是可重复读。  前面文章有讲过，MySQL InnoDB依靠MVCC实现事务隔离级别。MVCC又称多版本并发控制，它的全称是Multi-Version Concurrency Control，直白说就是在同一时刻同一条记录在系统中可以存在多个版本。  1.1 快照读和当前读 当前读： MySQL的MVCC决定了同一数据行可能会同时存在多个版本的情况，当前读表示读取的记录是最新版本的，且读取的时候，如果有其他并发事务要修改同一数据行，当前事务会通过加锁让其他事务阻塞等待。  比如select lock in share mode(共享锁)、select for update 、update、insert 、delete(排他锁)等操作都是一种当前读，这些操作会对读取的记录进行加锁。  快照读： 表示不加锁的非阻塞读，像普通的select操作就是快照读。快照读的实现基于MVCC，它实现了事务内任何时刻读取的数据都是历史某个版本的数据，不一定是当前时刻最新的数据。  MVCC这种实现方式也是一种锁的变种，但它避开了加锁操作，大大降低系统的开销，从而提高系统的性能。 需要特别注意的是，快照读在MySQL的串行隔离级别下会上升为当前读，即使是select操作也会加锁。 1.2 快照读如何解决幻读 假如我们有一张账户余额表bank_balance，其结构如下，里面的初始数据行有9行。  CREATE TABLE bank_balance (   id int NOT NULL AUTO_INCREMENT,   user_name varchar(45) NOT NULL COMMENT '用户名',   balance int NOT NULL DEFAULT '0' COMMENT '余额，单位：人民币分，比如100表示人民币1元，默认是0',   wealth tinyint NOT NULL DEFAULT '0' COMMENT '富有程度，0：贫穷，1：富有',   PRIMARY KEY (id),   UNIQUE KEY idx_bank_balance_user_name (user_name) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci  初始数据行： mysql> select *from bank_balance; +----+-----------+-----------+--------+ | id | user_name | balance | wealth | +----+-----------+-----------+--------+ | 1 | 小埃 | 0 | 0 | | 2 | 小克 | 300000000 | 0 | | 3 | Tom | 500 | 0 | | 4 | Eric | 100 | 0 | | 5 | AI | 0 | 0 | | 6 | Alex | 100 | 0 | | 7 | Max | 100 | 0 | | 8 | Mike | 100 | 0 | | 9 | Lyn | 200 | 0 | +----+-----------+-----------+--------+ 9 rows in set (0.01 sec)  假设现在有两个事务，事务A和事务B，同时操作这张余额表，两个事务的操作时间线如下：  事务A有两次查询，分别在③和⑤，都是采用相同的SQL语句：select * from bank_balance where balance > 0（普通select是一种快照读），目的都是查询所有balance > 0的rows。 ①和②：开启事务。 ③：事务A通过select * from where balance > 0得到的结果是7 Rows，如下： ④：事务B插入一行记录 (10, 'Loop', 100,0)。 ⑤：事务A通过select * from bank_balance where balance>0再次查询得到的结果，同样还是7 Rows。 ⑥和⑦：提交事务。  为什么第⑤处查询时结果还是7 Rows呢？大家应该都还记得MVCC，事务A在第③处就会生成一个ReadView记录当前的活跃事务，事务B就在活跃事务范围内，在第⑤处事务B insert的记录隐藏列事务id不满足事务A读取，事务A会顺着undo log的版本链查到满足的记录为止（当然，该记录是事务B新增的，顺着版本链找最终只能找到null，所以该记录不返回）。  1.3 当前读如何解决幻读 同样是上面的表和查询时间线，只是查询语句换成了当前读的查询select * from bank_balance where balance > 0 for update，假设没有锁，那么就会发生幻读现象，如下： ①和②：开启事务。 ③：事务A通过select * from bank_balance where balance>0 for update得到的结果是7 Rows，如下： ④：事务B插入一行记录 (10, 'Loop', 100,0)。 ⑤：事务A通过select * from bank_balance where balance > 0 for update再次查询得到的结果是8 Rows，如下： ⑥和⑦：提交事务。 第③和第⑤同样是查询bank_balance > 0 的记录但得到的结果却不一样，这就是幻读现象。 为了解决幻读问题，MySQL InnoDB 引擎引入了next-key lock，其等同于间隙锁+记录锁的组合。 记录锁，顾名思义，就是给数据行加的锁，那何为间隙锁？  假设，bank_balance表中只存在余额balance>0且主键id 为4和6的记录，那么当一个事务使用select * from where balance>0 for update查询时，其他事务就无法插入 id = 5的记录，就像是事务A把(4,6)这个范围锁住了，这就是间隙锁。  如果再把id=4和6的记录也同时一起锁了，合起来变成一个闭区间[4, 6]，那么整个区间锁也叫next-key lock。  还是以上的例子，事务B在事务A查询后进行insert操作： 事务 A 在③处执行了select * from bank_balance where balance > 0 for update这条锁定读语句后，就会把整个表所有记录锁上(因为balance字段无索引)，并根据主键id和表记录形成多个next-key lock，分别是：(-∞, 1]、(1, 2]、(2, 3]、(3, 4]、(4, 5]、(5, 6]、(6, 7]、(7, 8]、(8, 9]、(9, +∞]，每个next-key lock都是前开后闭区间。  然后，事务 B 在④处执行插入语句，发现id=10被事务 A 加了 next-key lock，于是事物 B 会生成一个写锁，开始阻塞等待，直到事务 A 提交了事务才会执行。这就避免了上述所说的幻读问题。  以上的例子比较特殊，如果我们的表中只有两条记录，分别是(4, 'Eric', 100,0)、(10, 'Loop', 100,0)，那么当我们执行select *from bank_balance where id > 8 for update时，就只会形成两个next-key lock，它就是(4, 10]，(10, +∞]，如果我们执行insert into bank_balance values(5,'MALL',100,0)将会被阻塞，但是我们执行insert into bank_balance values(2,'MALL',100,0)就不会被阻塞，因为id=2没有被锁住。  特别说明一下，next-key lock基于记录形成，不是基于查询条件形成，有些同学问到上文的例子中两个next-key lock为什么不是(8, 10]、(10, +∞]，就是这个原因。   二、可重复读完全解决幻读了么 2.1 鲜为人知的幻读 MySQL InnoDB默认的可重复读隔离级别加上next-key lock一定程度上解决了幻读问题，但依然存在特殊的情况下产生幻读问题。  *第一种情况， *先启动的事务A使用快照读，后启动的事务B插入新的数据行并提交，然后事务A再更新，其后A的查询都能查事务B新增的数据行。  ③：表中没有id=5的记录行，所以事务A查询的结果是0Rows。 ④-⑥：事务B启动，并插入一条id=5的记录，后提交事务。 ⑦：事务A更新id=5的记录。 ⑧：事务A查询id=5的记录，结果Rows=1，产生了幻读。  按MVCC的原理，第⑧处事务A查询结果不应该返回id=5的记录，但因为有update在先，所以该记录背查询了出来。（此处很绕，需要认真看一看这个文章才能理解： 快照读不会加锁，导致事务B可以insert成功，而update语句又是当前读，能够更新id=5的数据，所以，当执行⑧时，快照读也就能够查询出来id=5的记录了。  *第二种情况， *如果事务一开始没有使用当前读，当其他事务插入数据并提交后再使用当前读就会发生幻读现象。 ③：表中没有id=5的记录行，所以事务A采用快照读方式查询的结果是0Rows。 ④-⑥：事务B启动，并插入一条id=5的记录，后提交事务。 ⑦：事务A采用当前读的方式查询id=5的行，结果Rows为1，产生了幻读。  这种情况是因为快照读不生成next-key lock导致，其他事务可以插入本事务查询范围内的记录行，所以，当其他事务插入数据后再执行当前读，就能查到新的记录，从而产生幻读问题。  一般在开发过程中建议开启一个事务时尽快采用for update的查询方式，以生成next-key lock，避免幻读问题。  三、结语 我是tin，一个在努力让自己变得更优秀的普通工程师。自己阅历有限、学识浅薄，如有发现文章不妥之处，非常欢迎加我提出，我一定细心推敲并加以修改。 ———————————————— 原文链接：https://blog.csdn.net/wdj_yyds/article/details/131897705 

一. 幻读是什么        幻读的意思就是说在可重复读的隔离级别下会出现的一种情况.       我的理解是如下图  因为加上for update 所以现在的sql语句是当前读,所以现在每次在sessionA中都会将其他两个事务做的操作后的后果读出来,但是我们使用重复读就是想可以通过快照重复读原先的数据.但是现在的却出现了我们不想产生的现象,我们将这种现象称为幻读.  二. 幻读会产生什么问题 如果只是读错数据其实都还好,但是看下图,其实是会产生更加严重的后果的. 单独看好像除了会读出一些不属于此事务的数据之外不会产生其他的影响. 但是我们将binlog加入进来考虑一下的话,那么就不一样了,就是我们来写一下过程 T1 事务a开启，（5,5,5）变为（5,5,100） T2 事务b开启       （0,0,0）变为（0,5,5） T4事务c开启        加入一条（1,5,5） 但是binlog里是如何呢？ 事务B先结束，binlog里存两条 会让（0,0,0）变成（0,5,5）， 然后事务c结束 binlog再存两条 会让数据库里多一条数据 （1,5,5） 然后现在事务a才结束 binlog再存两条 会让（0,5,5）变为（0，5，100） （1,5,5）变为（1,5,100）  到这已经使得logbin和数据库里的数据不一样了。 那么我们这边一开始想到的策略就是给原先的所有行加上行锁， 但是这真的有用吗？  我们像像上面一样来推 三. 如何解决幻读 那么到底如何解决幻读带来的数据冲突呢  InnoDB只好引入新的锁，也就是间隙锁(Gap Lock)。  顾名思义，间隙锁，锁的就是两个值之间的空隙。比如文章开头的表t，初始化插入了6个记录，这就产生了7个间隙。 但是间隙锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。  间隙锁和next-key lock的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”。  在前面的文章中，就有同学提到了这个问题。我把他的问题转述一下，对应到我们这个例子的表来说，业务逻辑这样的：任意锁住一行，如果这一行不存在的话就插入，如果存在这一行就更新它的数据，代码如下：  begin; select * from t where id=N for update;   /*如果行不存在*/ insert into t values(N,N,N); /*如果行存在*/ update t set d=N set id=N;   commit; 可能你会说，这个不是insert ... on duplicate key update 就能解决吗？但其实在有多个唯一键的时候，这个方法是不能满足这位提问同学的需求的。至于为什么，我会在后面的文章中再展开说明。  现在，我们就只讨论这个逻辑。  这个同学碰到的现象是，这个逻辑一旦有并发，就会碰到死锁。你一定也觉得奇怪，这个逻辑每次操作前用for update锁起来，已经是最严格的模式了，怎么还会有死锁呢？ 这里，我用两个session来模拟并发，并假设N=9。 图8 间隙锁导致的死锁  你看到了，其实都不需要用到后面的update语句，就已经形成死锁了。我们按语句执行顺序来分析一下：  session A 执行select ... for update语句，由于id=9这一行并不存在，因此会加上间隙锁(5,10); session B 执行select ... for update语句，同样会加上间隙锁(5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功； session B 试图插入一行(9,9,9)，被session A的间隙锁挡住了，只好进入等待； session A试图插入一行(9,9,9)，被session B的间隙锁挡住了。  至此，两个session进入互相等待状态，形成死锁。当然，InnoDB的死锁检测马上就发现了这对死锁关系，让session A的insert语句报错返回了。 你现在知道了，间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。其实，这还只是一个简单的例子，在下一篇文章中我们还会碰到更多、更复杂的例子。  你可能会说，为了解决幻读的问题，我们引入了这么一大串内容，有没有更简单一点的处理方法呢。  我在文章一开始就说过，如果没有特别说明，今天和你分析的问题都是在可重复读隔离级别下的，间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把binlog格式设置为row。这，也是现在不少公司使用的配置组合。 前面文章的评论区有同学留言说，他们公司就使用的是读提交隔离级别加binlog_format=row的组合。他曾问他们公司的DBA说，你为什么要这么配置。DBA直接答复说，因为大家都这么用呀。 所以，这个同学在评论区就问说，这个配置到底合不合理。  关于这个问题本身的答案是，如果读提交隔离级别够用，也就是说，业务不需要可重复读的保证，这样考虑到读提交下操作数据的锁范围更小（没有间隙锁），这个选择是合理的。  但其实我想说的是，配置是否合理，跟业务场景有关，需要具体问题具体分析。 但是，如果DBA认为之所以这么用的原因是“大家都这么用”，那就有问题了，或者说，迟早会出问题。 比如说，大家都用读提交，可是逻辑备份的时候，mysqldump为什么要把备份线程设置成可重复读呢？（这个我在前面的文章中已经解释过了，你可以再回顾下第6篇文章《全局锁和表锁 ：给表加个字段怎么有这么多阻碍？》的内容）  然后，在备份期间，备份线程用的是可重复读，而业务线程用的是读提交。同时存在两种事务隔离级别，会不会有问题？ 进一步地，这两个不同的隔离级别现象有什么不一样的，关于我们的业务，“用读提交就够了”这个结论是怎么得到的？ 如果业务开发和运维团队这些问题都没有弄清楚，那么“没问题”这个结论，本身就是有问题的。 ————————————————                 原文链接：https://blog.csdn.net/qq_62556650/article/details/129480637 

一、什么是幻读 1.我们先来回顾一下MySQL中事务隔离级别 READ UNCOMMITTED ：未提交读。 READ COMMITTED ：已提交读。 REPEATABLE READ ：可重复读。 SERIALIZABLE ：可串行化。 2.针对不同的隔离级别，并发事务可以发生不同严重程度的问题 READ UNCOMMITTED 隔离级别下，可能发生脏读、不可重复读和幻读问题。 READ COMMITTED 隔离级别下，可能发生不可重复读和幻读问题，但是不会发生脏读问题。 REPEATABLE READ 隔离级别下，可能发生幻读问题，但是不会发生脏读和不可重复读的问题。 SERIALIZABLE 隔离级别下，各种问题都不会发生。 3.MySQL的默认隔离级别是REPEATABLE READ，可能会产生的问题是幻读，也就是我们本次要讲内容。 首先来看看 MySQL 文档是怎么定义幻读（Phantom Read）的:  当同一个查询在不同的时间产生不同的结果集时，事务中就会出现所谓的幻象问题。 例如，如果 SELECT 执行了两次，但第二次返回了第一次没有返回的行，则该行是“幻像”行。 二、可重复读是如何避免幻读的 快照读情况下 在可重复读隔离级别下是通过MVCC来避免幻读的，具体的实现方式在事务开启后的第一条select语句生成一张Read View(数据库系统当前的一个快照),之后的每一次快照读都会读取这个Read View。  即在第②时刻生成一张Read View，所以在第⑤时刻时读取到数据和第②时刻相同，避免了幻读。  当前读情况下 当前读：像select lock in share mode(共享锁), select for update ; update, insert ,delete这些操作都是一种当前读，读取的是记录的最新版本。 在当前读情况下是通过next-key lock来避免幻读的，即加锁阻塞其他事务的当前读。 事务A在第②时刻执行了select for update当前读，会对id=1和2加记录锁，以及(2,+∞)这个区间加间隙锁，两个都是排它锁，会阻塞其他事务的当前读，所以在第③时刻事务B更新时阻塞了，从而避免了当前读情况下的幻读。  三、可重复读完全解决幻读了吗？ MySQL的默认隔离级别可重复能避免大部分情况下的幻读，但是在一些特殊场景下还是无法完全解决幻读。例如  在第②时刻使用的是快照读，此时生成了Read View查询出来的数据是张三、李四，事务B在第③时刻插入了一条id为3的王二，因为事务A并没有对数据加锁，所以事务B可以正常插入。但当第⑤时刻事务A查询时却查出来了事务B插入的数据，产生了幻读。是因为使用的是当前读，不会读取Read View，是读取数据当前最新的数据，所以读出了事务B插入的数据。  四、总结 MySQL的默认隔离级别可重复读很大程度上解决了幻读问题。在快照读情况下是通过MVCC解决的，在第一次执行查询语 句时生成一张Read View，后续每次快照读都是读这张Read View。在当前读情况下是加锁来解决的，会阻塞其他事务的 当前读，从而避免幻读。然而可重复读并不能完全解决幻读，当一个事务里面使用快照读之后又使用当前读的话就还是 会出现幻读 ————————————————               原文链接：https://blog.csdn.net/ByLir/article/details/130901620 

一、什么是幻读 在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多或者少的那一行被叫做 幻行  二、为什么要解决幻读 在高并发数据库系统中，需要保证事务与事务之间的隔离性，还有事务本身的一致性。  三、MySQL 是如何解决幻读的 如果你看到了这篇文章，那么我会默认你了解了 脏读 、不可重复读与可重复读。  1. 多版本并发控制（MVCC）（快照读/一致性读） 多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的。 以 InnoDB 为例。可以理解为每一行中都冗余了两个字段，一个是行的创建版本，一个是行的删除（过期）版本。 具体的版本号（trx_id）存在 information_schema.INNODB_TRX 表中。 版本号（trx_id）随着每次事务的开启自增。 事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的数据。  普通的 select 就是快照读。 select * from T where number = 1;  原理：将历史数据存一份快照，所以其他事务增加与删除数据，对于当前事务来说是不可见的。 2. next-key 锁 （当前读） next-key 锁包含两部分  记录锁（行锁） 间隙锁 记录锁是加在索引上的锁，间隙锁是加在索引之间的。（思考：如果列上没有索引会发生什么？）  select * from T where number = 1 for update; select * from T where number = 1 lock in share mode; insert update delete  原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的。  四、其他：MySQL InnoDB 引擎 RR 隔离级别是否解决了幻读 引用一个 github 上面的评论 地址： Mysql官方给出的幻读解释是：只要在一个事务中，第二次select多出了row就算幻读。 a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意dml操作），a事务再select出来的结果在MVCC下还和第一次select一样，接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的），a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了，实测在RR级别下确实如此。  如果这样理解的话，Mysql的RR级别确实防不住幻读 有道友回复 地址：  在快照读读情况下，mysql通过mvcc来避免幻读。 在当前读读情况下，mysql通过next-key来避免幻读。 select * from t where a=1;属于快照读 select * from t where a=1 lock in share mode;属于当前读  不能把快照读和当前读得到的结果不一样这种情况认为是幻读，这是两种不同的使用方式。所以我认为mysql的rr级别是解决了幻读的。  先说结论，MySQL 存储引擎 InnoDB 隔离级别 RR 解决了幻读问题。 不能把快照读和当前读得到的结果不一样这种情况认为是幻读，这是两种不同的使用方式。所以认为 MySQL 的 RR 级别是解决了幻读的。 先说结论，MySQL 存储引擎 InnoDB 隔离级别 RR 解决了幻读问题。 如引用一问题所说，T1 select 之后 update，会将 T2 中 insert 的数据一起更新，那么认为多出来一行，所以防不住幻读。但是其实这种方式是一种 bad case。如图：  五、注意 next-key 固然很好的解决了幻读问题，但是还是遵循一般的定律，隔离级别越高，并发越低。 ————————————————     原文链接：https://blog.csdn.net/qq_32907195/article/details/113742248