Duplicar o clonar tablas en mysql

MySQL nos proporciona dos maneras de "clonar" una tabla, tanto su estructura como su estructura y sus datos, podemos querer duplicar una tabla para hacer algún tipo de backup rápido, mantener un histórico, o migrar la tabla a un esquema o base de datos diferentes, entre otras cosas.

Clonar una tabla usando SELECT

La sentencia de creación CREATE TABLE (..) SELECT nos permite crear la tabla con los registros que devuelva la consulta de selección, pero tiene las siguientes limitaciones:

  • No traspasa las constraints de tipo PRIMARY KEY
  • No traspasa las definiciones de AUTO_INCREMENT
  • No traspasa las definiciones de DEFAULT CURRENT_TIMESTAMP
  • Utiliza el storage engine por defecto y no el de la tabla (en caso de que sean distintos)
  • Solamente traspasa los registros afectados por la SELECT, que podría no devolver ninguno y crear la tabla vacía
Ejemplos de duplicado de tablas usando SELECT

La tabla origen será la siguiente:

CREATE TABLE tabla_origen (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
descripcion VARCHAR(30),
fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
ENGINE = MyISAM;

Si la describimos obtenemos lo siguiente:

mysql> DESCRIBE tabla_origen;
+-------------+-------------+------+-----+-------------------+----------------
| Field       | Type        | Null | Key | Default           | Extra
+-------------+-------------+------+-----+-------------------+----------------
| id          | int(11)     | NO   | PRI | NULL              | auto_increment
| descripcion | varchar(30) | YES  |     | NULL              |
| fecha       | timestamp   | NO   |     | CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+----------------
3 rows in set (0.00 sec)

Y hemos insertado diez registros de prueba:

mysql> SELECT * FROM tabla_origen;
+----+------------------+---------------------+
| id | descripcion      | fecha               |
+----+------------------+---------------------+
|  1 | Primer Registro  | 2008-07-20 16:42:16 |
|  2 | Segundo Registro | 2008-07-20 16:42:16 |
|  3 | Tercer Registro  | 2008-07-20 16:42:16 |
|  4 | Cuarto Registro  | 2008-07-20 16:42:16 |
|  5 | Quinto Registro  | 2008-07-20 16:42:16 |
|  6 | Sexto Registro   | 2008-07-20 16:42:16 |
|  7 | Séptimo Registro | 2008-07-20 16:42:16 |
|  8 | Octavo Registro  | 2008-07-20 16:42:16 |
|  9 | Noveno Registro  | 2008-07-20 16:42:16 |
| 10 | Décimo Registro  | 2008-07-20 16:42:16 |
+----+------------------+---------------------+
10 rows in set (0.00 sec)

El storage engine por defecto es InnoDB:

mysql> SELECT @@storage_engine;
+------------------+
| @@storage_engine |
+------------------+
| InnoDB           |
+------------------+
1 row in set (0.00 sec)

Usando esta sentencia, duplicaríamos la tabla, tanto su estructura como sus registros:

mysql> CREATE TABLE clone_select SELECT * FROM tabla_origen;
Query OK, 10 rows affected (0.08 sec)
Records: 10  Duplicates: 0  Warnings: 0

Pero veámos las diferencias:

mysql> DESCRIBE clone_select;
+-------------+-------------+------+-----+---------------------+-------+
| Field       | Type        | Null | Key | Default             | Extra |
+-------------+-------------+------+-----+---------------------+-------+
| id          | int(11)     | NO   |     | 0                   |       |
| descripcion | varchar(30) | YES  |     | NULL                |       |
| fecha       | timestamp   | NO   |     | 0000-00-00 00:00:00 |       |
+-------------+-------------+------+-----+---------------------+-------+
3 rows in set (0.00 sec)

Hemos perdido tanto la columna AUTO_INCREMENT como el DEFAULT CURRENT_TIMESTAMP, además de la PRIMARY KEY.
Además, si comparamos la información de la tabla que nos proporciona la tabla TABLES del INFORMATION_SCHEMA:

mysql> SELECT table_name, engine FROM information_schema.tables WHERE table_name 
IN ('tabla_origen','clone_select') AND table_schema = 'test';
+--------------+--------+
| table_name   | engine |
+--------------+--------+
| clone_select | InnoDB |
| tabla_origen | MyISAM |
+--------------+--------+
2 rows in set (0.00 sec)

Tambien vemos que el storage engine no es el que le indicamos a la tabla original, MyISAM, sino que ha tomado el que había por defecto en la base de datos, el InnoDB.

Para restringir el número de registros, solamente tenemos que agregarle condiciones a la SELECT:

mysql> CREATE TABLE clone_select_where1 SELECT * FROM tabla_origen WHERE id < 7;
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> CREATE TABLE clone_select_where2 SELECT * FROM tabla_origen LIMIT 3;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> CREATE TABLE clone_select_where3 SELECT * FROM tabla_origen WHERE 0;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

En el primer caso hemos restringido el número de registros a traspasar mediante el WHERE, en el segundo caso, usando LIMIT, y en el tercer caso hemos duplicado la tabla vacía usando una condición que nunca se cumplirá.

Clonar una tabla usando LIKE

Si se usa la sentencia CREATE TABLE (..) LIKE , se creará una tabla vacía que conserva la estructura de la original, pero no los registros.

La tabla origen será la siguiente:

CREATE TABLE tabla_origen (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
descripcion VARCHAR(30),
fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
ENGINE = MyISAM;

Si la describimos obtenemos lo siguiente:

mysql> DESCRIBE tabla_origen;
+-------------+-------------+------+-----+-------------------+----------------
| Field       | Type        | Null | Key | Default           | Extra
+-------------+-------------+------+-----+-------------------+----------------
| id          | int(11)     | NO   | PRI | NULL              | auto_increment
| descripcion | varchar(30) | YES  |     | NULL              |
| fecha       | timestamp   | NO   |     | CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+----------------
3 rows in set (0.00 sec)

Y hemos insertado diez registros de prueba:

mysql> SELECT * FROM tabla_origen;
+----+------------------+---------------------+
| id | descripcion      | fecha               |
+----+------------------+---------------------+
|  1 | Primer Registro  | 2008-07-20 16:42:16 |
|  2 | Segundo Registro | 2008-07-20 16:42:16 |
|  3 | Tercer Registro  | 2008-07-20 16:42:16 |
|  4 | Cuarto Registro  | 2008-07-20 16:42:16 |
|  5 | Quinto Registro  | 2008-07-20 16:42:16 |
|  6 | Sexto Registro   | 2008-07-20 16:42:16 |
|  7 | Séptimo Registro | 2008-07-20 16:42:16 |
|  8 | Octavo Registro  | 2008-07-20 16:42:16 |
|  9 | Noveno Registro  | 2008-07-20 16:42:16 |
| 10 | Décimo Registro  | 2008-07-20 16:42:16 |
+----+------------------+---------------------+
10 rows in set (0.00 sec)

El storage engine por defecto es InnoDB:

mysql> SELECT @@storage_engine;
+------------------+
| @@storage_engine |
+------------------+
| InnoDB           |
+------------------+
1 row in set (0.00 sec)

Utilizando la sentencia LIKE, clonaremos la tabla, pero no su contenido:

mysql> CREATE TABLE clone_like LIKE tabla_origen;
Query OK, 0 rows affected (0.02 sec)

Pero en este caso, a diferencia del de la SELECT, sí se mantiene la estructura básica de la tabla:

mysql> DESCRIBE clone_like;
+-------------+-------------+------+-----+-------------------+----------------+
| Field       | Type        | Null | Key | Default           | Extra          |
+-------------+-------------+------+-----+-------------------+----------------+
| id          | int(11)     | NO   | PRI | NULL              | auto_increment |
| descripcion | varchar(30) | YES  |     | NULL              |                |
| fecha       | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
+-------------+-------------+------+-----+-------------------+----------------+
3 rows in set (0.00 sec)

Y el storage engine también se conserva:

mysql> SELECT table_name, engine FROM information_schema.tables WHERE table_name
IN ('tabla_origen','clone_like') AND table_schema = 'test';
+--------------+--------+
| table_name   | engine |
+--------------+--------+
| clone_like   | MyISAM |
| tabla_origen | MyISAM |
+--------------+--------+
2 rows in set (0.00 sec)

Pero los datos no se traspasan, por lo que la tabla estará vacía:

mysql> SELECT * FROM clone_like;
Empty set (0.00 sec)

Excepciones para ambos casos

Tanto si utilizamos la clonación mediante SELECT como si usamos LIKE, habrá varios atributos de las tablas que no se van a copiar y debemos copiarlos manualmente:

  • Claves foráneas, las FOREIGN KEYS no se copian en ninguno de los casos, se debe hacer un ALTER TABLE manual para incluirlas después de realizar la copia.
  • En el caso del storage engine MyISAM, los opciones de DATA DIRECTORY e INDEX DIRECTORY tampoco se copian, los ficheros de datos e índices se ubicarán en el directorio general especificado para la base de datos destino.

Conclusión, consejos y recomendaciones

Por lo observado en estas pruebas, ambas opciones tienen sus ventajas e inconvenientes, por lo que se debe ser cuidadoso al elegir una de las dos alternativas si necesitamos duplicar o clonar una tabla.
Si lo que nos interesa conservar principalmente son los datos y la estructura ya la tenemos en otro lugar accesible como en las copias de seguridad, o en una copia del esquema, el método a elegir sería sin duda SELECT, pero si nos interesa copiar solamente la estructura, o la estructura es igual de importante que los datos para nuestra copia, se debe utilizar el LIKE, y posteriormente, un INSERT INTO (..) SELECT para copiar también los datos.

mysql> INSERT INTO clone_like SELECT * FROM tabla_origen;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM clone_like;
+----+------------------+---------------------+
| id | descripcion      | fecha               |
+----+------------------+---------------------+
|  1 | Primer Registro  | 2008-07-20 16:42:16 |
|  2 | Segundo Registro | 2008-07-20 16:42:16 |
|  3 | Tercer Registro  | 2008-07-20 16:42:16 |
|  4 | Cuarto Registro  | 2008-07-20 16:42:16 |
|  5 | Quinto Registro  | 2008-07-20 16:42:16 |
|  6 | Sexto Registro   | 2008-07-20 16:42:16 |
|  7 | Séptimo Registro | 2008-07-20 16:42:16 |
|  8 | Octavo Registro  | 2008-07-20 16:42:16 |
|  9 | Noveno Registro  | 2008-07-20 16:42:16 |
| 10 | Décimo Registro  | 2008-07-20 16:42:16 |
+----+------------------+---------------------+
10 rows in set (0.00 sec)

Es muy importante, en ambos casos, tener en cuenta que las FOREIGN KEYS no se traspasan.

Espero que os haya resultado útil.

Comentarios

[...] Vía: cambrico.net [...]

Muy buena la info, gracias

Muchas gracias por la info, me resolvió varias dudas.
viene muy bien explicado

Gracias a ti mugetsu por el comentario :)

Excelente información, me ha servido de mucho ya que yo usaba el create table ... por lo tanto los indices no los clonaba.... muchas gracias... Me ha servido de mucho. :)

