Gunakan INSERT ON DUPLICATE KEY UPDATE untuk menyisipkan atau memperbarui data - AnalyticDB

Saat mengeksekusi pernyataan INSERT ON DUPLICATE KEY UPDATE untuk memperbarui data dalam sebuah tabel, AnalyticDB for MySQL terlebih dahulu mencoba menyisipkan baris data ke dalam tabel. Jika baris baru memiliki kunci primer yang bertentangan dengan baris yang sudah ada, baris tersebut diperbarui berdasarkan kunci primer yang sama.

AnalyticDB for MySQL memilih pernyataan SQL berdasarkan aturan berikut:

Jika baris yang akan disisipkan belum ada, AnalyticDB for MySQL menggunakan pernyataan INSERT untuk menyisipkannya. Jumlah baris yang terpengaruh adalah 1.
Jika baris yang akan disisipkan sudah ada, AnalyticDB for MySQL menggunakan klausa ON DUPLICATE KEY UPDATE untuk memperbarui baris yang sudah ada. Jumlah baris yang terpengaruh adalah 1.

Catatan penggunaan

Anda dapat mengeksekusi pernyataan INSERT INTO ... ON DUPLICATE KEY UPDATE... untuk memperbarui data menggunakan penugasan nilai, tetapi tidak mendukung ekspresi kompleks seperti ekspresi aritmetika atau ekspresi kondisional.
Jika Anda mengeksekusi pernyataan INSERT ON DUPLICATE KEY UPDATE untuk memperbarui sejumlah besar data atau memperbarui data dengan frekuensi tinggi (lebih dari 100 QPS), pemanfaatan CPU dapat meningkat secara signifikan. Disarankan untuk menggunakan pernyataan REPLACE INTO guna memperbarui data secara batch. Untuk informasi selengkapnya, lihat REPLACE INTO.

Sintaks

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

Contoh

Dalam contoh berikut, digunakan tabel student_course. Eksekusi pernyataan berikut untuk membuat tabel bernama 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);

Eksekusi pernyataan berikut untuk menyisipkan satu baris data ke dalam tabel student_course:

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', 'Industrial Accounting Practice V9.0--55', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = 'Industrial Accounting Practice V9.0--55',
business_id = 'kuaiji';

Eksekusi pernyataan SELECT * FROM student_course; untuk mengkueri data dalam tabel student_course. Hasil kueri berikut menunjukkan bahwa satu baris data telah disisipkan:

+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
| id    | user_id  | nc_id               | nc_user_id          | nc_commodity_id     | course_no | course_name         |business_id |
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
|277941 | 11056941 | 1001EE1000000043G2T5|1001EE1000000043G2TO | 1001A5100000003YABO2|   kckm303 | Industrial Accounting Practice V9.0--55|   kuaiji   |
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+

Eksekusi pernyataan berikut untuk menyisipkan baris data lain ke dalam tabel 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', 'Industrial Accounting Practice V9.0--66', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = 'Industrial Accounting Practice V9.0--66',
business_id = 'kuaiji';

Baris yang ingin disisipkan menggunakan kunci primer yang sama (kolom user_id: 11056941) dengan baris pertama yang telah disisipkan. Setelah pernyataan di atas dieksekusi, hanya nilai-nilai course_name = 'Industrial Accounting Practice V9.0--66' dan business_id = 'kuaiji' dalam klausa ON DUPLICATE KEY UPDATE yang diperbarui. Setelah mengeksekusi pernyataan SELECT * FROM student_course; untuk mengkueri data yang diperbarui, hasil berikut dikembalikan:

+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
| id    | user_id  | nc_id               | nc_user_id          | nc_commodity_id     | course_no | course_name         |business_id |
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+
|277941 | 11056941 | 1001EE1000000043G2T5|1001EE1000000043G2TO | 1001A5100000003YABO2|   kckm303 | Industrial Accounting Practice V9.0--66|   kuaiji   |
+-------+----------+---------------------+---------------------+---------------------+-----------+---------------------+------------+

Pertanyaan Umum

Apa yang harus saya lakukan jika pesan kesalahan `insert on duplicate key update` statement only support 'primitive value' and values() expr. dikembalikan?

Penyebab: Pernyataan INSERT INTO ... ON DUPLICATE KEY UPDATE... mendukung penugasan nilai literal (Contoh 1) dan penugasan VALUES() (Contoh 2), tetapi tidak mendukung ekspresi kompleks (seperti ekspresi aritmetika dan ekspresi kondisional) atau fungsi. Jika klausa ON DUPLICATE KEY UPDATE mereferensikan ekspresi kompleks atau fungsi, maka terjadi kesalahan.

Solusi: Ubah pernyataan SQL dengan menggunakan penugasan nilai literal atau penugasan VALUES() dalam klausa ON DUPLICATE KEY UPDATE.

Contoh 1: penugasan nilai literal

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', 'Industrial Accounting Practice V9.0--55', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = 'Industrial Accounting Practice V9.0--55',
business_id = 'kuaiji';

Contoh 2: penugasan VALUES()

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', 'Industrial Accounting Practice V9.0--55', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = VALUES(course_name),
business_id = VALUES(business_id);

Bagaimana cara mengatasi kesalahan: Field 'xxxx' doesn't have a default value?

Penyebab: Kesalahan ini terjadi pada kolom yang didefinisikan sebagai `NOT NULL` (misalnya, column_name column_datatype NOT NULL) dan tidak memiliki nilai default. Kesalahan ini dipicu ketika Anda mengeksekusi pernyataan INSERT INTO ... ON DUPLICATE KEY UPDATE... tanpa menyisipkan nilai untuk kolom tersebut.

Solusi: Gunakan salah satu metode berikut.

Ubah pernyataan INSERT INTO ... ON DUPLICATE KEY UPDATE... untuk menyisipkan nilai ke kolom tersebut.
Ubah nilai default suatu kolom. Kemudian, jalankan kembali pernyataan INSERT INTO ... ON DUPLICATE KEY UPDATE....

Apakah saya dapat mengeksekusi pernyataan `INSERT ON DUPLICATE KEY UPDATE` untuk menyisipkan data secara batch?

Ya, Anda dapat mengeksekusi pernyataan ini untuk menyisipkan data secara batch. Saat mengeksekusi pernyataan INSERT ON DUPLICATE KEY UPDATE secara batch, Anda dapat memberikan beberapa set nilai dalam klausa VALUES dan menentukan cara menangani konflik dalam klausa ON DUPLICATE KEY UPDATE.

Sebagai contoh, eksekusi pernyataan berikut untuk menyisipkan tiga baris data ke dalam tabel student_course:

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', 'Industrial Accounting Practice V9.0--77', 'kuaiji'),
(277944, 11056943, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--88', 'kuaiji'),
(277945, 11056944, '1001EE1000000043G2T5', '1001EE1000000043G2TO', '1001A5100000003YABO2', 'kckm303', 'Industrial Accounting Practice V9.0--99', 'kuaiji')
ON DUPLICATE KEY UPDATE
course_name = VALUES(course_name),
business_id = VALUES(business_id);