13.2. Sentencias de manipulación de datos (Data Manipulation Statements)

MySQL 5.0

previous page next page

13.2. Sentencias de manipulación de datos (Data Manipulation Statements)

13.2.1. Sintaxis de `DELETE`

Sintaxis para una tabla:

         DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
         [WHERE where_definition]
         [ORDER BY ...]
         [LIMIT row_count]

Sintaxis para múltiples tablas:

       DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       tbl_name[.*] [, tbl_name[.*] ...]
       FROM table_references
       [WHERE where_definition]

       DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       FROM tbl_name[.*] [, tbl_name[.*] ...]
       USING table_references
       [WHERE where_definition]

DELETE borra los registros de tbl_name que satisfacen la condición dada por where_definition, y retorna el número de registros borrados.

Si realiza un comando DELETE sin cláusula WHERE se borran todos los registros. Una forma más rápida de hacerlo, cuando no quiere saber el número de registros borrados, se usa TRUNCATE TABLE. Consulte Sección 13.2.9, “Sintaxis de TRUNCATE”.

Si borra el registro conteniendo el máximo valor para una columna AUTO_INCREMENT , el valor se reúsa para una tabla BDB , pero no para tablas MyISAM o InnoDB . Si borra todos los registros en la tabla con DELETE FROMtbl_name (sin cláusula WHERE ) en modo AUTOCOMMIT , la secuencia comienza para todos los tipos de tabla excepto para InnoDB y MyISAM. Hay algunas excepciones para este comportamiento para tablas InnoDB , como se discute en Sección 15.6.3, “Cómo funciona una columna AUTO_INCREMENT en InnoDB”.

Para tablas MyISAM y BDB , puede especificar una columna AUTO_INCREMENT secundaria en una clave de múltiples columnas. En este caso, el reúso de valores borrados del inicio de la secuencia se realiza incluso para tablas MyISAM . Consulte Sección 3.6.9, “Utilización de AUTO_INCREMENT”.

El comando DELETE soporta los siguientes modificadores:

Si especifica LOW_PRIORITY, la ejecución de DELETE se retarda hasta que no hay más clientes leyendo de la tabla.

Para tablas MyISAM , si usa la palabra QUICK , el motor de almacenamiento no mezcla las hojas del índice durante el borrado, que puede acelerar algunos tipos de operaciones de borrado.

En MySQL 5.0, la palabra clave IGNORE hace que MySQL ignore todos los errores durante el proceso de borrar registros. (Los errores encontrados durante la etapa de parseo se procesan de la forma habitual.) Los errores que se ignoran debido al uso de esta opción se retornan como advertencias.

La velocidad de las operaciones de borrado pueden verse afectadas por factores discutidos en Sección 7.2.16, “Velocidad de sentencias DELETE”.

En tablas MyISAM , los registros borrados se mantienen en una lista enlazada y las operaciones INSERT siguientes reúsan antiguas posiciones de registro. Para reclamar espacio no usado y reducir tamaño de fichero, use el comando OPTIMIZE TABLE o la utilidad myisamchk para reorganizar las tablas. OPTIMIZE TABLE es más sencillo, pero myisamchk es más rápido. Consulte Sección 13.5.2.5, “Sintaxis de OPTIMIZE TABLE” y Sección 5.8.3.10, “Optimización de tablas”.

El modificador QUICK afecta si las hojas del índice es mezclan en operaciones de borrado. DELETE QUICK es más útil para aplicaciones en que los valores del índice para registros borrados se replazan con valores similares de registros insertados posteriormente. En este caso, los agujeros dejados por los valores borrados se reúsan.

DELETE QUICK no es útil cuando los valores borrados conducen a bloques de índices no rellenos con un rango de valores índice para el que vuelven a ocurrir nuevas inserciones. En este caso, el uso de QUICK puede conducir a un gasto de espacio que queda sin reclamar. Aquí hay un ejemplo de este escenario:

Cree una tabla que contenga una columna AUTO_INCREMENT indexada.

Inserta varios registros en la tabla. Cada inserción resulta en un valor índice que se añade al final del índice.

Borra un bloque de registros al final del rango de la columna usando DELETE QUICK.

En este escenario, los bloques de índice asociados con los valores de índice borrado quedan sin rellenar pero no se mezclan con otros bloques de índice debido al uso de QUICK. Quedan sin rellenar cuando hay nuevas inserciones, ya que los nuevos registros no tienen valores índice en el rango borrado. Además, quedan sin rellenar incluso si luego usa DELETE sin QUICK, a no ser que algunos de los valores de índice borrados estén en los bloques de índice dentro o adyacentes a los bloques no rellenos. Para reclamar el espacio de índice sin usar bajo estas circunstancias use OPTIMIZE TABLE.

Si va a borrar varios registros de una tabla, puede ser más sencillo usar DELETE QUICK seguido por OPTIMIZE TABLE. Esto reconstruye el índice en lugar de realizar varias operaciones de mezcla de bloques de índice.

La opción de MySQL LIMITrow_count para DELETE le dice al servidor el máximo número de registros a borrar antes de retornar el control al cliente. Esto puede usarse para asegurar que un comando DELETE específico no tarada demasiado tiempo. Puede simplemente repetir el comando DELETE hasta que el número de registros afectados sea menor que el valor LIMIT .

Si el comando DELETE incluye una cláusula ORDER BY , los registros se borran en el orden especificado por la cláusula. Esto es muy útil sólo en conjunción con LIMIT. Por ejemplo, el siguiente ejemplo encuentra registros coincidentes con la cláusula WHERE ordenados por timestamp_column, y borra el primero (el más viejo).

DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp_column LIMIT 1;

Puede especificar múltiples tablas en un comando DELETE para borrar registros de una o más tablas dependiendo de una condición particular en múltiples tablas. Sin embargo, no puede usar ORDER BY o LIMIT en un DELETE de múltiples tablas.

La parte table_references lista las tablas involucradas en el join. Esta sintaxis se describe en Sección 13.2.7.1, “Sintaxis de JOIN”.

Para la primera sintaxis, sólo los registros coincidentes de las tablas listadas antes de la cláusula FROM se borran. Para la segunda sintaxis, sólo los registros coincidentes de las tablas listadas en la cláusula FROM (antes de la cláusula USING ) se borran. El efecto es que puede borrar registros para varias tablas al mismo tiempo y tienen tablas adicionales que se usan para buscar:

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

O:

DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

Estos comandos usan las tres tablas al buscar registros a borrar, pero borrar los registros coincidentes sólo para las tablas t1 y t2.

Los ejemplos anteriores muestran inner joins usando el operador coma, pero comandos DELETE de varias tablas pueden usar cualquier tipo de join permitido por comandos SELECT tales como LEFT JOIN.

La sintaxis permite .* tras los nombres de tabla para compatibilidad con Access.

Si usa un comando DELETE de varias tablas incluyendo tablas InnoDB para las que hay restricciones de clave foránea, el optimizador MySQL puede procesar tablas en un orden ditinto del de su relación padre/hijo. En este caso, el comando falla y se deshace. En su lugar, debe borrar de una tabla úncia y confiar en la capacidad de ON DELETE que proporciona InnoDB para hacer que las otras tablas se modifiquen correctamente.

Nota: En MySQL 5.0, debe usar el alias (si se dió) al referirse a un nombre de tabla:

En MySQL 4.1:

DELETE t1 FROM test AS t1, test2 WHERE ...

Borrados cruzados entre bases de datos se soportan para borrados de varias tablas, pero en este caso, debe referirse a las tablas sin usar alias. Por ejemplo:

DELETE test1.tmp1, test2.tmp2 FROM test1.tmp1, test2.tmp2 WHERE ...

Actualmente, no puede borrar desde una tabla y seleccionar de la misma tabla en una subconsulta.

13.2.2. Sintaxis de DO

DO expr [, expr] ...

DO ejecuta la expresión pero no retorna ningún resultado. Esto es una abreviación de SELECTexpr, ..., pero tiene la ventaja que es más rápido cuando no le importa el resultado.

DO es útil principalmente con funciones que tienen efectos colaterales, tales como RELEASE_LOCK().

13.2.3. Sintaxis de HANDLER

HANDLER tbl_name OPEN [ AS alias ] HANDLER tbl_name READ index_name { = | >= | <= | < } (value1,value2,...) [ WHERE where_condition ] [LIMIT ... ] HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST } [ WHERE where_condition ] [LIMIT ... ] HANDLER tbl_name READ { FIRST | NEXT } [ WHERE where_condition ] [LIMIT ... ] HANDLER tbl_name CLOSE

El comando HANDLER proporciona acceso directo a las interfaces del motor de la tabla. En MySQL 5.0, está disponible para tablas MyISAM y InnoDB .

El comando HANDLER ... OPEN abre una tabla, haciéndola accesible mediante posteriores comandos HANDLER ... READ . Este objeto de tabla no se comparte con otros flujos y no se cierra hasta que el flujo llama HANDLER ... CLOSE o el flujo termina. Si abre la tabla usando un alias, referencias posteriores a la tabla con otros comandos HANDLER deben usar el alias en lugar del nombre de la tabla.

La primera sintaxis HANDLER ... READ recibe un registro donde el índice especificado satisface los valores dados y la condición WHERE se cumple. Si tiene un índice de múltiples columnas, especifique los valores de la columna índice como una lista separada por comas. Los valores epecificados para todas las columnas en el índice, o los valores específicos para un prefijo a la izquierda de las columnas índice. Suponga que un índice incluye tres columnas llamadas col_a, col_b, y col_c, en ese orden. El comando HANDLER puede especificar valores para las tres columnas en el índice, o para las columnas en el prefijo a la izquierda. Por ejemplo:

HANDLER ... index_name = (col_a_val,col_b_val,col_c_val) ... HANDLER ... index_name = (col_a_val,col_b_val) ... HANDLER ... index_name = (col_a_val) ...

La segunda sintaxis HANDLER ... READ recibe un registro de la tabla en orden del índice que cumple la condición WHERE .

La tercera sintaxis HANDLER ... READ recibe un registro de la tabla en orden de registro natural que cumple la condición WHERE . Es más rápido que HANDLER tbl_name READ index_name cuando se desea un escaneo completo de tabla. El orden de registro natural es el orden en que se almacenan los registros en un fichero de datos de una tabla MyISAM. Este comando funciona para tablas InnoDB también, pero no hay tal concepto porque no hay un fichero de datos separado.

Sin una cláusula LIMIT , todas las formas de HANDLER ... READ reciben un único registros si una está disponible. Para retornar un número específico de registros, incluya una cláusula LIMIT . Tiene la misma sintaxis que para el comando SELECT . Consulte Sección 13.2.7, “Sintaxis de SELECT”.

HANDLER ... CLOSE cierra una tabla que se abrió con HANDLER ... OPEN.

Nota: Para emplear la interfaz HANDLER para referirse a una tabla PRIMARY KEY, use el identificador `PRIMARY` entrecomillado:

HANDLER tbl_name READ `PRIMARY` > (...);

HANDLER es un comando de bajo nivel. Por ejemplo, no proporciona consistencia. Esto es, HANDLER ... OPEN no toma una muestra de la tabla, y no bloquea la tabla. Esto significa que tras un comando HANDLER ... OPEN realizado, los datos de la tabla pueden ser modificados (por este o por otro flujo) y estas modificaciones pueden aparecer sólo parcialmente en escaneos HANDLER ... NEXT o HANDLER ... PREV .

Hay varias razones para usar la interfaz HANDLER en lugar de comandos SELECT normales:

HANDLER es más rápido que SELECT:

Un objeto de tratamiento de motor de almacenamiento designado se reserva para HANDLER ... OPEN. El objeto se reúsa para posteriores comandos HANDLER para esa tabla; no necesita reinicializarse para cada una.

Hay menos parseo.

No hay sobrecarga del chequeo de consultas ni optimizador.

La tabla no tiene que estar bloqueada entre peticiones.

La interície del handler no tiene que propocionar una vista de los datos consistente (por ejemplo, se permiten dirty reads), así que el motor puede usar optimización que SELECT no permite.

HANDLER hace mucho más fácil portar aplicaciones que usen una interfaz tipo ISAM a MySQL.

HANDLER le permite consultar una base de datos de forma difícil o imposible de realizar con SELECT. La interfície de HANDLER es una forma más natural de consultar los datos cuando se trabaja con aplicaciones que proporcionan una interfaz de usuario interactiva a la base de datos.

13.2.4. Sintaxis de INSERT

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

O:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name SET col_name={expr | DEFAULT}, ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

O:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

INSERT inserta nuevos registros en una tabla existente. Las formas INSERT ... VALUES y INSERT ... SET del comando insertan registros basados en valores explícitamente especificados. La forma INSERT ... SELECT inserta registros seleccionados de otra tabla o tablas. INSERT ... SELECT se discute en Sección 13.2.4.1, “Sintaxis de INSERT ... SELECT”.

tbl_name es la tabla en que los registros deben insertarse. Las columnas para las que el comando proporciona valores pueden especificarse como sigue:

La lista de nombres de columna o la cláusula SET indican las columnas explícitamente.

Si no especifica la lista de columnas para INSERT ... VALUES o INSERT ... SELECT, los valores para cada columna en la tabla deben proporcionarse en la lista VALUES o por el SELECT. Si no sabe el orden de las columnas en la tabla, use DESCRIBEtbl_name para encontrarlo.

Los valores de columna pueden darse de distintos modos:

