mysql.h


LOAD DATA

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES 
        [STARTING BY '']    
        [TERMINATED BY '\n']
    ]
    [IGNORE number LINES]
    [(col_name,...)]

La sentencia LOAD DATA INFILE lee filas desde un fichero de texto a una tabla a gran velocidad. Si se especifica la palabra LOCAL, se interpreta con respecto al cliente final de la conexión. Cuando se especifica LOCAL, el fichero es leído por el programa del cliente en el ordenador cliente y se envía al servidor. Si no se especifica LOCAL, el fichero debe estar en el ordenador servidor y es leído directamente por el servidor. (LOCAL está disponible desde la versión 3.22.6 de MySQL.)

Por razones de seguridad, cuando se leen ficheros de texto del servidor, los ficheros deben residir en el directorio de la base de datos o tener acceso para todos. Además, para usar LOAD DATA INFILE en ficheros del servidor, se debe poseer el privilegio FILE en el ordenador servidor.

Desde MySQL 3.23.49 y MySQL 4.0.2 (4.0.13 en Windows), LOCAL funcionará sólo si el servidor y el cliente han sido configurados para admitirlo. Por ejemplo, si mysqld se ha arrancado con --local-infile=0, LOCAL no funcionará.

Si se especifica LOW_PRIORITY, la ejecución de la sentencia LOAD DATA se retrasará hasta que no haya otros clientes leyendo de la tabla.

Si se especifica CONCURRENT con una tabla MyISAM, entonces otros hilos pueden recuperar datos desde la tabla mientras LOAD DATA se está ejecutando. Usar esta opción puede afectar un poco, obviamente, al rendimiento de LOAD DATA salvo si no hay otros hilos usando la tabla al mismo tiempo.

Usar LOCAL puede ser un poco más lento que hacer la lectura desde ficheros del servidor directamente, ya que el contenido del fichero debe ser enviado a través de la conexión entre el cliente y el servidor. Por otra parte, no se necesita el privilegio FILE para cargar ficheros locales.

Si se usa una versión de MySQL anterior a 3.23.24 no será posible leer desde un FIFO con LOAD DATA INFILE. Si se necesita leer desde un FIFO (por ejemplo la salida de gunzip), se debe usar LOAD DATA LOCAL INFILE en su lugar.

También se pueden cargar ficheros de datos usando la utilidad mysqlimport; funciona mediante el envío de un comando LOAD DATA INFILE al servidor. La opción --local hace que mysqlimport lea ficheros de datos desde el ordenador cliente. Se puede especificar la opción --compress para un mayor rendimiento sobre redes lentas si el cliente y el servidor soportan protocolos comprimidos.

Cuando se usan ficheros en el ordenador servidor, éste usa las siguientes reglas:

  • Si se proporciona un camino completo, el servidor usa el camino tal cual.
  • Si se usa un camino relativo con uno o más componentes previos, el servidor busca el fichero relativa al directorio de datos del servidor.
  • Si se proporciona un nombre de fichero sin componentes previos, el servidor busca en el directorio de la base de datos actual.

Estas reglas implican que un fichero con el nombre './myfile.txt' se leerá desde el directotio de datos del servidor, mientras que el mismo nombre de fichero, escrito como 'myfile.txt' se leerá desde el directorio de base de datos de la base de datos actual. Por ejemplo, las siguientes sentecias LOAD DATA leen el fichero 'data.txt' desde el directoio de base de datos para db1 porque db1 es la base de datos actual, aunque la sentencia carga el fichero explícitamente en una tabla de la base de datos db2:

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

Las palabras clave REPLACE y IGNORE controlan la manipulación de los registros de entrada que coincidan con registros existentes con valores de clave únicos.

Si se especifica REPLACE, las filas de entrada reemplazan a las filas existentes (en otras palabras, filas que tengan el mismo valor para un índice primario o único que una fila existente).

Si se especifica IGNORE, las filas de entrada que dupliquen una fila existente en un valor de clave único serán ignoradas. Si no se especifica ninguna de las dos opciones, el comportamiento depende de si se ha especificado o no la palabra clave LOCAL. Sin LOCAL, ocurrirá un error cuando se encuentre una valor de clave duplicado, y el resto del fichero de texto será ignorado. 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 el transcurso de la operación.

Si se quiere ignorar la restricción de claves foráneas durante la carga se puede hacer SET FOREIGN_KEY_CHECKS=0 antes de ejecutar LOAD DATA.

