MySQL => INSERTAR

Sintaxis

INSERTAR [LOW_PRIORITY | RETRASADO | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name, ...)] [(col_name, ...)] {VALUES | VALOR} ({expr | DEFAULT}, ...), (...), ... [ACTUALIZACIÓN DE LA LLAVE DUPLICADA col_name = expr [, col_name = expr] ...]
INSERTAR [LOW_PRIORITY | RETRASADO | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name, ...)] SET col_name = {expr | DEFAULT}, ... [ON DUPLICATE KEY UPDATE col_name = expr [, col_name = expr] ...]
INSERTAR [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name, ...)] [(col_name, ...)] SELECT ... [ON DUPLICATE KEY UPDATE col_name = expr [, col_name = expr]]]
Una expresión expr puede hacer referencia a cualquier columna que se haya establecido anteriormente en una lista de valores. Por ejemplo, puede hacer esto porque el valor para col2 se refiere a col1, que se ha asignado previamente:
Insertar en tbl_name (col1, col2) VALORES (15, col1 * 2);
Las instrucciones INSERT que utilizan la sintaxis de VALUES pueden insertar varias filas. Para hacer esto, incluya múltiples listas de valores de columna, cada uno entre paréntesis y separados por comas. Ejemplo:
INSERTAR EN tbl_name (a, b, c) VALORES (1,2,3), (4,5,6), (7,8,9);
La lista de valores para cada fila debe estar entre paréntesis. La siguiente declaración es ilegal porque el número de valores en la lista no coincide con el número de nombres de columna:
Insertar en tbl_name (a, b, c) VALORES (1,2,3,4,5,6,7,8,9);
INSERTAR ... SELECCIONAR Sintaxis
INSERTAR [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name, ...)] [(col_name, ...)] SELECT ... [ON DUPLICATE KEY UPDATE col_name = expr, ...]
Con INSERT ... SELECT, puede insertar rápidamente muchas filas en una tabla de una o varias tablas. Por ejemplo:
INSERTAR EN tbl_temp2 (fld_id) SELECCIONE tbl_temp1.fld_order_id DESDE tbl_temp1 DONDE tbl_temp1.fld_order_id> 100;

Observaciones

Inserto Básico

INSERT INTO `table_name` (`field_one`, `field_two`) VALUES ('value_one', 'value_two');

En este ejemplo trivial, table_name es donde se agregarán los datos, field_one y field_two son campos para configurar los datos, y value_one y value_two son los datos que se deben hacer contra field_one y field_two respectivamente.

Es una buena práctica enumerar los campos en los que está insertando datos dentro de su código, ya que si la tabla cambia y se agregan nuevas columnas, su inserción se rompería si no estuvieran allí.

INSERTAR, ACTUALIZACIÓN CLAVE DUPLICADA

INSERT INTO `table_name` 
    (`index_field`, `other_field_1`, `other_field_2`) 
    VALUES 
    ('index_value', 'insert_value', 'other_value') 
    ON DUPLICATE KEY UPDATE 
        `other_field_1` = 'update_value',
        `other_field_2` = VALUES(`other_field_2`);

Esto INSERT en table_name los valores especificados, pero si la clave única ya existe, actualizará el other_field_1 para que tenga un nuevo valor.
A veces, al actualizar en clave duplicada resulta útil utilizar VALUES() para acceder al valor original que se pasó a INSERT lugar de establecer el valor directamente. De esta manera, puede establecer diferentes valores utilizando INSERT y UPDATE . Vea el ejemplo anterior donde other_field_1 se establece en insert_value en INSERT o en update_value en UPDATE mientras que other_field_2 siempre se establece en other_value .

Para que funcione Insertar en la actualización de la clave duplicada (IODKU), es crucial que el esquema contenga una clave única que indique un conflicto duplicado. Esta clave única puede ser una clave principal o no. Puede ser una clave única en una sola columna o una columna múltiple (clave compuesta).

Insertando múltiples filas

INSERT INTO `my_table` (`field_1`, `field_2`) VALUES 
    ('data_1', 'data_2'),
    ('data_1', 'data_3'),
    ('data_4', 'data_5');

