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.frmLe añadimos una nueva línea al fichero:
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.