MySQL 中使用 insert ... on duplicate key update 和 replace into 可能引起的问题

2,188 阅读3分钟

  在 InnoDB 中,索引按照定义的顺序创建。在往 InnoDB 中写入数据时,InnoDB 首先会将完整的数据记录写入主键索引中,然后按照其他索引定义的顺序依次更新索引信息。

  通常,在往数据库中写入数据时,首先会 select 进行查找,如果记录已经存在,则 update 更新,否则 insert 插入。虽然使用 insert ... on duplicate key updatereplace into 有时候也可以达到目的,但这两种方法在特定的条件下却会出问题。

CREATE TABLE secrets(
    id INT AUTO_INCREMENT PRIMARY KEY,
    global_secret_number INT NOT NULL UNIQUE,
    account_id INT NOT NULL UNIQUE,
    secret VARCHAR(50) NOT NULL
);

INSERT INTO secrets VALUES(0, 1, 10, 'account 10''s secret');
INSERT INTO secrets VALUES(0, 2, 20, 'account 20''s secret');

   在数据表 secrets 中,id 为主键,global_secret_numberaccount_id 都有唯一索引。所以,secrets 表中不会出现任何两行记录中 global_secret_numberaccount_id 相同。

   执行完上述 SQL 后,secrets 表的数据以及索引如下:

secrets 表中的数据

secrets 表中的索引

secrets 表中当前 auto_increment 自增情况

⒈ 使用 insert ... on duplicate key update 可能会引起的问题

   现在执行以下 SQL:

INSERT INTO secrets(global_secret_number, account_id, secret) VALUES(1, 20, 'account 20''s new secret') ON DUPLICATE KEY UPDATE secret = 'account 20''s new secret';

   执行完以上 SQL 后,我们期望的结果:

  • 因为 global_secret_number = 1 的记录已经存在,所以报错
  • 因为 account_id = 20 的记录已经存在,所以报错
  • 如果数据表中存在一条 global_secret_number = 1 AND account_id = 20 的记录,那么更新 secret

  实际运行之后的结果如下:

SQL 执行之后 secrets 表中的数据

   按照之前所说的,InnoDB 在插入数据时,索引按照定义的顺行更新。所以,在执行上述 SQL 时,InnoDB 首先会向主键索引树中插入完整的记录,此时 auto_increment 变成了 4。之后,InnoDB 会相机更新两个唯一索引 global_secret_numberaccount_id ,在更新 global_secrent_number 时,因为 1 已经存在,所以之前插入主键索引树的记录会被删除,同时将 global_secret_number = 1 的记录的 secret 值更新为 account 20‘s new secret

⒉ 使用 replace into 可能会引起的问题

   在 MySQL 中,replace into 的执行机制为

  • 当主键索引、任一唯一索引中有与 SQL 语句中对应字段相同的值时,会首先删除已经存在的记录,然后执行 insert 糙作
  • 否则直接执行 insert

   现在执行以下 SQL:

REPLACE INTO secrets VALUES(0, 1, 20, 'account 20''s new secret');

  期望的结果:

  • 如果 global_secret_number = 1 AND account_id = 20 的记录存在,则执行 update
  • 如果 global_secret_number = 1 AND account_id = 20 的记录不存在,则执行 insert
  • 由于唯一索引中 global_secret_number = 1 的值已经存在,会报错

  实际运行之后得到的结果如下:

选区_007.png

  首先,由于 global_secret_number = 1 的值在唯一索引中已经存在,所以 InnoDB 会首先删除相应的记录(即 id = 1 的记录);之后,由于 account_id = 20 的记录在唯一索引中也已经存在,所以 InnoDB 又会删除相应的记录(即 id =2 的记录)。最后,InnoDB 才会将 SQL 语句中的记录插入数据表。

所以,在写入数据库时,为了保证数据的一致性,最可靠的方法还是先 select,如果存在则 update,否则 insert