MySQL / Tipos de datos

Bienvenidos sean a mi post, hoy hablaremos sobre los distintos tipos de datos utilizables en MySQL, y en las bases de datos en general., como hemos visto en algunos posts anteriores cuando creabamos una tabla definiamos a las columnas un tipo de dato para almacenar una informacion en el campo correspondiente, por ejemplo veamos esta instancia:

CREATE TABLE mi_tabla
(
int_col INT,
str_col CHAR(20),
date_col DATE
)

En este caso creamos una tabla con tres columnas donde la primera va a almacenar datos del tipo entero (p.e. puede ser edad, documento, nota de un examen, etc), en la segunda columna se guardan de tipo cadena, o caracteres, (p.e. utilizar para nombre, apellido, calle, etc) y la ultima columna guarda datos del tipo fecha (p.e. 2018-02-03) en el formato visto en este post. A la hora de asignar los tipos de datos para nuestra tablas debemos pensar bien con cual dato es mas apropiado para la columna, por ejemplo para almacenar un dato como la edad se puede usar el tipo INT (entero), para una altura el tipo FLOAT (con decimales) o para almacenar un nombre o apellido el tipo CHAR o VARCHAR, pasemos a una breve descripcion de los tipos de variables de las bases de datos:

Tipo Numerico

• TINYINT : Tipo entero muy pequeña
• SMALLINT : Tipo entero pequeña
• MEDIUMINT : Tipo entero mediana
• INT : Tipo entero
• BIGINT : Tipo Entero largo
• FLOAT : Numero unico de precision de coma flotante
• DOUBLE : Numero doble de precision de coma flotante
• DECIMAL : Numero de coma flotante, representado como una cadena

Tipo Cadena

• CHAR : Cadena con caracteres con longitud fija
• VARCHAR : Cadena con caracteres con longitud variable
• TINYBLOB : BLOB (1) muy pequeño
• BLOB : BLOB (1) pequeño
• MEDIUMBLOB : BLOB (1) mediano
• LONGBLOB : BLOB (1) largo
• TINYTEXT : Cadena de texto pequeña
• TEXT : Cadena de texto
• MEDIUMTEXT : Cadena de texto mediana
• LONGTEXT : Cadena de texto larga
• ENUM : Enumeracion (2)
• SET : Conjunto (3)

(1) Binary Large OBject, Objeto binario largo.
(2) Columnas a las que se puede asignar un miembro de enumeracion
(3) Columnas a las que se puede asignar multiples conjuntos de miembros

Tipo Fecha

• DATE : Fecha con formato AAAA-MM-DD
• TIME : Hora con formato hh:mm:ss
• DATETIME : Fecha y hora con formato AAAA-MM-DD hh:mm:ss
• TIMESTAMP : Lapso de tiempo con formato AAAAMMDDhhmmss
• YEAR : Año con formato AAAA

Hasta aqui tenemos los tipos de datos para poder utilizar, ahora veremos una forma de declarar las columnas con los tipos de datos:

CREATE TABLE mi_tabla
(
campo_a FLOAT(10,4),
campo_b CHAR(15) NOT NULL DEFAULT "none",
campo_c TINYINT UNSIGNED NULL
)

Ahora observen, al momento de definir el tipo de columna primero declaramos el nombre de la columna, luego el tipo de dato y entre parentesis el tamaño del dato maximo de la columna, como ven en el tipo FLOAT se define para almacenar hasta un numero de diez digitos antes de la coma y cuatro digitos despues de la coma, en el caso de CHAR una cadena de texto de quince caracteres (o letras) y por ultimo despues de definido el tipo vienen las opciones, aca podemos observar las distintas opciones:

• NOT NULL : Esta columna no puede quedar vacia (es decir NULL)
• DEFAULT «none» : En el caso de dejar vacio la columna, la columna automaticamente ingresara el valor none para no tener un error en el query por eso se utiliza el valor por defecto.
• UNSIGNED : El valor debe ser sin signo
• NULL : La columna puede tener un valor vacio

