Capítulo 4. Trabajar con estructuras de bases de datos

Este trabajo se ha traducido utilizando IA. Agradecemos tus opiniones y comentarios: translation-feedback@oreilly.com

Este capítulo te muestra cómo crear tus propias bases de datos, añadir y eliminar estructuras como tablas e índices, y tomar decisiones sobre los tipos de columnas de tus tablas. Se centra en la sintaxis y las características de SQL, y no en la semántica de concebir, especificar y refinar el diseño de una base de datos; encontrarás una descripción introductoria de las técnicas de diseño de bases de datos en el Capítulo 2. Para trabajar en este capítulo, necesitas saber cómo trabajar con una base de datos existente y sus tablas, como se explica en el Capítulo 3.

En este capítulo se enumeran las estructuras de la base de datos de ejemplo sakila. Si has seguido las instrucciones para cargar la base de datos en "Ejemplos de modelado de relaciones entre entidades", ya tendrás la base de datos disponible y sabrás cómo restaurarla después de haber modificado sus estructuras.

Cuando termines este capítulo, tendrás todos los conocimientos básicos necesarios para crear, modificar y eliminar estructuras de bases de datos. Junto con las técnicas que aprendiste en el Capítulo 3, tendrás los conocimientos necesarios para realizar una amplia gama de operaciones básicas. Los Capítulos 5 y 7 cubren las habilidades que te permiten realizar operaciones más avanzadas con MySQL.

Crear y utilizar bases de datos

Cuando hayas terminado de diseñar una base de datos, el primer paso práctico que debes dar con MySQL es crearla. Esto se hace con la sentencia CREATE DATABASE. Supongamos que quieres crear una base de datos con el nombre lucy. Esta es la sentencia que escribirías:

mysql> CREATE DATABASE lucy;
Query OK, 1 row affected (0.10 sec)

Asumimos aquí que sabes cómo conectarte utilizando el cliente MySQL, tal y como se describe en el Capítulo 1. También suponemos que puedes conectarte como usuario root o como otro usuario que pueda crear, eliminar y modificar estructuras (encontrarás una discusión detallada sobre los privilegios de usuario en el Capítulo 8). Observa que cuando creas la base de datos, MySQL dice que se ha visto afectada una fila. En realidad, no se trata de una fila normal en una base de datos concreta, sino de una nueva entrada añadida a la lista que puedes ver en con el comando SHOW DATABASES.

Una vez que hayas creado la base de datos, el siguiente paso es utilizar -es decir, elegirla como la base de datos con la que vas a trabajar. Esto se hace con el comando MySQL USE:

mysql> USE lucy;
Database changed

Este comando debe introducirse en una sola línea y no es necesario que termine con punto y coma, aunque solemos hacerlo automáticamente por costumbre. Una vez que hayas utilizado (seleccionado) la base de datos, puedes empezar a crear tablas, índices y otras estructuras siguiendo los pasos que se explican en la siguiente sección.

Antes de pasar a la creación de otras estructuras, vamos a discutir algunas características y limitaciones de la creación de bases de datos. En primer lugar, veamos qué ocurre si intentas crear una base de datos que ya existe:

mysql> CREATE DATABASE lucy;
ERROR 1007 (HY000): Can't create database 'lucy'; database exists

Puedes evitar este error añadiendo la frase clave IF NOT EXISTS a ladeclaración:

mysql> CREATE DATABASE IF NOT EXISTS lucy;
Query OK, 0 rows affected (0.00 sec)

Puedes ver que MySQL no se ha quejado, pero tampoco ha hecho nada: el mensaje 0 rows affected indica que no se ha modificado ningún dato. Este añadido es útil cuando añades sentencias SQL a un script: evita que el script aborte por error.

Veamos cómo elegir nombres de bases de datos y utilizar mayúsculas y minúsculas. Los nombres de las bases de datos definen los nombres físicos de los directorios (o carpetas) en el disco. En algunos sistemas operativos, los nombres de directorio distinguen entre mayúsculas y minúsculas; en otros, las mayúsculas y minúsculas no importan. Por ejemplo, los sistemas tipo Unix, como Linux y macOS, suelen distinguir entre mayúsculas y minúsculas, mientras que Windows no. El resultado es que los nombres de las bases de datos tienen las mismas restricciones: cuando las mayúsculas son importantes para el sistema operativo, también lo son para MySQL. Por ejemplo, en una máquina Linux, LUCY, lucy, y Lucy son nombres de bases de datos diferentes; en Windows, se refieren a una sola base de datos. Utilizar mayúsculas incorrectas en Linux o macOS hará que MySQL se queje:

mysql> SELECT SaKilA.AcTor_id FROM ACTor;
ERROR 1146 (42S02): Table 'sakila.ACTor' doesn't exist

Pero en Windows, esto funcionará normalmente.

Consejo

Para que tu SQL sea independiente de la máquina, te recomendamos que utilices sistemáticamente nombres en minúsculas para las bases de datos (y para las tablas, columnas, alias e índices). Sin embargo, no es un requisito, y como han demostrado los ejemplos anteriores de este libro, puedes utilizar cualquier convención de nombres con la que te sientas cómodo. Sólo tienes que ser coherente y recordar cómo se comporta MySQL en diferentes sistemas operativos.

Este comportamiento está controlado por el parámetro lower_case_table_names. Si se establece en 0, los nombres de las tablas se almacenan tal y como se especifican, y las comparaciones distinguen entre mayúsculas y minúsculas. Si se establece en 1, los nombres de las tablas se almacenan en minúsculas en el disco y las comparaciones no distinguen mayúsculas de minúsculas. Si este parámetro se establece en 2, los nombres de las tablas se almacenan tal y como se indican, pero se comparan en minúsculas. En Windows, el valor por defecto es 1. En macOS, el valor por defecto es 2. En Linux, no se admite un valor de 2; el servidor fuerza el valor a 0 en su lugar.

Existen otras restricciones para los nombres de las bases de datos. Pueden tener como máximo 64 caracteres de longitud. Tampoco debes utilizar palabras reservadas de MySQL, como SELECT, FROM, y USE, como nombres de estructuras; pueden confundir al analizador sintáctico de MySQL, impidiéndole interpretar el significado de tus sentencias. Puedes eludir esta restricción encerrando la palabra reservada entre comillas (`), pero te costará más acordarte de hacerlo de lo que vale. Además, no puedes utilizar determinados caracteres en los nombres -específicamente, la barra oblicua, la barra invertida, el punto y coma y el punto- y un nombre de base de datos no puede terminar en un espacio en blanco. De nuevo, el uso de estos caracteres confunde al analizador sintáctico de MySQL y puede dar lugar a un comportamiento impredecible. Por ejemplo, esto es lo que ocurre cuando insertas un punto y coma en el nombre de una base de datos:

mysql> CREATE DATABASE IF NOT EXISTS lu;cy;
Query OK, 1 row affected (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'cy' at line 1

Como puede haber más de una sentencia SQL en una sola línea, el resultado es que se crea una base de datos lu, y luego se genera un error por la sentencia SQL muy corta e inesperada cy;. Si realmente quieres crear una base de datos con un punto y coma en su nombre, puedes hacerlo con puntos suspensivos:

mysql> CREATE DATABASE IF NOT EXISTS `lu;cy`;
Query OK, 1 row affected (0.01 sec)

Y puedes ver que ahora tienes dos bases de datos nuevas:

mysql> SHOW DATABASES LIKE `lu%`;
+----------------+
| Database (lu%) |
+----------------+
| lu             |
| lu;cy          |
+----------------+
2 rows in set (0.01 sec)

Crear tablas

Esta sección trata temas sobre las estructuras de las tablas. Te mostramos cómo hacerlo:

  • Crea tablas, mediante ejemplos introductorios.

  • Elige nombres para las tablas y las estructuras relacionadas con ellas.

  • Comprende y elige los tipos de columnas.

  • Comprender y elegir claves e índices.

  • Utiliza la función propia de MySQL AUTO_INCREMENT.

Cuando termines esta sección, habrás completado todo el material básico sobre la creación de estructuras de bases de datos; el resto de este capítulo trata de la base de datos de ejemplo sakila y de cómo modificar y eliminar las estructuras existentes.

Conceptos básicos

Para los ejemplos de esta sección, supondremos que aún no se ha creado la base de datos sakila . Si quieres seguir los ejemplos y ya has cargado la base de datos, puedes abandonarla para esta sección y volver a cargarla más tarde; al abandonarla se eliminan la base de datos, sus tablas y todos los datos, pero el original es fácil de restaurar siguiendo los pasos de "Ejemplos de modelado de relaciones entre entidades". A continuación te explicamos cómo abandonarla temporalmente:

mysql> DROP DATABASE sakila;
Query OK, 23 rows affected (0.06 sec)

La declaración DROP se trata con más detalle al final de este capítulo, en "Eliminar estructuras".

Para empezar, crea la base de datos sakila utilizando la sentencia

mysql> CREATE DATABASE sakila;
Query OK, 1 row affected (0.00 sec)

A continuación, selecciona la base de datos con:

mysql> USE sakila;
Database changed

Ya estamos listos para empezar a crear las tablas que contendrán nuestros datos de . Vamos a crear una tabla para guardar los datos de los actores. Por ahora, vamos a tener una estructura simplificada, ya añadiremos más complejidad más adelante. Ésta es la sentencia que utilizaremos

mysql> CREATE TABLE actor (
    -> actor_id SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    -> first_name VARCHAR(45) DEFAULT NULL,
    -> last_name VARCHAR(45),
    -> last_update TIMESTAMP,
    -> PRIMARY KEY (actor_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

Que no cunda el pánico: aunque MySQL informe de que se han visto afectadas cero filas, ha creado la tabla:

mysql> SHOW tables;
+------------------+
| Tables_in_sakila |
+------------------+
| actor            |
+------------------+
1 row in set (0.01 sec)

Veamos todo esto en detalle. El comando CREATE TABLE tiene tres secciones principales:

  1. La sentencia CREATE TABLE, que va seguida del nombre de la tabla a crear. En este ejemplo, es actor.

  2. Una lista de una o varias columnas que se añadirán a la tabla. En este ejemplo, hemos añadido unas cuantas: actor_id SMALLINT UNSIGNED NOT NULL DEFAULT 0, first_name VARCHAR(45) DEFAULT NULL, last_name VARCHAR(45), y last_update TIMESTAMP. Hablaremos de ellas dentro de un momento.

  3. Definiciones de clave opcionales. En este ejemplo, hemos definido una única clave: PRIMARY KEY (actor_id). Hablaremos en detalle de las claves y los índices más adelante en este capítulo.

Observa que el componente CREATE TABLE va seguido de un paréntesis de apertura que va acompañado de un paréntesis de cierre al final de la frase. Observa también que los demás componentes están separados por comas. Hay otros elementos que puedes añadir a una declaración CREATE TABLE, y hablaremos de algunos de ellos dentro de un momento.

Hablemos de las especificaciones de las columnas. La sintaxis básica es la siguiente: name type [NOT NULL | NULL] [DEFAULT value]. El campo name es el nombre de la columna, y tiene las mismas limitaciones que los nombres de las bases de datos, como se ha comentado en el apartado anterior. Puede tener un máximo de 64 caracteres, no se permiten barras diagonales ni diagonales inversas, no se permiten puntos, no puede terminar en espacios en blanco y la distinción entre mayúsculas y minúsculas depende del sistema operativo subyacente. El campo type define cómo y qué se almacena en la columna; por ejemplo, hemos visto que puede definirse como VARCHAR para cadenas, SMALLINT para números o TIMESTAMP para fecha y hora.

Si especificas NOT NULL, una fila no es válida sin un valor para la columna; si especificas NULL u omites esta cláusula, puede existir una fila sin un valor para la columna. Si especificas un value con la cláusula DEFAULT, se utilizará para rellenar la columna cuando no proporciones datos de otro modo; esto es especialmente útil cuando reutilizas con frecuencia un valor por defecto, como el nombre de un país. La cláusula value debe ser una constante (como 0, "cat", o 20060812045623), excepto si la columna es del tipo TIMESTAMP. Los tipos se tratan en detalle en "Tipos de columnas".

Las funciones NOT NULL y DEFAULT pueden utilizarse juntas. Si especificas NOT NULL y añades un valor DEFAULT, se utilizará el valor por defecto cuando no proporciones un valor para la columna. A veces, esto funciona bien:

mysql> INSERT INTO actor(first_name) VALUES ('John');
Query OK, 1 row affected (0.01 sec)

Y a veces no:

mysql> INSERT INTO actor(first_name) VALUES ('Elisabeth');
ERROR 1062 (23000): Duplicate entry '0' for key 'actor.PRIMARY'

Que funcione o no depende de las restricciones y condiciones subyacentes de la base de datos: en este ejemplo, actor_id tiene un valor por defecto de 0, pero también es la clave primaria. Tener dos filas con el mismo valor de clave primaria no está permitido, por lo que el segundo intento de insertar una fila sin valores (y un valor de clave primaria resultante de 0) falla. Hablamos en detalle de las claves primarias en "Claves e índices".

Los nombres de columnas tienen menos restricciones que los nombres de bases de datos y tablas . Además, los nombres no distinguen entre mayúsculas y minúsculas y son portables en todas las plataformas. Todos los caracteres están permitidos en los nombres de columna, aunque si quieres terminarlos con espacios en blanco o incluir puntos u otros caracteres especiales, como un punto y coma o un guión, tendrás que encerrar el nombre entre comillas (`). Una vez más, te recomendamos que elijas sistemáticamente nombres en minúsculas para las opciones impulsadas por el desarrollador (como los nombres de bases de datos, alias y tablas) y evites los caracteres que te obliguen a recordar el uso de puntos y comas.

