sql insert data - Lindorm - Alibaba Cloud Documentation Center

UPSERT menyisipkan baris jika kunci primer tidak ada atau memperbarui baris tersebut jika sudah ada. Anda harus menentukan kolom kunci primer dalam setiap pernyataan UPSERT. Fitur ini didukung oleh LindormTable dan LindormTSDB di semua versi.

Berbeda dengan database relasional, UPSERT di Lindorm tidak pernah gagal karena kunci primer duplikat—baris akan dibuat atau diperbarui secara diam-diam.

Pilih perilaku penulisan Anda

Gunakan tabel berikut untuk memilih klausa yang tepat sesuai kasus penggunaan Anda:

Tujuan	Sintaks
Sisipkan atau timpa (default)	`UPSERT INTO ... VALUES ...`
Hanya sisipkan; lewati jika baris sudah ada	`... ON DUPLICATE KEY IGNORE`
Perbarui kolom tertentu jika baris sudah ada; sisipkan jika belum ada	`... ON DUPLICATE KEY UPDATE col = val`
Hanya sisipkan; tampilkan error jika baris sudah ada	`... ON DUPLICATE KEY ERROR` (LindormTable 2.7.8+)

Catatan

Klausa ON DUPLICATE KEY hanya didukung oleh LindormTable dan hanya pada tabel yang parameter CONSISTENCY-nya diatur ke strong.

Perbedaan antara LindormTable dan LindormTSDB

Saat Anda menulis dua baris dengan kunci primer yang sama:

LindormTable — penulisan kedua menimpa baris yang ditulis pertama tanpa error. LindormTable menyimpan kedua penulisan sebagai versi berbeda. Perintah SELECT standar mengembalikan versi terbaru dari setiap kolom. Gunakan petunjuk _l_versions_ untuk mengambil semua versi.
LindormTSDB — penulisan kedua menimpa yang pertama. Tidak ada pengendalian versi yang dipertahankan.

Sintaks

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

Parameter

Ekspresi petunjuk (hint expression)

Hanya untuk LindormTable. Gunakan petunjuk _l_ts_ untuk mengatur timestamp eksplisit bagi baris yang sedang ditulis:

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

Untuk semua opsi petunjuk yang tersedia, lihat Parameters of hintOptions.

ON DUPLICATE KEY

Hanya untuk LindormTable. Memeriksa apakah baris yang ditentukan sudah ada sebelum menulis—mirip dengan operasi checkAndPut di HBase.

Penting

Pada Lindorm SQL 2.8.8.2 dan versi lebih baru, gunakan NOW() dalam klausa VALUES untuk menyisipkan timestamp saat ini secara otomatis. Contoh: UPSERT INTO tb (id, ts) VALUES (1, NOW());. Untuk memeriksa versi Lindorm SQL Anda, lihat SQL versions.

ON DUPLICATE KEY IGNORE

Jika baris sudah ada, penulisan dilewati tanpa error. Jika baris belum ada, data disisipkan.

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

ON DUPLICATE KEY UPDATE

Jika baris sudah ada, kolom yang ditentukan diperbarui ke nilai yang diberikan. Perilaku saat baris belum ada bergantung pada versi LindormTable:

Versi	Baris tidak ada
Lebih awal dari 2.7.8	Tidak ada pembaruan; tidak ada error
2.7.8 dan lebih baru	Error dilaporkan dan data dalam klausa `VALUES` disisipkan

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;

ON DUPLICATE KEY ERROR

Hanya untuk LindormTable 2.7.8 dan versi lebih baru. Jika baris sudah ada, error dilaporkan. Jika baris belum ada, data disisipkan.

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

Contoh

Semua contoh menggunakan tabel sampel berikut:

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);

Menulis satu baris

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

Verifikasi: SELECT * FROM sensor;

Menulis ke kolom tertentu

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

Verifikasi: SELECT * FROM sensor;

Menulis beberapa baris dalam satu pernyataan

Pisahkan setiap baris dengan koma dalam klausa VALUES.

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);

Verifikasi: SELECT * FROM sensor;

Menulis baris dengan kunci primer yang sama (pengendalian versi LindormTable)

LindormTable menyimpan setiap penulisan sebagai versi baru, bukan menimpa langsung. Langkah-langkah berikut menunjukkan cara kerjanya.

Catatan

Di LindormTSDB, penulisan kedua menimpa yang pertama dan tidak ada pengendalian versi yang dipertahankan.

Menulis baris pertama.

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

Meminta data.

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

Output:

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

Menulis baris tersebut untuk kedua kalinya dengan nilai berbeda tetapi kunci primer yang sama.

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

Meminta data lagi. Versi terbaru dikembalikan secara default.

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

Output:

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

Gunakan petunjuk _l_versions_ untuk mengambil semua versi yang tersimpan.

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

Output:

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

Kedua penulisan dipertahankan sebagai versi terpisah.

Langkah selanjutnya

ALTER TABLE — ubah parameter CONSISTENCY pada tabel yang sudah ada untuk mengaktifkan klausa ON DUPLICATE KEY
Atribut tabel (table_options) — atur CONSISTENCY=strong saat membuat tabel.
Parameters of hintOptions — referensi lengkap untuk _l_ts_ dan petunjuk lainnya
SQL versions — periksa versi Lindorm SQL Anda