Si se usa LOAD DATA INFILE en una tabla MyISAM vacía, todos los índices no únicos será creados en un proceso separado (como en REPAIR TABLE). Normalmente, esto hace que LOAD DATA INFILE sea mucho más rápido cuando se tienen muchos índices. Generalmente esto es muy rápido, pero en algunos casos extremos se pueden crear los índices más rápidamente desconectándolos con ALTER TABLE .. DISABLE KEYS y usando ALTER TABLE .. ENABLE KEYS para reconstruir los índices.

LOAD DATA INFILE es el complemento de SELECT ... INTO OUTFILE. Para escribir datos desde una tabla a un fichero, usar SELECT ... INTO OUTFILE. Para leer el fichero de nuevo a una tabla, usar LOAD DATA INFILE. La sintaxis de las cláusulas FIELDS y LINES es la misma en ambos comandos. Las dos cláusulas son opcionales, pero FIELDS debe preceder a LINES si ambas se especifican.

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

Si no se especifica una cláusula FIELDS, por defecto es lo mismo que si se escribe:

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

Si no se especifica una cláusula LINES, por defecto es lo mismo que si se escribe:

LINES TERMINATED BY '\n'

Nota: si se ha generado el fichero de texto en un sistema Windows se debe cambiar lo anterior por: LINES TERMINATED BY '\r\n' ya que Windows usa dos caracteres como terminador de línea. Algunos programas, como wordpad, pueden usar \r como terminado de línea.

Si todas las líneas que se quieren leer tienen un prefijo en común que se quiere ignorar, se puede usar LINES STARTING BY prefix_string para ello.

Es decir, por defecto LOAD DATA INFILE actúa como sigue:

  • Busca los límites de línea en los cambios de línea.
  • Si se usa LINES STARTING BY prefijo, lee hasta que se encuentra el prefijo y empieza a leer en el carácter siguiente al prefijo. Si la línea no incluye el prefijo será ignorada.
  • Rompe las líneas en campos usando los tabuladores.
  • No espera que los campos estén encerrados entre ningún tipo de comillas.
  • Interpreta los tabuladores, retornos de línea o '\' precedidos por '\' como caracteres literales que forman parte de los valores de campos.

Por el contrario, por defecto SELECT ... INTO OUTFILE:

  • Escribe tabuladores entre campos.
  • No encierra los campos entre comillas.
  • Usa '\' para escapar las apariciones de tabuladores, cambios de línea o '\' que ocurran dentro de valores de campos.
  • Escribe cambios de línea al final de las líneas.

Para escribir FIELDS ESCAPED BY '\\', se deben especificar dos barras para el valor que se leerá como una barra sencilla.

La opción IGNORE número LINES se puede usar para ignorar líneas al comienzo del fichero. Por ejemplo, se puede usar IGNORE 1 LINES para ignorar una línea inicial de cabecera que contenga los nombres de columnas:

mysql> LOAD DATA INFILE "/tmp/file_name" INTO TABLE test IGNORE 1 LINES;

Cuando se usa SELECT ... INTO OUTFILE en un tande con LOAD DATA INFILE para escribir datos desde una base de datos a un fichero y a continuación leer el fichero de nuevo en la base de datos, las opciones de manipulación de campos y líneas para ambos comandos deben coincidir. De otro modo, LOAD DATA INFILE puede no interpretar el contenido del fichero apropiadamente. Supongamos que se usa SELECT ... INTO OUTFILE para escribir un fichero con campos delimitados con comas:

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

Para leer el fichero delimitado con comas, la sentencia correcta será:

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

Si en lugar de eso se intenta leer el ficehro con la sentencia mostrada abajo, no funcionará porque indica a LOAD DATA INFILE que lea usando tabuladores entre los campos:

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

El resultado probable es que cada línea leída se interprete como un único campo.

LOAD DATA INFILE puede usarse también para leer ficheros obtenidos desde fuentes externas. Por ejemplo, un fichero en formato dBASE puede tener campos separados con comas y encerrados entre comillas dobles. Si las líneas del fichero terminan con caracteres de cambio de línea, el comando siguiente ilustra las opciones de manipulación de campos y líneas que se deben usar para cargar el fichero:

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

Cualquiera de las opciones de manipulación, de campo o de línea puede se una cadena vacía (''). Si no está vacía, los valores para FIELDS [OPTIONALLY] ENCLOSED BY y FIELDS ESCAPED BY deben ser un único carácter. Los valores FIELDS TERMINATED BY y LINES TERMINATED BY pueden ser más de un carácter. Por ejemplo, para escribir líneas que terminen con parejas de retorno de línea y avance de línea o para leer un fichero que contenga esas líneas, se especifica una cláusula LINES TERMINATED BY '\r\n'.

Por ejemplo, para leer un fichero con bromas, que están separadas con una línea de %%, dentro de una tabla SQL se puede hacer:

