文档

UPSERT

更新时间:

UPSERT是INSERT与UPDATE的结合语法,表示行存在时执行UPDATE,不存在时执行INSERT。执行UPSERT操作时必须指定完整的PRIMARY KEY的相关列信息。UPSERT语法支持带时间戳的数据写入和批量写入,其中的UPSERT谓词也可用INSERT来替代。

引擎与版本

UPSERT语法适用于宽表引擎和时序引擎。无版本限制。

与关系型数据库写入的区别

Lindorm的UPSERT语句写入数据的效果与传统关系型数据库的写入效果存在以下不同:

  • 传统关系型数据库

    如果表定义了PRIMARY KEY,那么当连续两次写入相同PRIMARY KEY的数据时(使用INSERT语句且不带ON DUPLICATE KEY子句),第二次写入会失败。此时需要执行UPDATE语句,或在第二次执行INSERT语句时,在语句中带上ON DUPLICATE KEY子句。

  • Lindorm

    • 宽表引擎:使用UPSERT语句写入数据时,即使连续两次写入的PRIMARY KEY相同,第二次写入数据时也不会报错,而是覆盖UPSERT涉及的数据。实际上,Lindorm会将两次写入的数据保存为两个版本,当使用SELECT语句查询某行数据时,默认返回最新版本的数据。

    • 时序引擎:使用UPSERT语句写入数据时,连续两次写入的PRIMARY KEY相同,则第二次写入的数据将直接覆盖第一次写入的数据。

使用UPSERT语句连续两次写入相同PRIMARY KEY数据的示例,请参见写入相同PRIMARY KEY的数据

语法

upsert_statement   ::= { UPSERT | INSERT } [ hint_expression ] 
                       INTO table_identifier columns_delaration
                       VALUES value_list ( ',' value_list)*
                       [ ON DUPLICATE KEY column_identifier = 
                         value_literal | IGNORE ]
columns_delaration ::=  '(' column_identifier ( ',' column_identifier)* ')'
value_list         ::=  '(' value_expression( ',' value_expression)* ')'

使用说明

