Mysql相关

[TOC]

事务

MySQL事务：概念、特性与实战详解

一、事务的定义与核心作用

事务（Transaction）是一组数据库操作的逻辑单元，这些操作要么全部成功执行，要么全部失败回滚，确保数据的一致性和完整性。

典型场景：银行转账（从账户A扣款与向账户B存款必须同时成功或同时失败）。

二、事务的四大特性（ACID）

特性	含义	示例场景
原子性	事务中的操作是最小单位，不可分割。要么全做，要么全不做。	转账时扣款与存款必须同时完成
一致性	事务执行前后，数据从一个合法状态变为另一个合法状态。	转账前后账户总金额不变
隔离性	多个事务并发执行时，相互之间不受干扰，如同单线程执行。	多个用户同时转账不互相影响
持久性	事务提交后，数据永久保存，即使系统崩溃也不会丢失。	转账成功后，数据永久写入磁盘

三、MySQL事务的实现与管理

1. 事务的开启与提交

• 显式事务（手动控制）：

  START TRANSACTION;  -- 或 BEGIN
  -- 一系列SQL操作
  COMMIT;  -- 提交事务，数据永久保存
  ROLLBACK;  -- 回滚事务，撤销所有操作
  

• 隐式事务（自动提交）：默认情况下，MySQL每条SQL语句都是一个独立事务（由AUTOCOMMIT参数控制）。

  SET AUTOCOMMIT = 0;  -- 关闭自动提交，后续语句需手动COMMIT
  SET AUTOCOMMIT = 1;  -- 开启自动提交（默认）
  

2. 事务隔离级别（解决并发问题）

MySQL支持4种隔离级别（由低到高），用于解决事务并发时的三大问题：

隔离级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED	允许	允许	允许	最高
READ COMMITTED	禁止	允许	允许	高
REPEATABLE READ	禁止	禁止	允许	中
SERIALIZABLE	禁止	禁止	禁止	最低

• 设置隔离级别：

  -- 查看当前隔离级别
  SELECT @@TRANSACTION_ISOLATION;
    
  -- 设置全局隔离级别（需重启生效）
  SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    
  -- 设置会话隔离级别（当前连接生效）
  SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  

• 典型问题解析：

  • 脏读：事务A读取到事务B未提交的数据，若B回滚，A读到的数据为脏数据。
  • 不可重复读：事务A两次读取同一数据，期间事务B修改并提交，导致A两次结果不同。
  • 幻读：事务A读取符合条件的记录，事务B插入新记录并提交，A再次读取时发现多了记录。

3. 事务日志（确保持久性）

MySQL通过重做日志（Redo Log） 和回滚日志（Undo Log） 实现事务持久性：

• Redo Log：记录事务对数据的修改，用于崩溃恢复时重做未完成的事务。
• Undo Log：记录事务修改前的状态，用于回滚操作或 MVCC（多版本并发控制）。

四、实战案例：转账事务示例

-- 假设存在账户表 accounts (id, name, balance)
START TRANSACTION;
BEGIN TRY
    -- 从A账户扣款100元
    UPDATE accounts SET balance = balance - 100 WHERE name = 'A';
    -- 向B账户存款100元
    UPDATE accounts SET balance = balance + 100 WHERE name = 'B';
    COMMIT;  -- 全部成功则提交
END TRY
BEGIN CATCH
    ROLLBACK;  -- 出错则回滚
    SELECT ERROR_MESSAGE() AS '错误信息';
END CATCH;

五、事务相关优化与注意事项

1. 避免长事务：长时间占用锁资源，影响并发性能。
2. 批量提交：大事务可拆分为小事务（如每1000条记录提交一次）。
3. 锁与隔离级别：高隔离级别会增加锁竞争，根据业务场景选择合适级别。
4. MVCC机制：在REPEATABLE READ级别下，MySQL通过MVCC解决不可重复读，但需显式加锁处理幻读。
5. 死锁处理：事务等待锁超时（默认50秒）会自动回滚，可通过SHOW ENGINE INNODB STATUS查看死锁日志。

六、InnoDB与MyISAM的事务支持

• InnoDB：支持事务、ACID特性、行级锁、外键约束，是默认存储引擎。
• MyISAM：不支持事务，仅支持表级锁，适用于读多写少的场景（如日志表）。

总结

MySQL事务通过ACID特性确保数据一致性，结合隔离级别和日志机制处理并发与持久化问题。在开发中，合理控制事务范围、选择隔离级别及处理异常回滚，是避免数据不一致的关键。

通过csv向目标表导入

在数据库导入设置中，替换方法（或冲突处理策略）的不同选项决定了遇到重复数据时的行为。以下是常见选项的含义及适用场景：

1. NONE（无替换，默认选项）

• 行为：遇到重复主键或唯一索引时，整批插入失败并回滚。
• 适用场景：确保数据严格唯一，不允许任何冲突。
• 风险：若数据中存在一条重复记录，所有插入都会失败。

2. ON DUPLICATE KEY UPDATE

• 行为：冲突时执行 UPDATE，用新数据覆盖旧记录。

• 示例：

  INSERT INTO users (id, name, age) VALUES (1, 'Alice', 25)
  ON DUPLICATE KEY UPDATE name = 'Alice', age = 25;
  

• 适用场景：需要定期同步数据，更新已有记录的部分字段。

3. INSERT IGNORE

• 行为：冲突时跳过当前行，继续处理后续数据（静默忽略错误）。
• 示例：

  INSERT IGNORE INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob');
  

• 适用场景：批量导入中允许部分重复数据，如日志记录、临时表。

4. REPLACE INTO

• 行为：冲突时先删除旧记录，再插入新数据（主键不变，其他字段被覆盖）。
• 示例：

  REPLACE INTO users (id, name, age) VALUES (1, 'Alice', 25);
  

• 适用场景：需要完全替换旧记录，而非部分更新。

对比表格

选项	冲突时操作	数据保留	性能影响
NONE	整批失败	所有旧数据保留	最低
ON DUPLICATE KEY	更新冲突行	保留旧数据中未更新字段	中等
INSERT IGNORE	跳过冲突行	所有旧数据保留	较低（需唯一索引）
REPLACE INTO	删除+插入	仅保留新数据	较高（涉及两次操作）

选择建议

• 数据同步：用 ON DUPLICATE KEY UPDATE，只更新变化的字段。
• 日志/临时数据：用 INSERT IGNORE，允许重复记录存在。
• 全量覆盖：用 TRUNCATE TABLE + 导入，确保数据一致性。
• 严格唯一：用 NONE，配合事务回滚保证原子性。

注意事项

1. 性能考量：REPLACE 和 TRUNCATE 会触发索引重建，大数据量时慎用。
2. 外键约束：TRUNCATE 会清空表，可能影响关联表数据。
3. 唯一索引：除主键外，其他唯一索引冲突也会触发替换逻辑。

根据你的业务需求（如数据是否允许重复、冲突时是否需要更新）选择合适的策略即可。