Crear y eliminar tablas en MySQL

En MySQL, una tabla se crea con la sentencia CREATE TABLE y se elimina con DROP TABLE. Si no sabes si la tabla existe y no quieres que la sentencia falle, tienes DROP TABLE IF EXISTS, que la borra si está y, si no está, se limita a dejar un aviso. Aquí tienes las 3 sentencias listas para copiar, y a continuación vamos a ver cada una con calma, junto con lo que suele dar problemas al borrar: las claves foráneas y la diferencia entre DELETE, TRUNCATE y DROP.

Crear y eliminar una tabla en MySQL: las 3 sentencias

-- Crear la tabla
CREATE TABLE clientes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  ciudad VARCHAR(100),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Eliminar la tabla con todos sus datos
DROP TABLE clientes;

-- Eliminarla solo si existe, sin error si no está
DROP TABLE IF EXISTS clientes;

Todos los ejemplos de este artículo están probados en MySQL 9.7 LTS, y en la 8.4 LTS funcionan igual. Las salidas que vas a ver son las que devuelve el cliente mysql en la terminal. Para ejecutarlos necesitas estar conectado al servidor y tener una base de datos seleccionada, porque si no, MySQL no sabe dónde crear la tabla y te responde con ERROR 1046 (3D000): No database selected.

Conectar con MySQL y seleccionar la base de datos

Para conectarnos desde la terminal usamos el cliente mysql, indicando el usuario con -u y la opción -p sin nada detrás:

mysql -u root -p

Fíjate en que la contraseña no aparece en el comando. Con -p a secas, el cliente te la pide con Enter password:, así que no se queda guardada en el historial de la terminal ni la puede ver otro usuario con ps mientras el comando se ejecuta, algo que en algunos sistemas sí puede pasar si la escribes pegada a la opción. En ese caso, el propio cliente te avisa con Using a password on the command line interface can be insecure. Una vez conectado, verás el prompt mysql> y a partir de ahí puedes ejecutar cualquier sentencia SQL.

Para los ejemplos vamos a crear una base de datos, tienda, la seleccionamos con USE y comprobamos con SELECT DATABASE() que estamos trabajando sobre ella:

CREATE DATABASE tienda;
USE tienda;
SELECT DATABASE();

+------------+
| DATABASE() |
+------------+
| tienda     |
+------------+
1 row in set (0.000 sec)

Desde ese momento, todas las sentencias que ejecutes se aplican a tienda hasta que cambies a otra base de datos con USE o cierres la sesión. Y si quieres ver qué bases de datos tienes en el servidor, SHOW DATABASES; te las lista.

¿Cómo crear una tabla en MySQL?

Con CREATE TABLE indicamos el nombre de la tabla y, entre paréntesis, sus columnas, cada una con su tipo de dato y, si hace falta, sus restricciones. Vamos a crear una tabla de clientes:

CREATE TABLE clientes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  ciudad VARCHAR(100),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

La columna id es un entero sin signo con AUTO_INCREMENT, así que MySQL le asigna 1, 2, 3... a cada fila nueva sin que tengas que pasarle el valor, y PRIMARY KEY la convierte en la clave primaria de la tabla, que no admite valores repetidos ni NULL. Las columnas nombre y email llevan NOT NULL, es decir, son obligatorias, y además email lleva UNIQUE para que no puedas tener 2 clientes con el mismo correo. La columna ciudad sí admite NULL, y created_at guarda la fecha de alta con DEFAULT CURRENT_TIMESTAMP, de forma que, si no le das un valor al insertar, MySQL pone la fecha y la hora del momento.

Motor y juego de caracteres por defecto en MySQL 9.7 y 8.4

¿Y el motor de la tabla y su juego de caracteres? En la sentencia no hemos dicho nada, así que MySQL ha usado los valores por defecto. Lo puedes ver con SHOW CREATE TABLE, que te devuelve la sentencia completa que crearía la tabla tal y como está ahora. Si terminas la sentencia con \G en lugar de con punto y coma, el cliente muestra el resultado en vertical, que para resultados largos se lee mucho mejor:

