mysql之binlog的三种格式:

MySQL 提供了三种 binlog 格式，通过参数 binlog_format 设置：
  
STATEMENT（默认）：记录 SQL 语句。缺点是某些语句在复制时可能导致不一致（如非确定性函数）。
ROW：记录每一行的更改，数据一致性高，但生成日志量大，适用于高一致性场景。
MIXED：混合模式，MySQL 根据情况自动选择 STATEMENT 或 ROW 格式。

STATEMENT和ROW的区别，主要从 记录内容 和 一致性 两方面来解释:

1. STATEMENT 模式

• 记录内容：在 STATEMENT 模式下，binlog 日志只记录 SQL 语句本身。例如：

  UPDATE users SET last_login = NOW() WHERE id = 1;
  

  这条语句会直接记录到 binlog 中，表示 “将 id 为 1 的用户的 last_login 字段更新为当前时间”。

• 数据一致性问题：因为 STATEMENT 模式记录的是 SQL 语句，所以在执行时需要依赖运行时环境。如果在不同的数据库环境中执行该 SQL 语句，可能会导致不一致的结果。例如，NOW() 是一个非确定性函数，它在执行时返回的时间依赖于运行时的实际时间。

  • 在主从复制中，假设主库和从库的系统时间不同，那么 NOW() 的值就可能不同，这样在主库和从库中记录的 last_login 就会不一致。
  • 类似的非确定性函数还包括 RAND()、UUID() 等，它们的执行结果每次都可能不同，因此 STATEMENT 模式在使用这些函数时容易导致数据不一致。

2. ROW 模式

• 记录内容：在 ROW 模式下，binlog 不记录 SQL 语句，而是直接记录行级别的数据更改。例如：

  • 假设执行一条 UPDATE 语句 UPDATE users SET last_login = '2024-11-10 12:00:00' WHERE id = 1;
  • 在 ROW 模式下，binlog 会直接记录 id=1 的用户 last_login 字段从旧值到新值的变化（即 '2024-11-10 12:00:00'），而不记录 UPDATE 语句本身。

• 数据一致性：因为 ROW 模式记录的是行的具体数据变化，所以无论在主库还是从库，直接按照数据变化来同步，不依赖运行时环境。因此即使是使用了非确定性函数，结果也会保持一致。

• 日志量大：由于 ROW 模式记录的是具体的行变更，因此如果有大批量的更新操作（例如更新数千条数据），binlog 会将这些行的每一个更改记录下来，日志量会非常大。所以在数据更新量较大的场景中，ROW 模式可能会生成大量的日志。

举个例子来说明

假设有一条语句：

UPDATE users SET score = score + 10 WHERE region = 'North';

• 在 STATEMENT 模式下，binlog 记录的就是这条 SQL 语句。执行时会对 region = 'North' 的所有用户逐个增加 10 分。如果从库的 users 表数据与主库不完全一致，执行结果可能也会不同。

• 在 ROW 模式下，binlog 不记录这条 SQL，而是直接记录每个符合条件的用户的 score 字段的具体变更。例如：

  id=2: score 从 50 变为 60
  id=5: score 从 70 变为 80
  

  这样无论在主库还是从库，都只是直接应用这些行变更，不会因为数据库中的其他数据不同而产生不同的结果，保持数据一致性。

总结

• STATEMENT 模式日志量小，但对于使用非确定性函数的操作可能导致主从数据不一致。
• ROW 模式日志量大，但能保证数据操作的高一致性，非常适合需要数据完全一致的复制场景。