MYSQL的binlog有几种格式分别有什么区别

MySQL 的 binlog（binary log） 是 MySQL 用于记录所有更改数据库状态的操作的日志文件，主要用于数据恢复和复制。binlog 有三种格式：STATEMENT、ROW 和 MIXED，它们分别记录了不同层次的操作信息。每种格式都有其适用场景和优缺点。

1. STATEMENT 格式

STATEMENT 格式是默认的 binlog 格式，它记录的是执行的 SQL 语句。这种格式记录的是每条 SQL 语句的操作，比如 INSERT、UPDATE 和 DELETE。当主库执行一条 SQL 语句时，这条语句会被记录到 binlog 中，然后从库会在自己的数据库上执行相同的 SQL 语句。

特点：

记录的是 SQL 语句本身。

从库收到 binlog 后，会执行相同的 SQL 语句。

对于一些具有非确定性行为的语句（如涉及 NOW()、UUID() 等的函数），这种格式的复制可能会导致数据不一致。

优点：

节省存储空间，因为只记录 SQL 语句。

对于简单的操作和确定性操作，它是高效的。

缺点：

对于某些语句，尤其是涉及非确定性函数的语句，从库执行时可能会产生不同的结果，导致主从数据不一致。

案例：  -- 在主库执行：  INSERT INTO users (id, name) VALUES (1, 'John');  ​  -- binlog 记录（以 STATEMENT 格式）：  INSERT INTO users (id, name) VALUES (1, 'John');

从库会在接收到 binlog 后执行同样的 SQL 语句。如果此时从库的数据环境与主库不同（例如，执行时的时间戳不同），可能会出现数据不一致。

---

2. ROW 格式

ROW 格式记录的是数据的变化，而不是 SQL 语句本身。也就是说，它会记录在每次数据修改时，哪些行的数据发生了变化，以及这些行的旧值和新值。这种格式比 STATEMENT 格式更精确，因为它记录了每一行数据的具体变化。

特点：

记录的是数据的变更（包括行级别的变更）。

不依赖于 SQL 语句，因此能够避免 STATEMENT 格式中可能出现的非确定性行为。

可以保证主从一致性，即使主库和从库的数据环境不完全相同。

优点：

通过记录数据的变更来保证主从一致性，避免了 STATEMENT 格式中的非确定性问题。

更加精确，特别是在复杂的 SQL 语句（如包含函数、时间戳等）下。

缺点：

存储空间较大，因为记录了每一行的变更。

对于大量数据变更的操作，性能开销较大。

案例：  -- 在主库执行：  INSERT INTO users (id, name) VALUES (1, 'John');  ​  -- binlog 记录（以 ROW 格式）：  # 插入的行数据：  table_map: `db`.`users` (id, name)  write_rows: table `db`.`users` (1, 'John')

在 ROW 格式下，binlog 记录了行级别的数据变动，而不是 SQL 语句本身。即使主库和从库的执行环境不一致（例如，时间函数的值不同），从库也能正确地复制主库的数据。

---

3. MIXED 格式

MIXED 格式是 STATEMENT 和 ROW 格式的结合体。MySQL 会根据语句的特性自动选择使用 STATEMENT 还是 ROW 格式：

对于 确定性 的 SQL 语句（如没有时间函数或随机数函数），MySQL 会使用 STATEMENT 格式。

对于 非确定性 的 SQL 语句（如使用了 NOW()、UUID() 等函数），MySQL 会使用 ROW 格式，以确保复制的数据一致性。

特点：

自动选择最合适的 binlog 格式。

默认情况下，当 binlog_format 设置为 MIXED 时，MySQL 会根据 SQL 语句的类型选择使用 STATEMENT 或 ROW 格式。

优点：

结合了 STATEMENT 格式的空间效率和 ROW 格式的准确性。

适用于大多数场景，避免了两种格式的缺点。

缺点：

由于自动选择格式，可能会导致一些不可预期的行为，尤其是在调试和排查问题时。

案例：  -- 在主库执行：  UPDATE users SET name = 'Alice' WHERE id = 1;  ​  -- 如果是一个确定性的操作，binlog 会记录为：  UPDATE users SET name = 'Alice' WHERE id = 1;  -- 采用 STATEMENT 格式  ​  -- 如果是一个非确定性的操作，如使用了 NOW() 函数：  UPDATE users SET created_at = NOW() WHERE id = 1;  ​  -- binlog 会记录为：  # 行级变动：  table_map: `db`.`users` (id, name, created_at)  write_rows: table `db`.`users` (1, 'Alice', '2025-01-06 00:00:00')

在 MIXED 格式下，MySQL 会根据语句是否包含非确定性元素来选择合适的记录格式。如果没有非确定性元素，它会选择 STATEMENT 格式；如果有，它会选择 ROW 格式。

---

总结与选择：

STATEMENT 格式：适用于简单的、确定性的数据更改操作，存储空间较小，但可能会导致主从数据不一致，特别是对于非确定性 SQL 语句。

ROW 格式：适用于对数据一致性要求较高的场景，能够确保主从数据一致，但存储开销较大。

MIXED 格式：结合了 STATEMENT 和 ROW 格式的优点，根据 SQL 语句的特性自动选择最合适的格式，适用于大多数场景。

选择哪种 binlog 格式应根据实际应用的需求来决定。如果你需要确保主从数据一致性，推荐使用 ROW 格式；如果你希望节省存储空间并且操作较为简单且确定性强，可以选择 STATEMENT 格式。MIXED 格式则是两者的折中方案，适用于大多数常见的应用场景。