在 InnoDB 中,索引按照定义的顺序创建。在往 InnoDB 中写入数据时,InnoDB 首先会将完整的数据记录写入主键索引中,然后按照其他索引定义的顺序依次更新索引信息。
通常,在往数据库中写入数据时,首先会 select
进行查找,如果记录已经存在,则 update
更新,否则 insert
插入。虽然使用 insert ... on duplicate key update
和 replace 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_number
和 account_id
都有唯一索引。所以,secrets 表中不会出现任何两行记录中 global_secret_number
或 account_id
相同。
执行完上述 SQL 后,secrets 表的数据以及索引如下:
⒈ 使用 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
实际运行之后的结果如下:
按照之前所说的,InnoDB 在插入数据时,索引按照定义的顺行更新。所以,在执行上述 SQL 时,InnoDB 首先会向主键索引树中插入完整的记录,此时 auto_increment 变成了 4。之后,InnoDB 会相机更新两个唯一索引 global_secret_number
和 account_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
的值已经存在,会报错
实际运行之后得到的结果如下:
首先,由于 global_secret_number = 1
的值在唯一索引中已经存在,所以 InnoDB 会首先删除相应的记录(即 id = 1
的记录);之后,由于 account_id = 20
的记录在唯一索引中也已经存在,所以 InnoDB 又会删除相应的记录(即 id =2
的记录)。最后,InnoDB 才会将 SQL 语句中的记录插入数据表。
所以,在写入数据库时,为了保证数据的一致性,最可靠的方法还是先 select,如果存在则 update,否则 insert