Obviamente esto dependera mucho del tipo de informacion a almacenar por ejemplo nosotros vamos a tener un determinado tipo de dato que no puede ser vacio o NULL, por ejemplo el codigo de identificacion de un alumno o de un usuario de una pagina, por otro lado en el caso donde la informacion no es importante (o no es vital para almacenar) como por un ejemplo una edad, telefono o fecha de nacimiento entonces podemos dejarlo como NULL, por eso al momento de diseñarla se deben tener estos datos en cuenta. El valor NULL implica la falta de un valor dentro de una tabla esto nos puede resultar util para los casos donde nosotros necesitamos la ausencia de un valor porque cero o un espacio es un valor almacenado dentro de la columna y nosotros en algunos casos preferiremos una ausencia de valor para hacer una busqueda mas eficiente porque MySQL omite los campos vacios. En el caso de los tipos numericos no es necesario definir el tamaño de la columna, esto es porque los tipos tienen un valor predeterminado de cantidad de memoria de almacenamiento y por ende la definicion de un valor es opcional, salvo para el caso de los tipo DECIMAL donde su tamaño si dependera de lo definido por el programador, un atributo de los tipos numericos en el caso de los tipos de enteros (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT) se puede utilizar la opcion UNSIGNED para no permitir numeros negativos en la columna y permitiendonos aumentar la cantidad disponible en este tipo para el resto de los tipos numericos esta opcion no esta permitida, otro atributo es ZEROFILL la cual llena de ceros a la izquierda hasta completar el tamaño  de la columna esta opcion funcionara siempre y cuando el numero sea menor al tamaño de la columna, otro atributo puede ser NULL o NOT NULL el cual permite o no permite valores NULL en la columna respectivamente, despues esta DEFAULT donde podremos definir el tipo de valor predeterminado para la columna (recureden, esta debe ser del mismo tipo de la columna) y finalmente AUTO_INCREMENT el cual permite generar numeros secuenciales automaticamente.

Si bien nosotros no le definimos ningun valor este iniciara desde el valor cero y a la proxima entrada le asignara el valor uno y luego el dos y asi sucesivamente, este metodos en gral. es utilizado para generar codigos de identificacion unicos ya sea para identificar un inventario, codigos de error, id de alumnos, etc y como dijimos anteriormente el valor no debe ser duplicado para una mejor identificacion si bien este atributo puede ser utilizado para los tipos enteros tambien se puede utilizar para los tipos de cadena, aunque su mejor funcionamiento es en el caso de los tipos enteros pasemos a los tipos de cadena, en el caso de los tipos de cadena podemos utilizar la mayoria de los atributos antes mencionados (NULL, NOT NULL y DEFAULT) con los mismos resultados, a este tipo tambien se lo denomina «genericos» porque pueden almacenar todo tipo de informacion (cadena, numero y fecha) si bien nosotros podremos efectuar operaciones matematicas y/o comparacion con este tipo nos consumira muchos procesos porque MySQL debera convertir los datos entre si, ahora hablemos de CHAR y VARCHAR, estas son columnas de cadena de textos pero la primera es de longitud fija y la segunda  variable, la primera es recomendable cuando se sabe que los datos son del mismo tamaño porque permite una busqueda mas eficiente y ocupa menos espacio en caso contrario, de ser menor al tamaño de la columna, va a completar los caracteres faltantes con espacios y nos puede dificultar la busquedas, en cambio VARCHAR al ser variable se va a adaptar mas facilmente a este inconveniente, el problema radica en el tamaño (ya que necesita un byte extra para almacenar el tamaño) y el otro problema es la necesidad de un mayor procesamiento por no ser iguales, otro inconveniente es no poder mezclar las columnas de CHAR y VARCHAR, o  definimos todas de un tipo u otro, pasemos al tipo de BLOB y TEXT.

