5 Tipos de columnas

Una vez que hemos decidido qué información debemos almacenar, hemos normalizado nuestras tablas y hemos creado claves principales, el siguiente paso consiste en elegir el tipo adecuado para cada atributo.

En MySQL existen bastantes tipos diferentes disponibles, de modo que será mejor que los agrupemos por categorías: de caracteres, enteros, de coma flotante, tiempos, bloques, enumerados y conjuntos.

Tipos de datos de cadenas de caracteres

CHAR

CHAR

Es un sinónimo de CHAR(1), y puede contener un único carácter.

CHAR()

[NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE]

Contiene una cadena de longitud constante. Para mantener la longitud de la cadena, se rellena a la derecha con espacios. Estos espacios se eliminan al recuperar el valor.

Los valores válidos para M son de 0 a 255, y de 1 a 255 para versiones de MySQL previas a 3.23.

Si no se especifica la palabra clave BINARY estos valores se ordenan y comparan sin distinguir mayúsculas y minúsculas.

CHAR es un alias para CHARACTER.

VARCHAR()

[NATIONAL] VARCHAR(M) [BINARY]

Contiene una cadena de longitud variable. Los valores válidos para M son de 0 a 255, y de 1 a 255 en versiones de MySQL anteriores a 4.0.2.

Los espacios al final se eliminan.

Si no se especifica la palabra clave BINARY estos valores se ordenan y comparan sin distinguir mayúsculas y minúsculas.

VARCHAR es un alias para CHARACTER VARYING.

Tipos de datos enteros

TINYINT

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

Contiene un valor entero muy pequeño. El rango con signo es entre -128 y 127. El rango sin singo, de 0 a 255.

BIT, BOOL, BOOLEAN

Todos son sinónimos de TINYINT(1).

SMALLINT

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

Contiene un entero corto. El rango con signo es de -32768 a 32767. El rango sin singo, de 0 a 65535.

MEDIUMINT

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

Contiene un entero de tamaño medio, el rango con signo está entre -8388608 y 8388607. El rango sin signo, entre 0 y 16777215.

INT

INT[(M)] [UNSIGNED] [ZEROFILL]

Contiene un entero de tamaño normal. El rango con signo está entre -2147483648 y 2147483647. El rango sin singo, entre 0 y 4294967295.

INTEGER

INTEGER[(M)] [UNSIGNED] [ZEROFILL]

Es sinónimo de INT.

BIGINT

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

Contiene un entero grande. El rango con signo es de -9223372036854775808 a 9223372036854775807. El rango sin signo, de 0 a 18446744073709551615.

Tipos de datos en coma flotante

FLOAT

FLOAT(precision) [UNSIGNED] [ZEROFILL]

Contiene un número en coma flotante. precision puede ser menor o igual que 24 para números de precisión sencilla y entre 25 y 53 para números en coma flotante de doble precisión. Estos tipos son idénticos que los tipos FLOAT y DOUBLE descritos a continuación. FLOAT(X) tiene el mismo rango que los tipos FLOAT y DOUBLE correspondientes, pero el tamaño mostrado y el número de decimales quedan indefinidos.

FLOAT()

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

Contiene un número en coma flotante pequeño (de precisión sencilla). Los valores permitidos son entre -3.402823466E+38 y -1.175494351E-38, 0, y entre 1.175494351E-38 y 3.402823466E+38. Si se especifica el modificador UNSIGNED, los valores negativos no se permiten.

El valor M es la anchura a mostrar y D es el número de decimales. Si se usa sin argumentos o si se usa FLOAT(X), donde X sea menor o igual que 24, se sigue definiendo un valor en coma flotante de precisión sencilla.

DOUBLE

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

Contiene un número en coma flotante de tamaño normal (precisión doble). Los valores permitidos están entre -1.7976931348623157E+308 y -2.2250738585072014E-308, 0, y entre 2.2250738585072014E-308 y 1.7976931348623157E+308. Si se especifica el modificador UNSIGNED, no se permiten los valores negativos.

El valor M es la anchura a mostrar y D es el número de decimales. Si se usa sin argumentos o si se usa FLOAT(X), donde X esté entre 25 y 53, se sigue definiendo un valor en coma flotante de doble precisión.

DOUBLE PRECISION, REAL

DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]
REAL[(M,D)] [UNSIGNED] [ZEROFILL]

Ambos son sinónimos de DOUBLE.

DECIMAL

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

Contiene un número en coma flotante sin empaquetar. Se comporta igual que una columna CHAR: "sin empaquetar" significa qu se almacena como una cadena, usando un carácter para cada dígito del valor. El punto decimal y el signo '-' para valores negativos, no se cuentan en M (pero el espacio para estos se reserva). Si D es 0, los valores no tendrán punto decimal ni decimales.

El rango de los valores DECIMAL es el mismo que para DOUBLE, pero el rango actual para una columna DECIMAL dada está restringido por la elección de los valores M y D.

Si se especifica el modificador UNSIGNED, los valores negativos no están permitidos.

Si se omite D, el valor por defecto es 0. Si se omite M, el valor por defecto es 10.

DEC, NUMERIC, FIXED

DEC[(M[,D])] [UNSIGNED] [ZEROFILL]
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

Todos ellos son sinónimos de DECIMAL.

Tipos de datos para tiempos

DATE

DATE

Contiene una fecha. El rango soportado está entre '1000-01-01' y '9999-12-31'. MySQL muestra los valores DATE con el formato 'AAAA-MM-DD', pero es posible asignar valores a columnas de este tipo usando tanto números como cadenas.

DATETIME

DATETIME

Contiene una combinación de fecha y hora. El rango soportado está entre '1000-01-01 00:00:00' y '9999-12-31 23:59:59'. MySQL muestra los valores DATETIME con el formato 'AAAA-MM-DD HH:MM:SS', pero es posible asignar valores a columnas de este tipo usando tanto cadenas como números.

TIMESTAMP

TIMESTAMP[(M)]

Contiene un valor del tipo timestamp. El rango está entre '1970-01-01 00:00:00' y algún momento del año 2037.

Hasta MySQL 4.0 los valores TIMESTAMP se mostraban como AAAAMMDDHHMMSS, AAMMDDHHMMSS, AAAAMMDD o AAMMDD, dependiendo del si el valor de M es 14 (o se omite), 12, 8 o 6, pero está permitido asignar valores a columnas TIMESTAMP usando tanto cadenas como números.

Desde MySQL 4.1, TIMESTAMP se devuelve como una cadena con el formato 'AAAA-MM-DD HH:MM:SS'. Para convertir este valor a un número, bastará con sumar el valor 0. Ya no se soportan distintas longitudes para estas columnas.

Se puede asignar fácilmente la fecha y hora actual a uno de estas columnas asignando el valor NULL.

El argumento M afecta sólo al modo en que se visualiza la columna TIMESTAMP. Los valores siempre se almacenan usando cuatro bytes. Además, los valores de columnas TIMESTAMP(M), cuando M es 8 ó 14 se devuelven como números, mientras que para el resto de valores se devuelven como cadenas.

TIME

TIME

Una hora. El rango está entre '-838:59:59' y '838:59:59'. MySQL muestra los valores TIME en el formato 'HH:MM:SS', pero permite asignar valores a columnas TIME usando tanto cadenas como números.

YEAR

YEAR[(2|4)]

Contiene un año en formato de 2 ó 4 dígitos (por defecto es 4). Los valores válidos son entre 1901 y 2155, y 0000 en el formato de 4 dígitos. Y entre 1970-2069 si se usa el formato de 3 dígitos (70-69).

MySQL muestra los valores YEAR usando el formato AAAA, pero permite asignar valores a una columna YEAR usando tanto cadenas como números.

Tipos de datos para datos sin tipo o grandes bloques de datos

TINYBLOB, TINYTEXT

TINYBLOB
TINYTEXT

Contiene una columna BLOB o TEXT con una longitud máxima de 255 caracteres (2⁸ - 1).

BLOB, TEXT

BLOB
TEXT

Contiene una columna BLOB o TEXT con una longitud máxima de 65535 caracteres (2¹⁶ - 1).

MEDIUMBLOB, MEDIUMTEXT

MEDIUMBLOB
MEDIUMTEXT

Contiene una columna BLOB o TEXT con una longitud máxima de 16777215 caracteres (2²⁴ - 1).

LONGBLOB, LONGTEXT

LONGBLOB
LONGTEXT