Si no está ejecutando el modo estricto, cualquier columna que no tenga un valor asignado explícitamente recibe su valor por defecto (explícito o implícito). Por ejemplo, si especifica una lista de columnas que no nombra todas las columnas en la tabla, las no nombradas reciben sus valores por defecto. Los valores por defecto asignados se describen en Sección 13.1.5, “Sintaxis de CREATE TABLE”. Consulte Sección 1.7.6.2, “Restricciones (constraints) sobre datos inválidos”.

Si quiere que un comando INSERT genere un error a no ser que especifique explícitamente valores para todas las columnas que no tienen un valor por defecto, debe usar modo STRICT . Consulte Sección 5.3.2, “El modo SQL del servidor”.

Use DEFAULT para asignar a una columna explícitamente su valor por defecto. Esto hace más fácil escribir comandos INSERT que asignan valores a todas las columnas excepto unas pocoas, ya que le permite evitar la escritura de una lista de valores VALUES incompleta. De otro modo, tendría que escribir la lista de los nombres de columna correspondientes a cada valor en la lista VALUES .

En MySQL 5.0, puede usar DEFAULT(col_name) como forma más general que puede usarse en expresiones para producir un valor por defecto de una columna.

Si la lista de columnas y la lista VALUES están vacías, INSERT crea un registro con cada conjunto de columnas con sus valores por defecto:

mysql> INSERT INTO tbl_name () VALUES();

En modo STRICT obtendrá un error si una columna no tiene un valor por defecto. De otro modo, MySQL usará el valor implícito para cualquier columna sin un valor explícito por defecto definido.

Puede especificar una expresión expr para proporcionar un valor de columna. Esto puede involucar convesión de tipos si el tipo de la expresión no coincide con el tipo de la columna, y la conversión de un valor dado puede resultar en distintos valores insertados dependiendo del tipo de columna. Por ejmplo, insertar la cadena '1999.0e-2' en una columna INT, FLOAT, DECIMAL(10,6), o YEAR resulta en los valores 1999, 19.9921, 19.992100, y 1999 insertados, respectivamente. La razón de que el valor almacenado en las columnas INT y YEAR sea 1999 es que la conversión cadena-a-entero consulta sólo el trozo de la parte inicial de la cadena que se puede considerar como un entero válido o año. Para las columnas de coma flotante o punto fijo, la conversión cadena-a-coma-flotante considera la cadena entera un valor válido.

Una expresión expr puede referirse a cualquier columna que se haya asignado antes en una lista de valores. Por ejemplo, puede hacer esto porque el valor para col2 se refiere a col1, que se ha asignado préviamente:

mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

Pero lo siguiente no es legal, ya que el valor para col1 se refiere a col2, que se asigna tras col1:

mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);

Una excepción involucra a columnas que contienen valores AUTO_INCREMENT . Como el valor AUTO_INCREMENT se genera tras otras asignaciones de valores, cualquier referencia a una columna AUTO_INCREMENT en la asignación retorna un 0.

El comando INSERT soporta los siguientes modificadores:

Si usa la palabra DELAYED, el servidor pone el registro o registros a ser insertados en un búffer, y el cliente realizando el comando INSERT DELAYED puede continuar. Si la tabla está en uso, el servidor trata los registros. Cuando la tabla se libera, el servidor comienza a insertar registros, chequeando periódicamente para ver si hay alguna petición de lectura para la tabla. Si la hay, la cola de registros retardados se suspende hasta que la tabla se libera de nuevo. Consulte Sección 13.2.4.2, “Sintaxis de INSERT DELAYED”.

Si usa la palabra LOW_PRIORITY , la ejecución de INSERT se retrasa hasta que no hay otros clientes leyendo de la tabla. Esto incluye a otros clientes que comiencen a leer mientras que los clientes existentes están leyendo, y meintras el comando INSERT LOW_PRIORITY está en espera. Es posible, por lo tanto, para un cliente que realice un comando INSERT LOW_PRIORITY esperar durante mucho tiempo (o incluso para siempre) en un entorno de muchas lecturas. (Esto es un contraste de INSERT DELAYED, que deja al cliente continuar. Consulte Sección 13.2.4.2, “Sintaxis de INSERT DELAYED”.) Tenga en cuenta que LOW_PRIORITY no debe usarse normalmente con tablas MyISAM y que hacerlo deshabilita inserciones concurrentes. Consulte Sección 14.1, “El motor de almacenamiento MyISAM”.

Si especifica HIGH_PRIORITY, deshabilita el efecto de la opción --low-priority-updates si el servidor se arrancó con esa opción. Hace que las insecionces concurrentes no se usen.

Los valores afectados por un INSERT pueden usarse usando la función mysql_affected_rows() de la API de C. Consulte Sección 24.3.3.1, “mysql_affected_rows()”.

Si usa la palabra IGNORE en un comando INSERT , los errores que ocurren mientras se ejecuta el comando se tratan como advertencias. Por ejemplo, sin IGNORE, un registro que duplique un índice UNIQUE existente o valor PRIMARY KEY en la tabla hace que un error de clave duplicada en el comando se aborte. Con IGNORE, el registro todavía no se inserta, pero no se muestra error. Las conversionse de datos dispararían errores y abortarían el comando si no se sepecificara IGNORE . Con IGNORE, los valores inválidaos se ajustan al valor más cercano y se insertan; las advertencias se producen pero el comando no se aborta. Puede determinar con la función mysql_info() de la API de C cuántos registros se insertan realmente en la tabla.

Si especifica ON DUPLICATE KEY UPDATE, y un registro se inerta que haría que un valor duplicado en un índice UNIQUE o PRIMARY KEY, se realiza un UPDATE del antiguo registro. Por ejemplo, si la columna a se declara como UNIQUE y contiene el valor 1, los siguientes dos comandos tienen efectos idénticos:

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3) -> ON DUPLICATE KEY UPDATE c=c+1; mysql> UPDATE table SET c=c+1 WHERE a=1;

El valor de registros afectados es 1 si el registros se inserta como un nuevo registro y 2 si un valor existente se actualiza.

Nota: Si la columna b es única, el INSERT sería equivalente a este comando UPDATE :

mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

Si a=1 OR b=2 se cumple para varios registros, sólo un registro se actualiza. En general, debería intentar evitar usar una cláusula ON DUPLICATE KEY en tablas con claves únicas múltiples.

MySQL 5.0 permite el uso de la función VALUES(col_name) en la cláusula UPDATE que se refiere a los valores de columna de la porción INSERT del comando INSERT ... UPDATE . En otras palabras, VALUES(col_name) en la cláusula UPDATE se refiere al valor de col_name que se insertarían, no ocurre conflicto de clave duplicada. Esta función es especialmente útil en inserciones de múltiples registros. La función VALUES() tiene sentido sólo en comandos INSERT ... UPDATE y retorna NULL de otro modo.

Ejemplo:

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

Este comando es idéntico a los siguientes dos comandos:

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3) -> ON DUPLICATE KEY UPDATE c=3; mysql> INSERT INTO table (a,b,c) VALUES (4,5,6) -> ON DUPLICATE KEY UPDATE c=9;

Cuando usa ON DUPLICATE KEY UPDATE, la opción DELAYED se ignora.

Puede encontrar el valor usado para una columna AUTO_INCREMENT usando la función SQL LAST_INSERT_ID() . Desde la API C, use la función mysql_insert_id() . Sin embargo, debe tener en cuenta que las dos funciones no siempre se comportan idénticamente. El comportamiento de comandos INSERT respecto a columnas AUTO_INCREMENT se discute en Sección 12.9.3, “Funciones de información” y Sección 24.3.3.34, “mysql_insert_id()”.

Si usa un comando INSERT ... VALUES con listas de múltiples valores o INSERT ... SELECT, el comando retorna una cadena de información en este formato:

Records: 100 Duplicates: 0 Warnings: 0

Records indica el número de registros procesados por el comando. (Este no es necesariamente el número de registros realmente insertados, ya que Duplicates puede ser distinto a cero.) Duplicates indica el número de registros que no pueden insertarse ya que duplicarían algunos valores de índice únicos existentes Warnings indicata el número de intentos para insertar valores de columna que fueron problemáticos por algo. Las advertencias pueden ocurrir bajo cualquiera de las siguientes condiciones:

Insertar NULL en una columna que se ha declarado NOT NULL. Para comandos INSERT de múltiples columnas o comandos INSERT INTO... SELECT, la columna se asigna con el valor por defecto para el tipo de datos de la columna. Este es 0 para tipos numéricos, la cadena vacía ('') para tipos de cadenas, y el valor “cero” para tipos de fecha y hora. Los comandos INSERT INTO ... SELECT se tratan del mismo modo que inserciones de múltiples registros porque el servidor no examina el resultado del SELECT para ver si retorna o no un único registro. (para un único registro INSERT, no hay ninguna advertencia cuando NULL se inserta en una columna NOT NULL . En lugar de eso, el comando falla con un error.)

Poner en una columna numérica un valor fuera del rango de la columna. El valor se redondea al punto final del rango más cercano.

Asigne un valor tal como '10.34 a' a una columna numérica. El texto final se elimina y la parte numérica se inserta. Si el valor de cadena no tiene parte inicial numérica, la columna se pone a 0.

Insertar una cadena en una columna de cadena (CHAR, VARCHAR, TEXT, o BLOB) que excede la maxima longitud de la columna. El valor se trunca a la máxima longitud de la columna.

Insertar un valor en una columna de fecha u hora que es ilegal para el tipo de la columna. La columna se asigna con el valor cero apropiado para el tipo.

Si usa la API de C, la cadena de información puede obtenerse invocando la función mysql_info() Consulte Sección 24.3.3.32, “mysql_info()”.

13.2.4.1. Sintaxis de INSERT ... SELECT

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Con INSERT ... SELECT, puede insertar rápidamente varios registros en un atabla desde una o varias tablas.

Por ejemplo:

INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

La siguiente condición sirve para un comando INSERT ... SELECT :

En MySQL 5.0, especifique IGNORE explícitamente para ignorar registros que causarían violaciones de clave duplicada.

No use DELAYED con INSERT ... SELECT.

En MySQL 5.0, la tabla objetivo del comando INSERT puede aparecer en la cláusula FROM de la parte SELECT de la consulta. (Esto no era posible en algunas versiones antiguas de MySQL.)

Las columnas AUTO_INCREMENT funcionan normalmente.

Para asegurar que el log binario puede usarse para recrear las tablas originales, MySQL no permite inserciones concurrentes durante INSERT ... SELECT.

Actualmente, no puede insertar en una tabla y seleccionar de la misma tabla en una subconsulta.

En las partes de valores de ON DUPLICATE KEY UPDATE puede referirse a una columna en otras tablas, mientras no use GROUP BY en la parte SELECT . Un efecto lateral es que debe calificar los nombres de columna no únicos en la parte de valores.

Puede usar REPLACE en lugar de INSERT para sobreescribir registros antiguos REPLACE es la contraparte de INSERT IGNORE en el tratamiento de nuevos registros que contienen valores de clave única que duplican registros antiguos: Los nuevos registros se usan para reemplazar los antiguos registros en lugar de descartarlos.

13.2.4.2. Sintaxis de INSERT DELAYED

INSERT DELAYED ...

La opción DELAYED para el comando INSERT es una extensión de MySQL del estándar SQL muy útil si tiene clientes que no pueden esperar a que se complete el INSERT . Este es un problema común cuando usa MySQL para loguear y periódicamente ejecuta comandos SELECT y UPDATE que tardan mucho tiempo en completarse.

Cuando un cliente usa INSERT DELAYED, obtiene un ok del servidor una vez, y el registro se encola para insertarse cuando la tabla no está en uso por otro flujo.

Otro beneficio de usar INSERT DELAYED es que las inserciones desde varios clientes se tratan juntas y se escriben en un bloque. Esto es mucho más rápido que realizar inserciones separadas.

Hay algunas restricciones al uso de DELAYED:

En MySQL 5.0, INSERT DELAYED funciona sólo con tablas MyISAM y MEMORY . Para tablas MyISAM , si no hay bloques libres en medio del fichero de datos, se soportan comandos SELECT y INSERT concurrentes. Bajo estas circunstáncias, muy raramente necesitará usar INSERT DELAYED con MyISAM. Consulte Sección 14.1, “El motor de almacenamiento MyISAM” y Sección 14.3, “El motor de almacenamiento MEMORY (HEAP)”.

En MySQL 5.0, INSERT DELAYED debe usarse sólo para comandos INSERT que especifiquen una lista de valores. El servidor ignora DELAYED para comandos INSERT DELAYED ... SELECT .

El servidor ignora DELAYED para comandos INSERT DELAYED ... ON DUPLICATE UPDATE.

Debido a que el comando retorna inmediatamente antes que los registros se inserten, no puede usar LAST_INSERT_ID() para obtener el valor AUTO_INCREMENT que el comando genera.

Los registros DELAYED no son visibles por los comandos SELECT hasta que se hayan insertado realmente.

DELAYED se ignora en la replicación de esclavos porque puede causar que el esclavo tenga distintos datos que el maestro.

Tenga en cuenta que los registros encolados se tratan sólo en memoria hasta que se insertan en la tabla. Esto significa que si termina mysqld forzadamente (por ejemplo, con kill -9) o si mysqld muere inesperadamente, cualquier registro encolado que no se escriba en disco se pierde.

A continuación se describe en detalle qué ocurre cuando usa la opción DELAYED con INSERT o REPLACE. En esta descriión, el “flujo” es el flujo que recibe un comando INSERT DELAYED y “handler” es el flujo que trata todos los comandos INSERT DELAYED para una tabla particular.