El primer tipo (BLOB) es un contenedor del tipo binario y es utilizado para contener todo tipo de informacion (texto, archivos, etc), en este caso no le podemos definir un tamaño o un valor por defecto, en cambio el tamaño es definido por el conjunto del tipo (TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB), el tipo TEXT es similar al de BLOB pero estos son utilizados para almacenar grandes cantidades de texto y el tamaño tambien es definido por su conjunto (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT), estos tipos de columnas no pueden ser utilizadas para hacer una busqueda porque al ser tipo binario no podria buscarse de forma convencional.

Nuestra siguiente parada son los tipos ENUM y SET, el ENUM como en otros lenguajes sirve para enumenar las opciones dentro de la misma, SET es similar a ENUM con la diferencia de en lugar de hacerlo de forma secuencial cada uno se corresponde a un bit del valor individual del SET cuyo valor maximo puede ser 64, para entender un poco mejor veamos los siguientes ejemplos:

CREATE TABLE e_table (e ENUM("jane", "fred", "will", "marcia"));
insert into e_table values ("marcia"),("jane"),("will"),("fred"),(NULL);

Con la primera linea vamos a crear la tabla donde va a tener una columna llamada e con los valores en orden secuencial con cada uno de los nombres informados, es decir jane = 1, fred = 2, will = 3 y marcia = 4 en este caso el valor 0 se le asigna al espacio en blanco, una vez creada la tabla nosotros podemos ingresar los datos, en este caso para ver mejor el ejemplo lo modifique con respecto al orden original  y tambien agregue un valor NULL, esto se deberia cargar sin ningun inconveniente, si nosotros ahora ejecutamos un query obtendriamos el siguiente resultado:

MariaDB [tutorial]> select e, e+0, e+1, e*3 from e_table;
+--------+------+------+------+
| e      | e+0  | e+1  | e*3  |
+--------+------+------+------+
| marcia |    4 |    5 |   12 |
| jane   |    1 |    2 |    3 |
| will   |    3 |    4 |    9 |
| fred   |    2 |    3 |    6 |
| NULL   | NULL | NULL | NULL |
+--------+------+------+------+
5 rows in set (0.000 sec)

MariaDB [tutorial]>

Observen como el orden de los nombres no corresponde al momento de ser creado pero si observamos el valor de la numeracion es respetado y lo podemos verificar a la hora de efectuar operaciones, como dijimos anteriormente el valor NULL al ser carente de valor siempre va a ser NULL, esto es lo comentado anteriormetne con respecto a NULL, como se observa la aplicacion de ENUM sirve para tener un mejor de identificacion de los valores designados en el mismo por ejemplo podemos utilizar estos querys:

MariaDB [tutorial]> select e from e_table where e=3;
+------+
| e    |
+------+
| will |
+------+
1 row in set (0.129 sec)

MariaDB [tutorial]>

MariaDB [tutorial]> select e from e_table where e='will';
+------+
| e    |
+------+
| will |
+------+
1 row in set (0.000 sec)

MariaDB [tutorial]>

Como se ve al efectuar por el valor asignado en la enumeracion o por el nombre siempre arroja el mismo resultado, pasemos a ver un caso de SET si bien no es muy distinto tiene una gran diferencia a la hora de asignar el valor de identificacion:

CREATE TABLE s_table
(s SET("jane","fred","will","marcia","marta","martin","diego","laura"));
insert into s_table
values ("will"),("jane"),("marcia"),("fred"),("marta"),("laura"),("martin"),("diego"),(""),(NULL);

Al igual que vimos en el ejemplo anterior ahora creamos una tabla con mas integrantes para poder ver mejor el ejemplo, tal como el caso anterior no es necesario respetar el orden pero a la hora de hacer un query vamos a observar los siguientes detalles:

MariaDB [tutorial]> select s, s+0,BIN(s+0) from s_table order by s;
+--------+------+----------+
| s      | s+0  | BIN(s+0) |
+--------+------+----------+
| NULL   | NULL | NULL     |
|        |    0 | 0        |
| jane   |    1 | 1        |
| fred   |    2 | 10       |
| will   |    4 | 100      |
| marcia |    8 | 1000     |
| marta  |   16 | 10000    |
| martin |   32 | 100000   |
| diego  |   64 | 1000000  |
| laura  |  128 | 10000000 |
+--------+------+----------+
10 rows in set (0.107 sec)

MariaDB [tutorial]>

En este caso al hacer al query lo ordene para poder distinguir el metodo de enumeracion de SET y tambien otro detalle, NULL siempre va a ser nuestro primer valor cuando ordernemos va a estar por encima de cero y el espacio vacio tambien pueden ver el valor en binario del valor asignado y como el siguiente valor, a diferencia de ENUM no es secuencial, va siendo el doble del anterior en bits, si bien yo no le encuentro una utilidad muy practica mas adelante explicare un poco mejor y buscare un ejemplo donde nos pueda ser mas util este tipo de dato.

Por ultimo pasemos a los de tipo fecha entre ellos pueden manipular fechas, hora y año, veamos el primer caso:

DATE, este sirve para almacenar la fecha, recuerden que debe ser siempre en el orden de año-mes-dia (AAAA-MM-DD), esto se puede almacenar con años de cuatro digitos o de dos digitos, p.e. 2018-02-07 o 18-02-07, donde automaticamente lo convertira al año si corresponde.

TIME para este el formato debe ser hora:minutos:segundos (hh:mm:ss) porque este tipo de dato trabaja de forma distinta, no guarda la hora sino guarda el tiempo transcurrido por eso podemos tener algunos problemas de utilizacion porque si queremos almacenar una hora como por ejemplo 12:30 y nosotros no especificamos los segundos, MySQL lo almacenara como si fuera «00:12:30» entonces para evitar esto cuando debemos especificar un horario como el citado anteriormente se debe utilizar «12:30:00».

DATETIME se almacena con la mezcla de los dos formatos, es decir año-mes-dia hora:minutos:segundos (AAAA-MM-DD hh:mm:ss) en donde se almacenara toda la informacion tanto de la fecha como de la hora, en gral. en estos casos es preferible usarlo con la opcion NOW() al momento de cargarlo para utilizar la hora y fecha del momento de efectuada la carga, sino deberia especificar ambos pero en el caso de utilizarlo y solo cargar la fecha MySQL automaticamente cargara al lado de la fecha el valor 00:00:00 para completar la sentencia.

TIMESTAMP este almacenara la fecha y hora en el momento de la carga tiene la particularidad de no ser necesaria la especificacion, este en el momento de ingresar un dato puede dejarse como vacio, como NULL o con la opcion NOW() y en todos los casos cargara la fecha y hora del momento, esto tambien es aplicable cuando se actualice con UPDATE el formato es de 14 digitos (AAAAMMDDhhmmss) pero nosotros podemos decir cual va a ser el ancho de la columna por ejemplo:

TIMESTAMP(8): mostrara solamente la fecha completa (20180207)
TIMESTAMP(12): mostrara la fecha con la hora en horas y minutos (201802071321)
TIMESTAMP: mostrara todo (20180207132120)

YEAR, donde solo se almacenara el año indicado, el formato es AAAA (de cuatro digitos) pero se pueden indicar dos solamente donde MySQL utilizara el siguiente criterio para asignar los faltantes, del 00 al 69 utilizara el 2000 del 70 al 99 utilizara el 1900, el unico incoveniente posible puede ser con 00 que al ingresar como numerico puede tomarlo como 0000 para evitar esto se deberia ingresar como cadena, es decir «0» o «00», pero al margen de este inconveniente despues funciona correctamente, en el caso de YEAR tambien podemos definir el ancho de la columna, es decir de cuatro o dos digitos.