Esta es una manera fácil de agregar varias filas a la vez con una INSERT .

Este tipo de inserción 'por lotes' es mucho más rápida que insertar filas una por una. Por lo general, insertar 100 filas en una sola inserción de lotes de esta manera es 10 veces más rápido que insertarlas todas individualmente.

Ignorando las filas existentes

Al importar conjuntos de datos grandes, puede ser preferible, bajo ciertas circunstancias, omitir filas que generalmente causan que la consulta falle debido a una restricción de columna, por ejemplo, claves primarias duplicadas. Esto se puede hacer utilizando INSERT IGNORE .

Considere la siguiente base de datos de ejemplo:

SELECT * FROM `people`;
--- Produces:
+----+------+
| id | name |
+----+------+
|  1 | john |
|  2 | anna |
+----+------+


INSERT IGNORE INTO `people` (`id`, `name`) VALUES
    ('2', 'anna'), --- Without the IGNORE keyword, this record would produce an error
    ('3', 'mike');

SELECT * FROM `people`;
--- Produces:
+----+--------+
| id |  name  |
+----+--------+
|  1 |  john  |
|  2 |  anna  |
|  3 |  mike  |
+----+--------+

Lo importante a recordar es que INSERT IGNORE también omitirá silenciosamente otros errores, aquí está lo que dice la documentación oficial de Mysql:

Las conversiones de datos que podrían generar errores abortan la declaración si IGNORE no está> especificado. Con IGNORE, los valores no válidos se ajustan a los valores más cercanos y se insertan; Se producen advertencias pero la declaración no se cancela.

Nota: - La sección a continuación se agrega para completar, pero no se considera la mejor práctica (esto podría fallar, por ejemplo, si se agrega otra columna a la tabla).

Si especifica el valor de la columna correspondiente para todas las columnas de la tabla, puede ignorar la lista de columnas en la INSERT siguiente manera:

INSERT INTO `my_table` VALUES 
        ('data_1', 'data_2'),
        ('data_1', 'data_3'),
        ('data_4', 'data_5');

INSERT SELECT (Insertando datos de otra tabla)

Esta es la forma básica de insertar datos de otra tabla con la instrucción SELECT.

INSERT INTO `tableA` (`field_one`, `field_two`) 
   SELECT `tableB`.`field_one`, `tableB`.`field_two` 
   FROM `tableB` 
   WHERE `tableB`.clmn <> 'someValue'
   ORDER BY `tableB`.`sorting_clmn`;

Puede SELECT * FROM , pero la tableA y la tableB deben tener un recuento de columnas coincidente y los tipos de datos correspondientes.

Las columnas con AUTO_INCREMENT se tratan como en la cláusula INSERT with VALUES .

Esta sintaxis facilita el llenado de tablas (temporales) con datos de otras tablas, incluso más cuando los datos se filtran en la inserción.

INSERTAR con AUTO_INCREMENT + LAST_INSERT_ID ()

Cuando una tabla tiene una AUTO_INCREMENT PRIMARY KEY , normalmente uno no insertar en esa columna. En su lugar, especifique todas las demás columnas, luego pregunte cuál era la nueva identificación.

CREATE TABLE t (
    id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
    this ...,
    that ...,
    PRIMARY KEY(id) );

INSERT INTO t (this, that) VALUES (..., ...);
SELECT LAST_INSERT_ID() INTO @id;
INSERT INTO another_table (..., t_id, ...) VALUES (..., @id, ...);

Tenga en cuenta que LAST_INSERT_ID() está vinculado a la sesión, por lo que incluso si se insertan varias conexiones en la misma tabla, cada una con su propia ID.

Es probable que la API de su cliente tenga una forma alternativa de obtener LAST_INSERT_ID() sin realizar realmente una SELECT y devolverle el valor al cliente en lugar de dejarla en una @variable dentro de MySQL. Tal es generalmente preferible.

Más largo, más detallado, ejemplo.

El uso "normal" de IODKU es activar una "clave duplicada" basada en alguna tecla UNIQUE , no en la AUTO_INCREMENT PRIMARY KEY . Lo siguiente demuestra tal. Tenga en cuenta que no proporciona el id en el INSERT.