Cuando un flujo ejecuta un comando DELAYED para una tabla, un flujo handler se crea para procesar todos los comandos DELAYED para la tabla, si tal handler no existía préviamente.

El flujo chequea si el handler ha adquirido préviamente un bloqueo DELAYED ; si no, le dice al flujo handler que lo haga. El bloqueo DELAYED puede obtenerse incluso si otros flujos tienen el bloqueo READ o WRITE en la tabla. Sin embargo, el handler espera a todos los bloqueos ALTER TABLE o FLUSH TABLES para asegurar que la estructura de tabla está actualizada.

El flujo ejecuta el comando INSERT, pero en lugar de escribir el registro en la tabla, pone una copia del registro final en una cola administrada por el flujo handler. Cualquier error de sintaxis es detectado por el flujo y se reporta al programa cliente.

El cliente no puede obtener del servidor el número de registros duplicados o el valor AUTO_INCREMENT del registro resultante, ya que INSERT retorna antes que se complete la operación de inserción. (Si usa la API C, la función mysql_info() no retorna nada inteligible por la misma razón.)

El log binario se actualiza por parte del flujo handler cuando el registro se inserta en la tabla. En caso de inserciones de múltiples registros, el log binario se actualiza cuando el primer registro se inserta.

Tras cada delayed_insert_limit los registros se escriben, el handler chequea si algún comando SELECT todavía está pendiente. Si es así, les permite ejecutarse antes de continuar.

Cuando el handler no tiene más registros en su cola, la tabla se desbloquea. Si no se reciben nuevos comandos INSERT DELAYED en delayed_insert_timeout segundos, el handler termina.

Si más de delayed_queue_size registros están pendientes en una cola de handler específica, el flujo que pida el INSERT DELAYED espera hasta que haya espacio en la cola. Esto se hace para asegurar que mysqld no usa toda la memoria para la cola de memoria retrasada.

El flujo handler se muestra en l lista de procesos MySQL con delayed_insert en la columna Command . Si muere si ejecuta un comando FLUSH TABLES o puede matarlo con KILL thread_id. Sin embargo, antes de salir, primero almacena todos los registros encolados en la tabla. Durante esta operación no acepta ningún nuevo comando INSERT de otros flujos. Si ejecuta un comando INSERT DELAYED a continuación, se crea un nuevo flujo handler.

Tenga en cuenta que esto significa que comandos INSERT DELAYED tienen mayor prioridad que comandos INSERT normales si hay un handler INSERT DELAYED en ejecución. Otros comandos de actualización tienen que esperar hast que la cola INSERT DELAYED está vacía, alguien termine el flujo handler (con KILL thread_id), o alguien ejecute un FLUSH TABLES.

Las siguientes variables de estado proporcionan información acerca de comandos INSERT DELAYED :

Variable de estado Significado

Delayed_insert_threads Número de flujos handler

Delayed_writes Número de registros escritos con INSERT DELAYED

Not_flushed_delayed_rows Número de registros esperando a ser escritos

Puede ver estas variables ejecutando un comando SHOW STATUS o mysqladmin extended-status.

Tenga en cuenta que INSERT DELAYED es más lento que un INSERT normal si la tabla no está en uso. También hay una sobrecarga adicional para el servidor debido a que tiene que tratar un flujo separado para cada tabla en que haya registros retardados. Esto significa que debe usar INSERT DELAYED sólo cuando esté realmente seguro que lo necesita.

13.2.5. Sintaxis de LOAD DATA INFILE

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] ] [IGNORE number LINES] [(col_name_or_user_var,...)] [SET col_name = expr,...)]

El comando LOAD DATA INFILE lee registros desde un fichero de texto a una tabla a muy alta velocidad. El nombre de fichero debe darse como una cadena literal.

Para más información acerca de la eficiencia de INSERT contra LOAD DATA INFILE y acelerar LOAD DATA INFILE, consulte Sección 7.2.14, “Velocidad de la sentencia INSERT”.

En MySQL 5.0, el conjunto de carácteres indicado por la variable de sistema character_set_database se usa para interpretar la información en el fichero. SET NAMES y el valor de character_set_client no afecta la interpretación de la entrada.

Puede cargar ficheros de datos usando la utilidad mysqlimport ; opera enviando un comando LOAD DATA INFILE al servidor. La opción --local hace que mysqlimport lea ficheros de datos desde el equipo cliente. Puede especificar la opción --compress para obtener un mejor rendimiento en redes lentas si el cliente y el servidor soportan el protocolo comprimido. Consulte Sección 8.9, “El programa para importar datos mysqlimport”.

Si usa LOW_PRIORITY, la ejecución del comando LOAD DATA se retarda hasta que no haya más clientes leyendo de la tabla.

Si especifica CONCURRENT con una tabla MyISAM que satisfaga la condición para inserciones concurrentes (esto es, no contiene bloques libres en medio), entonces otros flujos pueden recibir datos desde la tabla mientras se ejecuta LOAD DATA . Usar esta opción afecta al rendimiento de LOAD DATA ligeramente, incluso si no hay otro flujo usando la tabla al mismo tiempo.

Si se especifica LOCAL, se interpreta respecto al cliente final de la conexión:

Si se especifica LOCAL, el fichero se lee por parte del programa cliente en el equipo cliente y se envía al servidor. El fichero puede darse como una ruta completa para especificar su localización exacta. Si se da como ruta relativa, el nombre se interpreta relativo al directorio en que el cliente se inició.

Si no se especifica LOCAL , el fichero no debe localizarse en el equipo sevidor y se lee directamente por el servidor.

Al localizar ficheros en el equipo servidor, el servidor usa las siguientes reglas:

Si se da una ruta absoluta, el servidor usa la ruta como tal.

Si se da una ruta relativa con uno o más componentes el servidor busca este fichero relativo al directorio de datos del servidor.

Si se da un nombre de fichero sin componentes, el servidor busca el fichero en el directorio de base de datos de la base de datos por defecto.

Tenga en cuenta que estas reglas significan que un fichero llamado ./myfile.txt se lee del directorio de datos del servidor, mientras que el mismo fichero llamado como myfile.txt se lee desde el directorio de base de datos de la base de datos por defecto. Por ejemplo, el siguiente comando LOAD DATA lee el fichero data.txt del directorio de la base de datos para db1 porque db1 es la base de datos actual, incluso si el comando carga explícitamente el fichero en una tabla en la base de datos db2:

mysql> USE db1; mysql> LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Tenga en cuenta que las rutas de windows se especifican usando barras en lugar de antibarras. Si usa barras, debe doblarlas.

Por razones de seguridad, al leer ficheros de texto localizados en el servidor, los ficheros deben residir en el directorio de la base de datos o ser leíbles por todo el mundo. Además, para usar LOAD DATA INFILE en ficheros del servidor, debe tener el permiso FILE .

Consulte Sección 5.6.3, “Privilegios de los que provee MySQL”.

Usar LOCAL es un poco más lento que dejar al servidor acceder al fichero directamente, porque el contenido del fichero debe enviarse por la conexión desde el cliente al servidor . Por otra parte, no necesita el permiso FILE para cargar ficheros locales.

En MySQL 5.0, LOCAL funciona sólo si su servidor y su cliente lo tienen activado. Por ejemplo, si mysqld se arranca con --local-infile=0, entonces LOCAL no funciona. Consulte Sección 5.5.4, “Cuestiones relacionadas con la seguridad y LOAD DATA LOCAL”.

Si necesita LOAD DATA para leer desde un pipe, puede usar la siguiente técnica (aquí cargamos el listado del directorio / en una tabla):

mkfifo /mysql/db/x/x chmod 666 /mysql/db/x/x find / -ls > /mysql/db/x/x mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

Las palabaras REPLACE y IGNORE controlan el tratamiento de registros de entrada que duplican registros existentes en claves únicas.

Si especifica REPLACE, los registros de entrada reemplazan registros existentes (en otras palabras, los registros que tienen el mismo valor para una clave primaria o única que un registro existente). Consulte Sección 13.2.6, “Sintaxis de REPLACE”.

Si especifica IGNORE, los registros de entrada que dupliquen un registro existente en una clave única se ignoran. Si no especifica ninguna opción, el comportamiento depende de si la palabra LOCAL se ha especificado o no. Sin LOCAL, ocurre un error cuando se encuentra un valor de clave duplicado, y el resto del fichero de texto se ignora. Con LOCAL, el comportamiento por defecto es el mismo que si se especifica IGNORE, esto es porque el servidor no tiene forma de parar la transmisión del fichero en medio de la operación.

Si quiere ignorar restricciones de clave foránea durante la operación de carga, puede realizar un comando SET FOREIGN_KEY_CHECKS=0 antes de ejecutar LOAD DATA.

Si usa LOAD DATA INFILE en una tabla vacía MyISAM , todos los índices no únicos se crean en batch separados (como para REPAIR TABLE). Esto hace LOAD DATA INFILE mucho más rápido cuando tiene varios índices. Normalmente esto es muy rápido, pero en algunos casos extromos, puede crear los índices incluso más rápido desactivándolos con ALTER TABLE ... DISABLE KEYS antes de cargar el fichero en la tabla y usar ALTER TABLE ... ENABLE KEYS para recrear los índices tras cargar el fichero. Consulte Sección 7.2.14, “Velocidad de la sentencia INSERT”.

LOAD DATA INFILE es el complemento de SELECT ... INTO OUTFILE. (Consulte Sección 13.2.7, “Sintaxis de SELECT”.) Para escribir datos de una tabla en un fichero use SELECT ... INTO OUTFILE. Para leer el fichero de nuevo en una tabla, use LOAD DATA INFILE. La sintaxis de las cláusulas FIELDS y LINES es la misma para ambos. Ambas son opcionales, pero FIELDS debe preceder a LINES si se especifican ambas.

Si especifica una cláusula FIELDS , cada una de sus subcláusulas (TERMINATED BY, [OPTIONALLY] ENCLOSED BY, y ESCAPED BY) también es opcional, excepto que debe especificar al menos una de ellas.

Si no especifica una cláusula FIELDS, por defecto es como si hubiera escrito esto:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

Si no especifica una cláusula LINES, por defecto es como si hubiera escrito esto:

LINES TERMINATED BY '\n' STARTING BY ''

En otras palabras, por defecto LOAD DATA INFILE actúa como sigue al leer la entrada:

Busca delimitadores de línea como nuevas líneas.

No ignora ningún prefijo de línea.

Rompe las líneas en campos con los tabuladores.

No espera campos entrecomillados dentro de ningún carácter delimitador.

Interpreta las ocurrencias de tabuladores, nuevas líneas o '\' precedidas por '\' como carácteres literales que son parte de valores de campos.

Por defecto SELECT ... INTO OUTFILE actúa como sigue al escribir la salida:

Escribe tabuladores entre campos.

No entrecomilla los campos.

Usa '\' para escapar las instancias de tabuladores, nuevas líneas o '\' que ocurren entre valores de campos.

Escribe nuevas líneas al final de las líneas.

Tenga en cuenta que para escribir FIELDS ESCAPED BY '\\', debe escribir dos antibarras para que se interprete como una única antibarra.

Nota: Si ha generado el fichero de texto en un sistema Windows , puede tener que usar LINES TERMINATED BY '\r\n' para leer correctamente el fichero, ya que los programas de Windows típicamente usan dos carácteres como terminadores de línea . Algunos programas como WordPad, pueden usar \r como terminador de línea al escribir ficheros. Para leer tales ficheros, use LINES TERMINATED BY '\r'.

Si todas las líneas que quiere leer tienen un prefijo común que quiere ignorar, puede usar LINES STARTING BY 'prefix_string' para ignorar el prefijo (y cualquier cosa antes del mismo). Si una línea no incluye el prefijo, la línea entera se ignora. Nota prefix_string puede ocurrir en medio de una línea.

Ejemplo:

mysql> LOAD DATA INFILE '/tmp/test.txt' -> INTO TABLE test LINES STARTING BY "xxx";

Con esto puede leer en un fichero que contenga algo como:

xxx"row",1 something xxx"row",2

Y obtener los datos ("row",1) y ("row",2).

La opción IGNORE number LINES puede usarse para ignorar líneas al inicio del fichero. Por ejemplo, puede usar IGNORE 1 LINES para ignorar una cabecera inicial que contenga los nombres de las columnas:

mysql> LOAD DATA INFILE '/tmp/test.txt' -> INTO TABLE test IGNORE 1 LINES;

Cuando usa SELECT ... INTO OUTFILE junto con LOAD DATA INFILE para escribir datos desde una base de datos en un fichero y luego lee datos del fichero de nuevo en la base de datos, las opciones de tratamiento de fichero y de línea para ambos comandos deben coincidir. De otro modo, LOAD DATA INFILE no interpreta los contenidos del fichero correctamente. Suponga que usa SELECT ... INTO OUTFILE para escribir un fichero con campos delimitados por comas:

mysql> SELECT * INTO OUTFILE 'data.txt' -> FIELDS TERMINATED BY ',' -> FROM table2;

Para leer el fichero delimitado por comas, el comando correcto sería:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 -> FIELDS TERMINATED BY ',';

Si en lugar de esto trata de leer en el fichero con el comando mostrado aquí, no funcionaría porque le dice a LOAD DATA INFILE que busque tabuladores entre campos:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2 -> FIELDS TERMINATED BY '\t';

El resultado esperado es que cada línea de entrada se interprete como un único campo.