Contiene una columna BLOB o TEXT con una longitud máxima de 4294967298 caracteres (2³² - 1).

Tipos enumerados y conjuntos

ENUM

ENUM('valor1','valor2',...)

Contiene un enumerado. Un objeto de tipo cadena que puede tener un único valor, entre una lista de valores 'valor1', 'valor2', ..., NULL o el valor especial de error "". Un ENUM puede tener un máximo de 65535 valores diferentes.

SET

SET('valor1','valor2',...)

Contiene un conjunto. Un objeto de tipo cadena que puede tener cero o más valores, cada uno de los cuales debe estar entre una lista de valores 'valor1', 'valor2', ...

Un conjunto puede tener un máximo de 64 miembros.

Ejemplo 1

El siguiente paso del diseño nos obliga a elegir tipos para cada atributo de cada relación. Veamos cómo lo hacemos para los ejemplos que hacemos en cada capítulo.

Para el primer ejemplo teníamos el siguiente esquema:

Estación(Identificador, Latitud, Longitud, Altitud)
Muestra(IdentificadorEstación, Fecha, Temperatura mínima, Temperatura máxima,
  Precipitaciones, Humedad mínima, Humedad máxima, Velocidad del viento mínima,
  Velocidad del viento máxima)

En MySQL es importante, aunque no obligatorio, usar valores enteros como claves principales, ya que las optimizaciones que proporcionan un comportamiento mucho mejor para claves enteras.

Vamos a elegir el tipo de cada atributo, uno por uno:

Relación Estación

Identificador: podríamos pensar en un entero corto o medio, aunque no tenemos datos sobre el número de estaciones que debemos manejar, no es probable que sean más de 16 millones. Este dato habría que incluirlo en la documentación, pero supongamos que con MEDIUMINT UNSIGNED es suficiente.

Latitud: las latitudes se expresan en grados, minutos y segundos, al norte o sur del ecuador. Los valores están entre 'N90º00'00.000"' y 'S90º00'00.000"'. Además, los segundos, dependiendo de la precisión de la posición que almacenemos, pueden tener hasta tres decimales. Para este tipo de datos tenemos dos opciones. La primera es la que comentamos en el capítulo anterior: no considerar este valor como atómico, y guardar tres números y la orientación N/S como atributos separados. Si optamos por la segunda, deberemos usar una cadena, que tendrá como máximo 14 caracteres. El tipo puede ser CHAR(14) o VARCHAR(14).

Longitud: las longitudes se almacenan en un formato parecido, pero la orientación es este/oeste, y el valor de grados varía entre 0 y 180, es decir, que necesitamos un carácter más: CHAR(15) o VARCHAR(15).