SHOW CREATE TABLE clientes\G

*************************** 1. row ***************************
       Table: clientes
Create Table: CREATE TABLE `clientes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `nombre` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `ciudad` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.005 sec)

Fíjate en la última línea. El motor por defecto es InnoDB, que es el que nos permite trabajar con transacciones y claves foráneas, y el juego de caracteres es utf8mb4 con la collation utf8mb4_0900_ai_ci, que al comparar y ordenar no distingue entre mayúsculas y minúsculas ni entre letras con y sin tilde. Con utf8mb4 puedes guardar cualquier carácter Unicode, emojis incluidos, así que, salvo que tu servidor o tu base de datos tengan otra configuración, no necesitas añadir ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 al final de cada CREATE TABLE, como verás en muchos ejemplos. Además, el UNIQUE de email se ha convertido en un índice único con el mismo nombre que la columna.

CREATE TABLE IF NOT EXISTS

Si ejecutas CREATE TABLE con el nombre de una tabla que ya existe, MySQL se detiene con un error:

ERROR 1050 (42S01): Table 'clientes' already exists

En scripts de instalación o en archivos .sql que se ejecutan más de una vez, lo habitual es añadir IF NOT EXISTS, para que MySQL cree la tabla solo cuando no existe y, si ya está, deje una nota en lugar del error. Esa nota la puedes consultar con SHOW WARNINGS:

CREATE TABLE IF NOT EXISTS clientes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(255) NOT NULL
);
SHOW WARNINGS;

Query OK, 0 rows affected, 1 warning (0.003 sec)

+-------+------+---------------------------------+
| Level | Code | Message                         |
+-------+------+---------------------------------+
| Note  | 1050 | Table 'clientes' already exists |
+-------+------+---------------------------------+
1 row in set (0.000 sec)

Pero ojo, porque IF NOT EXISTS solo mira el nombre. La tabla que le acabamos de pasar tiene 2 columnas y la que ya existía tiene 5, y MySQL no compara la estructura: la tabla se queda como estaba y no te avisa de que no coincide con lo que acabas de escribir. Si lo que necesitas es cambiar la estructura de una tabla que ya existe, eso es trabajo de ALTER TABLE.

Ver las tablas con SHOW TABLES y DESCRIBE

Para comprobar que la tabla está creada, SHOW TABLES lista las tablas de la base de datos seleccionada, y DESCRIBE te enseña las columnas de una tabla:

SHOW TABLES;
DESCRIBE clientes;

+------------------+
| Tables_in_tienda |
+------------------+
| clientes         |
+------------------+
1 row in set (0.001 sec)

+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| id         | int unsigned | NO   | PRI | NULL              | auto_increment    |
| nombre     | varchar(255) | NO   |     | NULL              |                   |
| email      | varchar(255) | NO   | UNI | NULL              |                   |
| ciudad     | varchar(100) | YES  |     | NULL              |                   |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
5 rows in set (0.001 sec)

En la columna Key tienes PRI para la clave primaria y UNI para el índice único, y en Extra aparece el auto_increment del id y un DEFAULT_GENERATED en created_at, que indica que su valor por defecto es una expresión, CURRENT_TIMESTAMP, y no un valor fijo.

¿Cómo eliminar una tabla en MySQL?

Para eliminar una tabla usamos DROP TABLE seguido de su nombre:

DROP TABLE clientes;

Con esto desaparece todo: la estructura, los datos, los índices y los triggers de la tabla. ¿Se puede deshacer? No. MySQL no te pide confirmación, y DROP TABLE hace un commit implícito, así que aunque la ejecutes en mitad de una transacción, un ROLLBACK no te devuelve la tabla. Lo único que no se borra son los permisos que hayas dado a algún usuario sobre esa tabla en concreto, que se quedan en el servidor hasta que los quites con REVOKE.

Por eso, si la tabla tiene datos que te pueden hacer falta, antes de borrarla haz una copia con mysqldump, que guarda en un archivo la estructura y los datos. Igual que con el cliente mysql, -p te pide la contraseña:

mysqldump -u root -p --set-gtid-purged=OFF tienda clientes > clientes.sql

La opción --set-gtid-purged=OFF hace falta en MySQL 9.7, que trae activados por defecto los GTID, los identificadores que usa la replicación para seguir cada transacción. Sin ella, mysqldump te muestra un aviso sobre los GTID y añade al archivo una sentencia SET @@GLOBAL.GTID_PURGED que, al cargarlo en el mismo servidor, falla con ERROR 3546 (HY000): @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED. En la 8.4, donde los GTID vienen desactivados, la opción no cambia nada, así que puedes usar el mismo comando en las 2.

Si después necesitas recuperarla, basta con cargar ese archivo en la base de datos con mysql -u root -p tienda < clientes.sql.

Y si la tabla que intentas borrar no existe, la sentencia falla:

ERROR 1051 (42S02): Unknown table 'tienda.clientes'

DROP TABLE IF EXISTS

Igual que en la creación, IF EXISTS convierte ese error en una nota, y el script sigue adelante tanto si la tabla estaba como si no. Es la forma habitual de borrar en scripts, sobre todo justo antes de volver a crear una tabla desde cero. Sin ir más lejos, el archivo que genera mysqldump lleva un DROP TABLE IF EXISTS delante de cada CREATE TABLE:

DROP TABLE IF EXISTS clientes;
SHOW WARNINGS;

Query OK, 0 rows affected, 1 warning (0.003 sec)

+-------+------+---------------------------------+
| Level | Code | Message                         |
+-------+------+---------------------------------+
| Note  | 1051 | Unknown table 'tienda.clientes' |
+-------+------+---------------------------------+
1 row in set (0.000 sec)

Eliminar una tabla con claves foráneas

Con claves foráneas de por medio, borrar ya no es tan directo. Vamos a volver a crear la tabla clientes con la sentencia de antes y a añadir una tabla de pedidos en la que cada pedido apunta a su cliente:

CREATE TABLE pedidos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id INT UNSIGNED NOT NULL,
  total DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (cliente_id) REFERENCES clientes (id)
);

INSERT INTO clientes (nombre, email) VALUES ('Ana', '[email protected]');
INSERT INTO pedidos (cliente_id, total) VALUES (1, 49.90);

Si ahora intentas borrar clientes, MySQL no te deja, porque hay otra tabla que depende de ella:

DROP TABLE clientes;

ERROR 3730 (HY000): Cannot drop table 'clientes' referenced by a foreign key constraint 'pedidos_ibfk_1' on table 'pedidos'.

El nombre pedidos_ibfk_1 lo ha generado MySQL porque no le hemos dado nombre a la clave foránea. ¿Cómo lo resolvemos? La forma limpia es borrar primero la tabla hija, la que tiene la clave foránea, y después la tabla padre, o borrar las 2 en la misma sentencia, separadas por comas. En una misma sentencia el orden de las tablas no importa:

DROP TABLE pedidos, clientes;

Cuando borras varias tablas a la vez, debes tener en cuenta que, si alguna de ellas no existe, la sentencia falla entera con el mismo ERROR 1051 y no borra ninguna. Con DROP TABLE IF EXISTS pedidos, clientes;, en cambio, MySQL borra las que existen y deja una nota por cada una que no encuentra.

La otra opción, que verás en muchos scripts de importación, es desactivar la comprobación de claves foráneas, borrar y volver a activarla:

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE clientes;
SET FOREIGN_KEY_CHECKS = 1;

Esto solo afecta a tu sesión y funciona, pero la tabla pedidos sigue ahí, con su clave foránea apuntando a una tabla que ya no existe, y sus filas siguen haciendo referencia a clientes que tampoco existen. Al intentar añadir un pedido nuevo, MySQL lo rechaza:

INSERT INTO pedidos (cliente_id, total) VALUES (1, 19.90);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tienda`.`pedidos`, CONSTRAINT `pedidos_ibfk_1` FOREIGN KEY (`cliente_id`) REFERENCES `clientes` (`id`))