LOAD DATA INFILE puede usarse para leer ficheros obtenidos de fuentes externas. Por ejemplo, un fichero en formato dBASE tiene campos separados por comas y entrecomillados por comillas dobles. Si las líneas en el fichero se terminan con nuevas líneas, el comando mostrado aquí ilustra las opciones de campo y línea que debería usar para cargar el fichero:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' -> LINES TERMINATED BY '\n';

Cualquiera de las opciones de tratamiento de campo o línea pueden especificarse como una cadena vacía (''). Si no está vacía, los valores FIELDS [OPTIONALLY] ENCLOSED BY y FIELDS ESCAPED BY deben ser un único carácter. Los valores FIELDS TERMINATED BY, LINES STARTING BY, y LINES TERMINATED BY pueden tener más de un carácter . Por ejemplo, para escribir líneas terminadas por parejas de retorno de carro y nueva línea, o para leer un fichero conteniendo tales líneas, especifique una cláusula LINES TERMINATED BY '\r\n' .

Para leer un fichero que contenga bromas separadas por líneas consistentes de %%, puede hacer lo siguiente

mysql> CREATE TABLE jokes -> (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> joke TEXT NOT NULL); mysql> LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes -> FIELDS TERMINATED BY '' -> LINES TERMINATED BY '\n%%\n' (joke);

FIELDS [OPTIONALLY] ENCLOSED BY controla el entrecomillado de los campos. Para la salida (SELECT ... INTO OUTFILE), si omite la palabra OPTIONALLY, todos los campos se delimitan por el carácter ENCLOSED BY. Un ejemplo de tal salida (usando coma como el delimitador de campo) se muestra aquí:

"1","a string","100.20" "2","a string containing a , comma","102.20" "3","a string containing a \" quote","102.20" "4","a string containing a \", quote and comma","102.20"

Si especifica OPTIONALLY, el carácter ENCLOSED BY se usa sólo para delimitar valores en columnas que tienen datos de cadenas (tales como CHAR, BINARY, TEXT, o ENUM):

1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a \" quote",102.20 4,"a string containing a \", quote and comma",102.20

Tenga en cuenta que la ocurrencias del carácter ENCLOSED BY dentro de un campo se escapan mediante un prefijo del carácter ESCAPED BY. También tenta en cuenta que si especifica un valor ESCAPED BY vacío, es posible generar salida que no puede leerse correctamente con LOAD DATA INFILE. Por ejemplo, la salida precedente tendría la siguiente apariencia si el carácter de escape estuviera vacío. Observe que el segundo campo en la cuarta línea contiene una coma siguiendo la delimitación, que (erróneamente) parece que termine el campo:

1,"a string",100.20 2,"a string containing a , comma",102.20 3,"a string containing a " quote",102.20 4,"a string containing a ", quote and comma",102.20

Para entrada, el carácter ENCLOSED BY , si está presente, se elimina del final de los valores de campos . (Esto es cierto se especifique OPTIONALLY o no; OPTIONALLY no tiene efecto en la interpretación de la entrada.) Las ocurrencias del carácter ENCLOSED BY prececdidas por el carater ESCAPED BY se interpretan como parte del campo actual.

Si el campo comienza con el carácter ENCLOSED BY , las instancias del mismo se reorganizan como terminadores del campo sólo si van seguidas por el campo o la secuencia TERMINATED BY . Para evitar ambigüedad, las ocurrencias del carácter ENCLOSED BY dentro de un campo se pueden doblar y se interpretan como una única instancia del carácter. Por ejemplo, si se especifica ENCLOSED BY '"' , la delimitación se trata como se muestra aquí:

"The ""BIG"" boss" -> The "BIG" boss The "BIG" boss -> The "BIG" boss The ""BIG"" boss -> The ""BIG"" boss

FIELDS ESCAPED BY controla cómo escribir o leer carácteres especiales. Si el carácter FIELDS ESCAPED BY no está vacío, se usa como prefijo para los siguientes carácteres de salida:

El carácter FIELDS ESCAPED BY

El carácter FIELDS [OPTIONALLY] ENCLOSED BY

El primer carácter de los valores FIELDS TERMINATED BY y LINES TERMINATED BY

ASCII 0 (lo que realmente se escribe a continuación del carácter de escape es '0' en ASCCI, no un byte con valor cero)

Si el carácter FIELDS ESCAPED BY está vacío, no se escapan carácteres y NULL se muestra como NULL, no \N. Probablemente no es una buena idea especificar un carácter de escape vacío, particularmente si los valores de campos en sus datos contienen cualquiera de los carácteres en la lista dada.

Para entrada, si el carácter FIELDS ESCAPED BY no está vacío, las ocurrencias del mismo se eliminan y el siguiente carácter se toma literalmente como parte del campo. Las excepciones son un '0' escapado o 'N' (por ejemplo, \0 o \N si el carácter de escape es '\'). Estas secuencias se interpretan como ASCII NUL (un byte con valor cero) y NULL. Las reglas para tratamiento de NULL se describen posteriormente.

Para más infomación de la sintaxis de escape '\' consulte Sección 9.1, “Valores literales”.

En ciertos casos, las opciones de tratamiento de campos y línea interactúan:

Si LINES TERMINATED BY es una cadena vacío y FIELDS TERMINATED BY no está vacío, las líneas se terminan con FIELDS TERMINATED BY.

Si los valores FIELDS TERMINATED BY y FIELDS ENCLOSED BY están vacíois (''), se usa un formato fijo de registro (no delimitado). Con este formato, no se usan delimitadores entre campos (pero puede tener un terminador de línea). En su lugar, los valores de columna se escriben y leen usando los anchos de muestra de las columnas. Por ejemplo, si una columna se declara como INT(7), los valores para la columna se escriben usando campos de siete carácteres. En la entrada, los valores para la columna se obtienen leyendo siete carácteres.

LINES TERMINATED BY se usa para separar líneas. Si una línea no contiene todos los campos, el resto de columnas se asignan con sus valores por defecto. Si no tiene un terminador de línea, debe asignarlo a ''. En este caso, el fichero de texto debe contener todos los campos para cada registro.

El formato fijo de registro también afecta al tratamiento de valores NULL, como se describe posteriormente. Tenga en cuenta que el formato de tamaño fijo no funciona si está usando un conjunto de carácteres multi byte.

El tratamiento de valores NULL varía en función de las opciones FIELDS y LINES en uso:

Para los valores FIELDS y LINES por defecto, NULL se escribe como \N para la salida, y \N para la entrada se lee como NULL (considerando que el carácter ESCAPED BY es '\').

Si FIELDS ENCLOSED BY no está vacílo, un campo que contenga el literal NULL como valor se lee como el valor NULL . Esto difiere de la palabra NULL delimitada por carácteres FIELDS ENCLOSED BY , que se lee como la cadena 'NULL'.

Si FIELDS ESCAPED BY está vacío, NULL se escribe como la palabra NULL.

Con formato fijo de registro (lo que ocurre cuando FIELDS TERMINATED BY y FIELDS ENCLOSED BY están vacíos), NULL se escribe como una cadena vacía. Teng en cuenta que esto hace que ambos valores NULL y cadenas vacías en la tabla sean indistinguibles cuando se escriben en el fichero ya que ambos se escriben como cadenas vacías. Si necesita distinguir entre ambos al leer del fichero, no debe usar el formato de registro fijo.

Algunos casos no son soportados por LOAD DATA INFILE:

Registros de tamaño fijo (FIELDS TERMINATED BY y FIELDS ENCLOSED BY ambos vacíos) y columnas BLOB o TEXT .

Si especifica un separador que es igual o prefijo de otro, LOAD DATA INFILE no será capaz de interpretar la entrada correctamente. Por ejemplo, la siguiente cláusula FIELDS causaría problemas:

FIELDS TERMINATED BY '"' ENCLOSED BY '"'

Si FIELDS ESCAPED BY está vacío, un valor que contenga una ocurrencia de FIELDS ENCLOSED BY o LINES TERMINATED BY seguido por el valor FIELDS TERMINATED BY causa que LOAD DATA INFILE pare de leer un campo o línea demasiado rápido. Esto ocurre porque LOAD DATA INFILE no puede determinar apropiadamente dónde acaba el campo o línea.

El siguiente ejemplo carga todas las columnas de la tabla persondata :

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

Por defecto, cuando no se proporciona una lista al final de un comando LOAD DATA INFILE, las líneas de entrada se espera que contengan un campo para cada columna de la tabla. Si quiere cargar sólo algunas columnas de una tabla, especifique una lista de columnas:

mysql> LOAD DATA INFILE 'persondata.txt' -> INTO TABLE persondata (col1,col2,...);

Debe especificar una lista de columnas si el orden de los campos del fichero de entrada difiere del orden de las columnas en la tabla. De otro modo, MySQL no puede decir cómo hacer coincidir los campos de entrada con las columnas de la tabla.

Antes de MySQL 5.0.3, la lista de columnas debe contener sólo nombres de columnas en la tabla que se carga, y la cláusula SET no se soporta. Desde MySQL 5.0.3, la lista de columnas puede contener nombres de columna o variables y la cláusula SET se soporta. Esto le permite asignar valores de entrada a variables de usuario, y luego realizar transformaciones on estos valores antes de asignar los resultados a las columnas.

Las variables de usuario en la cláusula SET puede usarse de distintos modos. El siguiente ejemplo usa la primera columna en el fichero de datos directamente para el valor de t1.column1, y asigna la segunda columna a una variable de usuario que está sujeta a una operación de división antes de ser usada por el valor de t2.column2:

LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, @var1) SET column2 = @var1/100;

La cláusula SET puede usarse para proporcionar valores no derivados del fichero de entrada. Los siguientes comandos actualizan column3 con la fecha y hora actuales:

LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, column2) SET column3 = CURRENT_TIMESTAMP;

También puede descartar un valor de entrada asignándolo a una variable de usuario y no asignando la variable a una columna de tabla:

LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, @dummy, column2, @dummy, column3);

El uso de la lista de columnas/variables y la cláusula SET está sujeto a las siguientes restricciones:

Las asignaciones en la cláusula SET deben tener sólo nombres de columna en el lado izquierdo del operador de asignación.

Puede usar subconsultas en la parte derecha de la asignación de SET. Una subconsulta que retorne un valor a ser asignado a otra coluimna sólo puede ser una subconsulta escalar. Además, no puede usar una subconsulta para seleccionar desde la tabla que se está cargando.

Las líneas ignoradas por un cláusula IGNORE no se procesan por parta de la lista de columnas/variables o por la cláusula SET .

Las variables de usuario no pueden usarse al cargar datos con formato de registo ya que las variables de usuario no tienen un ancho de muestra.

Al procesar una línea de entrada, LOAD DATA la divide en campos y usa los valores según la lista de columnas/ variables y la cláusula SET , si están presentes. A continuación se inserta el registro resultante en la tabla. Si hay disparadores BEFORE INSERT o AFTER INSERT para la tabla, se activan antes o después de insertar el registro, respectivamente.

Si una línea de entrada tiene demasiados campos, los campos extra se ignoran y el número de advertencias se incrementa.

Si una línea de entrada no tiene suficientes campos, las columnas de la tabla que no tienen entrada adquieren su valor por defecto. Los valores por defecto se describen en Sección 13.1.5, “Sintaxis de CREATE TABLE”.

Un valor de campo vacío se interpreta de forma distinta que si el valor no está presente:

Para tipos de cadenas, la columna adquiere la cadena vacía.

Para tipos numéricos, la columna recibe el valor 0.

Para tipos de fecha y hora, la columna obtiene el valor “cero” apropiado para el tipo. Consulte Sección 11.3, “Tipos de fecha y hora”.

Estos son los mismos valores que resultan si asigna una cadena vacía explícitamente a un tipo de cadena de carácteres, numérico o de fecha u hora en un comando INSERT o UPDATE statement.

Las columnas TIMESTAMP obtienen la fecha y hora actuales sólo si hay un valor NULL para la columna (esto es, \N), o (para la primera columna TIMESTAMP únicamente) si se omite TIMESTAMP de la lista de campos cuando se especifica una.

LOAD DATA INFILE trata todas las entradas como cadenas, asi que no puede usar valores numéricos para columnas ENUM o SET del modo en que puede hacerlo con comandos INSERT . Todos los valores ENUM y SET deben especificarse como cadenas.

Cuando acaba el comando LOAD DATA INFILE, retorna una cadena de información con el siguiente formato:

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

Si usa la API de C, puede obtener información acerca del comando mediante la función mysql_info(). Consulte Sección 24.3.3.32, “mysql_info()”.

Las advertencias se producen bajo las mismas circunstancias que cuando los valores se insertan mediante el comando INSERT (consulte Sección 13.2.4, “Sintaxis de INSERT”), excepto que LOAD DATA INFILE también genera advertencias cuando hay muy pocos o demasiados campos en el registro de entrada. Las advertencias no se almacenan en ningún lugar; el número de las mismas puede usarse sólo como indicación de si todo ha ido bien.

En MySQL 5.0, puede usar SHOW WARNINGS para obtener una lista de las primeras max_error_count advertencias como información acerca de qué ha fallado. Consulte Sección 13.5.4.22, “Sintaxis de SHOW WARNINGS”.

13.2.6. Sintaxis de REPLACE

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),...

O:

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name SET col_name={expr | DEFAULT}, ...

O:

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)] SELECT ...

REPLACE funciona exactamente como INSERT, excepto que si un valor de la tabla tiene el mismo valor que un nuevo registro para un índice PRIMARY KEY o UNIQUE , el antiguo registro se borra antes de insertar el nuevo. Consulte Sección 13.2.4, “Sintaxis de INSERT”.

