sql insert data - Lindorm: Central de Documentação do Alibaba Cloud

O comando UPSERT insere uma linha se a chave primária não existir ou a atualiza caso já exista. Especifique as colunas de chave primária em todas as instruções UPSERT. O LindormTable e o LindormTSDB suportam esse recurso em todas as versões.

Ao contrário dos bancos de dados relacionais, o UPSERT no Lindorm nunca falha devido a uma chave primária duplicada: o sistema cria ou atualiza a linha silenciosamente.

Escolha o comportamento de gravação

Use a tabela abaixo para selecionar a cláusula adequada ao seu caso de uso:

Objetivo	Sintaxe
Inserir ou sobrescrever (padrão)	`UPSERT INTO ... VALUES ...`
Apenas inserir; ignorar se a linha existir	`... ON DUPLICATE KEY IGNORE`
Atualizar colunas específicas se a linha existir; inserir caso contrário	`... ON DUPLICATE KEY UPDATE col = val`
Apenas inserir; gerar erro se a linha existir	`... ON DUPLICATE KEY ERROR` (LindormTable 2.7.8+)

Nota

As cláusulas ON DUPLICATE KEY têm suporte apenas no LindormTable e exclusivamente em tabelas com o parâmetro CONSISTENCY definido como strong.

Diferenças entre LindormTable e LindormTSDB

Ao gravar duas linhas com a mesma chave primária:

LindormTable: a segunda gravação sobrescreve a linha da primeira sem gerar erros. O LindormTable armazena ambas as gravações como versões distintas. Uma instrução SELECT padrão retorna a versão mais recente de cada coluna. Use a dica _l_versions_ para recuperar todas as versões.
LindormTSDB: a segunda gravação substitui a primeira. Não há retenção de versionamento.

Sintaxe

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)* ')'

Parâmetros

Expressão de dica

Exclusivo do LindormTable. Use a dica _l_ts_ para definir um timestamp explícito na linha a ser gravada:

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

Para ver todas as opções de dicas disponíveis, consulte Parâmetros de hintOptions.

ON DUPLICATE KEY

Exclusivo do LindormTable. Verifica se a linha especificada existe antes da gravação, de forma semelhante à operação checkAndPut do HBase.

Importante

No Lindorm SQL 2.8.8.2 e posteriores, use NOW() em uma cláusula VALUES para inserir o timestamp atual automaticamente. Exemplo: UPSERT INTO tb (id, ts) VALUES (1, NOW());. Para verificar sua versão do Lindorm SQL, consulte Versões do SQL.

ON DUPLICATE KEY IGNORE

Se a linha já existir, o sistema ignora a gravação sem erro. Caso contrário, insere os dados normalmente.

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

Se a linha existir, a coluna especificada recebe o novo valor. O comportamento quando a linha não existe varia conforme a versão do LindormTable:

Versão	Linha inexistente
Anterior à 2.7.8	Nenhuma atualização; nenhum erro
2.7.8 e posteriores	Um erro é reportado e os dados da cláusula `VALUES` são inseridos

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

Disponível apenas no LindormTable 2.7.8 e posteriores. Gera um erro se a linha já existir. Caso contrário, insere os dados.

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

Exemplos

Todos os exemplos usam a seguinte tabela de amostra:

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

Gravar uma única linha

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

Verifique: SELECT * FROM sensor;

Gravar em colunas específicas

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

Verifique: SELECT * FROM sensor;

Gravar várias linhas em uma única instrução

Separe cada linha por vírgula dentro da cláusula 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);

Verifique: SELECT * FROM sensor;

Gravar linhas com a mesma chave primária (versionamento do LindormTable)

O LindormTable armazena cada gravação como uma nova versão em vez de sobrescrever os dados no local. Os passos a seguir demonstram esse funcionamento.

Nota

No LindormTSDB, a segunda gravação substitui a primeira e não há retenção de versionamento.

Grave a primeira linha.

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

Consulte os dados.

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

Saída:

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

Grave a linha uma segunda vez com valores diferentes, mas mantendo a mesma chave primária.

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

Consulte novamente. Por padrão, o sistema retorna a versão mais recente.

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

Saída:

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

Use a dica _l_versions_ para recuperar todas as versões armazenadas.

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

Saída:

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

Ambas as gravações são preservadas como versões separadas.

Próximos passos

ALTER TABLE: altere o parâmetro CONSISTENCY em uma tabela existente para ativar as cláusulas ON DUPLICATE KEY.
Atributos de tabela (table_options): defina CONSISTENCY=strong durante a criação da tabela.
Parâmetros de hintOptions: referência completa para _l_ts_ e outras dicas.
Versões do SQL: verifique a versão do seu Lindorm SQL.