14 Lenguaje SQL: Importar y exportar datos
MySQL permite copiar tablas en diferentes formatos de texto, así como importar datos a partir de fichero de texto en diferentes formatos.
Esto se puede usar para exportar los datos de nuestras bases de datos a otras aplicaciones, o bien para importar datos desde otras fuentes a nuestras tablas. También se puede usar para hacer copias de seguridad y restaurarlas posteriormente.
Exportar a otros ficheros
Para extraer datos desde una base de datos a un fichero se usa la sentencia SELECT ... INTO OUTFILE.
El resto de las cláusulas de SELECT siguen siendo aplicables, la única diferencia es que la salida de la selección se envía a un fichero en lugar de hacerlo a la consola.
La sintaxis de la parte INTO OUTFILE es:
[INTO OUTFILE 'file_name' export_options]
file_name es el nombre del fichero de salida. Ese fichero no debe existir, ya que en caso contrario la sentencia fallará.
En cuanto a las opciones de exportación son las mismas que para las cláusulas FIELDS y LINES de LOAD DATA. Su sintaxis es:
[FIELDS
[TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY '']
[ESCAPED BY '\\' ]
]
[LINES
[STARTING BY '']
[TERMINATED BY '\n']
]
Estas cláusulas nos permiten crear diferentes formatos de ficheros de salida.
La cláusula FIELDS se refiere a las opciones de cada columna:
- TERMINATED BY 'carácter': nos permite elegir el carácter delimitador que se usará para separar cada columna. Por defecto, el valor que se usa es el tabulador, pero podemos usar ';', ',', etc.
- [OPTIONALLY] ENCLOSED BY 'carácter': sirve para elegir el carácter usado para entrecomillar cada columna. Por defecto no se entrecomilla ninguna columna, pero podemos elegir cualquier carácter. Si se añade la palabra OPTIONALLY sólo se entrecomillarán las columnas de texto y fecha.
- ESCAPED BY 'carácter': sirve para indicar el carácter que se usará para escapar aquellos caracteres que pueden dificultar la lectura posterior del fichero. Por ejemplo, si terminamos las columnas con ',' y no las entrecomillamos, un carácter ',' dentro de una columna de texto se interpretará como un separador de columnas. Para evitar esto se puede escapar esa coma con otro carácter. Por defecto se usa el carácter '\'.
La cláusula LINES se refiere a las opciones para cada fila:
- STARTING BY 'carácter': permite seleccionar el carácter para comenzar cada línea. Por defecto no se usa ningún carácter para ello.
- TERMINATED BY 'carácter': permite elegir el carácter para terminar cada línea. Por defecto es el retorno de línea, pero se puede usar cualquier otro carácter o caracteres, por ejemplo '\r\n'.
Por ejemplo, para obtener un fichero de texto a partir de la tabla 'gente', con las columnas delimitadas por ';', entrecomillando las columnas de texto con '"' y separando cada fila por la secuencia '\r\n', usaremos la siguiente sentecia:
mysql> SELECT * FROM gente
-> INTO OUTFILE "gente.txt"
-> FIELDS TERMINATED BY ';'
-> OPTIONALLY ENCLOSED BY '\"'
-> LINES TERMINATED BY '\n\r';
Query OK, 5 rows affected (0.00 sec)
mysql>
El fichero de salida tendrá este aspecto:
"Fulano";"1974-04-12" "Mengano";"1978-06-15" "Tulano";"2000-12-02" "Pegano";"1993-02-10" "Mengano";\N
La fecha para "Mengano" era NULL, para indicarlo se muestra el valor \N.
Importar a partir de ficheros externos
Por supuesto, el proceso contrario también es posible. Podemos leer el contenido de un fichero de texto en una tabla. El fichero origen puede haber sido creado mediante una sentecia SELECT ... INTO OUTFILE, o mediante cualquier otro medio.
Para hacerlo disponemos de la sentencia LOAD DATA, cuya sintaxis más simple es:
LOAD DATA [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 cláusula LOCAL indica, si aparece, que el fichero está en el ordenador del cliente. Si no se especifica el fichero de texto se buscará en el servidor, concretamente en el mismo directorio donde esté la base de datos. Esto nos permite importar datos desde nuestro ordenador en un sistema en que el servidor de MySQL se encuentra en otra máquina.
Las cláusulas REPLACE e IGNORE afectan al modo en que se tratan las filas leídas que contengan el mismo valor para una clave principal o única para una fila existente en la tabla. Si se especifica REPLACE se sustituirá la fila actual por la leída. Si se especifica IGNORE el valor leído será ignorado.
La parte INTO TABLA tbl_name indica en qué tabla se insertarán los valores leídos.
No comentaremos mucho sobre las cláusulas FIELDS y LINES ya que su significado es el mismo que vimos para la sentencia SELECT ... INTO OUTFILE. Estas sentencias nos permiten interpretar correctamente cada fila y cada columna, adaptándonos al formato del fichero de texto de entrada.
La misma utilidad tiene la cláusula IGNORE número LINES, que nos permite que las primeras número líneas no se interpreten como datos a importar. Es frecuente que los ficheros de texto que usaremos como fuente de datos contengan algunas cabeceras que expliquen el contenido del fichero, o que contengan los nombres de cada columna. Usando esta cláusula podemos ignorarlas.
La última parte nos permite indicar la columna a la que será asignada cada una de las columnas leídas, esto será útil si el orden de las columnas en la tabla no es el mismo que en el fichero de texto, o si el número de columnas es diferente en ambos.
Por ejemplo, supongamos que queremos añadir el contenido de este fichero a la tabla "gente":
Fichero de datos de "gente" fecha,nombre 2004-03-15,Xulana 2000-09-09,Con Clase 1998-04-15,Pingrana
Como vemos, hay dos filas al principio que no contienen datos válidos, las columnas están separadas con comas y, como hemos editado el fichero con el "notepad", las líneas terminan con "\n\r". La sentencia adecuada para leer los datos es:
mysql> LOAD DATA INFILE "gente.txt"
-> INTO TABLE gente
-> FIELDS TERMINATED BY ','
-> LINES TERMINATED BY '\r\n'
-> IGNORE 2 LINES
-> (fecha,nombre);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
El nuevo contenido de la tabla es:
mysql> SELECT * FROM gente; +-----------+------------+ | nombre | fecha | +-----------+------------+ | Fulano | 1974-04-12 | | Mengano | 1978-06-15 | | Tulano | 2000-12-02 | | Pegano | 1993-02-10 | | Mengano | NULL | | Xulana | 2004-03-15 | | Con Clase | 2000-09-09 | | Pingrana | 1998-04-15 | +-----------+------------+ 8 rows in set (0.00 sec) mysql>