Tenga en cuenta que a menos que la tabla tenga un índice PRIMARY KEY, o UNIQUE usar un comando REPLACE no tiene sentido. Es equivalente a INSERT, ya que no hay índice para determinar si un nuevo registro duplica otro.

Los valores para todas las columnas se toman de los valores especificados en el comando REPLACE. Cualquier columna no presente adquiere su valor por defecto, como ocurre con INSERT. No puede referirse a valores del registro actual y usarlos en el nuevo registro. Si usa un comando tal como SETcol_name = col_name + 1, la referencia al nombre de columna en la parte derecha se trata como DEFAULT(col_name), así que es equivalente a SETcol_name = DEFAULT(col_name) + 1.

Para ser capaz de usar REPLACE, debe tener los permisos INSERT y DELETE para la tabla.

El comando REPLACE retorna un contador con el número de registros afectados. Esta es la suma de registros borrados e insertados. Si el contador es 1 para REPLACE de un único registro, se inserta un registro y no se borra ninguno. Si el contador es mayor que 1, uno o más registros se borraron antes de insertar el nuevo. Es posible para un único registro reemplazar más de un registro antiguo si la tabla contiene múltiples índices únicos y el nuevo registro duplica valores para distintos registros antiguos en distintos índices únicos.

El contador de registros afectados hace fácil determinar si REPLACE sólo añadió un registro o si también reemplazo alguno: Compruebe si el contador es 1 (añadido) o mayor (reemplazados).

Si usa la API de C, el contador de registros afectados puede obtenerse usando la función mysql_affected_rows().

Actualmente, no puede reemplzar en una tabla y seleccionar de la misma en una subconsulta.

Aquí sigue en más detalle el algoritmo usado (también se usa con LOAD DATA ... REPLACE):

Intenta insertar el nuevo registro en la tabla

Mientras falle la inserción debido a error de clave duplicada por clave única o primaria:

Borra de la tabla el registro conflictivo que tiene el valor de clave duplicada

Intenta insertar de nuevo el registro en la tabla

13.2.7. Sintaxis de SELECT

SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr, ... [INTO OUTFILE 'file_name' export_options | INTO DUMPFILE 'file_name'] [FROM table_references [WHERE where_definition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_definition] [ORDER BY {col_name | expr | position} [ASC | DESC] , ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT se usa para recibir registros seleccionados desde una o más tablas. MySQL 5.0 incluye soporte para comandos UNION y subconsultas. Consulte Sección 13.2.7.2, “Sintaxis de UNION” y Sección 13.2.8, “Sintaxis de subconsultas”.

Cada select_expr indicata una columna que quiere recibir.

table_references indicata la tabla o tablas desde la que recibir registros. Su sintaxis se describe en Sección 13.2.7.1, “Sintaxis de JOIN”.

where_definition consiste en la palabra clave WHERE seguida por una expresión que indica la condición o condiciones que deben satisfacer los registros para ser seleccionados.

SELECT también puede usarse para recuperar registros computados sin referencia a ninguna tabla.

Por ejemplo:

mysql> SELECT 1 + 1; -> 2

Todas las cláusulas usadas deben darse exactamente en el orden mostrado en la descripción de la sintaxis. Por ejemplo, una cláusula HAVING debe ir tras cualquier cláusula GROUP BY y antes de cualquier cláusula ORDER BY .

Una select_expr puede tener un alias usando AS alias_name. El alias se usa como el nombre de columna de la expresión y puede usarse en cláusulas GROUP BY, ORDER BY, o HAVING . Por ejemplo:

mysql> SELECT CONCAT(last_name,', ',first_name) AS full_name -> FROM mytable ORDER BY full_name;

La palabra clave AS es opcional cuando se usa un alias para select_expr. El ejemplo precedente podría haberse escrito como:

mysql> SELECT CONCAT(last_name,', ',first_name) full_name -> FROM mytable ORDER BY full_name;

Como AS es opcional, puede ocurrir un sutil problema si olvida la coma entre dos expresiones select_expr : MySQL interpreta el segundo como un nombre de alias. Por ejemplo, en el siguiente comando, columnb se tata como un nombre de alias:

mysql> SELECT columna columnb FROM mytable;

Por esta razón, es una buena práctica poner los alias de columnas usando AS.

No se permite usar un alias de columna en una cláusula WHERE, ya que el valor de columna puede no estar determinado cuando se ejecuta la cláusula WHERE . Consulte Sección A.5.4, “Problemas con alias de columnas”.

La cláusula FROMtable_references indica la tabla desde la que recibir registros. Si nombra más de una tabla, está realizando un join, Para información sobre la sintaxis de join, consulte Sección 13.2.7.1, “Sintaxis de JOIN”. Para cada tabla especificada, puede opcionalmente especificar un alias.

tbl_name [[AS] alias] [[USE INDEX (key_list)] | [IGNORE INDEX (key_list)] | [FORCE INDEX (key_list)]]

El uso de USE INDEX, IGNORE INDEX, FORCE INDEX para dar al optimizador pistas acerca de cómo escoger los indices se describe en Sección 13.2.7.1, “Sintaxis de JOIN”.

En MySQL 5.0, puede usar SET max_seeks_for_key=value como alternativa para forzar a MySQL a que realice escaneos de claves en lugar de escaneos de tabla.

Puede referirse a una tabla dentro de la base de datos actual como tbl_name (dentro de la base de datos actual) , o como db_name.tbl_name para referirse a una base de datos explícitamente. Puede referirse a una columna como col_name, tbl_name.col_name, o db_name.tbl_name.col_name. No necesita especificar un prefijo tbl_name o db_name.tbl_name para una referencia de columna a no ser que la referencia fuese ambígua. Consulte Sección 9.2, “Nombres de bases de datos, tablas, índices, columnas y alias” para ejemplos de ambigüedad que requieran las formas de referencia de columna más explícitas.

En MySQL 5.0, puede especificar DUAL como nombre de tabla falso en siguaciones donde no se referencian tablas:

mysql> SELECT 1 + 1 FROM DUAL; -> 2

DUAL es una característica puramente de compatibilidad. Otros servidores requieren esta sintaxis.

Una referencia de tabla puede tener un alias usando tbl_name AS alias_name o tbl_name alias_name:

mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 -> WHERE t1.name = t2.name; mysql> SELECT t1.name, t2.salary FROM employee t1, info t2 -> WHERE t1.name = t2.name;

En la cláusula WHERE , puede usar cualquiera de las funciones que soporta MySQL, escepto para funciones agregadas (resumen). Consulte Capítulo 12, Funciones y operadores.

Las columnas seleccionadas para la salida pueden ser referidas en cláusulas ORDER BY y GROUP BY usando nombres de columnas, alias, o posiciones. Las posiciones de columnas son enteros y comienzan con 1:

mysql> SELECT college, region, seed FROM tournament -> ORDER BY region, seed; mysql> SELECT college, region AS r, seed AS s FROM tournament -> ORDER BY r, s; mysql> SELECT college, region, seed FROM tournament -> ORDER BY 2, 3;

Para ordenar en orden inverso, añada la palabra clave DESC (descendiente) al nombre de la columna en la cláusula ORDER BY por la que está ordenando. Por defecto es orden ascendente; puede especificarse explícitamente usando la palabra clave ASC.

El uso de posiciones de columna está obsoleto ya que la sintaxis se ha eliminado del estándar SQL.

Si usa GROUP BY, los registros de salida se ordenan según las columnas GROUP BY como si tuviera un ORDER BY para las mismas columnas. MySQL 5.0 extiende la cláusula GROUP BY para que pueda especificar ASC y DESC tras las columnas nombradas en la cláusula:

SELECT a, COUNT(b) FROM test_table GROUP BY a DESC

MySQL extiende el uso de GROUP BY para permitir seleccionar campos que no se mencionan en la cláusula GROUP BY . Si no obtiene los resultados que espera de la consulta, por favor lea la descripción de GROUP BY en Sección 12.10, “Funciones y modificadores para cláusulas GROUP BY”.

En MySQL 5.0, GROUP BY permite un modificador WITH ROLLUP . Consulte Sección 12.10.2, “Modificadores de GROUP BY”.

La cláusula HAVING se aplica casi al final, justo antes de que los elementos se envíen al cliente, sin optimización. (LIMIT se aplica tras HAVING.)

Antes de MySQL 5.0.2, una cláusula HAVING podía referirse a cualquier columna o alias nombrado en una select_expr en la lista SELECT o en subconsultas externas, y para funciones agregadas. Sin embargo, el estándar SQL requiere que HAVING debe referirse sólo a columnas en la cláusula GROUP BY o columnas usadas en funciones agregadas. Para acomodar ambos estándars SQL y el comportamiento específico de MySQL en que es capaz de referirse a columans en la lista SELECT , MySQL 5.0.2 y posteior permite a HAVING referirse a columnas en la lista SELECT , en la cláusula GROUP BY , en subconsultas externas y en funciones agregadas.

Por ejemplo, el siguiente comando funciona en MySQL 5.0.2 pero produce un error en versiones aneriores:

mysql> SELECT COUNT(*) FROM t GROUP BY col1 HAVING col1 = 2;

Si la cláusula HAVING se refiere a una columna ambígua, se muestra una advertencia. En el siguiente comando, col2 es ambíguo porque se usa tanto para un alias como para un nombre de columna:

mysql> SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

Se da preferencia al comportamiento SQL estándar, así que si un nombre de columna HAVING se usa en un GROUP BY y como alias de columna en la lista de columnas de salida, se da preferencia a la columna en GROUP BY .

No use HAVING para elementos que deban estar en la cláusula WHERE . Por ejemplo, no escriba lo siguiente:

mysql> SELECT col_name FROM tbl_name HAVING col_name > 0;

Escriba esto en su lugar:

mysql> SELECT col_name FROM tbl_name WHERE col_name > 0;

La cláusula HAVING puede referirse a funciones de agregación, algo que no puede hacer la cláusula WHERE:

mysql> SELECT user, MAX(salary) FROM users -> GROUP BY user HAVING MAX(salary)>10;

(Esto no funciona en versiones antiguas de MySQL.)

La cláusula LIMIT puede usarse para restringir el número de registros retornados por el comando SELECT. LIMIT tiene uno o dos argumentos numéricos, que deben ser enteros positivos (incluyendo cero).

Con dos argumentos, el primer argumento especifica el desplazamiento del primer registro a retornar. El desplazamiento del registro inicial es 0 (no 1):

mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15

Por compatibilidad con PostgreSQL, MySQL también soporta la sintaxis LIMIT row_count OFFSET offset.

Para recibir todos los registros de un desplazamiento hasta el final del conjunto de resultados, puede usar algún número grande para el segundo parámetro. Ete comando recibe todos los registros desde el 96th hasta el último:

mysql> SELECT * FROM table LIMIT 95,18446744073709551615;

Con un argumento, el valor especifica el número de registros a retornar desde el comienzo del conjunto de resultados:

mysql> SELECT * FROM table LIMIT 5; # Retrieve first 5 rows

En otras palabras, LIMIT n es equivalente a LIMIT 0,n.

La forma SELECT ... INTO OUTFILE 'file_name' de SELECT escribe los registros seleccionados en un fichero. El fichero se crea en el equipo servidor, así que debe tener el permiso FILE para usar esta sintaxis. El fichero no puede existir, que entre otras cosas evita destruir ficheros cruciales tales como /etc/passwd y tablas de la base de datos.

El comando SELECT ... INTO OUTFILE existe principalmente para dejarle volcar una tabla rápidamente en la máquina servidor. Si quiere crear el fichero resultante en un equipo cliente distinto al equipo servidor, no puede usar SELECT ... INTO OUTFILE. En tal caso, debería usar algún comando como mysql -e "SELECT ..." > file_name en el equipo cliente para generar el fichero.

SELECT ... INTO OUTFILE es el complemento de LOAD DATA INFILE; la sintaxis para la parte export_options del comando consiste en las mismas cláusulas FIELDS y LINES usadas con el comando LOAD DATA INFILE . Consulte Sección 13.2.5, “Sintaxis de LOAD DATA INFILE”.

FIELDS ESCAPED BY controla cómo escribir carácteres especiales. Si el carácter FIELDS ESCAPED BY no está vacío, se usa como prefijo para los siguientes carácteres en la salida:

El carácter FIELDS ESCAPED BY

El carácter FIELDS [OPTIONALLY] ENCLOSED BY

El primer carácter de FIELDS TERMINATED BY y LINES TERMINATED BY

ASCII 0 (que se escribe siguiendo el carácter de escape ASCII '0', no un byte con valor cero)

Si el carácter FIELDS ESCAPED BY está vacío, no hay ningún carácter de escape y NULL se muestra por salida como NULL, no \N. Probablemente no es buena idea especificar un carácter de escape vacío, particularmente si los valores de los campos de sus datos contienen cualqiuera de los carácteres en la lista dada.

La razón de lo anterior es que debe escapar cualquier carácter FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY, o LINES TERMINATED BY para ser capaz de volver a leer el fichero correctamente. ASCII NUL se escapa para hacer más fácil visualizarlo con algunos visores.

El fichero resultante no tiene que estar conforme a la sintaxis SQL, así que nada más debe escaparse.

Este es un ejemplo que produce un fichero en formato de valores separados por comas usado por varios programas:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;

Si usa INTO DUMPFILE en lugar de INTO OUTFILE, MySQL escribe sólo un registro en el fichero, sin ninguna terminación de línea o columna y sin realizar ningún proceso de escape. Esto es útil si quiere almacenar un valor BLOB en un fichero.

Nota: Cualquier fichero creado por INTO OUTFILE o INTO DUMPFILE es modificable por todos los usuarios en el equipo servidor. La razón es que el servidor MySQL no puede crear un fichero con un propietario distinto al usuario que está en ejecución (nunca debe ejecutar mysqld como root por esta y otras razones). El fichero debe ser modificable por todo el mundo para que pueda maminpular sus contenidos.

Una cláusula PROCEDURE nombra a un procedimiento que debe procesar los datos en el conjunto de resultados. Para un ejemplo, consulte Sección 27.3.1, “Procedimiento Analyse”.

Si usa FOR UPDATE en un motor de almacenamiento que usa bloqueo de páginas o registros, los registros examinados por la consulta se bloquean para escritura hasta el final de la transacción actual. Usar LOCK IN SHARE MODE crea un bloqueo compartido que evita a otras transacciones actualizar o borrar los registros examinados. Consulte Sección 15.10.5, “Bloquear lecturas SELECT ... FOR UPDATE y SELECT ... LOCK IN SHARE MODE”.

Tras la palabra clave SELECT , puede usar un número de opciones que afectan la operación del comando.

Las opciones ALL, DISTINCT, and DISTINCTROW especifican si deben retornarse los registros duplicados. Si no se da ninguna de estas opciones, por defecto es ALL (se retornan todos los registros coincidentes). DISTINCT y DISTINCTROW son sinónimos y especifican que los registros duplicados en el conjunto de resultados deben borrarse.

HIGH_PRIORITY, STRAIGHT_JOIN, y opciones que comiencen con SQL_ son extensiones de MySQL al estándar SQL.

HIGH_PRIORITY da a SELECT prioridad más alta que un comando que actualice una tabla. Debe usar esto sólo para consultas que son muy rápidas y deben realizarse una vez. Una consulta SELECT HIGH_PRIORITY que se realiza mientras la tabla está bloqueada para lectura se ejectua incluso si hay un comando de actualización esperando a que se libere la tabla.

HIGH_PRIORITY no puede usarse con comandos SELECT que sean parte de una UNION.

STRAIGHT_JOIN fuerza al optimizador a hacer un join de las tablas en el orden en que se listan en la cláusula FROM . Puede usarlo para acelerar una consulta si el optimizador hace un join con las tablas en orden no óptimo. Consulte Sección 7.2.1, “Sintaxis de EXPLAIN (Obtener información acerca de un SELECT)”. STRAIGHT_JOIN también puede usarse en la lista table_references . Consulte Sección 13.2.7.1, “Sintaxis de JOIN”.

SQL_BIG_RESULT puede usarse con GROUP BY o DISTINCT para decir al optimizador que el conjunto de resultados tiene muchos registros. En este caso, MySQL usa directamente tablas temporales en disco si son necesarias con una clave en los elementos GROUP BY .

SQL_BUFFER_RESULT fuerza a que el resultado se ponga en una tabla temporal . Esto ayuda a MySQL a liberar los bloqueos de tabla rápidamente y ayuda en casos en que tarda mucho tiempo en enviar el resultado al cliente.

SQL_SMALL_RESULT puede usarse con GROUP BY o DISTINCT para decir al optimizador que el conjunto de resultados es pequeño. En este caso, MySQL usa tablas temporales rápidas para almacenar la tabla resultante en lugar de usar ordenación. En MySQL 5.0, esto no hará falta normalmente.

SQL_CALC_FOUND_ROWS le dice a MySQL que calcule cuántos registros habrán en el conjunto de resultados, sin tener en cuenta ninguna cláusula LIMIT. El número de registros pueden encontrarse con SELECT FOUND_ROWS(). Consulte Sección 12.9.3, “Funciones de información”.

SQL_CACHE le dice a MySQL que almacene el resultado de la consulta en la caché de consultas si está usando un valor de query_cache_type de 2 o DEMAND. Para una consulta que use UNION o subconsultas, esta opción afecta a cualquier SELECT en la consulta. Consulte Sección 5.12, “La caché de consultas de MySQL”.

SQL_NO_CACHE le dice a MySQL que no almacene los resultados de consulta en la caché de consultas. Consulte Sección 5.12, “La caché de consultas de MySQL”. Para una consulta que use UNION o subconsultas esta opción afecta a cualquier SELECT en la consulta.

13.2.7.1. Sintaxis de JOIN

MySQL soporta las siguientes sintaxis de JOIN para la parte table_references de comandos SELECT y DELETE y UPDATE de múltiples tablas:

table_reference, table_reference table_reference [INNER | CROSS] JOIN table_reference [join_condition] table_reference STRAIGHT_JOIN table_reference table_reference LEFT [OUTER] JOIN table_reference join_condition table_reference NATURAL [LEFT [OUTER]] JOIN table_reference { ON table_reference LEFT OUTER JOIN table_reference ON conditional_expr } table_reference RIGHT [OUTER] JOIN table_reference join_condition table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

table_reference se define como:

tbl_name [[AS] alias] [[USE INDEX (key_list)] | [IGNORE INDEX (key_list)] | [FORCE INDEX (key_list)]]

join_condition se define como:

ON conditional_expr | USING (column_list)

Generalmente no debería tener ninguna condición en la parte ON que se usa para restringir qué registros desea en el conjunto de resultados, pero en su lugar especificar esas condiciones en la cláusula WHERE . Hay excepciones a esta regla.

La sintaxis { OJ ... LEFT OUTER JOIN ...} mostrada en la lista precedente existe sólo por compatibilidad con ODBC.

Puede oner un alias en una referencia de tabla usando tbl_name AS alias_name o tbl_name alias_name:

mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 -> WHERE t1.name = t2.name; mysql> SELECT t1.name, t2.salary FROM employee t1, info t2 -> WHERE t1.name = t2.name;

El condicional ON es cualquier expresión condicional de la forma que puede usarse en una cláusula WHERE .

Si no hay ningún registro coincidiente para la tabla de la derecha en la parte ON o USING en un LEFT JOIN, se usa un registro con todos las columnas a NULL para la tabla de la derecha. Puede usar este hecho para encontrar registros en una tabla que no tengan contraparte en otra tabla:

mysql> SELECT table1.* FROM table1 -> LEFT JOIN table2 ON table1.id=table2.id -> WHERE table2.id IS NULL;

Este ejemplo encuentra todos los registros en table1 con un valor id no presente en table2 (esto es, todos los registros en table1 sin registro correspondiente en table2). Esto asume que table2.id se declara NOT NULL. Consulte Sección 7.2.9, “Cómo optimiza MySQL los LEFT JOIN y RIGHT JOIN”.

La cláusula USING (column_list) muestra una lista de columnas que deben existir en ambas tablas. Las siguientes dos cláusulas son semánticamente idénticas:

a LEFT JOIN b USING (c1,c2,c3) a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

El NATURAL [LEFT] JOIN de dos tablas se define semánticamente equivalente a un INNER JOIN o LEFT JOIN con una cláusula USING que nombra todas las columnas que existen en ambas tablas.

INNER JOIN y , (coma) son semánticamente equivalentes en la ausencia de una condicicón de join: ambos producen un producto Cartesiano entre las tablas especificadas (esto es, cada registro en la primera tabla se junta con cada registro en la segunda tabla).

RIGHT JOIN funciona análogamente a LEFT JOIN. Para mantener el código portable entre bases de datos, se recomienda que use LEFT JOIN en lugar de RIGHT JOIN.

STRAIGHT_JOIN es idéntico a JOIN, excepto que la tabla de la izquierda se lee siempre antes que la de la derecha. Esto puede usarse para aquéllos casos (escasos) en que el optimizador de join pone las tablas en orden incorrecto.

Puede proporcionar pistas de qué índice debe usar MySQL cuando recibe información de una tabla. Especificando USE INDEX (key_list), puede decirle a MySQL que use sólo uno de los posibles índices para encontrar registros en la tabla. La sintaxis alternativa IGNORE INDEX (key_list) puede usarse para decir a MySQL que no use algún índice particular. Estos trucos son útiles si EXPLAIN muestra que MySQL está usando el índice incorrecto de la lista de posibles índices.

También puede usar FORCE INDEX, que actúa como USE INDEX (key_list) pero con la adición que un escaneo de tabla se asume como operación muy cara. En otras palabras, un escaneo de tabla se usa sólo si no hay forma de usar uno de los índices dados para encontrar registros en la tabla.

USE KEY, IGNORE KEY, y FORCE KEY son sinónimos de USE INDEX, IGNORE INDEX, y FORCE INDEX.

Nota: USE INDEX, IGNORE INDEX, y FORCE INDEX sólo afecta los índices usados cuando MySQL decide cómo encontrar registros en la tabla y cómo hacer el join. No afecta si un índice está en uso cuando se resuelve unORDER BY o GROUP BY.

Algunos ejemplos de join:

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id; mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id); mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id -> LEFT JOIN table3 ON table2.id=table3.id; mysql> SELECT * FROM table1 USE INDEX (key1,key2) -> WHERE key1=1 AND key2=2 AND key3=3; mysql> SELECT * FROM table1 IGNORE INDEX (key3) -> WHERE key1=1 AND key2=2 AND key3=3;

Consulte Sección 7.2.9, “Cómo optimiza MySQL los LEFT JOIN y RIGHT JOIN”.

13.2.7.2. Sintaxis de UNION

SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...]