Nombrar las columnas y otros objetos de la base de datos es algo de preferencia personal cuando se empieza de nuevo (puedes inspirarte mirando las bases de datos de ejemplo) o una cuestión de seguir las normas cuando se trabaja en una base de código existente. En general, procura evitar las repeticiones: en una tabla llamada actor, utiliza el nombre de columna first_name en lugar de actor_first_name, que parecería redundante cuando fuera precedido por el nombre de la tabla en una consulta compleja (actor.actor_first_name frente a actor.first_name). Una excepción a esto es cuando se utiliza el omnipresente nombre de columna id; evita utilizarlo o antepón el nombre de la tabla para mayor claridad (por ejemplo, actor_id). Es una buena práctica utilizar el carácter de subrayado para separar palabras. Podrías utilizar otro carácter, como un guión o una barra oblicua, pero tendrías que acordarte de encerrar los nombres con puntos suspensivos (por ejemplo, actor-id). También puedes omitir por completo el formato de separación de palabras, pero podría decirse que "CamelCase" es más difícil de leer. Al igual que ocurre con los nombres de bases de datos y tablas, la longitud máxima permitida para un nombre de columna es de 64 caracteres.

Intercalación y juegos de caracteres

Cuando comparas u ordenas cadenas, la forma en que MySQL evalúa el resultado depende del conjunto de caracteres y de la intercalación utilizados. Los juegos de caracteres, o charsets, definen qué caracteres pueden almacenarse; por ejemplo, puede que necesites almacenar caracteres no ingleses como ю o ü. Una intercalación define cómo se ordenan las cadenas, y hay diferentes intercalaciones para los distintos idiomas: por ejemplo, la posición del carácter ü en el alfabeto es diferente en dos ordenaciones alemanas, y diferente de nuevo en sueco y finlandés. Como no todo el mundo quiere almacenar cadenas en inglés, es importante que un servidor de bases de datos sea capaz de gestionar caracteres no ingleses y distintas formas de ordenarcaracteres.

Entendemos que hablar de colaciones y conjuntos de caracteres puede parecer demasiado avanzado cuando estás empezando a aprender MySQL. Sin embargo, también pensamos que son temas que merece la pena tratar, ya que los conjuntos de caracteres y las colaciones no coincidentes pueden provocar situaciones inesperadas, como la pérdida de datos y resultados incorrectos de las consultas. Si lo prefieres, puedes saltarte esta sección y algunas de las discusiones posteriores de este capítulo y volver a estos temas cuando quieras aprender sobre ellos específicamente. Eso no afectará a tu comprensión del resto del material de este libro.

En nuestros ejemplos anteriores de comparación de cadenas, ignoramos la cuestión de la colación y el conjunto de caracteres y dejamos que MySQL utilizara sus valores por defecto. En versiones de MySQL anteriores a la 8.0, el juego de caracteres por defecto es latin1, y la colación por defecto es latin1_swedish_ci. MySQL 8.0 cambió los valores por defecto, y ahora el juego de caracteres por defecto es utf8mb4, y la intercalación por defecto es utf8mb4_0900_ai_ci. MySQL puede configurarse para utilizar diferentes conjuntos de caracteres y órdenes de intercalación en los niveles de conexión, base de datos, tabla y columna. Los resultados mostrados aquí son de MySQL 8.0.

Puedes listar los juegos de caracteres disponibles en tu servidor con el comando SHOW CHARACTER SET. Esto muestra una breve descripción de cada juego de caracteres, su intercalación por defecto y el número máximo de bytes utilizados para cada carácter en ese juego de caracteres:

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| ...                                                                       |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

Por ejemplo, el juego de caracteres latin1 es en realidad, el juego de caracteres de la página de código 1252 de Windows que admite las lenguas de Europa Occidental. La intercalación por defecto para este juego de caracteres es latin1_swedish_ci, que sigue las convenciones suecas para ordenar los caracteres acentuados (el inglés se maneja como cabría esperar). Esta intercalación no distingue entre mayúsculas y minúsculas, como indican las letras ci. Por último, cada carácter ocupa 1 byte. En comparación, si utilizas el juego de caracteres por defecto utf8mb4, cada carácter ocupará hasta 4 bytes de almacenamiento. A veces, tiene sentido cambiar el valor por defecto. Por ejemplo, no hay razón para almacenar datos codificados en base64 (que, por definición, son ASCII) en utf8mb4.

Del mismo modo, puedes enumerar las órdenes de cotejo y los conjuntos de caracteres a los que se aplican:

mysql> SHOW COLLATION;
+---------------------+----------+-----+---------+...+---------------+
| Collation           | Charset  | Id  | Default |...| Pad_attribute |
+---------------------+----------+-----+---------+...+---------------+
| armscii8_bin        | armscii8 |  64 |         |...| PAD SPACE     |
| armscii8_general_ci | armscii8 |  32 | Yes     |...| PAD SPACE     |
| ascii_bin           | ascii    |  65 |         |...| PAD SPACE     |
| ascii_general_ci    | ascii    |  11 | Yes     |...| PAD SPACE     |
| ...                                            |...|               |
| utf8mb4_0900_ai_ci  | utf8mb4  | 255 | Yes     |...| NO PAD        |
| utf8mb4_0900_as_ci  | utf8mb4  | 305 |         |...| NO PAD        |
| utf8mb4_0900_as_cs  | utf8mb4  | 278 |         |...| NO PAD        |
| utf8mb4_0900_bin    | utf8mb4  | 309 |         |...| NO PAD        |
| ...                                            |...|               |
| utf8_unicode_ci     | utf8     | 192 |         |...| PAD SPACE     |
| utf8_vietnamese_ci  | utf8     | 215 |         |...| PAD SPACE     |
+---------------------+----------+-----+---------+...+---------------+
272 rows in set (0.02 sec)
Nota

El número de juegos de caracteres y colaciones disponibles depende de cómo se haya construido y empaquetado el servidor MySQL. Los ejemplos que mostramos proceden de una instalación por defecto de MySQL 8.0, y se pueden ver los mismos números en Linux y Windows. MariaDB 10.5, sin embargo, tiene 322 colaciones pero 40 juegos de caracteres.

Puedes ver los valores predeterminados actuales en tu servidor de la siguiente manera:

mysql> SHOW VARIABLES LIKE 'c%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| ...                                                       |
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
| ...                                                       |
| collation_connection     | utf8mb4_0900_ai_ci             |
| collation_database       | utf8mb4_0900_ai_ci             |
| collation_server         | utf8mb4_0900_ai_ci             |
| ...                                                       |
+--------------------------+--------------------------------+
21 rows in set (0.00 sec)

Al crear una base de datos, puedes establecer el juego de caracteres por defecto y el orden de clasificación para la base de datos y sus tablas. Por ejemplo, si quieres utilizar el juego de caracteres utf8mb4 y el orden de intercalación utf8mb4_ru_0900_as_cs (distingue mayúsculas de minúsculas), escribirías:

mysql> CREATE DATABASE rose DEFAULT CHARACTER SET utf8mb4
    -> COLLATE utf8mb4_ru_0900_as_cs;
Query OK, 1 row affected (0.00 sec)

Normalmente, no hay necesidad de hacer esto si has instalado MySQL correctamente para tu idioma y región y si no planeas internacionalizar tu aplicación. Con utf8mb4 por defecto desde MySQL 8.0, hay incluso menos necesidad de cambiar el conjunto de caracteres. También puedes controlar el conjunto de caracteres y la colación para tablas o columnas individuales, pero no entraremos aquí en los detalles de cómo hacerlo. Hablaremos de cómo afectan las colaciones a los tipos de cadenas en "Tipos de cadenas".

Otras características

Esta sección describe brevemente otras funciones de la sentencia CREATE TABLE . Incluye un ejemplo en el que se utiliza la función IF NOT EXISTS, y una lista de funciones avanzadas y dónde encontrar más información sobre ellas en este libro. La sentencia mostrada es la representación completa de la tabla extraída de la base de datos sakila, a diferencia del ejemplo simplificado anterior.

Puedes utilizar la frase de palabras clave IF NOT EXISTS al crear una tabla , y funciona de forma muy similar a las bases de datos. Aquí tienes un ejemplo que no informará de un error aunque la tabla actor exista:

mysql> CREATE TABLE IF NOT EXISTS actor (
    -> actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> first_name VARCHAR(45) NOT NULL,
    -> last_name VARCHAR(45) NOT NULL,
    -> last_update TIMESTAMP NOT NULL DEFAULT
    -> CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> PRIMARY KEY  (actor_id),
    -> KEY idx_actor_last_name (last_name));
Query OK, 0 rows affected, 1 warning (0.01 sec)

Puedes ver que no hay ninguna fila afectada y que se informa de una advertencia. Echemos un vistazo:

mysql> SHOW WARNINGS;
+-------+------+------------------------------+
| Level | Code | Message                      |
+-------+------+------------------------------+
| Note  | 1050 | Table 'actor' already exists |
+-------+------+------------------------------+
1 row in set (0.01 sec)

Existe una amplia gama de funciones adicionales que puedes añadir a una sentencia CREATE TABLE, de las cuales sólo unas pocas están presentes en este ejemplo. Muchas de ellas son avanzadas y no se tratan en este libro, pero puedes encontrar más información en el Manual de Referencia de MySQL en la sección sobre la sentenciaCREATE TABLE . Entre estas funciones adicionales se incluyen las siguientes:

La función AUTO_INCREMENT para columnas numéricas

Esta función te permite crear automáticamente identificadores únicos para una tabla. Lo tratamos en detalle en "La función AUTO_INCREMENTO".

Comentarios de la columna

Puedes añadir un comentario a una columna; esto se muestra cuando utilizas el comando SHOW CREATE TABLE, del que hablaremos más adelante en esta sección.

Restricciones de clave foránea

Puedes indicar a MySQL que compruebe si los datos de una o varias columnas coinciden con los datos de otra tabla. Por ejemplo, la base de datos sakila tiene una restricción de clave foránea en la columna city_id de la tabla address, que hace referencia a la columna city_id de la tabla city. Esto significa que es imposible tener una dirección en una ciudad que no esté presente en la tabla city. Hemos introducido las restricciones de clave externa en el Capítulo 2, y veremos qué motores admiten restricciones de clave externa en "Motores de almacenamiento alternativos". No todos los motores de almacenamiento de MySQL admiten claves foráneas.

Crear tablas temporales

Si creas una tabla utilizando la frase clave CREATE TEMPORARY TABLE , será eliminada (dropped) cuando se cierre la conexión. Esto es útil para copiar y reformatear datos, porque no tienes que acordarte de limpiarlos. A veces también se utilizan tablas temporales como optimización para mantener algunos datos intermedios.

Opciones avanzadas de la tabla

Puedes controlar una amplia gama de características de la tabla utilizando las opciones de la tabla. Entre ellas se incluyen el valor inicial de AUTO_INCREMENT, la forma en que se almacenan los índices y las filas, y opciones para anular la información que el optimizador de consultas MySQL obtiene de la tabla. También es posible especificar columnas generadas, que contienen datos como la suma de otras dos columnas, así como índices sobre dichas columnas.

Control de las estructuras de índices

Algunos motores de almacenamiento de MySQL te permiten especificar y controlar qué tipo de estructura interna -como un árbol B o una tabla hash- utiliza MySQL para sus índices. También puedes decirle a MySQL que quieres un índice de texto completo o de datos espaciales en una columna, lo que permite tipos especiales de búsqueda.

Partición

MySQL admite diferentes estrategias de particionado, que puedes seleccionar en el momento de crear la tabla o posteriormente. En este libro no trataremos la partición.

Puedes ver la sentencia utilizada para crear una tabla utilizando la sentencia SHOW CREATE TABLE introducida en el Capítulo 3. Esto suele mostrar una salida que incluye algunas de las funciones avanzadas que acabamos de comentar; la salida rara vez coincide con lo que realmente has escrito para crear la tabla. Aquí tienes un ejemplo para la tabla actor:

mysql> SHOW CREATE TABLE actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Observarás que la salida incluye contenido añadido por MySQL que no estaba en nuestra sentencia CREATE TABLE original:

  • Los nombres de la tabla y las columnas van entre comillas. Esto no es necesario, pero evita los problemas de análisis que puede causar el uso de palabras reservadas y caracteres especiales, como ya se ha explicado.

  • Se incluye una cláusula adicional por defecto ENGINE, que indica explícitamente el tipo de tabla que debe utilizarse. La configuración en una instalación por defecto de MySQL es InnoDB, por lo que no tiene ningún efecto en este ejemplo.

  • Se incluye una cláusula adicional DEFAULT CHARSET, que indica a MySQL qué juego de caracteres utilizan las columnas de la tabla. De nuevo, esto no tiene ningún efecto en una instalación por defecto.

Tipos de columnas

Esta sección describe los tipos de columna que puedes utilizar en MySQL. Explica cuándo debe utilizarse cada uno y las limitaciones que tiene. Los tipos están agrupados por su finalidad. Cubriremos los tipos de datos más utilizados y mencionaremos de pasada tipos más avanzados o menos utilizados. Eso no significa que no tengan utilidad, pero considera el aprendizaje de los mismos como un ejercicio. Lo más probable es que no recuerdes cada uno de los tipos de datos y sus entresijos particulares, y no pasa nada. Merece la pena releer este capítulo más adelante y consultar la documentación de MySQL sobre el tema para mantener al día tus conocimientos.

Tipos enteros

Empezaremos con los tipos de datos numéricos, y más concretamente con los tipos enteros, o los tipos que contienen números enteros concretos. En primer lugar, los dos tipos enteros más populares:

INT[(width)] [UNSIGNED] [ZEROFILL]

Es el tipo numérico más utilizado; almacena valores enteros (números enteros) en el rango -2.147.483.648 a 2.147.483.647. Si se añade la palabra clave opcional UNSIGNED, el rango es de 0 a 4.294.967.295. La palabra clave INT es la abreviatura de INTEGER, y pueden utilizarse indistintamente. Una columna INT requiere 4 bytes de espacio de almacenamiento.

INT, al igual que otros tipos enteros, tiene dos propiedades específicas para MySQL: argumentos opcionales width y ZEROFILL argumentos. No forman parte de un estándar SQL, y a partir de MySQL 8.0 están obsoletos. Aún así, seguro que las encuentras en muchas bases de código, así que hablaremos brevemente de ambas.