Los tipos de datos tienen sus respectivos operadores, ya sea para comparacion o para calcular, en general. nosotros vamos a utilizar valores del tipo numerico en los casos donde vamos a almacenar notas de examenes, cantidad de productos en un stock, ingresos del dia, cantidad de asisitentes de una conferencia, etc para el caso donde despues necesitemos ejecutar una operacion matematica ya sea desde un promedio, la diferencia de un dia al otro, la cantidad total de asistentes, etc. para el caso de los tipos cadena utilizaremos justamente para una descripcion de algun dato en particular, para indicar dicha cantidad de que es, almacenar un resumen y tambien podemos almacenar informacion de tipo numerico pero no es recomendable porque se requiere un mayor procesamiento para convertir primero y luego operar, y por ultimo los tipos fecha podemos utilizarlos para almacenar la fecha de un documento, la hora o el año pero como hemos dicho anteriormente al no tener una forma estandar de mostrar dicho dato es preferible utilizarlo como tipo log, donde podremos saber en cual dia y fecha fue cargada y/o modificada dicha entrada y tener otro campo con la fecha de tipo cadena, lo bueno de los tipos fechas es el hecho de poder ordernarlas por ese campo de forma ascendente o descendente permitiendonos una mejor visualizacion a la hora de llevar un control sobre las entradas en nuestra tabla y cuando fueron modificadas.

Obviamente esto es un vistazo muy por arriba de como utilizar los distintos tipos de datos, despues tendremos que ajustarlo a nuestras necesidades, por ejemplo para llevar una identificacion de los empleados en una oficina se puede utilizar una columna de nombre ID donde va a ser d tipo INT NOT NULL y a su vez AUTO_INCREMENT para que cada nuevo empleado tengo su propia identificacion, y en el caso de guardar tambien la informacion se puede utilizar un tipo FLOAT para el sueldo, o DOUBLE dependiendo de la precision necesitada, y usar cuatro campos del tipo VARCHAR donde podemos almacenar nombre, apellido, fecha de ingreso a la compañia y una fecha de salida, tambien puede tener una columna de tipo TEXT donde podemos ingresar con un poco mas de detalle el motivo de la salida de la compañia, esto puede ser opcional.

Para ir finalizando, se pueden utilizar los operadores matematicos para los tipos numericos (tambien para los de tipo de cadena pero no es recomendado), en el caso de los tipos cadena pueden utilizar el CONCAT() esto nos permitira unir varias cadenas de texto y por ultimo vamos a estudiar algo muy importante para ejecutar busquedas, uno es el operador LIKE y el otro es el comodin (%), esto nos permitiran hacer busquedas no exactas, por ejemplo podriamos crear una opcion donde se podria buscar por un extracto de una palabra, aca les dejo un ejemplo de como se utiliza:

select nombre,apellido from empleados where nombre like '%tin';
select nombre,apellido from empleados where nombre like 'mar%';
select nombre,apellido from empleados where nombre like '%mar%';

En estos ejemplos podemos observar como el like sustitye al igual (=) y en conjunto con el comodin (%) se pueden buscar por coincidencias no exactas, en el primer caso devolvera todas las entradas cuyo nombre termine con «tin», en el segundo caso donde comiencen con «mar» y en la ultima linea nos devolvera todos los nombres que contengan la palabra «mar» en cualquier parte del nombre, como se ve dependiendo de donde coloquemos el comodin (%) modificara el tipo de respuesta del query.

En resumen, hoy hemos visto los distintos tipos de datos disponibles, que tipo de informacion pueden almacenar, que operaciones se pueden realizar sobre las mismas, los pros y contras de usar un tipo determinado como si fuera otro, cual es mas conveniente y algunos tipos especiales, espero les haya sido util sigueme en tumblr, Twitter o Facebook para recibir una notificacion cada vez que subo un nuevo post en este blog, nos vemos en el proximo post.

Tengo un Patreon donde podes acceder de manera exclusiva a material para este blog antes de ser publicado, sigue los pasos del link para saber como.