UNION se usa para combinar el resultado de un número de comandos SELECT en un conjunto de resultados.

Las columnas seleccionadas lisatadas en posiciones correspondientes de cada comando SELECT deben tener el mismo tipo. (Por ejemplo, la primera columna seleccionada por el primer comando debe tener el mismo tipo que la primer columna seleccionada por otros comandos.) Los nombres de columna usados por el primer comando SELECT se usan como nombres de columna para los resultados retornados.

Los comandos SELECT son comandos select normales, pero con las siguientes restricciones:

Sólo el último comando SELECT puede usar INTO OUTFILE.

HIGH_PRIORITY no puede usarse con comandos SELECT que sean parte de una UNION. Si lo especifica para el primer SELECT, no tiene efecto. Si lo especifica para cualquier SELECT posterior, aparece un error de sintaxis.

Si no usa la palabra clave ALL para UNION, todos los registros retornados son únicos, como si hubiera hecho un DISTINCT para el conjunto de resultados total. Si especifica ALL, obtiene todos los registros coincidentes de todos los comandos SELECT usados.

La palabra clave DISTINCT es una palabra opcional que no tiene efecto, pero se permite en la sintaxis como requiere el estándar SQL . (En MySQL, DISTINCT representa el comportamiento por defecto de una union.)

En MySQL 5.0, puede mezclar UNION ALL y UNION DISTINCT en la misma consulta. Tipos de UNION mezclados se tratan de forma que una unión DISTINCT sobreescribe cualquier unión ALL a su izquierda. Una unión DISTINCT puede producirse explícitamente usando UNION DISTINCT o implícitamente usando UNION sin palabra clave DISTINCT o ALL a continuación.

Si quiere usar una cláusula ORDER BY o LIMIT para ordenar o limitar el resultado UNION entero, ponga entre paréntesis los comandos SELECT individuales y ponga el ORDER BY o LIMIT tras el último. El siguiente ejemplo usa ambas cláusulas:

(SELECT a FROM tbl_name WHERE a=10 AND B=1) UNION (SELECT a FROM tbl_name WHERE a=11 AND B=2) ORDER BY a LIMIT 10;

Este tipo de ORDER BY no puede usar referencias de columnas que incluyan un nombre de columna (esto es, nombres en formato tbl_name.col_name ). En su lugar, proporcione un alias de columna al primer comando SELECT y refiérase al alias en el ORDER BY, o a la columna en el ORDER BY usando su posición de columna. (Un alias es preferible porque el uso de la posición de la columna está obsoleto.)

Para aplicar ORDER BY o LIMIT a un SELECT individual, ponga la cláusula dentro de los paréntesis alrededor del SELECT:

(SELECT a FROM tbl_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM tbl_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Los ORDER BY para comandos SELECT individuales entre paréntesis tienen efecto sólo al combinarlos con LIMIT. De otro modo, el ORDER BY se optimiza a parte.

En MySQL 5.0, los tipos y longitudes de las columnas en el conjunto de resultados de una UNION tienen en cuenta los valores recibidos por todos los comandos SELECT. Por ejemplo, considere lo siguiente:

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10); +---------------+ | REPEAT('a',1) | +---------------+ | a | | bbbbbbbbbb | +---------------+

(En alguna versión anterior de MySQL, el segundo registro se habría truncado a una longitud de 1.)

13.2.8. Sintaxis de subconsultas

Una subconsulta es un comando SELECT dentro de otro comando.

MySQL 5.0 soporta todas las formas de subconsultas y operaciones que requiere el estándar SQL, así como algunas características específicas de MySQL.

Aquí hay un ejemplo de subconsulta:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

En este ejemplo, SELECT * FROM t1 ... es la consulta externa (o comando externo), y (SELECT column1 FROM t2) es la subconsulta. Decimos que la subconsulta está anidada dentro de la consulta exterior, y de hecho, es posible anidar subconsultas dentro de otras subconsultas hasta una profundidad considerable. Una subconsulta debe siempre aparecer entre paréntesis.

Las principales ventajas de subconsultas son:

Permiten consultas estructuradas de forma que es posible aislar cada parte de un comando.

Proporcionan un modo alternativo de realizar operaciones que de otro modo necesitarían joins y uniones complejos.

Son, en la opinión de mucha gente, leíbles. De hecho, fue la innovación de las subconsultas lo que dio a la gente la idea original de llamar a SQL “Structured Query Language.”