Así que desactivar FOREIGN_KEY_CHECKS tiene sentido cuando vas a borrar y volver a crear todas las tablas relacionadas, por ejemplo al importar un volcado completo, pero no para borrar una tabla suelta y dejar el resto como está.

Diferencias entre DELETE, TRUNCATE y DROP

Muchas veces, cuando buscamos cómo borrar una tabla, lo que necesitamos en realidad es vaciarla y conservar su estructura. Para eso MySQL tiene 3 sentencias que se parecen, pero que hacen cosas distintas.

DELETE FROM clientes borra filas. Admite un WHERE para borrar solo las que cumplen una condición, se puede deshacer con ROLLBACK si estás dentro de una transacción, ejecuta los triggers ON DELETE y no reinicia el contador de AUTO_INCREMENT.

TRUNCATE TABLE clientes vacía la tabla entera y conserva su estructura. Internamente, MySQL elimina la tabla y la vuelve a crear, de modo que es mucho más rápido que borrar las filas una a una con DELETE, sobre todo en tablas grandes, pero a cambio hace un commit implícito, no ejecuta los triggers, reinicia el AUTO_INCREMENT y falla si otra tabla tiene una clave foránea que apunta a ella. Con la tabla pedidos de antes, intentar vaciar clientes te devuelve ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint.

