SQL插入資料 - Lindorm

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關鍵字後的語句，更新指定列的值。
- 如果指定行不存在，2.7.8以下版本的寬表引擎不更新也不報錯，2.7.8及以上版本的寬表引擎不會報錯，將直接寫入VALUES子句中的資料。
ON DUPLICATE KEY子句後跟隨IGNORE時，如果指定行已存在，則既不報錯也不寫入；如果指定行不存在，則直接將資料寫入。
在寬表引擎2.7.8及以上版本中，ON DUPLICATE KEY子句後跟隨ERROR時，如果指定行已存在，則寫入報錯；如果指定行不存在，則直接寫入資料。
僅支援CONSISTENCY為strong的表。CONSISTENCY參數的詳細介紹，請參見表屬性（table_options）。如果您想要更改CONSISTENCY參數的值，請參見ALTER TABLE。

重要

自SQL引擎2.8.8.2版本開始，使用UPSERT語句寫入TIMESTAMP資料時，如果希望在執行SQL時自動寫入目前時間戳，可以在寫入語句中添加NOW() 函數。例如UPSERT INTO tb (id, ts) VALUES (1, NOW());。如何查看Lindorm SQL版本，請參見SQL版本說明。

樣本

假設樣本表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子句寫入資料：

寬表引擎2.7.8以下版本：
如果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;
```
寬表引擎2.7.8及以上版本：
如果device_id='F07A1260'，region='north-cn'，time='2021-04-22 15:33:10'，temperature=13.2的行存在，更新temperature列的值為30；如果該行不存在，則寫入VALUES子句中的資料。
```
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;查看資料是否寫入成功。

資料已存在時，寫入報錯（ON DUPLICATE KEY ERROR）

在寬表引擎2.7.8及以上版本，使用ON DUPLICATE KEY ERROR子句寫入資料：

如果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 ERROR;

寫入帶有時間戳記的資料

在寬表引擎，向表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相同，第二次寫入的資料將直接覆蓋第一次寫入的資料。

第一次寫入資料。

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

查詢寫入結果。

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       |
+-----------+----------+-------------------------------+-------------+----------+

第二次寫入資料。

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

查詢第二次寫入後的結果。

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列第一次寫入的資料被第二次寫入的資料覆蓋。

通過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語句實際是將兩次寫入的資料儲存為了兩個版本。