Aquí hay un comando de ejemplo que muestra los puntos principales de la sintaxis de subconsultas como especifica el estándar SQL y soporta MySQL:

DELETE FROM t1 WHERE s11 > ANY (SELECT COUNT(*) /* no hint */ FROM t2 WHERE NOT EXISTS (SELECT * FROM t3 WHERE ROW(5*t2.s1,77)= (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM (SELECT * FROM t5) AS t5)));

Una subconsulta puede retornar un escalar (un valor único), un registro, una columna o una tabla (uno o más registros de una o más columnas). Éstas se llaman consultas de escalar, columna, registro y tabla. Las subconsultas que retornan una clase particular de resultado a menudo pueden usarse sólo en ciertos contextos, como se describe en las siguientes secciones.

Hay pocas restricciones sobre los tipos de comandos en que pueden usarse las subconsultas. Una subconsulta puede contener cualquiera de las palabras claves o cláusulas que puede contener un SELECT ordinario: DISTINCT, GROUP BY, ORDER BY, LIMIT, joins, trucos de índices, constructores UNION , comentarios, funciones, y así.

Una restricción es que el comando exterior de una subconsulta debe ser: SELECT, INSERT, UPDATE, DELETE, SET, o DO. Otra restricción es que actualmente no puede modificar una tabla y seleccionar de la misma tabla en la subconsulta. Esto se aplica a comandos tales como DELETE, INSERT, REPLACE, y UPDATE. Una discusión más comprensible de las restricciones en las subconsultas se da en Apéndice H, Restricciones en características de MySQL.

13.2.8.1. La subconsulta, como un operador sobre valores escalares

En su forma más sencilla, una subconsulta es una subconsulta escalar que retorna un único valor. Una subconsulta escalar es un operando simple, y puede usarlo prácticamente en cualquier sitio en que un valor de columna o literal sea legal, y puede esperar que tenga las características que tienen todos los operandos: un tipo de datos, una longitud, una indicación de si puede ser NULL, etcétera. Por ejemplo:

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL); INSERT INTO t1 VALUES(100, 'abcde'); SELECT (SELECT s2 FROM t1);

La subconsulta en este SELECT retorna un valor único ('abcde') que tiene un tipo de datos CHAR, una longitud de 5, un conjunto de carácteres y una colación iguales a la que había por defecto cuando se realizó el CREATE TABLE , y una indicación que el valor en la columna puede ser NULL. De hecho, casi todas las consultas pueden ser NULL. Si la tabla usada en este ejemplo estuviese vacía, la tabla de la subconsulta sería NULL.

Hay algunos contextos en que una subconsulta escalar no se puede usar. Si un comando permite sólo un valor literal, no puede usar una subconsulta. Por ejemplo, LIMIT necesita argumentos enteros, y LOAD DATA necesita una cadena con un nombre de fichero. No puede usar subconsultas para proporcionar estos valores.

Cuando vea los ejemplos en las siguientes secciones que contengan el constructor (SELECT column1 FROM t1), imagine que su própio código contiene construcciones mucho más diversas y complejas.

Por ejemplo, suponga que hacemos dos tablas:

CREATE TABLE t1 (s1 INT); INSERT INTO t1 VALUES (1); CREATE TABLE t2 (s1 INT); INSERT INTO t2 VALUES (2);

Luego realice SELECT:

SELECT (SELECT s1 FROM t2) FROM t1;

El resultado es 2 ya que hay un registro en t2 que contiene una columna s1 con un valor de 2.

Una subconsulta escalar puede ser parte de una expresión. No olvide los paréntesis, incluso si la subconsulta es un operando que proporciona un argumento para una función. Por ejemplo:

SELECT UPPER((SELECT s1 FROM t1)) FROM t2;

13.2.8.2. Uso de subconsultas en subconsultas

El uso más común de una subconsulta es de la forma:

non_subquery_operand comparison_operator (subquery)

Donde comparison_operator es uno de estos operadores:

= > < >= <= <>

Por ejemplo:

... 'a' = (SELECT column1 FROM t1)

Hace tiempo el único sitio legal para una subconsulta fue en la parte derecha de la comparación, y puede encontrar algunos SGBDs que insistan en ello.

Aquí hay un ejemplo de una comparación común de subconsultas que no puede hacer mediante un join. Encuentra todos los valores en la tabla t1 que son iguales a un valor máximo en la tabla t2:

SELECT column1 FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2);

Aquí hay otro ejemplo, que de nuevo es imposible de hacer con un join ya que involucra agregación para una de las tablas. Encuentra todos los registros en la tabla t1 que contengan un valor que ocurre dos veces en una columna dada:

SELECT * FROM t1 AS t WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

Para una comparación realizada con uno de estos operadores, la subconsulta debe retornar un escalar, con la excepción que = puede usarse con subconsultas de registro. Consulte Sección 13.2.8.5, “Subconsultas de registro”.

13.2.8.3. Subconsultas con ANY, IN y SOME

Sintaxis:

operand comparison_operator ANY (subquery) operand IN (subquery) operand comparison_operator SOME (subquery)

La palabra clave ANY , que debe seguir a un operador de comparación, significa “return TRUE si la comparación es TRUE para ANY (cualquiera) de los valores en la columna que retorna la subconsulta.” Por ejemplo:

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

Suponga que hay un registro en una tabla t1 que contiene (10). La expresión es TRUE si la tabla t2 contiene (21,14,7) ya que hay un valor 7 en t2 que es menor que 10. La expresión es FALSE si la tabla t2 contiene (20,10), o si la tabla t2 está vacía. La expresión es UNKNOWN si la tabla t2 contiene (NULL,NULL,NULL).

La palabra IN es un alias para = ANY. Por lo tanto, estos dos comandos son lo mismo:

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2); SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

Sin embargo, NOT IN no es un alias para <> ANY, sino para <> ALL. Consulte Sección 13.2.8.4, “Subconsultas con ALL”.

La palabra SOME es un alias para ANY. Por lo tanto, estos dos comandos son el mismo:

SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2); SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

El uso de la palabra SOME es raro, pero este ejemplo muestra cómo puede ser útil. Para la mayoría de gente, la frase en inglés “a is not equal to any b” significa “there is no b which is equal to a,” pero eso no es lo que quiere decir la sintaxis SQL. La sintaxis significa “there is some b to which a is not equal.” Usando <> SOME en su lugar ayuda a asegurar que todo el mundo entiende el significado de la consulta.

13.2.8.4. Subconsultas con ALL

Sintaxis:

operand comparison_operator ALL (subquery)

La palabra ALL, que debe seguir a un operador de comparación, significa “return TRUE si la comparación es TRUE para ALL todos los valores en la columna que retorna la subconsulta.” Por ejemplo:

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

Suponga que hay un registro en la tabla t1 que contiene (10). La expresión es TRUE si la tabla t2 contiene (-5,0,+5) ya que 10 es mayor que los otros tres valores en t2. La expresión es FALSE si la tabla t2 contiene (12,6,NULL,-100) ya que hay un único valor 12 en la tabla t2 mayor que 10. La expresión es UNKNOWN si la tabla t2 contiene (0,NULL,1).

Finalmente, si la tabla t2 está vacía, el resultado es TRUE. Puede pensar que el resultado debería ser UNKNOWN, pero lo sentimos, es TRUE. Así, aunque extraño, el siguiente comando es TRUE cuando la tabla t2 está vacía:

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);

Pero este comando es UNKNOWN cuando la tabla t2 está vacía:

SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);

Además, el siguiente comando es UNKNOWN cuando la tabla t2 está vacía:

SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);

En general, las tablas con valores NULL y las tablas vacías son casos extremos. Al escribir código para subconsultas, siempre considere si ha tenido en cuenta estas dos posibilidades.

NOT IN es un alias para <> ALL. Por lo tanto, estos dos comandos son equivalentes:

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2); SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

13.2.8.5. Subconsultas de registro

La discusión en este punto ha sido entre subconsultas escalares o de columnas, esto es, subcolumnas que retornan un único valor o una columna de valores. Una subconsulta de registro es una variante de subconsulta que retorna un único registro y por lo tanto retorna más de un valor de columna. Aquí hay dos ejemplos:

SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2); SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);

Las consultas aquí son ambas TRUE si la tabla t2 tiene un registro en que column1 = 1 y column2 = 2.

Las expresiones (1,2) y ROW(1,2) a veces se llaman constructores de registros. Ambos son equivalentes. También son legales en otros contextos. Por ejemplo, los siguientes dos comandos son semánticamente equivalentes (aunque actualmente sólo puede optimizarse el segundo):

SELECT * FROM t1 WHERE (column1,column2) = (1,1); SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

El uso normal de constructores de registros, sin embargo, es para comparaciones con subconsultas que retornan dos o más columnas. Por ejemplo, la siguiente consulta responde a la petición, “encuentra todos los registros en la tabla t1 que también existen en la tabla t2”:

SELECT column1,column2,column3 FROM t1 WHERE (column1,column2,column3) IN (SELECT column1,column2,column3 FROM t2);

13.2.8.6. EXISTS y NOT EXISTS

Si una subconsulta no retorna ningún registro, entonces EXISTS subquery es TRUE, y NOT EXISTSsubquery es FALSE. Por ejemplo:

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

Tradicionalmente, una subconsulta EXISTS comienza con SELECT *, pero puede comenzar con SELECT 5 o SELECT col1 o nada. MySQL ignora la lista SELECT en tales subconsultas, así que no hace distinción.

Para el ejemplo precedente, si t2 contiene algún registro, incluso registros sólo con valores NULL entonces la condición EXISTS es TRUE. Este es un ejemplo poco probable, ya que prácticamente siempre una subconsulta [NOT] EXISTS contiene correlaciones. Aquí hay algunos ejemplos más realistas:

¿Qué clase de tienda hay en una o más ciudades?

SELECT DISTINCT store_type FROM Stores WHERE EXISTS (SELECT * FROM Cities_Stores WHERE Cities_Stores.store_type = Stores.store_type);

¿Qué clase de tienda no hay en ninguna ciudad?

SELECT DISTINCT store_type FROM Stores WHERE NOT EXISTS (SELECT * FROM Cities_Stores WHERE Cities_Stores.store_type = Stores.store_type);

¿Qué clase de tienda hay en todas las ciudades?

SELECT DISTINCT store_type FROM Stores S1 WHERE NOT EXISTS ( SELECT * FROM Cities WHERE NOT EXISTS ( SELECT * FROM Cities_Stores WHERE Cities_Stores.city = Cities.city AND Cities_Stores.store_type = Stores.store_type));

El último ejemplo es un doblemente anidado NOT EXISTS . Esto es, tiene una cláusula NOT EXISTS dentro de otra NOT EXISTS. Formalmente, responde a la pregunta “¿existe una ciudad con una tienda que no esté en Stores?” Sin embargo, es más fácil decir que un NOT EXISTS responde a la pregunta “¿es x TRUE para todo y?”

13.2.8.7. Subconsultas correlacionadas

Una subconsulta correlacionada es una subconsulta que contiene una referencia a una tabla que también aparece en la consulta exterior. Por ejemplo:

SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);

Tenga en cuenta que la subconsulta contiene una referencia a una columna de t1, incluso aunque la cláusula FROM de la subconsulta no menciona una tabla t1. Por lo tanto, MySQL busca fuera de la subconsulta y encuentra t1 en la consulta externa.

Suponga que la tabla t1 contiene un registro en que column1 = 5 y column2 = 6; mientras, la tabla t2 contiene un registro en que column1 = 5 y column2 = 7. La expresión ... WHERE column1 = ANY (SELECT column1 FROM t2) sería TRUE, pero en este ejemplo, la cláusula WHERE dentro de la subconsulta es FALSE (ya que (5,6) no es igual a (5,7)), así que la subconsulta como un todo es FALSE.

Regla de visibilidad: MySQL evalúa desde dentro hacia fuera. Por ejemplo:

SELECT column1 FROM t1 AS x WHERE x.column1 = (SELECT column1 FROM t2 AS x WHERE x.column1 = (SELECT column1 FROM t3 WHERE x.column2 = t3.column1));

En este comando, x.column2 debe ser una columna en la tabla t2 ya que SELECT column1 FROM t2 AS x ... renombra t2. No hay una columna en la tabla t1 porque SELECT column1 FROM t1 ... es una consulta externa que está demasiado fuera.

Para subconsultas en cláusulas HAVING o ORDER BY , MySQL busca nombres de columna en la lista de selección exterior.

Para ciertos casos, una subconsulta correlacionada es óptima. Por ejemplo:

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

De otro modo, son ineficientes y lentas. Reescribir la consulta como un join puede mejorar el rendimiento.

Las subconsultas correlatadas no pueden referirse a los resultados de funciones agregadas de la consulta exterior.

13.2.8.8. Subconsultas en la cláusula FROM

Las subconsultas son legales en la cláusula FROM de un comando SELECT. La sintaxis que vería es:

SELECT ... FROM (subquery) [AS] name ...

La cláusula [AS]name es obligatoria, ya que cada tabla en la cláusula FROM debe tener un nombre. Cualquier columna en la lista selecta de la subquery debe tener nombre único. Puede encontrar esta sintaxis descrita en este manual, dónde se usa el término “tablas derivadas.”

Asuma que tiene la tabla:

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

Aquí se muestra cómo usar una subconsulta en la cláusula FROM usando la tabla de ejemplo:

INSERT INTO t1 VALUES (1,'1',1.0); INSERT INTO t1 VALUES (2,'2',2.0); SELECT sb1,sb2,sb3 FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb WHERE sb1 > 1;