Configuración de ejemplos a seguir:

CREATE TABLE iodku (
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(99) NOT NULL,
    misc INT NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(name)
) ENGINE=InnoDB;

INSERT INTO iodku (name, misc)
    VALUES
    ('Leslie', 123),
    ('Sally', 456);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
+----+--------+------+
| id | name   | misc |
+----+--------+------+
|  1 | Leslie |  123 |
|  2 | Sally  |  456 |
+----+--------+------+

El caso de IODKU realizando una "actualización" y LAST_INSERT_ID() recuperando la id relevante:

INSERT INTO iodku (name, misc)
    VALUES
    ('Sally', 3333)            -- should update
    ON DUPLICATE KEY UPDATE    -- `name` will trigger "duplicate key"
    id = LAST_INSERT_ID(id),
    misc = VALUES(misc);
SELECT LAST_INSERT_ID();       -- picking up existing value
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

El caso en el que IODKU realiza una "inserción" y LAST_INSERT_ID() recupera la nueva id :

INSERT INTO iodku (name, misc)
    VALUES
    ('Dana', 789)          -- Should insert
    ON DUPLICATE KEY UPDATE
    id = LAST_INSERT_ID(id),
    misc = VALUES(misc);
SELECT LAST_INSERT_ID();   -- picking up new value
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+

Contenido de la tabla resultante:

SELECT * FROM iodku;
+----+--------+------+
| id | name   | misc |
+----+--------+------+
|  1 | Leslie |  123 |
|  2 | Sally  | 3333 |  -- IODKU changed this
|  3 | Dana   |  789 |  -- IODKU added this
+----+--------+------+

IDs AUTO_INCREMENT perdidos

Varias funciones 'insertar' pueden "quemar" los identificadores. Aquí hay un ejemplo, usando InnoDB (otros motores pueden funcionar de manera diferente):

CREATE TABLE Burn (
    id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
    name VARCHAR(99) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(name)
        ) ENGINE=InnoDB;

INSERT IGNORE INTO Burn (name) VALUES ('first'), ('second');
SELECT LAST_INSERT_ID();          -- 1
SELECT * FROM Burn ORDER BY id;
  +----+--------+
  |  1 | first  |
  |  2 | second |
  +----+--------+

INSERT IGNORE INTO Burn (name) VALUES ('second');  -- dup 'IGNOREd', but id=3 is burned
SELECT LAST_INSERT_ID();          -- Still "1" -- can't trust in this situation
SELECT * FROM Burn ORDER BY id;
  +----+--------+
  |  1 | first  |
  |  2 | second |
  +----+--------+

INSERT IGNORE INTO Burn (name) VALUES ('third');
SELECT LAST_INSERT_ID();           -- now "4"
SELECT * FROM Burn ORDER BY id;    -- note that id=3 was skipped over
  +----+--------+
  |  1 | first  |
  |  2 | second |
  |  4 | third  |    -- notice that id=3 has been 'burned'
  +----+--------+

Piénselo (aproximadamente) de esta manera: primero, la inserción se ve para ver cuántas filas se pueden insertar. Luego, toma tantos valores del auto_increment para esa tabla. Finalmente, inserte las filas, utilizando los identificadores que sean necesarios y queme las sobras.

La única vez que los restos son recuperables es si el sistema se apaga y se reinicia. Al reiniciar, efectivamente se realiza MAX(id) . Esto puede reutilizar los identificadores que fueron quemados o que fueron liberados por DELETEs de los identificadores más altos.

Esencialmente, cualquier versión de INSERT (incluido REPLACE , que es DELETE + INSERT ) puede grabar identificadores. En InnoDB, la variable global (¡no la sesión!) innodb_autoinc_lock_mode se puede usar para controlar algo de lo que está sucediendo.

Cuando se "normalizan" cadenas largas en un AUTO INCREMENT id , la grabación puede ocurrir fácilmente. Esto podría llevar a desbordar el tamaño de la INT que eligió.

Modified text is an extract of the original Stack Overflow Documentation

Licenciado bajo CC BY-SA 3.0

No afiliado a Stack Overflow

MySQL
INSERTAR

Buscar..