Mysql insert ... on duplicate key update 的用法及在 SQLAlchemy 中的使用

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

语法

使用 insert ... on duplicate key update .. 语法可以避免这种情况，举个例子

drop table if exists `test`;
create table `test` (
	`id` int(11) not null AUTO_INCREMENT,
	`name` varchar(32) not null default '',
	`update_ts` timestamp not null default current_timestamp(),
	primary key (`id`)
) engine=InnoDB default charset=utf8mb4;

主键 id 是天然的唯一索引，我们插入重复数据时会报错

1
2
3

> INSERT INTO test (id, name) VALUES (1, 'wxnacy');
> INSERT INTO test (id, name) VALUES (1, 'wxnacy');
Error 1062: Duplicate entry '1' for key 'PRIMARY'

> SELECT * FROM `test`;
| id | name   | update_ts           |
|------------------------------------
| 1  | wxnacy | 2019-02-24 12:26:58 |

下面我们来换个语句

> insert into test (id, name) values (1, 'wxnacy') on duplicate key update update_ts = current_timestamp();
> SELECT * FROM `test`;

+----+--------+---------------------+
| id | name   | update_ts           |
+----+--------+---------------------+
|  1 | wxnacy | 2019-02-24 12:31:49 |
+----+--------+---------------------+

on duplicate key update 前面是正常的插入语句，其后跟着的是当唯一索引冲突时，想要更新的数据。

再换个使用场景，如果我想让数据库中用户名是唯一的，则可以先建立唯一索引，在使用该语法。

> alter table test add unique index_name (name);
> insert into test (name) values ('wenn') on duplicate key update update_ts = current_timestamp();
> SELECT * FROM `test`;
+----+--------+---------------------+
| id | name   | update_ts           |
+----+--------+---------------------+
|  1 | wxnacy | 2019-02-24 12:31:49 |
|  2 | wenn   | 2019-02-24 12:39:25 |
+----+--------+---------------------+

> insert into test (name) values ('wenn') on duplicate key update update_ts = current_timestamp();
> SELECT * FROM `test`;

+----+--------+---------------------+
| id | name   | update_ts           |
+----+--------+---------------------+
|  1 | wxnacy | 2019-02-24 12:31:49 |
|  2 | wenn   | 2019-02-24 12:40:15 |
+----+--------+---------------------+

这样及保证了避免插入重复数据，同时程序也没有报错，我还可以根据 update 的数据来分析问题的根源。

SQLAlchemy 中的使用

这个功能需要 SQLAlchemy 1.2 版本以上才支持，官方的例子

from sqlalchemy.dialects.mysql import insert

insert_stmt = insert(my_table). \
    values(id='some_id', data='some data to insert')

on_conflict_stmt = insert_stmt.on_duplicate_key_update(
    data=insert_stmt.inserted.data,
    status='U'
)

conn.execute(on_conflict_stmt)

析出的 sql 语句为

1
2
3

INSERT INTO my_table (id, data)
VALUES (:id, :data)
ON DUPLICATE KEY UPDATE data=VALUES(data), status=:status_1

INSERT … ON DUPLICATE KEY UPDATE Syntax

Support for INSERT..ON DUPLICATE KEY UPDATE

缺失模块。
1、请确保node版本大于6.2
2、在博客根目录（注意不是yilia根目录）执行以下命令：
npm i hexo-generator-json-content --save

3、在根目录_config.yml里添加配置： jsonContent: meta: false pages: false posts: title: true date: true path: true text: false raw: false content: false slug: false updated: false comments: false link: false permalink: false excerpt: false categories: false tags: true