Altitud: es un número entero, que puede ser negativo si la estación está situada en una depresión, y como máximo a unos 8000 metros (si alguien se atreve a colocar una estación en el Everest. Esto significa que con un MEDIUMINT tenemos más que suficiente.

Relación Muestra

IdentificadorEstación: se trata de una clave foránea, de modo que debe ser del mismo tipo que la clave primaria de la que procede: MEDIUMINT UNSIGNED.

Fecha: sólo necesitamos almacenar una fecha, de modo que con el tipo DATE será más que suficiente.

Temperatura mínima: las temperaturas ambientes en grados centígrados (Celsius) se pueden almacenar en un entero muy pequeño, TINYINT, que permite un rango entre -128 y 127. Salvo que se sitúen estaciones en volcanes, no es probable que se salga de estos rangos. Recordemos que las muestras tienen aplicaciones meteorológicas.

Temperatura máxima: lo mismo que para la temperatura mínima: TINYINT.

Precipitaciones: personalmente, ignoro cuánto puede llegar a llover en un día, pero supongamos que 255 litros por metro cuadrado sea una cantidad que se puede superar. En ese caso estamos obligados a usar el siguiente rango: SMALLINT UNSIGNED, que nos permite almacenar números hasta 65535.

Humedad mínima: las humedades se miden en porcentajes, el valor está acotado entre 0 y 100, de nuevo nos bastará con un TINYINT, nos da lo mismo con o sin signo, pero usaremos el UNSIGNED.

Humedad máxima: También TINYINT UNSIGNED.

Velocidad del viento mínima: también estamos en valores siempre positivos, aunque es posible que se superen los 255 Km/h, de modo que, para estar seguros, usaremos SMALLINT UNSIGNED.

Velocidad del viento máxima: también usaremos SMALLINT UNSIGNED.

Ejemplo 2

Para el segundo ejemplo partimos del siguiente esquema:

Libro(ClaveLibro, Título, Idioma, Formato, ClaveEditorial)
Tema(ClaveTema, Nombre)
Autor(ClaveAutor, Nombre)
Editorial(ClaveEditorial, Nombre, Dirección, Teléfono)
Ejemplar(ClaveEjemplar, ClaveLibro, NúmeroOrden, Edición, Ubicación, Categoría)
Socio(ClaveSocio, Nombre, Dirección, Teléfono, Categoría)
Préstamo(ClaveSocio, ClaveEjemplar, NúmeroOrden, Fecha_préstamo,
  Fecha_devolución, Notas)
Trata_sobre(ClaveLibro, ClaveTema)
Escrito_por(ClaveLibro, ClaveAutor)

Relación Libro

ClaveLibro: como clave principal que es, este atributo debe ser de tipo entero. Una biblioteca puede tener muchos libros, de modo que podemos usar el tipo INT.

Título: para este atributo usaremos una cadena de caracteres, la longitud es algo difícil de decidir, pero como primera aproximación podemos usar un VARCHAR(60).

Idioma: usaremos una cadena de caracteres, por ejemplo, VARCHAR(15).

Formato: se trata de otra palabra, por ejemplo, VARCHAR(15).

ClaveEditorial: es una clave foránea, y por lo tanto, el tipo debe ser el mismo que para el atributo 'ClaveEditorial' de la tabla 'Editorial', que será SMALLINT.

Relación Tema

ClaveTema: el número de temas puede ser relativamente pequeño, con un SMALLINT será más que suficiente.

Nombre: otra cadena, por ejemplo, VARCHAR(40).

Relación Autor

ClaveAutor: usaremos, al igual que con los libros, el tipo INT.

Nombre: aplicando el mismo criterio que con los título, usaremos el tipo VARCHAR(60).

Relación Editorial

ClaveEditorial: no existen demasiadas editoriales, probablemente un SMALLINT sea suficiente.

Nombre: usaremos el mismo criterio que para títulos y nombres de autores, VARCHAR(60).

Dirección: también VARCHAR(60).

Teléfono: los números de teléfono, a pesar de ser números, no se suelen almacenar como tales. El problema es que a veces se incluyen otros caracteres, como el '+' para el prefijo, o los paréntesis. En ciertos paises se usan caracteres como sinónimos de dígitos, etc. Usaremos una cadena lo bastante larga, VARCHAR(15).

Relación Ejemplar

ClaveEjemplar: es la clave principal, el tipo debe ser INT.

ClaveLibro: es una clave foránea, el tipo debe ser INT.

NúmeroOrden: tal vez el tipo TINYINT sea pequeño en algunos casos, de modo que usaremos SMALLINT.

Edición: pasa lo mismo que con el valor anterior, puede haber libros con más de 255 ediciones, no podemos arriesgarnos. Usaremos SMALLINT.

Ubicación: esto depende de cómo se organice la biblioteca, pero un campo de texto puede almacenar tanto coordenadas como etiquetas, podemos usar un VARCHAR(15).

Categoría: recordemos que para este atributo usábamos una letra, por lo tanto usaremos el tipo CHAR.

Relación Socio

ClaveSocio: usaremos un INT.

Nombre: seguimos con el mismo criterio, VARCHAR(60).

Dirección: lo mismo, VARCHAR(60).

Teléfono: como en el caso de la editorial, VARCHAR(15).

Categoría: ya vimos que este atributo es un carácter, CHAR.

Relación Préstamo

ClaveSocio: como clave foránea, el tipo está predeterminado. INT.

ClaveEjemplar: igual que el anterior, INT.

NúmeroOrden: y lo mismo en este caso, SMALLINT.

Fecha_préstamo: tipo DATE, sólo almacenamos la fecha.

Fecha_devolución: también DATE.

Notas: necesitamos espacio, para este tipo de atributos se usa el tipo BLOB.

Relación Trata_sobre

ClaveLibro: INT.

ClaveTema: SMALLINT.

Relación Escrito_por

ClaveLibro: INT.

ClaveAutor: INT.