HINT表达式(hint_expression

宽表引擎支持HINT表达式。

支持在UPSERT语句中添加HINT参数_l_ts_,用于指定写入数据的时间戳。_l_ts_参数的详细介绍,请参见hintOption参数说明

ON DUPLICATE KEY

宽表引擎支持ON DUPLICATE KEY子句。

ON DUPLICATE KEY子句用于检查指定行是否已存在,类似于HBase中的checkAndPut操作,具体规则如下:

  • ON DUPLICATE KEY子句后跟随UPDATE,用于更新某一列的值。如果指定行已存在,则执行UPDATE关键字后的语句,更新指定列的值;如果指定行不存在,则既不报错也不更新指定列的值。例如ON DUPLICATE KEY UPDATE c1 = 20是指如果指定行已存在,则将c1列的值更新为20;如果指定行不存在则不更新也不报错。

  • ON DUPLICATE KEY子句后跟随IGNORE时,如果指定行已存在,则既不报错也不写入;如果指定行不存在,则直接将数据写入。

  • 仅支持CONSISTENCYstrong的表。CONSISTENCY参数的详细介绍,请参见表属性(table_options)。如果您想要更改CONSISTENCY参数的值,请参见ALTER TABLE

示例

假设示例表sensor的结构如下:

CREATE TABLE sensor (
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
)WITH(VERSIONS=2);

写入数据

UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-04-22 15:33:00',12.1,45);

结果验证

您可以执行SELECT * FROM sensor;查看数据是否写入成功。

部分列写入数据

UPSERT INTO sensor(device_id, region, time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',13.2);

结果验证

您可以执行SELECT * FROM sensor;查看数据是否写入成功。

数据已存在时,忽略写入(ON DUPLICATE KEY IGNORE

在宽表引擎中,使用ON DUPLICATE KEY IGNORE子句写入数据:

如果device_id='F07A1260',region='north-cn',time='2021-04-22 15:33:10',temperature=13.2的行存在,则不写入数据;如果该行不存在,写入数据。

UPSERT INTO sensor(device_id,region,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',13.2) ON DUPLICATE KEY IGNORE;

结果验证

您可以执行SELECT * FROM sensor;查看数据是否写入成功。

数据已存在时,更新写入(ON DUPLICATE KEY UPDATE)

在宽表引擎中,使用ON DUPLICATE KEY UPDATE子句写入数据:

如果device_id='F07A1260',region='north-cn',time='2021-04-22 15:33:10',temperature=13.2的行存在,更新temperature列的值为30;如果该行不存在,既不更新temperature列的值也不报错。

UPSERT INTO sensor(device_id,region,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',13.2) ON DUPLICATE KEY UPDATE temperature = 30;

结果验证

您可以执行SELECT * FROM sensor;查看数据是否写入成功。

写入带有时间戳的数据

在宽表引擎,向表sensor中写入一行数据,并设置时间戳为111232

UPSERT /*+ _l_ts_(111232) */ INTO sensor (device_id ,region ,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:00',12.1);

结果验证

您可以执行SELECT * FROM sensor;查看数据是否写入成功。

批量写入数据

UPSERT INTO sensor (device_id ,region ,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:20',10.6), ('F07A1261','south-cn','2021-04-22 15:33:00',18.1), ('F07A1261','south-cn','2021-04-22 15:33:10',19.7);

结果验证

您可以执行SELECT * FROM sensor;查看数据是否写入成功。

写入相同PRIMARY KEY的数据

在宽表引擎中,使用UPSERT语句连续两次写入相同PRIMARY KEY的数据,并通过HINT查询写入结果。

说明

时序引擎连续两次写入的PRIMARY KEY相同,第二次写入的数据将直接覆盖第一次写入的数据。

  1. 第一次写入数据。

    UPSERT INTO sensor(device_id ,region ,time,temperature,humidity) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',13.2,45); 
  2. 查询写入结果。

    SELECT * FROM sensor WHERE device_id='F07A1260' AND region='north-cn'; 

    返回结果:

    +-----------+----------+-------------------------------+-------------+----------+
    | device_id |  region  |             time              | temperature | humidity |
    +-----------+----------+-------------------------------+-------------+----------+
    | F07A1260  | north-cn | 2021-04-22 15:33:10 +0000 UTC | 13.2        | 45       |
    +-----------+----------+-------------------------------+-------------+----------+
  3. 第二次写入数据。

    UPSERT INTO sensor(device_id ,region ,time,temperature,humidity) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',16.7,52); 
  4. 查询第二次写入后的结果。

    SELECT * FROM sensor WHERE device_id='F07A1260' AND region='north-cn'; 

    返回结果:

    +-----------+----------+-------------------------------+-------------+----------+
    | device_id |  region  |             time              | temperature | humidity |
    +-----------+----------+-------------------------------+-------------+----------+
    | F07A1260  | north-cn | 2021-04-22 15:33:10 +0000 UTC | 16.7        | 52       |
    +-----------+----------+-------------------------------+-------------+----------+

    通过返回结果可以看到,两次写入数据时,主键列device_id、region和time中的数据相同,最终temperature列第一次写入的数据被第二次写入的数据覆盖。

  5. 通过HINT查询所有版本的数据。

    SELECT /*+ _l_versions_(2)  */ device_id, region, time,temperature,humidity FROM sensor WHERE device_id='F07A1260';

    返回结果:

    +-----------+----------+-------------------------------+-------------+----------+
    | device_id |  region  |             time              | temperature | humidity |
    +-----------+----------+-------------------------------+-------------+----------+
    | F07A1260  | north-cn | 2021-04-22 15:33:10 +0000 UTC | 16.7        | 52       |
    | F07A1260  | north-cn | 2021-04-22 15:33:10 +0000 UTC | 13.2        | 45       |
    +-----------+----------+-------------------------------+-------------+----------+

    通过返回结果可以看到,UPSERT语句实际是将两次写入的数据保存为了两个版本。

  • 本页导读 (1)
文档反馈