El parámetro width especifica la anchura de visualización, que las aplicaciones pueden leer como parte de los metadatos de la columna. A diferencia de los parámetros en una posición similar para otros tipos de datos, este parámetro no tiene ningún efecto sobre las características de almacenamiento de un tipo de entero concreto y no restringe el rango utilizable de valores. INT(4) y INT(32) son iguales a efectos de almacenamiento de datos.

ZEROFILL es un argumento adicional que se utiliza para rellenar los valores con ceros a la izquierda hasta la longitud especificada por el parámetro width parámetro. Si utilizas ZEROFILL, MySQL añade automáticamente UNSIGNED a la declaración (ya que el relleno de ceros sólo tiene sentido en el contexto de números positivos).

En algunas aplicaciones en las que ZEROFILL y width son útiles, se puede utilizar la función LPAD(), o almacenar los números formateados en columnas CHAR.

BIGINT[(width)] [UNSIGNED] [ZEROFILL]

En el mundo de los datos cada vez más grandes, tener tablas con recuentos de filas demiles de millones es cada vez más común. Incluso las columnas sencillas de tipo id pueden necesitar un rango más amplio que el que proporciona un INT normal. BIGINT resuelve ese problema. Es un tipo entero grande con un rango con signo de -9.223.372.036.854.775.808 a 9.223.372.036.854.775.807. Un BIGINT sin signo puede almacenar números de 0 a 18.446.744.073.709.551.615. Las columnas de este tipo necesitarán 8 bytes de almacenamiento.

Internamente, todos los cálculos dentro de MySQL se realizan utilizando valores con signo BIGINT o DOUBLE. La consecuencia importante de esto es que debes tener mucho cuidado cuando manejes números extremadamente grandes. Hay dos cuestiones que debes tener en cuenta. En primer lugar, los enteros grandes sin signo mayores que 9.223.372.036.854.775.807 sólo deben utilizarse con funciones de bits. En segundo lugar, si el resultado de una operación aritmética es mayor que 9.223.372.036.854.775.807, podrían observarse resultados inesperados.

Por ejemplo:

mysql> CREATE TABLE test_bigint (id BIGINT UNSIGNED);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test_bigint VALUES (18446744073709551615);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test_bigint VALUES (18446744073709551615-1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test_bigint VALUES (184467440737095516*100);
ERROR 1690 (22003): BIGINT value
is out of range in '(184467440737095516 * 100)'

Aunque 18.446.744.073.709.551.600 es menor que 18.446.744.073.709.551.615, como internamente se utiliza un BIGINT con signo para la multiplicación, se observa el error de fuera de rango.

Consejo

El tipo de datos SERIAL puede utilizarse como un alias de para BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. A menos que debas optimizar el tamaño de los datos y el rendimiento, considera la posibilidad de utilizar SERIAL para tus columnas de tipo id. Incluso el UNSIGNED INT puede agotarse mucho más rápido de lo que cabría esperar, y a menudo en el peor momento posible.

Ten en cuenta que, aunque es posible almacenar cada número entero como BIGINT, eso supone un desperdicio de espacio de almacenamiento. Además, como ya hemos dicho, el parámetro width no limita el rango de valores. Para ahorrar espacio y poner restricciones a los valores almacenados, debes utilizar distintos tipos de enteros:

SMALLINT[(width)] [UNSIGNED] [ZEROFILL]

Almacena enteros pequeños, con un rango de -32.768 a 32.767 con signo y de 0 a 65.535 sin signo. Ocupa 2 bytes de almacenamiento.

TINYINT[(width)] [UNSIGNED] [ZEROFILL]

El tipo de datos numéricos más pequeño, que almacena números enteros aún más pequeños. El rango de este tipo es de -128 a 127 con signo y de 0 a 255 sin signo. Sólo ocupa 1 byte de almacenamiento.

BOOL[(width)]

Abreviatura de BOOLEAN, y sinónimo de TINYINT(1). Normalmente, los tipos booleanos sólo aceptan dos valores: verdadero o falso. Sin embargo, como BOOL en MySQL es un tipo entero, puedes almacenar valores de -128 a 127 en un BOOL. El valor 0 se tratará como falso, y todos los valores distintos de cero como verdaderos. También es posible utilizar alias especiales true y false para 1 y 0, respectivamente. Aquí tienes algunos ejemplos:

mysql> CREATE TABLE test_bool (i BOOL);
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO test_bool VALUES (true),(false);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> INSERT INTO test_bool VALUES (1),(0),(-128),(127);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> SELECT i, IF(i,'true','false') FROM test_bool;
+------+----------------------+
| i    | IF(i,'true','false') |
+------+----------------------+
|    1 | true                 |
|    0 | false                |
|    1 | true                 |
|    0 | false                |
| -128 | true                 |
|  127 | true                 |
+------+----------------------+
6 rows in set (0.01 sec)
MEDIUMINT[(width)] [UNSIGNED] [ZEROFILL]

Almacena valores en el rango con signo de -8.388.608 a 8.388.607 y en el rango sin signo de 0 a 16.777.215. Ocupa 3 bytes de almacenamiento.

BIT[(M)]

Tipo especial utilizado para almacenar valores de bits. M especifica el número de bits por valor y por defecto es 1 si se omite. MySQL utiliza una sintaxis b'value para los valores binarios.

Tipos de punto fijo

Los tipos de datos DECIMAL y NUMERIC de MySQL son los mismos , por lo que, aunque aquí sólo describiremos DECIMAL, esta descripción también se aplica a NUMERIC. La principal diferencia entre los tipos de punto fijo y los de punto flotante es la precisión. Para los tipos de punto fijo, el valor recuperado es idéntico al valor almacenado; no siempre es así con los tipos que contienen puntos decimales, como los tipos FLOAT y DOUBLE descritos más adelante. Esta es la propiedad más importante del tipo de datos DECIMAL, que es un tipo numérico de uso común en MySQL:

DECIMAL[(width[,decimals])] [UNSIGNED] [ZEROFILL]

Almacena un número de coma fija, como un salario o una distancia, con un total de width dígitos de los cuales algún número menor decimals que siguen a un punto decimal. Por ejemplo, una columna declarada como price DECIMAL(6,2) puede utilizarse para almacenar valores en el rango de -9.999,99 a 9.999,99. price DECIMAL(10,4) permitiría valores como 123.456,1234.

Antes de MySQL 5.7, si intentabas almacenar un valor fuera de este rango, se almacenaba como el valor más cercano del rango permitido. Por ejemplo, 100 se almacenaría como 99,99, y -100 se almacenaría como -99,99. Sin embargo, a partir de la versión 5.7.5, el modo SQL por defecto incluye el modo STRICT_TRANS_TABLES, que prohíbe éste y otros comportamientos inseguros. Utilizar el comportamiento antiguo es posible, pero podría provocar la pérdida de datos.

Los modos SQL son configuraciones especiales que controlan el comportamiento de MySQL cuando se trata de consultas. Por ejemplo, pueden restringir el comportamiento "inseguro" o afectar al modo en que se interpretan las consultas. Para el aprendizaje de MySQL, te recomendamos que te ciñas a los valores predeterminados, ya que son seguros. Cambiar los modos SQL puede ser necesario para la compatibilidad con aplicaciones heredadas a través de las versiones de MySQL.

El parámetro width es opcional, y se asume un valor de 10 cuando se omite. El número de decimals también es opcional y, si se omite, se asume el valor 0; el valor máximo de decimals no puede superar el valor de width. El valor máximo de width es 65, y el valor máximo de decimals es 30.

Si sólo almacenas valores positivos, puedes utilizar la palabra clave UNSIGNED como se describe en INT. Si quieres un relleno cero, utiliza la palabra clave ZEROFILL para obtener el mismo comportamiento que el descrito para INT. La palabra clave DECIMAL tiene tres alternativas idénticas e intercambiables: DEC, NUMERIC, y FIXED.

Los valores de las columnas de DECIMAL se almacenan utilizando un formato binario. Este formato utiliza 4 bytes por cada nueve dígitos.

Tipos de coma flotante

Además del tipo de punto fijo DECIMAL descrito en la sección anterior , hay otros dos tipos que admiten puntos decimales: DOUBLE (también conocido como REAL) y FLOAT. Están diseñados para almacenar valores numéricos aproximados en lugar de los valores exactos almacenados por DECIMAL.

¿Por qué querrías valores aproximados? La respuesta es que muchos números con punto decimal son aproximaciones de cantidades reales. Por ejemplo, supongamos que ganas 50.000 $ al año y quieres almacenarlo como salario mensual. Si lo conviertes en una cantidad mensual, son 4.166 $ más 66 y 2/3 céntimos. Si lo almacenas como 4.166,67 $, no es lo suficientemente exacto como para convertirlo en un salario anual (ya que 12 multiplicado por 4.166,67 $ son 50.000,04 $). Sin embargo, si almacenas 2/3 con suficientes decimales, es una aproximación más cercana. Comprobarás que es lo suficientemente exacta como para multiplicar correctamente y obtener el valor original en un entorno de alta precisión como MySQL, utilizando sólo un poco de redondeo. Ahí es donde DOUBLE y FLOAT son útiles: te permiten almacenar valores como 2/3 o pi con un gran número de decimales, permitiendo representaciones aproximadas precisas de cantidades exactas. Posteriormente, puedes utilizar la función ROUND() para restablecer los resultados a una precisión determinada.

Continuemos con el ejemplo anterior utilizando DOUBLE. Supongamos que creas una tabla comola siguiente

mysql> CREATE TABLE wage (monthly DOUBLE);
Query OK, 0 rows affected (0.09 sec)

Ahora puedes introducir el salario mensual utilizando:

mysql> INSERT INTO wage VALUES (50000/12);
Query OK, 1 row affected (0.00 sec)

Y mira lo que hay almacenado:

mysql> SELECT * FROM wage;
+----------------+
| monthly        |
+----------------+
| 4166.666666666 |
+----------------+
1 row in set (0.00 sec)

Sin embargo, cuando lo multiplicas para obtener un valor anual, obtienes una aproximación de gran precisión:

mysql> SELECT monthly*12 FROM wage;
+--------------------+
| monthly*12         |
+--------------------+
| 49999.999999992004 |
+--------------------+
1 row in set (0.00 sec)

Para recuperar el valor original, aún tienes que realizar el redondeo con la precisión deseada. Por ejemplo, tu empresa podría requerir una precisión de cinco decimales. En este caso, podrías recuperar el valor original con:

mysql> SELECT ROUND(monthly*12,5) FROM wage;
+---------------------+
| ROUND(monthly*12,5) |
+---------------------+
|         50000.00000 |
+---------------------+
1 row in set (0.00 sec)

Pero una precisión de ocho decimales no daría como resultado el valor original:

mysql> SELECT ROUND(monthly*12,8) FROM wage;
+---------------------+
| ROUND(monthly*12,8) |
+---------------------+
|      49999.99999999 |
+---------------------+
1 row in set (0.00 sec)

Es importante comprender la naturaleza imprecisa y aproximada de los tipos de datos en coma flotante.

Aquí tienes los detalles de los tipos FLOAT y DOUBLE:

FLOAT[(width, decimals)] [UNSIGNED] [ZEROFILL] o FLOAT[(precision)] [UNSIGNED] [ZEROFILL]

Almacena números en coma flotante. Tiene dos sintaxis opcionales: la primera permite un número opcional de decimals y una indicación opcional widthy la segunda permite una precision que controla la precisión de la aproximación medida en bits. Sin parámetros (el uso típico), el tipo almacena valores pequeños de coma flotante de 4 bytes y precisión simple. Cuando precision está entre 0 y 24, se produce el comportamiento por defecto. Cuando precision está entre 25 y 53, el tipo se comporta como DOUBLE. El parámetro width no afecta a lo que se almacena, sólo a lo que se muestra. Las opciones UNSIGNED y ZEROFILL se comportan como para INT.

DOUBLE[(width, decimals)] [UNSIGNED] [ZEROFILL]

Almacena números en coma flotante. Permite especificar un número opcional de decimals y una indicación opcional width. Sin parámetros (el uso típico), el tipo almacena valores normales de coma flotante de 8 bytes y doble precisión. El parámetro width no afecta a lo que se almacena, sólo a lo que se muestra. Las opciones UNSIGNED y ZEROFILL se comportan igual que INT. El tipo DOUBLE tiene dos sinónimos idénticos: REAL y DOUBLE PRECISION.

Tipos de cadena

Los tipos de datos cadena se utilizan para almacenar texto y, menos obviamente, datos binarios. MySQL admite los siguientes tipos de cadenas:

[NATIONAL] VARCHAR(width) [CHARACTER SET charset_name] [COLLATE collation_name]

Probablemente el tipo de cadena más utilizado, VARCHAR almacena cadenas de longitud variable hasta un máximo de width. El valor máximo de width es de 65.535 caracteres. La mayor parte de la información aplicable a este tipo se aplicará también a otros tipos de cadena.

Los tipos CHAR y VARCHAR son muy similares, pero hay algunas distinciones importantes. VARCHAR incurre en uno o dos bytes extra de sobrecarga para almacenar el valor de la cadena, dependiendo de si el valor es menor o mayor que 255 bytes. Ten en cuenta que este tamaño es diferente de la longitud de la cadena en caracteres, ya que algunos caracteres pueden requerir hasta 4 bytes de espacio. Podría parecer obvio, entonces, que VARCHAR es menos eficiente. Sin embargo, esto no siempre es cierto. Dado que VARCHAR puede almacenar cadenas de longitud arbitraria (hasta la longitud width definido), las cadenas más cortas requerirán menos espacio de almacenamiento que un CHAR de longitud similar.

Otra diferencia entre CHAR y VARCHAR es su gestión de los espacios finales . VARCHAR conserva los espacios finales hasta el ancho de columna especificado y truncará el exceso, produciendo una advertencia. Como se verá más adelante, los valores de CHAR se rellenan por la derecha hasta el ancho de columna, y no se conservan los espacios finales. Para VARCHAR, los espacios finales son significativos a menos que se recorten y contarán como valores únicos. Hagamos una demostración:

mysql> CREATE TABLE test_varchar_trailing(d VARCHAR(2) UNIQUE);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO test_varchar_trailing VALUES ('a'), ('a ');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT d, LENGTH(d) FROM test_varchar_trailing;
+------+-----------+
| d    | LENGTH(d) |
+------+-----------+
| a    |         1 |
| a    |         2 |
+------+-----------+
2 rows in set (0.00 sec)

La segunda fila que hemos insertado tiene un espacio al final, y como el valor width para la columna d es 2, ese espacio cuenta para la unicidad de una fila. Sin embargo, si intentamos insertar una fila con dos espacios finales

mysql> INSERT INTO test_varchar_trailing VALUES ('a  ');
ERROR 1062 (23000): Duplicate entry 'a '
for key 'test_varchar_trailing.d'

MySQL se niega a aceptar la nueva fila. VARCHAR(2) trunca implícitamente los espacios finales más allá del conjunto widthpor lo que el valor almacenado cambia de "a " (con un doble espacio después de a) a "a " (con un único espacio después de a). Como ya tenemos una fila con ese valor, se produce un error de entrada duplicada. Este comportamiento de VARCHAR y TEXT puede controlarse cambiando la intercalación de las columnas. Algunas colaciones, como latin1_bin, tienen el atributo PAD SPACE, lo que significa que al recuperarlas se rellenan hasta el width con espacios. Esto no afecta al almacenamiento, pero sí a las comprobaciones de unicidad y al funcionamiento de los operadores GROUP BY y DISTINCT, que veremos en el Capítulo 5. Puedes comprobar si una intercalación es PAD SPACE o NO PAD ejecutando el comando SHOW COLLATION, como hemos mostrado en "Intercalación y juegos de caracteres". Veamos el efecto en acción creando una tabla con una intercalación PAD SPACE:

mysql> CREATE TABLE test_varchar_pad_collation(
    -> data VARCHAR(5) CHARACTER SET latin1
    -> COLLATE latin1_bin UNIQUE);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO test_varchar_pad_collation VALUES ('a');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test_varchar_pad_collation VALUES ('a ');
ERROR 1062 (23000): Duplicate entry 'a '
for key 'test_varchar_pad_collation.data'

La intercalación NO PAD es una nueva incorporación de MySQL 8.0. En versiones anteriores de MySQL, que todavía puedes ver a menudo en uso, todas las intercalaciones tienen implícitamente el atributo PAD SPACE. Por lo tanto, en MySQL 5.7 y versiones anteriores, tu única opción para conservar los espacios finales es utilizar un tipo binario: VARBINARY o BLOB.

Nota

Tanto los tipos de datos CHAR como VARCHAR no permiten el almacenamiento de valores superiores a widtha menos que el modo SQL estricto esté desactivado (es decir, si no están activados ni STRICT_ALL_TABLES ni STRICT_TRANS_TABLES ). Con la protección desactivada, los valores superiores a width se truncan y se muestra una advertencia. No recomendamos activar el comportamiento heredado, ya que podría provocar la pérdida de datos.

La ordenación y comparación de los tipos VARCHAR, CHAR, y TEXT se produce según la intercalación del conjunto de caracteres asignado. Puedes ver que es posible especificar el juego de caracteres, así como la intercalación para cada columna individual de tipo cadena. También es posible especificar el juego de caracteres binary, que convierte de hecho VARCHAR en VARBINARY. No confundas el juego de caracteres binary con un atributo BINARY para un juego de caracteres; este último es una abreviatura exclusiva de MySQL para especificar una intercalación binaria (_bin).

Además, es posible especificar una intercalación directamente en la cláusula ORDER BY . Las intercalaciones disponibles dependerán del conjunto de caracteres de la columna. Siguiendo con la tabla test_varchar_pad_collation, es posible almacenar allí un símbolo ä y ver el efecto que tienen las colaciones en el ordenamiento de las cadenas:

mysql> INSERT INTO test_varchar_pad_collation VALUES ('ä'), ('z');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM test_varchar_pad_collation
    -> ORDER BY data COLLATE latin1_german1_ci;
+------+
| data |
+------+
| a    |
| ä    |
| z    |
+------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM test_varchar_pad_collation
    -> ORDER BY data COLLATE latin1_swedish_ci;
+------+
| data |
+------+
| a    |
| z    |
| ä    |
+------+
3 rows in set (0.00 sec)

El atributo NATIONAL (o su forma abreviada equivalente, NCHAR) es una forma estándar de SQL para especificar que una columna de tipo cadena debe utilizar un conjunto de caracteres predefinido. MySQL utiliza utf8 como este conjunto de caracteres. Sin embargo, es importante señalar que MySQL 5.7 y 8.0 discrepan sobre qué es exactamente utf8: el primero lo utiliza como alias de utf8mb3, y el segundo de utf8mb4. Por tanto, es mejor no utilizar el atributo NATIONAL, así como alias ambiguos. La mejor práctica con cualquier columna y dato relacionado con el texto es ser lo menos ambiguo y específico posible.

[NATIONAL] CHAR(width) [CHARACTER SET charset_name] [COLLATE collation_name]

CHAR almacena una cadena de longitud fija (como un nombre, una dirección, o una ciudad) de longitud width. Si no se proporciona un width se asume CHAR(1). El valor máximo de width es 255. Al igual que en VARCHAR, los valores de las columnas CHAR siempre se almacenan con la longitud especificada. Una sola letra almacenada en una columna CHAR(255) ocupará 255 bytes (en el juego de caracteres latin1 ) y se rellenará con espacios. El relleno se elimina al leer los datos, a menos que esté activado el modo SQL de PAD_CHAR_TO_FULL_LENGTH. Vale la pena mencionar de nuevo que esto significa que las cadenas almacenadas en columnas CHAR perderán todos sus espacios finales.

En el pasado, el width de una columna CHAR solía llevar asociado un tamaño en bytes. Ahora no siempre es así, y definitivamente no lo es por defecto. Los conjuntos de caracteres multibyte, como el predeterminado utf8mb4 en MySQL 8.0, pueden dar lugar a valores mucho mayores. De hecho, InnoDB codificará las columnas de longitud fija como columnas de longitud variable si su tamaño máximo supera los 768 bytes. Así, en MySQL 8.0, por defecto InnoDB almacenará una columna CHAR(255) como lo haría con una columna VARCHAR. He aquí un ejemplo:

mysql> CREATE TABLE test_char_length(
    ->   utf8char CHAR(10) CHARACTER SET utf8mb4
    -> , asciichar CHAR(10) CHARACTER SET binary
    -> );
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO test_char_length VALUES ('Plain text', 'Plain text');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test_char_length VALUES ('的開源軟體', 'Plain text');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT LENGTH(utf8char), LENGTH(asciichar) FROM test_char_length;
+------------------+-------------------+
| LENGTH(utf8char) | LENGTH(asciichar) |
+------------------+-------------------+
|               10 |                10 |
|               15 |                10 |
+------------------+-------------------+
2 rows in set (0.00 sec)

Como los valores se alinean a la izquierda y se rellenan con espacios a la derecha, y los espacios finales no se tienen en cuenta en absoluto para CHAR, es imposible comparar cadenas formadas sólo por espacios. Si te encuentras en una situación en la que eso es importante, VARCHAR es el tipo de datos que debes utilizar.

BINARY[(width)] y VARBINARY(width)

Estos tipos son muy similares a CHAR y VARCHAR, pero almacenan cadenas binarias. Las cadenas binarias tienen el juego de caracteres y la colación especiales de binary, y su ordenación depende de los valores numéricos de los bytes de los valores almacenados. En lugar de cadenas de caracteres, se almacenan cadenas de bytes. En la discusión anterior sobre VARCHAR describimos el conjunto de caracteres binary y el atributo BINARY. Sólo el conjunto de caracteres binary "convierte" un VARCHAR o CHAR en su respectiva forma BINARY. Aplicar el atributo BINARY a un conjunto de caracteres no cambiará el hecho de que se almacenen cadenas de caracteres. A diferencia de lo que ocurre con VARCHAR y CHAR, width aquí se trata exactamente del número de bytes. Cuando width se omite en BINARY, por defecto es 1.

Al igual que en CHAR, los datos de la columna BINARY se rellenan por la derecha. Sin embargo, al ser un dato binario, se rellena utilizando bytes cero, normalmente escritos como 0x00 o \0. BINARY trata un espacio como carácter significativo, no como relleno. Si necesitas almacenar datos que puedan terminar en bytes cero que sean significativos para ti, utiliza los tipos VARBINARY o BLOB.

Es importante tener presente el concepto de cadenas binarias cuando trabajes con estos dos tipos de datos. Aunque acepten cadenas, no son sinónimos de los tipos de datos que utilizan cadenas de texto. Por ejemplo, no puedes cambiar las mayúsculas y minúsculas de las letras almacenadas, ya que ese concepto no se aplica realmente a los datos binarios. Eso queda bastante claro cuando consideras los datos reales almacenados. Veamos un ejemplo:

mysql> CREATE TABLE test_binary_data (
    ->   d1 BINARY(16)
    -> , d2 VARBINARY(16)
    -> , d3 CHAR(16)
    -> , d4 VARCHAR(16)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO test_binary_data VALUES (
    ->   'something'
    -> , 'something'
    -> , 'something'
    -> , 'something');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT d1, d2, d3, d4 FROM test_binary_data;
*************************** 1. row ***************************
d1: 0x736F6D657468696E6700000000000000
d2: 0x736F6D657468696E67
d3: something
d4: something
1 row in set (0.00 sec)
mysql> SELECT UPPER(d2), UPPER(d4) FROM test_binary_data;
*************************** 1. row ***************************
UPPER(d2): 0x736F6D657468696E67
UPPER(d4): SOMETHING
1 row in set (0.01 sec)

Observa cómo el cliente de línea de comandos MySQL muestra realmente los valores de los tipos binarios en formato hexadecimal. Creemos que esto es mucho mejor que las conversiones silenciosas que se realizaban antes de MySQL 8.0, que podían dar lugar a malentendidos. Para recuperar los datos de texto reales, tienes que convertir explícitamente los datos binarios en texto:

mysql> SELECT CAST(d1 AS CHAR) d1t, CAST(d2 AS CHAR) d2t
    -> FROM test_binary_data;
+------------------+-----------+
| d1t              | d2t       |
+------------------+-----------+
| something        | something |
+------------------+-----------+
1 row in set (0.00 sec)

También puedes ver que el relleno de BINARY se convirtió en espacios al realizar la fundición.

BLOB[(width)] y TEXT[(width)] [CHARACTER SET charset_name] [COLLATE collation_name]

BLOB y TEXT son tipos de datos utilizados habitualmente para almacenar datos de gran tamaño. Puedes pensar en BLOB como un VARBINARY que almacena tantos datos como quieras, y lo mismo para TEXT y VARCHAR. Los tipos BLOB y TEXT pueden almacenar hasta 65.535 bytes o caracteres, respectivamente. Como siempre, ten en cuenta que existen conjuntos de caracteres multibyte. El atributo width es opcional, y cuando se especifica, MySQL cambiará el tipo de datos BLOB o TEXT por el tipo más pequeño capaz de almacenar esa cantidad de datos. Por ejemplo, BLOB(128) hará que se utilice TINYBLOB:

mysql> CREATE TABLE test_blob(data BLOB(128));
Query OK, 0 rows affected (0.07 sec)
mysql> DESC test_blob;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| data  | tinyblob | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

Para el tipo BLOB y los tipos relacionados, los datos se tratan exactamente igual que en el caso de VARBINARY. Es decir, no se asume ningún conjunto de caracteres, y la comparación y la ordenación se basan en los valores numéricos de los bytes reales almacenados. Para TEXT, puedes especificar el juego de caracteres y la intercalación exactos que desees. Para ambos tipos y sus variantes, no se realiza ningún relleno en INSERT, ni ningún recorte en SELECT, lo que los hace ideales para almacenar datos tal cual. Además, no se permite una cláusula DEFAULT, y cuando se crea un índice en una columna BLOB o TEXT, debe definirse un prefijo que limite la longitud de los valores indexados. Hablamos más sobre esto en "Claves e índices".

Una posible diferencia entre BLOB y TEXT es su gestión de los espacios finales en. Como ya hemos demostrado, VARCHAR y TEXT pueden rellenar cadenas dependiendo de la intercalación utilizada. BLOB y VARBINARY utilizan el conjunto de caracteres binary con una única intercalación binary sin relleno y son inmunes a las confusiones de intercalación y problemas relacionados. A veces, puede ser una buena opción utilizar estos tipos para mayor seguridad. Además, antes de MySQL 8.0, eran los únicos tipos que conservaban los espacios finales.

TINYBLOB y TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

Son idénticos a BLOB y TEXT, respectivamente, excepto que se puede almacenar un máximo de 255 bytes o caracteres.

MEDIUMBLOB y MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

Son idénticos a BLOB y TEXT, respectivamente, excepto que se puede almacenar un máximo de 16.777.215 bytes o caracteres. Los tipos LONG y LONG VARCHAR se asignan al tipo de datos MEDIUMTEXT por compatibilidad.

LONGBLOB y LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

Son idénticos a BLOB y TEXT, respectivamente, excepto que se puede almacenar un máximo de 4 GB de datos. Ten en cuenta que éste es un límite duro incluso en el caso de LONGTEXT, por lo que el número de caracteres en conjuntos de caracteres multibyte puede ser inferior a 4.294.967.295. El tamaño máximo efectivo de los datos que puede almacenar un cliente estará limitado por la cantidad de memoria disponible, así como por el valor de la variable max_packet_size, que por defecto es de 64 MiB.

ENUM(value1[,value2[, …]]) [CHARACTER SET charset_name] [COLLATE collation_name]

Este tipo almacena una lista, o enumeración, de valores de cadena. Una columna de tipo ENUM puede establecerse en un valor de la lista value1, value2y así sucesivamente, hasta un máximo de 65.535 valores diferentes. Aunque los valores se almacenan y recuperan como cadenas, lo que se almacena en la base de datos es una representación entera. La columna ENUM puede contener valores NULL (almacenados como NULL), la cadena vacía '' (almacenada como 0), o cualquiera de los elementos válidos (almacenados como 1, 2, 3, etc.). Puedes evitar que se acepten los valores NULL declarando la columna como NOT NULL al crear la tabla.

Este tipo ofrece una forma compacta de almacenar valores de una lista de valores predefinidos, como nombres de estados o países. Considera este ejemplo utilizando nombres de frutas; el nombre puede ser cualquiera de los valores predefinidos Apple, Orange, o Pear (además de NULL y la cadena vacía):

mysql> CREATE TABLE fruits_enum
    -> (fruit_name ENUM('Apple', 'Orange', 'Pear'));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO fruits_enum VALUES ('Apple');
Query OK, 1 row affected (0.00 sec)

Si intentas insertar un valor que no está en la lista, MySQL produce un error para indicarte que no ha almacenado los datos que le pediste:

mysql> INSERT INTO fruits_enum VALUES ('Banana');
ERROR 1265 (01000): Data truncated for column 'fruit_name' at row 1

Tampoco se acepta una lista de varios valores permitidos:

mysql> INSERT INTO fruits_enum VALUES ('Apple,Orange');
ERROR 1265 (01000): Data truncated for column 'fruit_name' at row 1

Visualizando el contenido de la tabla, puedes ver que no se han almacenado valores no válidos:

mysql> SELECT * FROM fruits_enum;
+------------+
| fruit_name |
+------------+
| Apple      |
+------------+
1 row in set (0.00 sec)

Las versiones anteriores de MySQL producían una advertencia en lugar de un error y almacenaban una cadena vacía en lugar de un valor no válido. Ese comportamiento puede activarse desactivando el modo SQL estricto por defecto. También es posible especificar un valor por defecto distinto de la cadena vacía:

mysql> CREATE TABLE new_fruits_enum
    -> (fruit_name ENUM('Apple', 'Orange', 'Pear')
    -> DEFAULT 'Pear');
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO new_fruits_enum VALUES();
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM new_fruits_enum;
+------------+
| fruit_name |
+------------+
| Pear       |
+------------+
1 row in set (0.00 sec)

Aquí, si no se especifica un valor, se almacena el valor por defecto Pear.

SET( value1 [, value2 [, …]]) [CHARACTER SET charset_name] [COLLATE collation_name]

Este tipo almacena un conjunto de valores de cadena. Una columna de tipo SET se puede establecer en cero o más valores de la lista value1, value2y así sucesivamente, hasta un máximo de 64 valores diferentes. Aunque los valores son cadenas, lo que se almacena en la base de datos es una representación entera. SET se diferencia de ENUM en que cada fila sólo puede almacenar un valor ENUM en una columna, pero puede almacenar varios valores SET. Este tipo es útil para almacenar una selección de opciones de una lista, como las preferencias del usuario. Considera este ejemplo utilizando nombres de frutas; el nombre puede ser cualquier combinación de los valores predefinidos:

mysql> CREATE TABLE fruits_set
    -> ( fruit_name SET('Apple', 'Orange', 'Pear') );
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO fruits_set VALUES ('Apple');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO fruits_set VALUES ('Banana');
ERROR 1265 (01000): Data truncated for column 'fruit_name' at row 1
mysql> INSERT INTO fruits_set VALUES ('Apple,Orange');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM fruits_set;
+--------------+
| fruit_name   |
+--------------+
| Apple        |
| Apple,Orange |
+--------------+
2 rows in set (0.00 sec)

De nuevo, ten en cuenta que podemos almacenar varios valores del conjunto en un solo campo y que se almacena una cadena vacía para las entradas no válidas.

Al igual que con los tipos numéricos, te recomendamos que elijas siempre el tipo más pequeño posible para almacenar valores. Por ejemplo, si estás almacenando el nombre de una ciudad, utiliza CHAR o VARCHAR en lugar de, por ejemplo, el tipo TEXT. Tener columnas más cortas ayuda a mantener bajo el tamaño de tu tabla, lo que a su vez ayuda al rendimiento cuando el servidor tiene que buscar en una tabla.

Utilizar un tamaño fijo con el tipo CHAR suele ser más rápido que utilizar un tamaño variable con VARCHAR, ya que el servidor MySQL sabe dónde empieza y acaba cada fila y puede saltarse rápidamente las filas para encontrar la que necesita. Sin embargo, con los campos de longitud fija, todo el espacio que no utilices se desperdicia. Por ejemplo, si permites hasta 40 caracteres en el nombre de una ciudad, CHAR(40) utilizará siempre 40 caracteres, independientemente de la longitud real del nombre de la ciudad. Si declaras que el nombre de la ciudad es VARCHAR(40), sólo utilizarás el espacio que necesites, más 1 byte para almacenar la longitud del nombre. Si el nombre medio de una ciudad tiene 10 caracteres, esto significa que utilizar un campo de longitud variable ocupará una media de 29 bytes menos por entrada. Esto puede suponer una gran diferencia si almacenas millones de direcciones.

En general, si el espacio de almacenamiento es escaso o esperas grandes variaciones en la longitud de las cadenas que se van a almacenar, utiliza un campo de longitud variable; si el rendimiento es una prioridad, utiliza un campo de longitud fija.

Tipos de fecha y hora

Estos tipos sirven para almacenar determinadas marcas de tiempo, fechas o intervalos de tiempo. Hay que tener especial cuidado con las zonas horarias. Intentaremos explicar los detalles, pero merece la pena releer esta sección y la documentación más adelante, cuando necesites trabajar realmente con husos horarios. Los tipos de fecha y hora en MySQL son:

DATE

Almacena y muestra una fecha en el formato YYYY-MM-DD para el intervalo 1000-01-01 a 9999-12-31. Las fechas deben introducirse siempre como triples de año, mes y día, pero el formato de la entrada puede variar, como se muestra en los siguientes ejemplos:

YYYY-MM-DD o YY-MM-DD

Es opcional si proporcionas años de dos o cuatro dígitos. Te recomendamos encarecidamente que utilices la versión de cuatro dígitos para evitar confusiones sobre el siglo. En la práctica, si utilizas la versión de dos dígitos, verás que del 70 al 99 se interpretan como de 1970 a 1999, y del 00 al 69 como de 2000 a 2069.

YYYY/MM/DD, YYYY:MM:DD, YY-MM-DD, u otros formatos puntuados

MySQL permite cualquier carácter de puntuación para separar los componentes de una fecha. Recomendamos utilizar guiones y, de nuevo, evitar los años de dos dígitos.

YYYY-M-D, YYYY-MM-Do YYYY-M-DD

Cuando se utilizan signos de puntuación (de nuevo, se permite cualquier carácter de puntuación), los días y meses de un solo dígito pueden especificarse como tales. Por ejemplo, el 2 de febrero de 2006 puede especificarse como 2006-2-2. Los años de dos dígitos equivalentes están disponibles, pero no se recomiendan.

YYYYMMDD o YYMMDD

La puntuación puede omitirse en ambos estilos de fecha, pero las secuencias de dígitos deben tener seis u ocho dígitos de longitud.

También puedes introducir una fecha proporcionando tanto una fecha como una hora en los formatos que se describen más adelante para DATETIME y TIMESTAMP, pero sólo el componente fecha se almacena en una columna DATE. Independientemente del tipo de entrada, el tipo de almacenamiento y visualización es siempre YYYY-MM-DD. La fecha cero 0000-00-00 está permitida en todas las versiones y puede utilizarse para representar un valor desconocido o ficticio. Si una fecha de entrada está fuera de rango, se almacena la fecha cero. Sin embargo, sólo las versiones de MySQL hasta la 5.6 inclusive lo permiten por defecto. Tanto la 5.7 como la 8.0 establecen por defecto modos SQL que prohíben este comportamiento: STRICT_TRANS_TABLES, NO_ZERO_DATE, y NO_ZERO_IN_DATE.

Si utilizas una versión antigua de MySQL, te recomendamos que añadas estos modos a tu sesión actual:

mysql> SET sql_mode=CONCAT(@@sql_mode,
    -> ',STRICT_TRANS_TABLES',
    -> ',NO_ZERO_DATE', ',NO_ZERO_IN_DATE');
Consejo

También puedes establecer la variable sql_mode a nivel global del servidor y en el archivo de configuración. Esta variable debe listar cada modo que quieras que esté habilitado.

Aquí tienes algunos ejemplos de inserción de fechas en un servidor MySQL 8.0 con la configuración por defecto:

mysql> CREATE TABLE testdate (mydate DATE);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO testdate VALUES ('2020/02/0');
ERROR 1292 (22007): Incorrect date value: '2020/02/0'
for column 'mydate' at row 1
mysql> INSERT INTO testdate VALUES ('2020/02/1');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO testdate VALUES ('2020/02/31');
ERROR 1292 (22007): Incorrect date value: '2020/02/31'
for column 'mydate' at row 1
mysql> INSERT INTO testdate VALUES ('2020/02/100');
ERROR 1292 (22007): Incorrect date value: '2020/02/100'
for column 'mydate' at row 1

Una vez ejecutadas las sentencias INSERT, la tabla tendrá los siguientes datos:

mysql> SELECT * FROM testdate;
+------------+
| mydate     |
+------------+
| 2020-02-01 |
+------------+
1 row in set (0.00 sec)

MySQL te protegía de tener datos "malos" almacenados en tu tabla. A veces puedes necesitar conservar la entrada real y procesarla manualmente más tarde. Puedes hacerlo eliminando los modos SQL mencionados de la lista de modos de la variable sql_mode. En ese caso, después de ejecutar las sentencias INSERT anteriores, acabarías con los siguientes datos:

mysql> SELECT * FROM testdate;
+------------+
| mydate     |
+------------+
| 2020-02-00 |
| 2020-02-01 |
| 0000-00-00 |
| 0000-00-00 |
+------------+
4 rows in set (0.01 sec)

Observa de nuevo que la fecha se muestra en el formato YYYY-MM-DD independientemente de cómo se haya introducido.

TIME [fraction]

Almacena una hora en el formato HHH:MM:SS para el intervalo -838:59:59 a 838:59:59. Esto es útil para almacenar la duración de alguna actividad. Los valores que se pueden almacenar están fuera del rango del reloj de 24 horas para permitir calcular y almacenar grandes diferencias entre valores de tiempo (hasta 34 días, 22 horas, 59 minutos y 59 segundos). fraction en TIME y otros tipos de datos relacionados especifica la precisión fraccionaria en segundos en el rango de 0 a 6. El valor por defecto es 0, lo que significa que no se conservan los segundos fraccionarios.

Las horas deben introducirse siempre en el orden días, horas, minutos, segundos, utilizando los siguientes formatos:

DD HH:MM:SS[.fraction], HH:MM:SS[.fraction], DD HH:MM, HH:MM, DD HHo SS[.fraction]

DD representa un valor de días de uno o dos dígitos en el intervalo de 0 a 34. El valor DD se separa del valor de la hora HHpor un espacio, mientras que los demás componentes se separan por dos puntos. Ten en cuenta que MM:SS no es unacombinación válida, ya que no se puede desambiguar de HH:MM. Si la definición de TIME no especifica fraction o lo establece en 0, la inserción de segundos fraccionarios hará que los valores se redondeen al segundo más próximo.

Por ejemplo, si insertas 2 13:25:58.999999 en una columna TIME con un fraction de 0, se almacena el valor 61:25:59, ya que la suma de 2 días (48 horas) y 13 horas es 61 horas. A partir de MySQL 5.7, el modo SQL establecido por defecto prohíbe la inserción de valores incorrectos. Sin embargo, es posible activar el comportamiento anterior. Entonces, si intentas insertar un valor que está fuera de los límites, se genera una advertencia y el valor se limita al tiempo máximo disponible. Del mismo modo, si intentas insertar un valor no válido, se genera una advertencia y el valor se pone a cero. Puedes utilizar el comando SHOW WARNINGS para informar de los detalles de la advertencia generada por la sentencia SQL anterior. Nuestra recomendación es que te ciñas al modo SQL estricto por defecto. A diferencia de lo que ocurre con el tipo DATE, aparentemente no hay ninguna ventaja en permitir entradas incorrectas en TIME, aparte de una gestión de errores más sencilla por parte de la aplicación y de mantener los comportamientos heredados.

Vamos a probar todo esto en la práctica:

mysql> CREATE TABLE test_time(id SMALLINT, mytime TIME);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test_time VALUES(1, "2 13:25:59");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test_time VALUES(2, "35 13:25:59");
ERROR 1292 (22007): Incorrect time value: '35 13:25:59'
for column 'mytime' at row 1
mysql> INSERT INTO test_time VALUES(3, "900.32");
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test_time;
+------+----------+
| id   | mytime   |
+------+----------+
|    1 | 61:25:59 |
|    3 | 00:09:00 |
+------+----------+
2 rows in set (0.00 sec)
H:M:Sy combinaciones de uno, dos y tres dígitos

Puedes utilizar diferentes combinaciones de dígitos al insertar o actualizar datos; MySQL los convierte al formato de hora interno y los muestra de forma coherente. Por ejemplo, 1:1:3 equivale a 01:01:03. Se pueden mezclar diferentesnúmeros de dígitos; por ejemplo, 1:12:3 equivale a 01:12:03. Considera estos ejemplos:

mysql> CREATE TABLE mytime (testtime TIME);
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO mytime VALUES
    -> ('-1:1:1'), ('1:1:1'),
    -> ('1:23:45'), ('123:4:5'),
    -> ('123:45:6'), ('-123:45:6');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM mytime;
+------------+
| testtime   |
+------------+
|  -01:01:01 |
|   01:01:01 |
|   01:23:45 |
|  123:04:05 |
|  123:45:06 |
| -123:45:06 |
+------------+
5 rows in set (0.01 sec)

Ten en cuenta que las horas se muestran con dos dígitos para los valores comprendidos entre -99 y 99.

HHMMSS, MMSSy SS

Se pueden omitir los signos de puntuación, pero las secuencias de dígitos deben tener una longitud de dos, cuatro o seis dígitos. Ten en cuenta que el par de dígitos situado más a la derecha siempre se interpreta como un valor de SS (segundos), el segundo par de la derecha (si está presente) como MM (minutos), y el tercer par de la derecha (si está presente) como HH (horas). El resultado es que un valor como 1222 se interpreta como 12 minutos y 22 segundos, no como 12 horas y 22 minutos.

También puedes introducir una hora proporcionando tanto una fecha como una hora en los formatos descritos para DATETIME y TIMESTAMP, pero sólo el componente de la hora se almacena en una columna TIME. Independientemente del tipo de entrada, el tipo de almacenamiento y visualización es siempre HH:MM:SS. La hora cero 00:00:00 puede utilizarse para representar un valor desconocido o ficticio.

TIMESTAMP[(fraction)]

Almacena y muestra un par de fecha y hora en el formato YYYY-MM-DD HH:MM:SS[.fraction][time zone offset] para el rango 1970-01-01 00:00:01.000000 a 2038-01-19 03:14:07.999999. Este tipo es muy similar al tipo DATETIME, pero hay algunas diferencias. Ambos tipos aceptan un modificador de zona horaria al valor de entrada MySQL 8.0, y ambos tipos almacenarán y presentarán los datos de la misma forma a cualquier cliente de la misma zona horaria. Sin embargo, los valores de las columnas de TIMESTAMP se almacenan internamente siempre en la zona horaria UTC, lo que permite obtener automáticamente una zona horaria local para clientes situados en zonas horarias diferentes. Esta es una distinción muy importante que debes recordar. Podría decirse que TIMESTAMP es más cómodo de utilizar cuando se trata de diferentes zonas horarias.

Antes de MySQL 5.6, sólo el tipo TIMESTAMP admitía la inicialización y actualización automáticas. Además, sólo una única columna de este tipo por tabla podía hacerlo. Sin embargo, a partir de la 5.6, tanto TIMESTAMP como DATETIME admiten estos comportamientos, y cualquier número de columnas puede hacerlo.

Los valores almacenados en una columna TIMESTAMP siempre coinciden con el modelo YYYY-MM-DD HH:MM:SS[.fraction][time zone offset]pero los valores pueden proporcionarse en una amplia gama de formatos:

YYYY-MM-DD HH:MM:SS o YY-MM-DD HH:MM:SS

Los componentes de fecha y hora siguen las mismas restricciones relajadas que los componentes DATE y TIME descritos anteriormente. Esto incluye la admisión de cualquier carácter de puntuación, incluida (a diferencia de TIME) la flexibilidad en la puntuación utilizada en el componente de hora. Por ejemplo, –0— es válido.

YYYYMMDDHHMMSS o YYMMDDHHMMSS

Se pueden omitir los signos de puntuación, pero la cadena debe tener una longitud de 12 ó 14 dígitos. Recomendamos utilizar sólo la versión inequívoca de 14 dígitos, por las razones expuestas para el tipo DATE. Puedes especificar valores con otras longitudes sin proporcionar separadores, pero no recomendamos hacerlo.

Veamos la función de actualización automática con más detalle. Puedes controlarla en añadiendo los siguientes atributos a la definición de la columna al crear una tabla, o más tarde, como explicaremos en "Modificación de estructuras":

  1. Si quieres que la marca de tiempo se establezca sólo cuando se inserte una nueva fila en la tabla, añade DEFAULT CURRENT_TIMESTAMP al final de la declaración de la columna.

  2. Si no quieres una marca de tiempo por defecto, pero quieres que se utilice la hora actual de siempre que se actualicen los datos de una fila, añade ON UPDATE CURRENT_TIMESTAMP al final de la declaración de la columna.

  3. Si quieres ambas cosas, es decir, que la marca de tiempo se ajuste a la hora actual en cada nueva fila y siempre que se modifique una fila existente, añade DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP al final de la declaración de la columna.

Si no especificas DEFAULT NULL o NULL para una columna TIMESTAMP, tendrá 0 como valor por defecto.

YEAR[(4)]

Almacena un año de cuatro dígitos en el intervalo de 1901 a 2155, así como el año cero, 0000. Los valores ilegales se convierten al año cero. Puedes introducir los valores del año como cadenas (como '2005') o enteros (como 2005). El tipo YEAR requiere 1 byte de espacio de almacenamiento.

En versiones anteriores de MySQL, era posible especificar el parámetro digits pasando 2 o 4. La versión de dos dígitos almacenaba valores de 70 a 69, que representaban de 1970 a 2069. MySQL 8.0 no admite el tipo de dos dígitos YEAR, y especificar el parámetro digits con fines de visualización está obsoleto.

DATETIME[(fraction)]

Almacena y muestra un par de fecha y hora en el formato YYYY-MM-DD HH:MM:SS[.fraction][time zone offset] para el intervalo 1000-01-01 00:00:00 a 9999-12-31 23:59:59. Al igual que en TIMESTAMP, el valor almacenado siempre coincide con la plantilla YYYY-MM-DD HH:MM:SS, pero el valor puede introducirse en los mismos formatos enumerados en la descripción de TIMESTAMP. Si asignas sólo una fecha a una columna DATETIME, se asume la hora cero 00:00:00. Si asignas sólo una hora a una columna DATETIME, se asume la fecha cero 0000-00-00. Este tipo tiene las mismas características de actualización automática que TIMESTAMP. A menos que se especifique el atributo NOT NULL para una columna DATETIME, el valor por defecto es NULL; en caso contrario, el valor por defecto es 0. A diferencia de TIMESTAMP, los valores de DATETIME no se convierten a la zona horaria UTC para sualmacenamiento.

Otros tipos

Actualmente, a partir de MySQL 8.0, los tipos de datos espaciales y JSON entran en esta amplia categoría. Utilizarlos es un tema bastante avanzado, y no los trataremos en profundidad.

Los tipos de datos espaciales se ocupan de almacenar objetos geométricos, y MySQL tiene tipos correspondientes a las clases OpenGIS. Trabajar con estos tipos es un tema que merece un libro por sí solo.

El tipo de datos JSON permite el almacenamiento nativo de documentos JSON válidos. Antes de MySQL 5.7, JSON solía almacenarse en una columna TEXT o similar. Sin embargo, eso tiene muchas desventajas: por ejemplo, los documentos no se validan, y no se realiza ninguna optimización del almacenamiento (todo el JSON se almacena simplemente en su forma de texto). Con el tipo nativo JSON, se almacena en formato binario. Si tuviéramos que resumirlo en una frase: utiliza el tipo de datos JSON para JSON, querido lector.

Claves e índices

Verás que casi todas las tablas que utilices tendrán una cláusula PRIMARY KEY declarada en su declaración CREATE TABLE, y a veces varias cláusulas KEY. Las razones por las que necesitas una clave primaria y claves secundarias se trataron en el Capítulo 2. En esta sección se explica cómo se declaran las claves primarias, qué ocurre entre bastidores cuando lo haces, y por qué puede interesarte crear también otras claves e índices sobre tus datos.

Una clave primaria identifica de forma única cada fila de una tabla. Y lo que es aún más importante, para el motor de almacenamiento InnoDB por defecto, una clave primaria también se utiliza como índice agrupado. Eso significa que todos los datos reales de la tabla se almacenan en una estructura de índices. Esto es diferente de MyISAM, que almacena los datos y los índices por separado. Cuando una tabla utiliza un índice agrupado, se denomina tabla agrupada. Como hemos dicho, en una tabla agrupada cada fila se almacena dentro de un índice, en lugar de almacenarse en lo que suele llamarse un montón. Al agrupar una tabla, sus filas se ordenan según el índice agrupado y se almacenan físicamente en las páginas hoja de ese índice. No puede haber más de un índice agrupado por tabla. Para estas tablas, los índices secundarios se refieren a los registros del índice agrupado en lugar de a las filas reales de la tabla. Esto suele mejorar el rendimiento de las consultas, aunque puede ser perjudicial para las escrituras. InnoDB no te permite elegir entre tablas agrupadas y no agrupadas; se trata de una decisión de diseño que no puedes cambiar.

Las claves primarias suelen ser una parte recomendable de cualquier diseño de base de datos , pero para InnoDB son necesarias. De hecho, si no especificas una cláusula PRIMARY KEY al crear una tabla InnoDB, MySQL utilizará la primera columna UNIQUE NOT NULL como base para el índice agrupado. Si no se dispone de dicha columna, se crea un índice agrupado oculto, basado en los valores ID asignados por InnoDB a cada fila.

Dado que InnoDB es el motor de almacenamiento por defecto de MySQL y un estándar de facto en la actualidad, nos centraremos en su comportamiento en este capítulo. Los motores de almacenamiento alternativos como MyISAM, MEMORY o MyRocks se tratarán en "Motores de almacenamiento alternativos".

Como ya se ha dicho, cuando se define una clave primaria, ésta se convierte en un índice agrupado, y todos los datos de la tabla se almacenan en los bloques hoja de ese índice. InnoDB utiliza índices de árbol B (más concretamente, la variante B+árbol), a excepción de los índices sobre tipos de datos espaciales, que utilizan la estructura de árbol R. Otros motores de almacenamiento pueden implementar tipos de índice diferentes, pero cuando no se especifica el motor de almacenamiento de una tabla, puedes asumir que todos los índices son de árbol B.

Tener un índice agrupado, o dicho de otro modo, tener tablas organizadas por índices, acelera las consultas y ordenaciones en las que intervienen las columnas de clave primaria. Sin embargo, un inconveniente es que modificar las columnas de una clave primaria es caro. Por tanto, un buen diseño requerirá una clave primaria basada en columnas que se utilicen frecuentemente para filtrar en las consultas, pero que se modifiquen con poca frecuencia. Recuerda que no tener ninguna clave primaria hará que InnoDB utilice un índice de clúster implícito; por tanto, si no estás seguro de qué columnas elegir para una clave primaria, considera la posibilidad de utilizar una columna sintética similar a id. Por ejemplo, el tipo de datos SERIAL podría encajar bien en ese caso.

Alejándonos de los detalles internos de InnoDB, cuando declaras una clave primaria para una tabla en MySQL, se crea una estructura que almacena información sobre dónde se guardan los datos de cada fila de la tabla. Esta información se denomina índice, y su finalidad es acelerar las búsquedas que utilizan la clave primaria. Por ejemplo, cuando declaras PRIMARY KEY (actor_id) en la tabla actor de la base de datos sakila, MySQL crea una estructura que le permite encontrar filas que coincidan con un actor_id concreto (o con un rango de identificadores) con extrema rapidez.

Esto es útil para emparejar actores con películas o películas con categorías, por ejemplo. Puedes mostrar los índices disponibles en una tabla utilizando elcomando SHOW INDEX (o SHOW INDEXES):

mysql> SHOW INDEX FROM category\G
*************************** 1. row ***************************
        Table: category
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: category_id
    Collation: A
  Cardinality: 16
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

La cardinalidad es el número de valores únicos del índice; para un índice sobre una clave primaria, es igual al número de filas de la tabla.

Ten en cuenta que todas las columnas que forman parte de una clave primaria deben estar declaradas como NOT NULL, ya que deben tener un valor para que la fila sea válida. Sin el índice, la única forma de encontrar filas en la tabla es leer cada una del disco y comprobar si coincide con el category_id que estás buscando. Para tablas con muchas filas, esta búsqueda exhaustiva y secuencial es extremadamente lenta. Sin embargo, no puedes indexarlo todo; volveremos sobre este punto al final de esta sección.

Puedes crear otros índices sobre los datos de una tabla. Lo haces para que otras búsquedas (ya sean sobre otras columnas o combinaciones de columnas) sean rápidas, y para evitar escaneos secuenciales. Por ejemplo, tomemos de nuevo la tabla actor. Además de tener una claveprimaria en actor_id, también tiene una clave secundaria en last_name para mejorar la búsqueda por el apellido de un actor:

mysql> SHOW CREATE TABLE actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  ...
  `last_name` varchar(45) NOT NULL,
  ...
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ...
1 row in set (0.00 sec)

Puedes ver que la palabra clave KEY se utiliza para indicar a MySQL que se necesita un índice adicional. Alternativamente, puedes utilizar la palabra INDEX en lugar de KEY. A continuación de esa palabra clave aparece el nombre del índice, y luego se incluye entre paréntesis la columna a indexar. También puedes añadir índices después de crear las tablas; de hecho, puedes cambiar prácticamente cualquier cosa de una tabla después de su creación. Esto se trata en "Modificar estructuras".

Puedes construir un índice sobre más de una columna. Por ejemplo, considera la siguiente tabla, que es una tabla modificada de sakila:

mysql> CREATE TABLE customer_mod (
    -> customer_id smallint unsigned NOT NULL AUTO_INCREMENT,
    -> first_name varchar(45) NOT NULL,
    -> last_name varchar(45) NOT NULL,
    -> email varchar(50) DEFAULT NULL,
    -> PRIMARY KEY (customer_id),
    -> KEY idx_names_email (first_name, last_name, email));
Query OK, 0 rows affected (0.06 sec)

Puedes ver que hemos añadido un índice de clave primaria en la columna identificadora customer_id, y también hemos añadido otro índice -llamado idx_names_email- que incluye las columnas first_name, last_name y email en este orden. Veamos ahora cómo puedes utilizar ese índice adicional.

Puedes utilizar el índice idx_names_email para realizar búsquedas rápidas en por combinaciones de las tres columnas de nombre. Por ejemplo, es útil en la siguiente consulta:

mysql> SELECT * FROM customer_mod WHERE
    -> first_name = 'Rose' AND
    -> last_name = 'Williams' AND
    -> email = 'rose.w@nonexistent.edu';

Sabemos que ayuda a la búsqueda, porque todas las columnas que figuran en el índice se utilizan en la consulta. Puedes utilizar la sentencia EXPLAIN para comprobar si lo que crees que debería ocurrir está ocurriendo en realidad:

mysql> EXPLAIN SELECT * FROM customer_mod WHERE
    -> first_name = 'Rose' AND
    -> last_name = 'Williams' AND
    -> email = 'rose.w@nonexistent.edu'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer_mod
   partitions: NULL
         type: ref
possible_keys: idx_names_email
          key: idx_names_email
      key_len: 567
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Puedes ver que MySQL informa de que los possible_keys son idx_names_email (lo que significa que el índice podría utilizarse para esta consulta) y que el key que ha decidido utilizar es idx_names_email. Por tanto, lo que esperas y lo que está ocurriendo es lo mismo, ¡y eso son buenas noticias! Encontrarás más información sobre la sentencia EXPLAIN en el capítulo 7.

El índice que hemos creado también es útil para realizar consultas sólo sobre la columna first_name. Por ejemplo, puede ser utilizado por la siguiente consulta:

mysql> SELECT * FROM customer_mod WHERE
    -> first_name = 'Rose';

Puedes volver a utilizar EXPLAIN para comprobar si se está utilizando el índice. La razón por la que se puede utilizar es porque la columna first_name es la primera que aparece en el índice. En la práctica, esto significa que el índice agrupa, o almacena junta, la información sobre las filas de todas las personas con el mismo nombre de pila, por lo que el índice puede utilizarse para encontrar a cualquier persona con un nombre de pila coincidente.

El índice también puede utilizarse para búsquedas que incluyan combinaciones de nombre y apellidos, exactamente por las mismas razones que acabamos de exponer. El índice agrupa a las personas con el mismo nombre, y agrupa a las personas con nombres idénticos por apellidos. Por tanto, puede utilizarse para esta consulta:

mysql> SELECT * FROM customer_mod WHERE
    -> first_name = 'Rose' AND
    -> last_name = 'Williams';

Sin embargo, el índice no puede utilizarse para esta consulta porque la columna situada más a la izquierda del índice, first_name, no aparece en la consulta:

mysql> SELECT * FROM customer_mod WHERE
    -> last_name = 'Williams' AND
    -> email = 'rose.w@nonexistent.edu';

El índice debe ayudar a reducir el conjunto de filas a un conjunto menor de posibles respuestas. Para que MySQL pueda utilizar un índice, la consulta debe cumplir las dos condiciones siguientes:

  1. La columna situada más a la izquierda en la cláusula KEY (o PRIMARY KEY) debe estar en la consulta.

  2. La consulta no debe contener cláusulas OR para columnas que no estén indexadas.

De nuevo, siempre puedes utilizar la sentencia EXPLAIN para comprobar si se puede utilizar un índice para una consulta concreta.

Antes de terminar esta sección, aquí tienes algunas ideas sobre cómo elegir y diseñar índices. Cuando te plantees añadir un índice, piensa en lo siguiente:

  • Los índices cuestan espacio en disco, y deben actualizarse cada vez que cambian los datos. Si tus datos cambian con frecuencia, o cambian muchos datos cuando haces una modificación, los índices ralentizarán el proceso. Sin embargo, en la práctica, como las sentencias SELECT (lecturas de datos) suelen ser mucho más frecuentes que otras sentencias (modificaciones de datos), los índices suelen ser beneficiosos.

  • Añade sólo un índice que se vaya a utilizar con frecuencia. No te molestes en indexar columnas antes de ver qué consultas necesitan tus usuarios y tus aplicaciones. Siempre puedes añadir índices después.

  • Si todas las columnas de un índice se utilizan en todas las consultas, enumera la columna con mayor número de duplicados a la izquierda de la cláusula KEY. Esto minimiza el tamaño del índice.

  • Cuanto más pequeño sea el índice, más rápido será. Si indexas columnas grandes, obtendrás un índice mayor. Ésta es una buena razón para asegurarte de que tus columnas sean lo más pequeñas posible cuando diseñes tus tablas.

  • Para columnas largas, puedes utilizar sólo un prefijo de los valores de una columna para crear el índice. Puedes hacerlo añadiendo un valor entre paréntesis después de la definición de la columna, como KEY idx_names_email (first_name(3), last_name(2), email(10)). Esto significa que sólo se indexan los 3 primeros caracteres de first_name, después los 2 primeros caracteres de last_name, y a continuación 10 caracteres de email. ¡Esto supone un ahorro importante respecto a indexar 140 caracteres de las tres columnas! Cuando hagas esto, tu índice será menos capaz de identificar filas de forma única, pero será mucho más pequeño y seguirá siendo razonablemente bueno para encontrar filas coincidentes. Utilizar un prefijo es obligatorio para tipos largos como TEXT.

Para terminar esta sección, tenemos que hablar de algunas peculiaridades relativas a las claves secundarias en InnoDB. Recuerda que todos los datos de la tabla se almacenan en las hojas del índice agrupado. Eso significa, utilizando el ejemplo de actor, que si necesitamos obtener los datos de first_name al filtrar por last_name, aunque podamos utilizar idx_actor_last_name para un filtrado rápido, tendremos que acceder a los datos por la clave primaria. Como consecuencia, cada clave secundaria en InnoDB tiene todas las columnas de clave primaria añadidas a su definición de forma implícita. Tener claves primarias innecesariamente largas en InnoDB da como resultado claves secundarias significativamente hinchadas.

Esto también puede verse en la salida de EXPLAIN (observa la Extra: Using index en la primera salida del primer comando):

mysql> EXPLAIN SELECT actor_id, last_name FROM actor WHERE last_name = 'Smith'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 182
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT first_name FROM actor WHERE last_name = 'Smith'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 182
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

Efectivamente, idx_actor_last_name es un índice de cobertura para la primera consulta, lo que significa que InnoDB puede extraer todos los datos necesarios sólo de ese índice. Sin embargo, para la segunda consulta, InnoDB tendrá que hacer una búsqueda adicional en un índice agrupado para obtener el valor de la columna first_name.

La función AUTO_INCREMENT

La función AUTO_INCREMENT, propiedad de MySQL, te permite crear un identificador único para una fila sin ejecutar una consulta SELECT. Así es como funciona. Tomemos de nuevo la tabla simplificada actor:

mysql> CREATE TABLE actor (
    -> actor_id smallint unsigned NOT NULL AUTO_INCREMENT,
    -> first_name varchar(45) NOT NULL,
    -> last_name varchar(45) NOT NULL,
    -> PRIMARY KEY (actor_id)
    -> );
Query OK, 0 rows affected (0.03 sec)

Es posible insertar filas en esa tabla sin especificar la dirección actor_id:

mysql> INSERT INTO actor VALUES (NULL, 'Alexander', 'Kaidanovsky');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO actor VALUES (NULL, 'Anatoly', 'Solonitsyn');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO actor VALUES (NULL, 'Nikolai', 'Grinko');
Query OK, 1 row affected (0.00 sec)

Al visualizar los datos de esta tabla, puedes ver que cada fila tiene asignado un valor para la columna actor_id:

mysql> SELECT * FROM actor;
+----------+------------+-------------+
| actor_id | first_name | last_name   |
+----------+------------+-------------+
|        1 | Alexander  | Kaidanovsky |
|        2 | Anatoly    | Solonitsyn  |
|        3 | Nikolai    | Grinko      |
+----------+------------+-------------+
3 rows in set (0.00 sec)

Cada vez que se inserta una nueva fila, se crea un valor único para la columna actor_id para esa nueva fila.

Considera cómo funciona esta característica. Puedes ver que la columna actor_id se declara como un número entero con las cláusulas NOT NULL AUTO_INCREMENT. AUTO_INCREMENT indica a MySQL que cuando no se proporciona un valor para esta columna, el valor asignado debe ser uno más que el máximo almacenado actualmente en la tabla. La secuencia AUTO_INCREMENT comienza en 1 para una tabla vacía.

La cláusula NOT NULL es necesaria para las columnas AUTO_INCREMENT; cuando insertas NULL (o 0, aunque esto no es recomendable), el servidor MySQL busca automáticamente el siguiente identificador disponible y lo asigna a la nueva fila. Puedes insertar manualmente valores negativos si la columna no estaba definida como UNSIGNED; sin embargo, para el siguiente incremento automático, MySQL simplemente utilizará el mayor valor (positivo) de la columna, o empezará desde 1 si no hay valores positivos.

La función AUTO_INCREMENT tiene los siguientes requisitos:

  • La columna en la que se utiliza debe estar indexada.

  • La columna en la que se utiliza no puede tener un valor DEFAULT.

  • Sólo puede haber una columna AUTO_INCREMENT por tabla.

MySQL admite distintos motores de almacenamiento; hablaremos más sobre ellos en "Motores de almacenamiento alternativos". Cuando utilizas el tipo de tabla MyISAM no predeterminado, puedes utilizar la función AUTO_INCREMENT en claves que comprenden varias columnas. En efecto, puedes tener varios contadores independientes dentro de una única columna AUTO_INCREMENT. Sin embargo, esto no es posible con InnoDB.

Aunque la función AUTO_INCREMENT es útil, no es portable a otros entornos de bases de datos, y oculta los pasos lógicos para crear nuevos identificadores. También puede dar lugar a ambigüedades; por ejemplo, al eliminar o truncar una tabla se restablecerá el contador, pero al eliminar filas seleccionadas (con una cláusula WHERE ) no se restablece el contador. Además, si se inserta una fila dentro de una transacción, pero luego esa transacción se deshace, el identificador se agotará de todos modos. Como ejemplo, vamos a crear la tabla count que contiene un campo autoincrementable counter:

mysql> CREATE TABLE count (counter INT AUTO_INCREMENT KEY);
Query OK, 0 rows affected (0.13 sec)
mysql> INSERT INTO count VALUES (),(),(),(),(),();
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM count;
+---------+
| counter |
+---------+
| 1       |
| 2       |
| 3       |
| 4       |
| 5       |
| 6       |
+---------+
6 rows in set (0.00 sec)

Insertar varios valores funciona como se esperaba. Ahora, vamos a borrar unas cuantas filas y a añadir seis filas nuevas:

mysql> DELETE FROM count WHERE counter > 4;
Query OK, 2 rows affected (0.00 sec)
mysql> INSERT INTO count VALUES (),(),(),(),(),();
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM count;
+---------+
| counter |
+---------+
| 1       |
| 2       |
| 3       |
| 4       |
| 7       |
| 8       |
| 9       |
| 10      |
| 11      |
| 12      |
+---------+
10 rows in set (0.00 sec)

Aquí vemos que el contador no se pone a cero y continúa a partir de 7. Sin embargo, si truncamos la tabla, eliminando así todos los datos, el contador se pone a 1:

mysql> TRUNCATE TABLE count;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO count VALUES (),(),(),(),(),();
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM count;
+---------+
| counter |
+---------+
| 1       |
| 2       |
| 3       |
| 4       |
| 5       |
| 6       |
+---------+
6 rows in set (0.00 sec)

En resumen: AUTO_INCREMENT garantiza una secuencia de valores transaccionales y monotónicamente crecientes. Sin embargo, no garantiza en modo alguno que cada identificador individual proporcionado siga exactamente al anterior. Normalmente, este comportamiento de AUTO_INCREMENT es suficientemente claro y no debería suponer un problema. Sin embargo, si tu caso de uso particular requiere un contador que garantice que no haya huecos, deberías plantearte utilizar algún tipo de solución. Por desgracia, es probable que se implemente en el lado de la aplicación.

Alteración de estructuras

Ya te hemos enseñado todo lo que necesitas para crear bases de datos, tablas, índices y columnas. En esta sección aprenderás a añadir, eliminar y modificar columnas, bases de datos, tablas e índices en estructuras ya existentes.

Añadir, quitar y cambiar columnas

Puedes utilizar la sentencia ALTER TABLE para añadir nuevas columnas a una tabla, eliminar columnas existentes y cambiar los nombres, tipos y longitudes de las columnas.

Empecemos por considerar cómo se modifican las columnas existentes. Consideremos un ejemplo en el que renombramos una columna de la tabla. La tabla language tiene una columna llamada last_update que contiene la hora en que se modificó el registro. Para cambiar el nombre de esta columna a last_updated_time, escribirías

mysql> ALTER TABLE language RENAME COLUMN last_update TO last_updated_time;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Este ejemplo concreto utiliza la función DDL en línea de MySQL. Lo que realmente ocurre entre bastidores es que MySQL sólo modifica los metadatos y no necesita reescribir realmente la tabla de ninguna manera. Puedes verlo por la ausencia de filas afectadas. No todas las sentencias DDL pueden realizarse en línea, por lo que éste no será el caso de muchos de los cambios que realices.

Nota

DDL significa lenguaje de definición de datos, y en el contexto de SQL es un subconjunto de sintaxis y sentencias utilizadas para crear, modificar y eliminar objetos del esquema, como bases de datos, tablas, índices y columnas. CREATE TABLE y ALTER TABLE son operaciones DDL, por ejemplo.

Ejecutar sentencias DDL requiere mecanismos internos especiales de, incluidos bloqueos especiales, lo cual es bueno, ¡ya que probablemente no te gustaría que las tablas cambiaran mientras se ejecutan tus consultas! Estos bloqueos especiales se denominan bloqueos de metadatos en MySQL, y en "Bloqueos de metadatos" te explicamos detalladamente cómo funcionan .

Ten en cuenta que todas las sentencias DDL, incluidas las que se ejecutan mediante DDL en línea, requieren la obtención de bloqueos de metadatos. En ese sentido, las sentencias DDL en línea no son tan "en línea", pero no bloquearán la tabla de destino por completo mientras se estén ejecutando. Ejecutar sentencias DDL en un sistema en funcionamiento bajo carga es una aventura arriesgada: incluso una sentencia que debería ejecutarse casi instantáneamente puede causar estragos. Te recomendamos que leas detenidamente sobre el bloqueo de metadatos en el Capítulo 6 y en el enlace a la documentación de MySQL, y que experimentes ejecutando distintas sentencias DDL con y sin carga concurrente. Puede que esto no sea demasiado importante mientras aprendes MySQL, pero creemos que merece la pena advertirte por adelantado. Con esto cubierto, volvamos a nuestro ALTER de la tabla language.

Puedes comprobar el resultado con la declaración SHOW COLUMNS:

mysql> SHOW COLUMNS FROM language;
+-------------------+------------------+------+-----+-------------------+...
| Field             | Type             | Null | Key | Default           |...
+-------------------+------------------+------+-----+-------------------+...
| language_id       | tinyint unsigned | NO   | PRI | NULL              |...
| name              | char(20)         | NO   |     | NULL              |...
| last_updated_time | timestamp        | NO   |     | CURRENT_TIMESTAMP |...
+-------------------+------------------+------+-----+-------------------+...
3 rows in set (0.01 sec)

En el ejemplo anterior hemos utilizado la sentencia ALTER TABLE con la palabra clave RENAME COLUMN. Se trata de una función de MySQL 8.0. Alternativamente podríamos utilizar ALTER TABLE con la palabra clave CHANGE por compatibilidad:

mysql> ALTER TABLE language CHANGE last_update last_updated_time TIMESTAMP
    -> NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

En este ejemplo, puedes ver que hemos proporcionado cuatro parámetros a la sentencia ALTER TABLE con la palabra clave CHANGE:

  1. El nombre de la tabla, language

  2. El nombre original de la columna, last_update

  3. El nuevo nombre de la columna, last_updated_time

  4. El tipo de columna, TIMESTAMP, con un montón de atributos extra, que son necesarios para no cambiar la definición original

Debes proporcionar los cuatro; eso significa que tienes que reespecificar el tipo y las cláusulas que lo acompañan. En este ejemplo, como estamos utilizando MySQL 8.0 con la configuración predeterminada, TIMESTAMP ya no tiene valores predeterminados explícitos. Como puedes ver, utilizar RENAME COLUMN es mucho más fácil que CHANGE.

Si quieres modificar el tipo y las cláusulas de una columna, pero no su nombre, puedes utilizar la palabra clave MODIFY:

mysql> ALTER TABLE language MODIFY name CHAR(20) DEFAULT 'n/a';
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

También puedes hacerlo con la palabra clave CHANGE, pero especificando dos veces el mismo nombre de columna:

mysql> ALTER TABLE language CHANGE name name CHAR(20) DEFAULT 'n/a';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Ten cuidado cuando modifiques tipos:

  • No utilices tipos incompatibles, ya que confías en que MySQL convierta correctamente los datos de un formato a otro (por ejemplo, convertir una columna INT en una columna DATETIME no es probable que haga lo que esperabas).

  • No trunques los datos a menos que sea lo que quieres. Si reduces el tamaño de un tipo, los valores se editarán para ajustarse a la nueva anchura, y puedes perder datos.

Supongamos que quieres añadir una columna adicional a una tabla existente. He aquí cómo hacerlo con la sentencia ALTER TABLE:

mysql> ALTER TABLE language ADD native_name CHAR(20);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Debes proporcionar la palabra clave ADD, el nombre de la nueva columna, y el tipo de columna y las cláusulas. Este ejemplo añade la nueva columna, native_name, como última columna de la tabla, tal y como se muestra con la sentencia SHOW COLUMNS:

mysql> SHOW COLUMNS FROM artist;
+-------------------+------------------+------+-----+-------------------+...
| Field             | Type             | Null | Key | Default           |...
+-------------------+------------------+------+-----+-------------------+...
| language_id       | tinyint unsigned | NO   | PRI | NULL              |...
| name              | char(20)         | YES  |     | n/a               |...
| last_updated_time | timestamp        | NO   |     | CURRENT_TIMESTAMP |...
| native_name       | char(20)         | YES  |     | NULL              |...
+-------------------+------------------+------+-----+-------------------+...
4 rows in set (0.00 sec)

Si quieres que en su lugar sea la primera columna, utiliza la palabra clave FIRST como se indica a continuación:

mysql> ALTER TABLE language ADD native_name CHAR(20) FIRST;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW COLUMNS FROM language;
+-------------------+------------------+------+-----+-------------------+...
| Field             | Type             | Null | Key | Default           |...
+-------------------+------------------+------+-----+-------------------+...
| native_name       | char(20)         | YES  |     | NULL              |...
| language_id       | tinyint unsigned | NO   | PRI | NULL              |...
| name              | char(20)         | YES  |     | n/a               |...
| last_updated_time | timestamp        | NO   |     | CURRENT_TIMESTAMP |...
+-------------------+------------------+------+-----+-------------------+...
4 rows in set (0.01 sec)

Si quieres que se añada en una posición concreta, utiliza la palabra clave AFTER:

mysql> ALTER TABLE language ADD native_name CHAR(20) AFTER name;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW COLUMNS FROM language;
+-------------------+------------------+------+-----+-------------------+...
| Field             | Type             | Null | Key | Default           |...
+-------------------+------------------+------+-----+-------------------+...
| language_id       | tinyint unsigned | NO   | PRI | NULL              |...
| name              | char(20)         | YES  |     | n/a               |...
| native_name       | char(20)         | YES  |     | NULL              |...
| last_updated_time | timestamp        | NO   |     | CURRENT_TIMESTAMP |...
+-------------------+------------------+------+-----+-------------------+...
4 rows in set (0.00 sec)

Para eliminar una columna, utiliza la palabra clave DROP seguida del nombre de la columna. He aquí cómo deshacerte de la columna native_name recién añadida:

mysql> ALTER TABLE language DROP native_name;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

Esto elimina tanto la estructura de la columna como los datos que contenga. También elimina la columna de cualquier índice en el que estuviera; si es la única columna del índice, éste también se elimina. No puedes eliminar una columna si es la única de una tabla; para ello, elimina la tabla, como se explica en "Eliminar estructuras". Ten cuidado al eliminar columnas, porque cuando cambia la estructura de una tabla, generalmente tendrás que modificar cualquier sentencia INSERT que utilices para insertar valores en un orden determinado. Para más información, consulta "La sentencia INSERT".

MySQL te permite especificar varias alteraciones en una sola sentencia ALTER TABLE separándolas con comas. Aquí tienes un ejemplo que añade una nueva columna y ajusta otra:

mysql> ALTER TABLE language ADD native_name CHAR(255), MODIFY name CHAR(255);
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

Observa que esta vez puedes ver que se han modificado seis registros. En los comandos anteriores de ALTER TABLE, MySQL informó de que no se había visto afectada ninguna fila. La diferencia es que esta vez no estamos realizando una operación DDL en línea, porque cambiar el tipo de cualquier columna siempre provocará la reconstrucción de la tabla. Te recomendamos que leas sobre las operaciones DDL en línea en el Manual de Referencia cuando planifiques tus cambios. Combinar operaciones online y offline dará como resultado una operación offline.

Cuando no se utiliza DDL en línea o cuando alguna de las modificaciones está "desconectada", es muy eficaz unir varias modificaciones en una sola operación. Eso ahorra potencialmente el coste de crear una tabla nueva, copiar los datos de la tabla antigua a la nueva, eliminar la tabla antigua y renombrar la tabla nueva con el nombre de la tabla antigua para cada modificación individualmente.

Añadir, eliminar y modificar índices

Como hemos comentado anteriormente, a menudo es difícil saber qué índices de son útiles antes de que se utilice la aplicación que estás construyendo. Puede que descubras que una función concreta de la aplicación es mucho más popular de lo que esperabas, lo que te hará evaluar cómo mejorar el rendimiento de las consultas asociadas. Por tanto, te resultará útil poder añadir, modificar y eliminar índices sobre la marcha, una vez implementada la aplicación. Esta sección te muestra cómo hacerlo. Ten en cuenta que la modificación de índices no afecta a los datos almacenados en una tabla.

Empezaremos añadiendo un nuevo índice. Imagina que la tabla language se consulta con frecuencia utilizando una cláusula WHERE que especifica la name. Para acelerar estas consultas, has decidido añadir un nuevo índice, al que has llamado idx_name. He aquí cómo lo añades después de crear la tabla:

mysql> ALTER TABLE language ADD INDEX idx_name (name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

De nuevo, puedes utilizar indistintamente los términos KEY y INDEX. Puedes comprobar los resultados con la declaración SHOW CREATE TABLE:

mysql> SHOW CREATE TABLE language\G
*************************** 1. row ***************************
       Table: language
Create Table: CREATE TABLE `language` (
  `language_id` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `name` char(255) DEFAULT NULL,
  `last_updated_time` timestamp NOT NULL
    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`language_id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8
    DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Como era de esperar, el nuevo índice forma parte de la estructura de la tabla. También puedes especificar una clave primaria para una tabla después de crearla:

mysql> CREATE TABLE no_pk (id INT);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO no_pk VALUES (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> ALTER TABLE no_pk ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

Veamos ahora cómo eliminar un índice. Para eliminar un índice de clave no primaria, haz lo siguiente:

mysql> ALTER TABLE language DROP INDEX idx_name;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

Puedes eliminar un índice de clave primaria del siguiente modo:

mysql> ALTER TABLE no_pk DROP PRIMARY KEY;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

MySQL no te permite tener varias claves primarias en una tabla. Si quieres cambiar la clave primaria, tendrás que eliminar el índice existente antes de añadir el nuevo. Sin embargo, sabemos que es posible agrupar operaciones DDL. Considera este ejemplo:

mysql> ALTER TABLE language DROP PRIMARY KEY,
    -> ADD PRIMARY KEY (language_id, name);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

No puedes modificar un índice una vez creado. Sin embargo, en a veces querrás hacerlo; por ejemplo, puede que quieras reducir el número de caracteres indexados de una columna o añadir otra columna al índice. El mejor método para hacerlo es eliminar el índice y volver a crearlo con la nueva especificación. Por ejemplo, supongamos que decides que quieres que el índice idx_name incluya sólo los 10 primeros caracteres del artist_name. Simplemente haz lo siguiente:

mysql> ALTER TABLE language DROP INDEX idx_name,
    -> ADD INDEX idx_name (name(10));
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Cambiar el nombre de las tablas y modificar otras estructuras

Hemos visto cómo modificar columnas e índices de una tabla; ahora vamos a ver cómo modificar las propias tablas. Es fácil cambiar el nombre de una tabla. Supongamos que quieres cambiar el nombre de language a languages. Utiliza el siguiente comando:

mysql> ALTER TABLE language RENAME TO languages;
Query OK, 0 rows affected (0.04 sec)

La palabra clave TO es opcional.

Hay otras cosas que puedes hacer con las declaraciones de ALTER, entre ellas:

  • Cambia el juego de caracteres y el orden de intercalación por defecto de una base de datos, una tabla o una columna.

  • Gestionar y modificar restricciones. Por ejemplo, puedes añadir y eliminar claves foráneas.

  • Añade particiones a una tabla o modifica la definición actual de particiones.

  • Cambiar el motor de almacenamiento de una tabla.

Puedes encontrar más información sobre estas operaciones en el Manual de Referencia de MySQL, en las secciones sobre ALTER DATABASE y ALTER TABLE . Una notación alternativa más corta para la misma sentencia es RENAME TABLE:

mysql> RENAME TABLE languages TO language;
Query OK, 0 rows affected (0.04 sec)

Una cosa que no es posible alterar es el nombre de una determinada base de datos . Sin embargo, si utilizas el motor InnoDB, puedes utilizar RENAME para mover tablas entrebases de datos:

mysql> CREATE DATABASE sakila_new;
Query OK, 1 row affected (0.05 sec)
mysql> RENAME TABLE sakila.language TO sakila_new.language;
Query OK, 0 rows affected (0.05 sec)
mysql> USE sakila;
Database changed
mysql> SHOW TABLES LIKE 'lang%';
Empty set (0.00 sec)
mysql> USE sakila_new;
Database changed
mysql> SHOW TABLES LIKE 'lang%';
+------------------------------+
| Tables_in_sakila_new (lang%) |
+------------------------------+
| language                     |
+------------------------------+
1 row in set (0.00 sec)

Borrar estructuras

En la sección anterior mostramos cómo puedes eliminar columnas y filas de una base de datos; ahora describiremos cómo eliminar bases de datos y tablas.

Dar de baja bases de datos

Eliminar una base de datos es muy sencillo. A continuación te explicamos cómo eliminar la base de datos sakila:

mysql> DROP DATABASE sakila;
Query OK, 25 rows affected (0.16 sec)

El número de filas devuelto en la respuesta es el número de tablas eliminadas. Debes tener cuidado al eliminar una base de datos, ya que se borran todas sus tablas, índices y columnas, así como todos los archivos y directorios asociados en disco que MySQL utiliza para mantenerlos.

Si una base de datos no existe, al intentar eliminarla MySQL informa de un error. Intentemos eliminar de nuevo la base de datos sakila:

mysql> DROP DATABASE sakila;
ERROR 1008 (HY000): Can't drop database 'sakila'; database doesn't exist

Puedes evitar el error, que es útil cuando incluyes la declaración en un script, utilizando la frase IF EXISTS:

mysql> DROP DATABASE IF EXISTS sakila;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Puedes ver que se informa de una advertencia, ya que la base de datos sakila ya se ha eliminado.

Eliminar tablas

Eliminar tablas es tan fácil como eliminar una base de datos. Vamos a crear y eliminar una tabla de la base de datos sakila:

mysql> CREATE TABLE temp (id SERIAL PRIMARY KEY);
Query OK, 0 rows affected (0.05 sec)
mysql> DROP TABLE temp;
Query OK, 0 rows affected (0.03 sec)

No te preocupes: el mensaje 0 rows affected es engañoso. Verás que la tabla ha desaparecido definitivamente.

Puedes utilizar la frase IF EXISTS para evitar errores. Probemos a eliminar de nuevo la tabla temp:

mysql> DROP TABLE IF EXISTS temp;
Query OK, 0 rows affected, 1 warning (0.01 sec)

Como siempre, puedes investigar la advertencia con la declaración SHOW WARNINGS:

mysql> SHOW WARNINGS;
+-------+------+-----------------------------+
| Level | Code | Message                     |
+-------+------+-----------------------------+
| Note  | 1051 | Unknown table 'sakila.temp' |
+-------+------+-----------------------------+
1 row in set (0.00 sec)

Puedes eliminar más de una tabla en una única sentencia separando los nombres de las tablas con comas:

mysql> DROP TABLE IF EXISTS temp, temp1, temp2;
Query OK, 0 rows affected, 3 warnings (0.00 sec)

En este caso hay tres advertencias porque ninguna de estas tablas existía.

Get Aprender MySQL, 2ª Edición now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial