sql insert data - Lindorm - Centre de documentation Alibaba Cloud

L'instruction UPSERT insère une ligne si la clé primaire n'existe pas, ou la met à jour si elle existe déjà. Spécifiez les colonnes de la clé primaire dans chaque instruction UPSERT. LindormTable et LindormTSDB prennent en charge l'UPSERT dans toutes les versions.

Contrairement aux bases de données relationnelles, l'UPSERT dans Lindorm ne génère jamais d'erreur en cas de clé primaire dupliquée : la ligne est créée ou mise à jour de manière transparente.

Choisissez le comportement d'écriture approprié

Utilisez le tableau ci-dessous pour sélectionner la clause adaptée à votre cas d'utilisation :

Objectif	Syntaxe
Insérer ou écraser (par défaut)	`UPSERT INTO ... VALUES ...`
Insérer uniquement ; ignorer si la ligne existe	`... ON DUPLICATE KEY IGNORE`
Mettre à jour des colonnes spécifiques si la ligne existe ; insérer sinon	`... ON DUPLICATE KEY UPDATE col = val`
Insérer uniquement ; générer une erreur si la ligne existe	`... ON DUPLICATE KEY ERROR` (LindormTable 2.7.8+)

Remarque

Les clauses ON DUPLICATE KEY sont prises en charge uniquement par LindormTable, et seulement sur les tables dont le paramètre CONSISTENCY est défini sur strong.

Différences entre LindormTable et LindormTSDB

Lorsque vous écrivez deux lignes possédant la même clé primaire :

LindormTable — la seconde écriture écrase la première sans générer d'erreur. LindormTable stocke les deux écritures sous forme de versions distinctes. Une instruction SELECT standard renvoie la dernière version de chaque colonne. Utilisez l'indicateur _l_versions_ pour récupérer toutes les versions.
LindormTSDB — la seconde écriture écrase la première. Aucune gestion des versions n'est conservée.

Syntaxe

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

Paramètres

Expression d'indicateur

Uniquement pour LindormTable. Utilisez l'indicateur _l_ts_ pour définir un horodatage explicite pour la ligne en cours d'écriture :

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

Pour consulter toutes les options d'indicateurs disponibles, reportez-vous à la section Paramètres de hintOptions.

ON DUPLICATE KEY

Uniquement pour LindormTable. Vérifie si la ligne spécifiée existe avant d'écrire, de manière similaire à l'opération checkAndPut dans HBase.

Important

Dans Lindorm SQL 2.8.8.2 et versions ultérieures, utilisez NOW() dans une clause VALUES pour insérer automatiquement l'horodatage actuel. Exemple : UPSERT INTO tb (id, ts) VALUES (1, NOW());. Pour vérifier votre version de Lindorm SQL, consultez la page Versions SQL.

ON DUPLICATE KEY IGNORE

Si la ligne existe, l'écriture est ignorée sans générer d'erreur. Si la ligne n'existe pas, les données sont insérées.

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

Si la ligne existe, la colonne spécifiée est mise à jour avec la valeur indiquée. Le comportement lorsque la ligne n'existe pas dépend de la version de LindormTable :

Version	La ligne n'existe pas
Antérieure à 2.7.8	Aucune mise à jour ; aucune erreur
2.7.8 et ultérieure	Une erreur est signalée et les données de la clause `VALUES` sont insérées

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

Uniquement pour LindormTable 2.7.8 et versions ultérieures. Si la ligne existe, une erreur est signalée. Si la ligne n'existe pas, les données sont insérées.

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

Exemples

Tous les exemples utilisent la table d'exemple suivante :

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

Écriture d'une seule ligne

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

Vérification : SELECT * FROM sensor;

Écriture dans des colonnes spécifiques

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

Vérification : SELECT * FROM sensor;

Écriture de plusieurs lignes en une seule instruction

Séparez chaque ligne par une virgule dans la clause 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);

Vérification : SELECT * FROM sensor;

Écriture de lignes ayant la même clé primaire (gestion des versions LindormTable)

LindormTable stocke chaque écriture comme une nouvelle version au lieu d'écraser les données sur place. Les étapes suivantes illustrent ce fonctionnement.

Remarque

Dans LindormTSDB, la seconde écriture écrase la première et aucune gestion des versions n'est conservée.

Écrivez la première ligne.

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

Interrogez les données.

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

Résultat :

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

Écrivez à nouveau la ligne avec des valeurs différentes, mais en conservant la même clé primaire.

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

Effectuez à nouveau la requête. La dernière version est renvoyée par défaut.

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

Résultat :

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

Utilisez l'indicateur _l_versions_ pour récupérer toutes les versions stockées.

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

Résultat :

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

Les deux écritures sont conservées sous forme de versions distinctes.

Étapes suivantes

ALTER TABLE — modifiez le paramètre CONSISTENCY sur une table existante pour activer les clauses ON DUPLICATE KEY
Attributs de table (table_options) — définissez CONSISTENCY=strong lors de la création de la table
Paramètres de hintOptions — référence complète pour _l_ts_ et autres indicateurs
Versions SQL — vérifiez votre version de Lindorm SQL