No hay de qué, Teo, me alegro mucho que te haya sido de utilidad!

[...] 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 [...]

Exelente informacion, me fue de gran utilidad, miles de gracias :-) !!!

Gracias, estaba buscando como hacer lo de clonar tablas (estructura) y me sirvió mucho tu post, gracias.

quiero insertar fecha y hora en mi página web

Gracias!!!!. Era justo lo que estaba buscando!!!!.

Que manera de dar vueltas cabron!

Me ha sido de ayuda gracias

Hola, llevo 4 horas sin encontrar algo qye creo debe ser muy tonto, quiero anadir a una tabla los campos(no datos) de otras tablas....

Ya probe con merge , alter, create like, pero al no encontrar ejemplos he provado como loco y no doy...

Agradezco!

Yo lo que he hecho ha sido exportar la tabla en formato sql y queda algo asi:

CREATE TABLE IF NOT EXISTS `NOMBRE_ANTIGUO` (
`umeta_id` bigint(20) unsigned NOT NULL auto_increment,
`user_id` bigint(20) unsigned NOT NULL default '0',
`meta_key` varchar(255) default NULL,
`meta_value` longtext,
PRIMARY KEY (`umeta_id`),
KEY `user_id` (`user_id`),
KEY `meta_key` (`meta_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;

Si tenes datos tambien debes copar todos los insert que le siguen:

INSERT INTO `NOMBRE_ANTIGUO` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES
(1, 1, 'first_name', ''),
(2, 1, 'last_name', '');

Cambias en los dos sitiso el NOMBRE_ANTIGUO por el nuevo y listo.

Si solo creas la tabla sin registros recuerda poner el AUTO_INCREMENT=1 (ultima linea al crear la tabal)

Espero que sea de utilidad

Al hacer la clonación me aparece: ERROR 1210 (HY000): Incorrect arguments to DATA DIRECTORY

Al fin ! .. no había manera ... hasta que he visto lo que pones del LIKE. Ahora sí, perfecto. Muchas gracias.

muy buen articulo. gracias me ayudo mucho

Añadir nuevo comentario