CREATE TABLE jokes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, joke 
    TEXT NOT NULL);
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 campos. Para salida (SELECT ... INTO OUTFILE), si se omite la parabla OPTIONALLY, todos los campos se encerrarán entre el carácter ENCLOSED BY. Un ejemplo de esa salida así (usando una coma como delimitador de campo) es este:

"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 se especifica OPTIONALLY, el carácter ENCLOSED BY se usa sólo para encerrar campos de tipos CHAR y VARCHAR:

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

Se puede ver que las ocurrencias del carácter ENCLOSED BY dentro del valor de campo se escapan precediéndolas con el carácter ESCAPED BY. También se puede ver que si se especifica un valor vacío en ESCAPED BY, es posible generar una salida que no puede ser leída apropiadamente por LOAD DATA INFILE. Por ejemplo, la salida anterior se muestra a continuación tal como aparecería si el carácter de escape fuese vacío. El segundo campo en la cuarta línea contiene una coma seguida de unas comillas dobles, el cual (erroneamente) aparenta la terminación de un 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 la entrada, el carácter ENCLOSED BY, si está presente, se extrae de los extremos de los valores de campo. (Esto es cierto siempre que se especifique OPTIONALLY; OPTIONALLY no tiene efecto en la interpretación de entradas.) Las apariciones del carácter ENCLOSED BY precedidas por el carácter ESCAPED BY se interpretan como parte del valor del campo actual.

Si el campo empieza con el carácter ENCLOSED BY, las apariciones de ese carácter se reconocen como el valor de terminación del campo sólo si va seguido por el separador de campo o por la secuencia de TERMINATED BY. Para impredir ambigüedad, las apariciones del carácter ENCLOSED BY en el interior de un valor de campo pueden ser duplicadas y serán interpretadas como una aparición sencilla del carácter. Por ejemplo, si se especifica ENCLOSED BY '"', las comillas se manipulan así:

"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 caracteres especiales. Si el carácter FIELDS ESCAPED BY no es vacío, se usa como prefijo para los siguientes caracteres en las salidas:

  • 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 (que se escribe actualmente siguiendo el carácter de escape por ASCII '0', y no un byte de valor cero).

Si el carácter FIELDS ESCAPED BY es vacío, no se escapan caracteres. Probablemente no sea una buena idea especificar un carácter vacío como carácter de escape, particularmente si los valores de campos en los datos contienen cualquiera de los caracteres en la lista anterior.