DROP TABLE clientes, como hemos visto, elimina la tabla completa, estructura incluida.

La diferencia con el AUTO_INCREMENT se ve muy bien con un ejemplo. Partimos de la tabla clientes recién creada, sin la tabla pedidos, con 2 clientes, Ana y Luis, que han recibido los id 1 y 2. Los borramos con DELETE y damos de alta un cliente nuevo:

DELETE FROM clientes;
INSERT INTO clientes (nombre, email) VALUES ('Marta', '[email protected]');
SELECT id, nombre FROM clientes;

+----+--------+
| id | nombre |
+----+--------+
|  3 | Marta  |
+----+--------+
1 row in set (0.000 sec)

Marta recibe el id 3, porque DELETE no toca el contador. Ahora hacemos lo mismo con TRUNCATE:

TRUNCATE TABLE clientes;
INSERT INTO clientes (nombre, email) VALUES ('Marta', '[email protected]');
SELECT id, nombre FROM clientes;

+----+--------+
| id | nombre |
+----+--------+
|  1 | Marta  |
+----+--------+
1 row in set (0.000 sec)

Esta vez la numeración vuelve a empezar desde 1. Y si lo que te estás planteando es si borrar los registros o solo marcarlos como borrados, en ¿Borrados lógicos o físicos en MySQL?, ¿qué es mejor? te explico qué ofrece cada opción.

Crear y eliminar tablas con Laravel

Si trabajas con Laravel, lo normal es que no escribas estas sentencias a mano: las tablas se crean en las migraciones con Schema::create y se eliminan con Schema::drop o Schema::dropIfExists, que en MySQL terminan ejecutando un CREATE TABLE y un DROP TABLE como los que acabamos de ver. Lo tienes explicado paso a paso en Migraciones en Laravel.

Con esto ya sabes crear una tabla en MySQL, comprobar su estructura y eliminarla sin llevarte sorpresas con las claves foráneas. Todas las opciones de cada sentencia las tienes en la documentación oficial de MySQL 9.7: CREATE TABLE, DROP TABLE y TRUNCATE TABLE.

Espero que te haya resultado útil. Y si quieres seguir trabajando con MySQL desde PHP, no te pierdas el curso Bases de datos MySQL con PHP o el artículo Formulario de inicio de sesión con PHP y MySQL, en el que creamos una tabla de usuarios y la usamos para validar el acceso.