Resultado: 2, '2', 4.0.

Aquí hay otro ejemplo: suponga que quiere conocer la media de un conjunto de sumas para una tabla agrupada. Esto no funcionaría:

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

Sin embargo, esta consulta proporciona la información deseada:

SELECT AVG(sum_column1) FROM (SELECT SUM(column1) AS sum_column1 FROM t1 GROUP BY column1) AS t1;

Tenga en cuenta que el nombre de columna usado dentro de la subconsultas (sum_column1) se reconoce en la consulta exterior.

Las subconsultas en la cláusula FROM pueden retornar un escalar, columna, registro o tabla. De momento, las subconsultas en la cláusula FROM no pueden ser subconsultas correladas.

Las subconsultas en la cláusula FROM se ejecutan incluso para el comando EXPLAIN (esto es, se construyen las tablas temporales derivadas). Esto ocurre porque las consultas de niveles superiores necesitan información acerca de todas las tablas durante la fase de optimización.

13.2.8.9. Errores en subconsultas

Hay algunos retornos de error nuevos que se aplican sólo a subconsultas. Esta sección los agrupa ya que revisarlos ayuda a recordar algunos puntos importantes.

Número incorrecto de columnas de la subconsulta:

ERROR 1241 (ER_OPERAND_COL) SQLSTATE = 21000 Message = "Operand should contain 1 column(s)"

Este error ocurre en casos como este:

SELECT (SELECT column1, column2 FROM t2) FROM t1;

Se permite usar una subconsulta que retorne múltiples columnas, si el propósito es la comparación. Consulte Sección 13.2.8.5, “Subconsultas de registro”. Sin embargo, en otros contextos, la subconsulta debe ser un operando escalar.

Número incorrecto de registros de la subconsulta:

ERROR 1242 (ER_SUBSELECT_NO_1_ROW) SQLSTATE = 21000 Message = "Subquery returns more than 1 row"

Este error ocurre de comandos en que la subconsulta retorna más de un registro. Considere el siguiente ejemplo:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

Si SELECT column1 FROM t2 retorna sólo un registro la consulta anterior funcionará. Si la subconsulta retorna más de un registro, ocurre el error 1242 . En ese caso, la consulta debe reescribirse como:

SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);

Tabla usada incorrectamente en la subconsulta:

Error 1093 (ER_UPDATE_TABLE_USED) SQLSTATE = HY000 Message = "You can't specify target table 'x' for update in FROM clause"

Este error ocurre en casos como el siguiente:

UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);

Puede usar una subconsulta para asignaciones dentro del comando UPDATE, ya que las subconsultas son legales en los comandos UPDATE y DELETE así como en los SELECT. Sin embargo, no puede usar la misma tabla (en este caso la tabla t1) para la cláusula FROM de la subconsulta y el objetivo a actualizar.

Para motores transaccionales, el fallo de una subconsulta provoca que falle el comando entero. Para motores no transaccionales, las modificaciones de datos hechas antes de encontrar el error se preservan.

13.2.8.10. Optimizar subconsultas

El desarrollo está en marcha, por lo que no hay trucos de optimización fiables a largo plazo. Algunos trucos interesantes que puede usar son:

Use cláusulas de subconsulta que afecten al número u orden de los registros en la subconsulta. Por ejemplo:

SELECT * FROM t1 WHERE t1.column1 IN (SELECT column1 FROM t2 ORDER BY column1); SELECT * FROM t1 WHERE t1.column1 IN (SELECT DISTINCT column1 FROM t2); SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 LIMIT 1);

Reemplace un join con una subconsulta. Por ejemplo, pruebe:

SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN ( SELECT column1 FROM t2);

En lugar de:

SELECT DISTINCT t1.column1 FROM t1, t2 WHERE t1.column1 = t2.column1;

Algunas subconsultas pueden transformarse en joins por compatibilidad con versiones anteriores de MySQL que no soportan subconsultas. Sin embargo, en algunos casos, incluso en MySQL 5.0, convertir una subconsulta en un join puede mejorar el rendimiento. Consulte Sección 13.2.8.11, “Re-escribir subconsultas como joins en versiones de MySQL anteriores”.

Mueva las cláusulas desde fuera hacia dentro en la subconsulta. Por ejemplo , use esta consulta:

SELECT * FROM t1 WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);

En lugar de:

SELECT * FROM t1 WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);

Otro ejemplo. Use esta consulta:

SELECT (SELECT column1 + 5 FROM t1) FROM t2;

En lugar de:

SELECT (SELECT column1 FROM t1) + 5 FROM t2;

Use una subconsulta de registro en lugar de una subconsulta correlacionada . Por ejemplo, use:

SELECT * FROM t1 WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);

En lugar de:

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1 AND t2.column2=t1.column2);

Use NOT (a = ANY (...)) en lugar de a <> ALL (...).

Use x = ANY (table containing (1,2)) en lugar de x=1 OR x=2.

Use = ANY en lugar de EXISTS.

Para subconsultas no correlacionadas que siempre retornan un registro, IN siempre es más lento que =. Por ejemplo, use esta consulta:

SELECT * FROM t1 WHERE t1.col_name = (SELECT a FROM t2 WHERE b = some_const);

En lugar de:

SELECT * FROM t1 WHERE t1.col_name IN (SELECT a FROM t2 WHERE b = some_const);

Estos trucos pueden hacer que los programas vayan más rápidos o lentos. Usar recursos MySQL como la función BENCHMARK() es una buena idea para ver cuáles funcionan.

Algunas optimizaciones que realiza MySQL son:

MySQL ejecuta subconsultas no correlacionadas sólo una vez. Use EXPLAIN para asegurar que una subconsulta dada realmente no está correlacionada.

MySQL reescribe subconsultas IN, ALL, ANY, y SOME para aprovechar que las columnas de la lista de select de la subconsulta está indexada.

MySQL reemplaza subconsultas de la siguiente forma con una función de búsqueda de índice, que EXPLAIN describe como tipo especial de join (unique_subquery o index_subquery):

... IN (SELECT indexed_column FROM single_table ...)

MySQL mejora expresiones de la siguiente forma con una expresión que involucre MIN() o MAX(), a no ser que hayan involucrados valores NULL o conjuntos vacíos:

value {ALL|ANY|SOME} {> | < | >= | <=} (non-correlated subquery)

Por ejemplo, esta cláusula WHERE:

WHERE 5 > ALL (SELECT x FROM t)

puede tratarse por el optimizador como:

WHERE 5 > (SELECT MAX(x) FROM t)

Hay un capítulo titulado “Cómo transforma las subconsultas MySQL” en el manual MySQL Internals Manual. Puede obtener este documento descargando el paquete fuente MySQL y buscando un fichero llamado internals.texi en el directorio Docs .

13.2.8.11. Re-escribir subconsultas como joins en versiones de MySQL anteriores

En versiones prévias de MySQL (anteriores a la MySQL 4.1), sólo se soportaban consultas anidadas de la forma INSERT ... SELECT ... y REPLACE ... SELECT .... Este no es el caso en MySQL 5.0, pero es cierto que hay a veces otras formas de testear la pertenencia a un grupo de valores. También es cierto que en algunas ocasiones, no es sólo posible reescribir una consulta sin una subconsulta, sino que puede ser más eficiente hacerlo que usar subconsultas. Una de las técnicas disponibles es usar el constructor IN() :

Por ejemplo, esta consulta:

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

Puede reescribirse como:

SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id;

Las consultas:

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2); SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

Pueden reescribirse usando IN():

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;

Un LEFT [OUTER] JOIN puede ser más rápido que la subconsulta equivalente ya que el servidor puede ser capaz de optimizarlo mejor — este es un hecho no específico de MySQL Server . Antes de SQL-92, los outer joins no existían, así que las subconsultas eran el único modo de hacer ciertas cosas. Hoy, MySQL Server y otros sistemas de bases de datos ofrecen un ámplio rango de tipos de outer join.

MySQL Server soporta comandos DELETE para múltiples tablas que pueden usarse para borrar registros basándose en la información de una tabla o de varias al mismo tiempo. Los comandos UPDATE para múltiples tablas también se soportan en MySQL 5.0.

13.2.9. Sintaxis de TRUNCATE

TRUNCATE TABLE tbl_name

TRUNCATE TABLE vacía una tabla completamente. Lógicamente, esto es equivalente a un comando DELETE que borre todos los registros, pero hay diferencias prácticas bajo ciertas circunstáncias.

Para InnoDB antes de la versión 5.0.3, TRUNCATE TABLE se mapea a DELETE, así que no hay diferencia. A partir de MySQL/InnoDB-5.0.3, está disponible TRUNCATE TABLE muy rápido. La operación se mapea a DELETE si hay restricciones de clave foránea que referencien la tabla.

Para otros motores, TRUNCATE TABLE difiere de DELETE FROM en los siguientes puntos en MySQL 5.0:

Las operaciones de truncado destruyen y recrean la tabla, que es mucho más rápido que borrar registros uno a uno.

Las operaciones de truncado no son transaccionales; ocurre un error al intentar un truncado durante una transacción o un bloqueo de tabla.

No se retorna el número de registros borrados.

Mientras el fichero de definición de la tabla tbl_name.frm sea válido, la tabla puede recrearse como una vacía con TRUNCATE TABLE, incluso si los ficheros de datos o de índice se han corrompido.

El tratador de tablas no recuerda el último valor AUTO_INCREMENT usado, pero empieza a contar desde el principio. Esto es cierto incluso para MyISAM y InnoDB, que normalmente no reúsan valores de secuencia.

TRUNCATE TABLE es una extensión de Oracle SQL adoptada en MySQL.

13.2.10. Sintaxis de UPDATE

Sintaxis para una tabla:

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

Sintaxis para múltiples tablas:

UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]

El comando UPDATE actualiza columnas en registros de tabla existentes con nuevos valores. La cláusula SET indica qué columna modificar y los valores que puede recibir. La cláusula WHERE , si se da, especifica qué registros deben actualizarse. De otro modo, se actualizan todos los registros. Si la cláusula ORDER BY se especifica, los registros se actualizan en el orden que se especifica. La cláusula LIMIT es el límite de registros a actualizar.

El comando UPDATE soporta los siguientes modificadores:

Si usa la palabra clave LOW_PRIORITY , la ejecución de UPDATE se retrasa hasta que no haya otros clientes leyendo de la tabla.

Si usa la palabra clave IGNORE, el comando de actualización no aborta incluso si ocurren errores durante la actualización. Los registros que presenten conflictos de clave duplicada no se actualizan. Los registros cuyas columnas se actualizan a valores que provocarían errores de conversión de datos se actualizan al valor válido más próximo.

Si accede a una columna de tbl_name en una expresión, UPDATE usa el valora ctual de la columna. Por ejemplo, el siguiente comando pone la columna age a uno más que su valor actual:

mysql> UPDATE persondata SET age=age+1;

Las asignaciones UPDATE se avalúna de izquierda a derecha. Por ejemplo, el siguiente comando dobla la columna age y luego la incrementa:

mysql> UPDATE persondata SET age=age*2, age=age+1;

Si pone en una columna el valor que tiene actualmente, MySQL se da cuenta y no la actualiza.

Si actualiza una columna declarada como NOT NULL con un valor NULL, la columna recibe el valor por defecto apropiado para el tipo de la columna y se incrementa el contador de advertencias. El valor por defecto es 0 para tipos numéricos, la cadena vacía ('') para tipos de cadena, y el valor “cero” para valores de fecha y hora.

UPDATE retorna el número de registros que se cambian. En MySQL 5.0, la función mysql_info() de la API de C retorna el número de registros coincidentes actualizados y el número de advertencias que ocurren durante el UPDATE.

Puede usar LIMITrow_count para restringir el alcance del UPDATE. Una cláusula LIMIT es una restricción de registros coincidentes. El comando para en cuanto encuentra row_count registos que satisfagan la cláusula WHERE , tanto si han sido cambiados como si no.

Si un comando UPDATE incluye una cláusula ORDER BY, los registros se actualizan en el orden especificado por la cláusula.

Puede realizar operaciones UPDATE que cubran varias tablas. La parte table_references lista las tablas involucradas en el join. Su sintaxis se describe ámpliamente en Sección 13.2.7.1, “Sintaxis de JOIN”. Aquí hay un ejemplo:

UPDATE items,month SET items.price=month.price WHERE items.id=month.id;

Este ejemplo muestra un inner join usando el operador coma, pero los comandos UPDATE de múltiples tablas pueden usar cualquier tipo de join permitido en comandos SELECT tales como LEFT JOIN.

Nota: No puede usar ORDER BY o LIMIT con un UPDATE de múltiples tablas.

En MySQL 5.0, necesita el permiso UPDATE sólo para columnas referenciadas en un UPDATE de múltiples tablas que se actualizan realmente. Necesita sólo el permiso SELECT para algunas columnas que se leen pero no se modifican.

Si usa un comando UPDATE de múltiples tablas que involucren tablas InnoDB con restricciones de claves foráneas, el optimizador de MySQL puede procesar tablas en un orden distinto al de la relación padre/hijo. En este caso, el comando fall y hace un roll back. En su lugar, actualice una única tabla y confíen en las capacidades de ON UPDATE que proporciona InnoDB para que el resto de tablas se modifiquen acórdemente. Consulte Sección 15.6.4, “Restricciones (constraints) FOREIGN KEY”.

Actualmente, no puede actualizar una tabla y seleccionar de la misma en una subconsulta.

previous page start next page

13.2. Sentencias de manipulación de datos (Data Manipulation Statements)

MySQL 5.0