Para entrada, si el carácter FIELDS ESCAPED BY no es vacío, las apariciones de ese carácter se eliminan y se toma el siguiente carácter literalmente como parte del valor del campo. Las excepciones son los caracteres '0' y 'N' (por ejemplo, \0 o \N si el carácter de escape es '\'). Estas secuencias se interpretan como ASCII 0 (un byte de valor cero) y NULL. Mirar más abajo para ver las reglas de manipulación de NULL.

En ciertos casos, las opciones de manipulación de campos y líneas interactual:

  • Si LINES TERMINATED BY es una cadena vacía y FIELDS TERMINATED BY es un carácter no vacío, las líneas son terminadas también con el carácter FIELDS TERMINATED BY.
  • Si los valores para FIELDS TERMINATED BY y FIELDS ENCLOSED BY son ambos vacíos (''), se usa un formato de fila fija (no delimitada). Con el formato de fila fija, no se usan delimitadores entre campos (pero aún es posible tener un terminador de línea). En su lugar, los valores de columna se escriben y leen usando las anchuras de visualización de las columnas. Por ejemplo, si una columna se declara como INT(7), los valores para la columna se escriben usando campos de siete caracteres. En la entrada, los valores para la columna se obtienen leyendo siete caracteres. LINES TERMINATED BY se sigue usando para separar líneas. Si una línea no contiene todos los campos, el resto serán asignados a sus valores por defecto. Si no se dispone de un terminador de línea, se debe asignar a ''. En ese caso, el fichero de texto debe contener todos los campos para cada fila. El formato de fila fija también adecta a la manipulación de valores NULL; ver abajo. El formato de fila fija no funcionará si se usa un conjunto de caracteres multibyte.

La manipulación de valores NULL varía dependiendo de las opciones para FIELDS y LINES que se usen:

  • Para los valores por defecto de FIELDS y LINES, NULL se escribe como \N para salida y \N se lee como NULL en entrada (asumiendo que el carácter ESCAPED BY es `\').
  • Si FIELDS ENCLOSED BY no es vacío, un campo que contenga la palabra NULL como valor (esto es diferente si la palabra NULL está encerrada entre caracteres FIELDS ENCLOSED BY, el cual se lee como la cadena 'NULL').
  • Si FIELDS ESCAPED BY es vacío, NULL se escribe como la palabra NULL.
  • Con el formato de fila fija (que es cuando FIELDS TERMINATED BY y FIELDS ENCLOSED BY son ambos vacíos), NULL se escribe como una cadena vacía. Esto hace que tanto los valores NULL como las cadenas vacías no se puedan diferenciar cuando se escriben en el fichero, ya que ambos se escriben como cadenas vacías. Si se necesita diferenciar cuando se lee entre ambos casos, no se debe usar el formato de fila fija.

Algunos casos no son soportados por LOAD DATA INFILE:

  • Filas de tamaño constante (FIELDS TERMINATED BY y FIELDS ENCLOSED BY vacios) y columnas de tipo BLOB o TEXT.
  • Si se especifica un separador que el el mismo o un prefijo de otro, LOAD DATA INFILE no podrá interpretar la entrada de forma adecuada. Por ejemplo, las siguientes cláusulas FIELDS causarán problemas:
    FIELDS TERMINATED BY '"' ENCLOSED BY '"'
  • Si FIELDS ESCAPED BY es vacía, un valor de campo que contenga el carácter FIELDS ENCLOSED BY o LINES TERMINATED BY seguido del valor de FIELDS TERMINATED BY provocarám que LOAD DATA INFILE dejen de leer un campo o línea demasiado pronto. Esto sucede porque LOAD DATA INFILE no puede determinar dónde termina el valor de campo o de línea.

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

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

No se especifica ninguna lista de campos, de modo que LOAD DATA INFILE espera que las filas de entrada contengan un campo para cada columna de la tabla. Se usan los valores por defecto para FIELDS y LINES.

Si se desea cargar sólo algunas columnas de la tabla, se debe especificar una lista de campos:

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

Se debe especificar una lista de campos si el orden de los campos dentro del fichero de entrada es distinto del orden de las columnas en la tabla. De otro modo, MySQL no puede hacer coincidir cada campo de entrada con su columa en la tabla.

Si una fila tiene pocos campos, las columnas para las que no exista campo de entrada tomarán sus valores por defecto.

Un valor de campo vacío se interpreta de forma diferente que si el valor del campo se ha perdido:

  • Para tipos cadena, se asigna una cadena vacía a la columna.
  • Para tipos numéricos, se asigna 0 a la columna.
  • Para tipos de fecha y tiempo, se asigna el valor de "cero" apropiado según el tipo.

Estos son los mismos valores que se asignan si se asigna una cadena vacía explícitamente a una cadena, un número o a un tipo echa o hora en una sentencia INSERT o UPDATE.

A las columnas TIMESTAMP sólo se les asigna la fecha y hora actual si hay un valor NULL para la columna (es decir, \N), o (sólo para la primera columna TIMESTAMP) si la columna TIMESTAMP se omite de la lista de campos cuando se especifica una lista de campos.

Si una fila de entrada tiene demasiados campos, los campos extra serán ignorados y el número de avisos se incrementa. Antes de la versión 4.1.1 de MySQL, los avisos son sólo un número que indican que algo fue mal. En MySQL 4.1.1 se puede usar SHOW WARNINGS para obtener más información sobre qué ha ido mal.

LOAD DATA INFILE tiene preferencia por las entradas como cadenas, de modo que no se pueden usar valores numéricos para columnas ENUM o SET, modo que sí es aplicable a sentencias INSERT. Todos los valores ENUM y SET deben ser especificados como cadenas.

Si se usa el API C, se puede obtener información sobre la consulta llamando a la función del API mysql_info cuando la consulta LOAD DATA INFILE finaliza. El formato de la cadena de información es:

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

Los avisos (warnings) ocurren en las mismas circunstancias que cuando los valores son insertados vía una sentencia INSERT, excepto que LOAD DATA INFILE también genera avisos cuando hay pocos o demasiados campos en una fila de entrada. Los avisos no son almacenados; el número de avisos sólo pueden ser usados como una indicación de que todo fue bien.

Si se obtienen avisos y se quiere saber exactamente por qué, una forma de hacerlo es usar SELECT ... INTO OUTFILE en otro fichero y compararlo con el original.

Si se necesita usar LOAD DATA para leer desde un pipe, se puede usar el siguiente truco:

mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
cat < /dev/tcp/10.1.1.12/4711 > /nt/mysql/db/x/x
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

Si se usa la versión 3.23.25 de MySQL o anterior sólo se puede hacer lo anterior con LOAD DATA LOCAL INFILE.

En MySQL 4.1.1 se puede usar SHOW WARNINGS para obtener una lista de los primeros max_error_count avisos.