Use INSERT ON DUPLICATE KEY UPDATE to insert or update data - AnalyticDB: Central de Documentação do Alibaba Cloud

O comando INSERT ON DUPLICATE KEY UPDATE (conhecido como upsert) insere uma linha em uma tabela. Se já existir uma linha com a mesma chave primária, o comando atualiza essa linha.

O AnalyticDB for MySQL aplica a seguinte lógica:

Condição	Ação	Linhas afetadas
Sem conflito	Executa `INSERT`	1
Conflito de chave primária	Executa `UPDATE` na linha existente	1

Observações de uso

A cláusula ON DUPLICATE KEY UPDATE aceita apenas atribuições de valores literais e atribuições com VALUES(). O sistema não suporta expressões aritméticas, condicionais ou funções. A referência a um tipo de expressão incompatível gera erro.

-- Supported: literal value assignment
ON DUPLICATE KEY UPDATE course_name = 'Intro to SQL'

-- Supported: VALUES() assignment
ON DUPLICATE KEY UPDATE course_name = VALUES(course_name)

-- Not supported: arithmetic expression (causes an error)
ON DUPLICATE KEY UPDATE count = count + 1

Gravações de grande volume ou alta frequência (acima de 100 QPS) podem aumentar significativamente o uso da CPU. Para atualizações em lote, use REPLACE INTO.

Sintaxe

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

Exemplos

Os exemplos abaixo usam a tabela student_course. Crie-a com a seguinte instrução:

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

Inserir uma nova linha

A instrução a seguir insere uma linha. Como ainda não existe linha com user_id = 11056941, o AnalyticDB for MySQL a insere.

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

Consulte a tabela para confirmar a inserção:

SELECT * FROM student_course;

Saída esperada:

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

Atualizar uma linha existente

A instrução abaixo usa user_id = 11056941, que corresponde à linha existente. O AnalyticDB for MySQL atualiza apenas as colunas especificadas na cláusula ON DUPLICATE KEY UPDATE (course_name e business_id). As demais colunas permanecem inalteradas.

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

Consulte a tabela novamente:

SELECT * FROM student_course;

Saída esperada — o valor de id permanece 277941 (inalterado) e course_name é atualizado para --66:

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

Perguntas frequentes

Por que recebo o erro "insert on duplicate key update statement only support 'primitive value' and values() expr"?

A cláusula ON DUPLICATE KEY UPDATE aceita somente valores literais ou atribuições com VALUES(). O sistema não suporta expressões aritméticas (como count + 1), condicionais ou funções.

Corrija a instrução usando uma atribuição literal ou com VALUES():

Atribuição de valor 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';

Atribuição com 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);

Por que recebo o erro "Error: Field 'xxxx' doesn't have a default value"?

Esse erro ocorre quando a coluna é definida como NOT NULL sem valor padrão e a instrução INSERT não fornece um valor para ela.

Resolva o problema com uma das abordagens abaixo:

Adicione o valor da coluna ausente à instrução INSERT INTO ... ON DUPLICATE KEY UPDATE.
Altere a tabela para adicionar um valor padrão à coluna e execute a instrução novamente.

Posso inserir várias linhas em uma única instrução?

Sim. Forneça múltiplos conjuntos de valores na cláusula VALUES e trate os conflitos na cláusula ON DUPLICATE KEY UPDATE.

A instrução a seguir insere três linhas. Se o user_id corresponder a uma linha existente, o sistema atualiza apenas course_name e business_id.

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