sql insert data - Lindorm - Centro de documentación de Alibaba Cloud

UPSERT inserta una fila si la clave primaria no existe o la actualiza si ya existe. Debe especificar las columnas de clave primaria en cada instrucción UPSERT. Tanto LindormTable como LindormTSDB son compatibles con UPSERT en todas sus versiones.

A diferencia de las bases de datos relacionales, UPSERT en Lindorm nunca falla por una clave primaria duplicada. La fila se crea o se actualiza de forma silenciosa.

Elija el comportamiento de escritura

Utilice la siguiente tabla para elegir la cláusula adecuada según su caso de uso:

Objetivo	Sintaxis
Insertar o sobrescribir (comportamiento predeterminado)	`UPSERT INTO ... VALUES ...`
Solo insertar; omitir si la fila ya existe	`... ON DUPLICATE KEY IGNORE`
Actualizar columnas específicas si la fila existe; insertar si no existe	`... ON DUPLICATE KEY UPDATE col = val`
Solo insertar; generar un error si la fila existe	`... ON DUPLICATE KEY ERROR` (LindormTable 2.7.8+)

Nota

Las cláusulas ON DUPLICATE KEY solo son compatibles con LindormTable. Además, únicamente funcionan en tablas donde el parámetro CONSISTENCY está establecido en strong.

Diferencias entre LindormTable y LindormTSDB

Cuando escribe dos filas con la misma clave primaria, ocurre lo siguiente:

LindormTable: la segunda escritura sobrescribe la fila de la primera sin generar errores. LindormTable almacena ambas escrituras como versiones distintas. Una instrucción SELECT estándar devuelve la versión más reciente de cada columna. Utilice el hint _l_versions_ para recuperar todas las versiones.
LindormTSDB: la segunda escritura sobrescribe a la primera. No se conserva ningún historial de versiones.

Sintaxis

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

Expresión de hint

Exclusivo de LindormTable. Utilice el hint _l_ts_ para establecer una marca de tiempo explícita en la fila que se va a escribir:

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

Para conocer todas las opciones de hint disponibles, consulte Parameters of hintOptions.

ON DUPLICATE KEY

Exclusivo de LindormTable. Comprueba si la fila especificada existe antes de escribir. Este comportamiento es similar a la operación checkAndPut de HBase.

Importante

En Lindorm SQL 2.8.8.2 y versiones posteriores, utilice NOW() dentro de una cláusula VALUES para insertar automáticamente la marca de tiempo actual. Por ejemplo: UPSERT INTO tb (id, ts) VALUES (1, NOW());. Para comprobar su versión de Lindorm SQL, consulte SQL versions.

ON DUPLICATE KEY IGNORE

Si la fila existe, la escritura se omite sin generar errores. Si la fila no existe, los datos se insertan.

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 fila existe, la columna especificada se actualiza con el valor indicado. El comportamiento cuando la fila no existe depende de la versión de LindormTable:

Versión	La fila no existe
Anterior a la 2.7.8	No se actualiza ni se genera ningún error
2.7.8 y posteriores	Se reporta un error y se insertan los datos de la cláusula `VALUES`

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

Exclusivo de LindormTable 2.7.8 y versiones posteriores. Si la fila existe, se reporta un error. Si la fila no existe, los datos se insertan.

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

Ejemplos

Todos los ejemplos utilizan la siguiente tabla de muestra:

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

Escribir una sola fila

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

Compruebe el resultado: SELECT * FROM sensor;

Escribir en columnas específicas

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

Compruebe el resultado: SELECT * FROM sensor;

Escribir varias filas en una sola instrucción

Separe cada fila con una coma dentro de la 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);

Compruebe el resultado: SELECT * FROM sensor;

Escribir filas con la misma clave primaria (control de versiones de LindormTable)

LindormTable almacena cada escritura como una versión nueva en lugar de sobrescribirla en el mismo lugar. A continuación se muestra cómo funciona este proceso.

Nota

En LindormTSDB, la segunda escritura sobrescribe a la primera y no se conserva ningún historial de versiones.

Escriba la primera fila.

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

Consulte los datos.

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

Resultado:

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

Vuelva a escribir la fila con valores distintos, pero mantenga la misma clave primaria.

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

Consulte los datos de nuevo. De forma predeterminada, se devuelve la versión más reciente.

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

Resultado:

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

Utilice el hint _l_versions_ para recuperar todas las versiones almacenadas.

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

Resultado:

+-----------+----------+-------------------------------+-------------+----------+
| 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 escrituras se conservan como versiones independientes.

Pasos siguientes

ALTER TABLE: cambie el parámetro CONSISTENCY de una tabla existente para habilitar las cláusulas ON DUPLICATE KEY.
Table attributes (table_options): establezca CONSISTENCY=strong al crear la tabla.
Parameters of hintOptions: referencia completa sobre _l_ts_ y otros hints.
Versiones de SQL: compruebe su versión de Lindorm SQL.