Importar/exportar datos en MySql con LOAD DATA INFILE desde y hacia un fichero externo

La instrucción de MySql LOAD DATA INFILE nos permite cargar datos desde un archivo externo, básicamente se trata de leer el contenido de un fichero y volcarlo en una tabla. El comando en cuestión tiene la siguiente sintaxis básica:

LOAD DATA INFILE 'c:/archivo_datos.txt' INTO TABLE nombre_tabla;

Las opciones de las que dispone esta instrucción son numerosas y permiten adaptar el tipo de volcado a nuestras necesidades concretas. Veamos un ejemplo:

LOAD DATA INFILE 'c:/archivo_datos.txt'
INTO TABLE nombre_tabla
FIELDS TERMINATED BY ';'
ENCLOSED BY '\"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'

En este caso le estamos indicando al motor de base de datos que cargue el archivo considerando que cada campo está separado por el carácter de punto y coma, también que todos los valores le llegaran entrecomillados con comillas dobles (si usamos el modificador OPTIONALLY sólo se entrecomillarán las columnas de texto y fecha), el carácter de escape es la barra invertida y cada salto de línea se indica con \r\n (Importante: Si se ha generado el fichero de texto en un sistema Windows , se tiene que usar LINES TERMINATED BY '\r\n' para leer correctamente el fichero, ya que los programas de Windows típicamente usan dos caracteres como terminadores de línea, de lo contrario bastará el signo de nueva línea '\n').

Otra cuestión a considerar y que puede llegar a producir un verdadero dolor de cabeza es el "conjunto de caracteres" y "collation" de la tabla. Aunque nuestra tabla sea utf-8 (CHARACTER SET utf8, COLLATION utf8_general_ci) los acentos y las eñes no se importarán correctamente. Para solucionar este problema debemos indicar CHARACTER SET UTF8 después del nombre de la tabla, y emplear LOCAL como parte del comando si estamos cargando datos desde nuestro propio ordenador.

LOAD DATA LOCAL INFILE 'c:/archivo_datos.txt'
INTO TABLE nombre_tabla CHARACTER SET UTF8
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '\"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'

Del mismo modo podemos realizar la operación contraria, es decir, generar un archivo de texto plano con los datos de una tabla dada. Veamos un ejemplo:

SELECT * FROM nombre_tabla
INTO OUTFILE 'c:/archivo_datos.txt'
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n';

De esta manera obtenemos un archivo llamado "archivo_datos.txt" con un registro por línea, cuyos valores de columna se encuentran separados por un punto y coma y los campos con valores de texto y fechas se entrecomillarán.

MySql, PHP y la codificación utf8

Para evitar problemas con acentos, eñes, etc. y de paso maximizar la compatibilidad de nuestras páginas y bases de datos MySql con cualquier idioma y distintos conjuntos de caracteres, lo mejor es utilizar o establecer por defecto el tipo de codificación UTF-8.

La mejor opción es configurar el servidor MySql para que trabaje con codificación UTF-8 por defecto añadiendo estas líneas en el archivo my.ini localizar [mysqld].

default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci
init_connect = 'SET collation_connection = utf8_general_ci'
init_connect = 'SET NAMES utf8'

Si te interesa profundizar en el tema de los "encodings" y mysql no te pierdas este artículo del Blog personal de Jynus.

El servidor Apache también dispone de una directiva llamada AddDefaultCharset que debemos establecer como utf-8 para que no aparezcan caracteres extraños en lugar de acentos, eñes, etc.

AddDefaultCharset utf-8

En el php.ini la directiva default_charset también tiene algo que ver en todo este galimatias de tipos de codificación y caracteres.

default_charset = "iso-8859-1"

Mediante PHP también es posible configurar el charset en el momento de usar cualquier tabla, para ello debemos especificar el siguiente código después de realizar la conexión y la selección de la BD (mysql_connect – mysql_select_db) y antes de ejecutar cualquier consulta.

mysql_query ("SET NAMES 'utf8'");

En PHP 5.2 o superior podemos también usar:

mysql_set_charset("utf8", $conn);

Una función muy práctica que sirve para ver el charset que está devolviendo MySql.

mysql_client_encoding($conn);