本文介绍如何使用INSERT ON DUPLICATE KEY UPDATE

功能说明

执行INSERT ON DUPLICATE KEY UPDATE语句时,AnalyticDB MySQL版会首先尝试在表中插入新行,但如果新的数据与已有数据的主键重复,将使用INSERT ON DUPLICATE KEY UPDATE子句中指定的值更新现有行。AnalyticDB MySQL版会根据待写入行是否存在选择对应的执行语句,规则如下:
  • 待写入行不存在,则执行INSERT来插入新行,受影响的行数为1。
  • 待写入行存在,则执行UPDATE来更新现有行,受影响的行数也为1。

注意事项

INSERT INTO ... ON DUPLICATE KEY UPDATE...只支持等值更新,不支持算数表达式。

语法

INSERT INTO table_name[(column_name[, …])]
[VALUES]
[(value_list[, …])]
ON DUPLICATE KEY UPDATE
   c1 = v1, 
   c2 = v2,
   ...;

示例

本文所有示例均基于student_course表,建表语句如下:
CREATE TABLE student_course(
    id bigint,
    user_id bigint,
    nc_id varchar,
    nc_user_id varchar,
    nc_commodity_id varchar,
    course_no varchar,
    course_name varchar,
    business_id varchar,
    PRIMARY KEY(user_id)
) DISTRIBUTED BY HASH(user_id);
使用如下语句插入一行数据:
INSERT INTO student_course (`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`)
VALUES(277941, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', '工业会计实战V9.0--55', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = '工业会计实战V9.0--55',
business_id = 'kuaiji';
执行SELECT * FROM student_course;语句,返回如下结果说明数据插入成功:
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
| id    | user_id  | nc_id               | nc_user_id          | nc_commodity_id     | course_no | course_name         |business_id |
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
|277941 | 11056941 |1001EE1000000043G2T5 |1001EE1000000043G2TO |1001A5100000003YABO2 |kckm303    |工业会计实战V9.0--55 |kuaiji      |
此时,需要再往student_course表中入一行新数据:
INSERT INTO student_course(`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`)
VALUES(277942, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', '工业会计实战V9.0--66', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = '工业会计实战V9.0--66',
business_id = 'kuaiji';
但由于新插入的数据中存在重复主键(即user_id与第一次插入的数据重复,均为11056941),因此执行上述语句后只会更新ON DUPLICATE KEY UPDATE子句中的course_name = '工业会计实战V9.0--66',business_id = 'kuaiji'值,您可以执行SELECT * FROM student_course;语句来查看更新后的数据,返回结果如下:
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
| id    | user_id  | nc_id               | nc_user_id          | nc_commodity_id     | course_no | course_name         |business_id |
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
|277941 | 11056941 |1001EE1000000043G2T5 |1001EE1000000043G2TO |1001A5100000003YABO2 |kckm303    |工业会计实战V9.0--66 |kuaiji      |

常见问题

Q:是否支持通过Logstash插件使用INSERT ON DUPLICATE KEY UPDATE语句批量插入数据?

A:支持。使用INSERT ON DUPLICATE KEY UPDATE语句批量插入数据时,您无需在每个VALUES()语句后都添加ON DUPLICATE KEY UPDATE,仅需在最后一个VALUES()后加上即可。

例如,需要在student_course表中批量插入3条数据,您可以执行如下语句:
INSERT INTO student_course(`id`, `user_id`, `nc_id`, `nc_user_id`, `nc_commodity_id`, `course_no`, `course_name`, `business_id`)
VALUES(277943, 11056941, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', '工业会计实战V9.0--77', 'kuaiji'),
(277944, 11056943, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', '工业会计实战V9.0--88', 'kuaiji'),
(277945, 11056944, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', '工业会计实战V9.0--99', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = '工业会计实战V9.0--77',
business_id = 'kuaiji';

更多关于Logstash插件详情,请参见Logstash概述