Exportar en CSV utilizando el ENGINE CSV

A partir de MySQL 5.1 el motor de almacenamiento en CSV (Valores separados por comas) viene instalado por defecto en el servidor MySQL, lo que nos facilita un método para exportar datos de tablas en este formato, utilizándolo de forma nativa.
También es posible exportar datos en este formato directamente a través de una sola consulta, podéis verlo aquí.

¿cómo se si el método de almacenamiento en CSV está activado?

Si la versión instalada es 5.1 o superior, normalmente viene por defecto, para comprobarlo, nos conectamos con un usuario administrador:

mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.30 MySQL Community Server (GPL)

Y listamos los engines disponibles, mediante el comando SHOW engines o show variables like 'have_csv':

mysql> SHOW ENGINES\G;
*************************** 1. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
7 rows in set (0.00 sec)

Si el Engine CSV nos devuelve YES, podemos utilizar el método de exportación en CSV de forma nativa, si estamos en versiones anteriores a 5.1, probablemente nos aparezca como NO, eso quiere decir que el servidor MySQL ha sido compilado sin soporte para el engine y no se puede cambiar en modo de ejecución, para estos casos, hay que evaluar si merece la pena recompilar el servidor utilizando el script configure con la opción --with-csv-storage-engine, más información en la documentación de mysql.

utilizando el engine CSV para exportar datos de una tabla

Una vez hayamos comprobado que podemos utilizar el motor CSV para trabajar con nuestras tablas, podremos usar de forma nativa un fichero CSV para modificar datos, incluso cuando el servidor MySQL esté bajado.
Por ejemplo, utilizaremos una tabla de ejemplo que he creado en la base de datos test:

mysql>SELECT * from frutas;
+------------+----------+
| nombre     | color    |
+------------+----------+
| fresa      | rojo     |
| plátano    | amarillo |
| manzana    | verde    |
| uva        | verde    |
| pera       | verde    |
| mandarina  | naranja  |
| melocotón  | marrón   |
| limón      | amarillo |
+------------+----------+
8 rows in set (0.10 sec)

Para poder modificar datos en CSV, es recomendable trabajar con una copia de la tabla, ya que significa modificar el motor de almacenamiento, y esto modificaría la estructura de datos, y en tablas de producción no es nada recomendable. Ya he hablado en otra ocasión de cómo clonar o duplicar tablas en MySQL. Podríamos crear una tabla por ejemplo a partir de una consulta compleja de varias tablas y generar reportes en CSV.

Duplicamos nuestra tabla de ejemplo:

mysql> CREATE TABLE frutas_copia AS SELECT * FROM frutas;
Query OK, 8 rows affected (0.13 sec)
Records: 8  Duplicates: 0  Warnings: 0

Y también modificaremos las columnas, ya que el engine CSV no soporta columnas que admitan valores NULL.

mysql> ALTER TABLE frutas_copia MODIFY COLUMN color VARCHAR(10) NOT NULL;
Query OK, 8 rows affected (0.11 sec)
Records: 8  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE frutas_copia MODIFY COLUMN nombre VARCHAR(10) NOT NULL;
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0

Finalmente, modificamos el motor de almacenamiento de la tabla copia:

mysql> ALTER TABLE frutas_copia ENGINE=CSV;
Query OK, 8 rows affected (0.33 sec)
Records: 8  Duplicates: 0  Warnings: 0
mysql> select * from frutas_copia;
+------------+----------+
| nombre     | color    |
+------------+----------+
| fresa      | rojo     |
| plátano    | amarillo |
| manzana    | verde    |
| uva        | verde    |
| pera       | verde    |
| mandarina  | naranja  |
| melocotón  | marrón   |
| limón      | amarillo |
+------------+----------+
8 rows in set (0.10 sec)

Desde este momento, MySQL genera un nuevo fichero de base de datos , con extensión .csv que podremos modificar en modo texto, con Excel, OpenOffice, o el editor que más nos convenga:

$ ls -l frutas_copia.*
-rw-rw----  1 _mysql  staff    35  1 ene 20:14 frutas_copia.CSM
-rw-rw----  1 _mysql  staff   149  1 ene 20:13 frutas_copia.CSV
-rw-rw----  1 _mysql  staff  8596  1 ene 19:36 frutas_copia.frm

Le añadimos una nueva línea al fichero:

editar_csv.png

Ahora, para seleccionar nuestros modificaciones, deberemos utilizar el comando FLUSH TABLES, para refrescar los datos:

mysql> flush tables;
Query OK, 0 rows affected (0.10 sec)
mysql> select * from frutas_copia;
+------------+----------+
| nombre     | color    |
+------------+----------+
| fresa      | rojo     |
| plátano   | amarillo |
| manzana    | verde    |
| uva        | verde    |
| pera       | verde    |
| mandarina  | naranja  |
| melocotón | marrón  |
| limón     | amarillo |
| mango      | amarillo |
+------------+----------+
9 rows in set (0.04 sec)

Como podéis ver, si tenemos un entorno de réplica, es una forma muy rápida de modificar y gestionar datos en CSV, incluso con el servidor MySQL bajado. Los inconvenientes también se ven claros, en versiones anteriores a la 5.1, es un tanto complejo de configurar y además necesitamos realizar una copia física de nuestros datos para trabajar.
Además, el engine CSV todavía no soporta índices, por lo que su uso a nivel de aplicación, substituyendo a MyISAM no sería factible, ya que daría graves problemas de rendimiento.
Más en la documentación de MySQL.
Actualizo, como comenta William, este método no está disponible de ninguna de las maneras en plataformas Windows hasta la versión MySQL 5.1, ni siquiera recompilando el server.

categorías: 

Comentarios

Hola Pedro,

Muy buen artículo!

Solo comentarte que el ENGINE CSV no era soportado (en ninguna forma) en versiones anteriores a la 5.1 en plataforma Windows, es decir, ni con la opción de recompilar el código del servidor.

Saludos.

Gracias William, añado tu puntualización al artículo, es cierto que hasta la 5.1 no se puede utilizar bajo Windows!
Un saludo

Me ha parecido tremendamente interesante esta entrada Pedro. No me habia planteado hasta la fecha usar este metodo para exportaciones CSV.

Gracias Miquel, el engine CSV es una opción muy interesante si necesitas modificar/ver los ficheros CSV en el Excel por ejemplo, al mismo tiempo que quieres mostrarlos por aplicación y mantener consistencia. La lástima es que no se pueda utilizar de forma nativa en versiones anteriores a la 5.1

Añadir nuevo comentario

 
 
 

Creative Commons License
Excepto donde se indique lo contrario, el contenido de este sitio está sujeto a una licencia de Creative Commons.