Listado y
descripción de los distintos tipos de datos de MySQL.
Después de la
fase de diseño de una base de datos, y una vez se ha realizado el paso a tablas
del mismo, en necesario crear las tablas correspondientes dentro de la base de
datos. Para cada campo de cada una de las tablas, es necesario determinar el
tipo de datos que contiene, para de esa forma ajustar el diseño de la base de
datos, y conseguir un almacenamiento óptimo con la menor utilización de
espacio. El presente artículo describe cada uno de los tipos de datos que puede
tener un campo en Mysql, para la versión 4.xx.xx.
Los tipos de
datos que puede haber en un campo, se pueden agrupar en tres grandes grupos:
Tipos numéricos
Tipos de Fecha
Tipos de Cadena
1 Tipos
numéricos:
Existen tipos de
datos numéricos, que se pueden dividir en dos grandes grupos, los que están en
coma flotante (con decimales) y los que no.
TinyInt: es un
número entero con o sin signo. Con signo el rango de valores válidos va desde
-128 a 127. Sin signo, el rango de valores es de 0 a 255
Bit ó Bool: un
número entero que puede ser 0 ó 1
SmallInt: número
entero con o sin signo. Con signo el rango de valores va desde -32768 a 32767.
Sin signo, el rango de valores es de 0 a 65535.
MediumInt:
número entero con o sin signo. Con signo el rango de valores va desde
-8.388.608 a 8.388.607. Sin signo el rango va desde 0 a16777215.
Integer, Int:
número entero con o sin signo. Con signo el rango de valores va desde
-2147483648 a 2147483647. Sin signo el rango va desde 0 a 429.4967.295
BigInt: número
entero con o sin signo. Con signo el rango de valores va desde
-9.223.372.036.854.775.808 a 9.223.372.036.854.775.807. Sin signo el rango va
desde 0 a 18.446.744.073.709.551.615.
Float: número
pequeño en coma flotante de precisión simple. Los valores válidos van desde
-3.402823466E+38 a -1.175494351E-38, 0 y desde 1.175494351E-38 a
3.402823466E+38.
xReal, Double:
número en coma flotante de precisión doble. Los valores permitidos van desde
-1.7976931348623157E+308 a -2.2250738585072014E-308, 0 y desde
2.2250738585072014E-308 a 1.7976931348623157E+308
Decimal, Dec,
Numeric: Número en coma flotante desempaquetado. El número se almacena como una
cadena
Tipo de Campo
Tamaño de
Almacenamiento
TINYINT
1 byte
SMALLINT
2 bytes
MEDIUMINT
3 bytes
INT
4 bytes
INTEGER
4 bytes
BIGINT
8 bytes
FLOAT(X)
4 ú 8 bytes
FLOAT
4 bytes
DOUBLE
8 bytes
DOUBLE PRECISION
8 bytes
REAL
8 bytes
M+2 bytes sí D
> 0, M+1 bytes sí D = 0
DECIMAL(M,D
M+2 bytes if D
> 0, M+1 bytes if D = 0
NUMERIC(M,D)
2 Tipos fecha:
A la hora de
almacenar fechas, hay que tener en cuenta que Mysql no comprueba de una manera
estricta si una fecha es válida o no. Simplemente comprueba que el mes esta
comprendido entre 0 y 12 y que el día esta comprendido entre 0 y 31.
Date: tipo
fecha, almacena una fecha. El rango de valores va desde el 1 de enero del 1001
al 31 de diciembre de 9999. El formato de almacenamiento es de año-mes-dia
DateTime:
Combinación de fecha y hora. El rango de valores va desde el 1 de enero del
1001 a las 0 horas, 0 minutos y 0 segundos al 31 de diciembre del 9999 a las 23
horas, 59 minutos y 59 segundos. El formato de almacenamiento es de año-mes-dia
horas:minutos:segundos
TimeStamp:
Combinación de fecha y hora. El rango va desde el 1 de enero de 1970 al año
2037. El formato de almacenamiento depende del tamaño del campo:
Tamaño
Formato
AñoMesDiaHoraMinutoSegundo
aaaammddhhmmss
14
AñoMesDiaHoraMinutoSegundo
aammddhhmmss
12
8
ñoMesDia
aaaammdd
6
AñoMesDia aammdd
4
AñoMes aamm
2
Año aa
Time: almacena
una hora. El rango de horas va desde -838 horas, 59 minutos y 59 segundos a
838, 59 minutos y 59 segundos. El formato de almacenamiento es de 'HH:MM:SS'
Year: almacena
un año. El rango de valores permitidos va desde el año 1901 al año 2155. El
campo puede tener tamaño dos o tamaño 4 dependiendo de si queremos almacenar el
año con dos o cuatro dígitos.
Tipo de Campo
Tamaño de
Almacenamiento
DATE
3 bytes
DATETIME
8 bytes
TIMESTAMP
4 bytes
TIME
3 bytes
YEAR
1 byte
3 Tipos de
cadena:
Char(n):
almacena una cadena de longitud fija. La cadena podrá contener desde 0 a 255
caracteres.
VarChar(n):
almacena una cadena de longitud variable. La cadena podrá contener desde 0 a
255 caracteres.
Dentro de los
tipos de cadena se pueden distinguir otros dos subtipos, los tipo Test y los
tipo BLOB (Binary large Object)
La diferencia
entre un tipo y otro es el tratamiento que reciben a la hora de realizar
ordenamientos y comparaciones. Mientras que el tipo test se ordena sin tener en
cuenta las Mayúsculas y las minúsculas, el tipo BLOB se ordena teniéndolas en
cuenta.
Los tipos BLOB
se utilizan para almacenar datos binarios como pueden ser ficheros.
TinyText y
TinyBlob: Columna con una longitud máxima de 255 caracteres.
Blob y Text: un
texto con un máximo de 65535 caracteres.
MediumBlob y
MediumText: un texto con un máximo de 16.777.215 caracteres.
LongBlob y
LongText: un texto con un máximo de caracteres 4.294.967.295. Hay que tener en
cuenta que debido a los protocolos de comunicación los paquetes pueden tener un
máximo de 16 Mb.
Enum: campo que
puede tener un único valor de una lista que se especifica. El tipo Enum acepta
hasta 65535 valores distintos
Set: un campo
que puede contener ninguno, uno ó varios valores de una lista. La lista puede
tener un máximo de 64 valores.
Tipo de campo
Tamaño de
Almacenamiento
CHAR(n)
n bytes
VARCHAR(n)
n +1 bytes
TINYBLOB,
TINYTEXT
Longitud+1 bytes
BLOB, TEXT
Longitud +2 bytes
MEDIUMBLOB,
MEDIUMTEXT
Longitud +3
bytes
LONGBLOB,
LONGTEXT
Longitud +4
bytes
ENUM('value1','value2',...)
1 ó dos bytes
dependiendo del número de valores
SET('value1','value2',...)
1, 2, 3, 4 ó 8
bytes, dependiendo del número de valores
Diferencia de
almacenamiento entre los tipos Char y VarChar
Almace
namiento
Almace
namiento
Valor
CHAR(4)
VARCHAR(4)
''
''
4 bytes
"
1 byte
'ab'
'ab '
4 bytes
'ab'
3 bytes
'abcd'
'abcd'
4 bytes
'abcd'
'abcdefgh'
'abcd'
4 bytes
'abcd'
5 bytes
Sintaxis de
sentencias SQL
Tabla de
contenidos
13.1. Sentencias
de definición de datos (Data Definition
Statements) [+/-]
Sentencias de
definición de datos (Data Definition Statements)
[+/-]
13.1.1. Sintaxis
de ALTER DATABASE
13.1.2. Sintaxis
de ALTER TABLE
13.1.3. Sintaxis
de CREATE DATABASE
13.1.4. Sintaxis
de CREATE INDEX
13.1.5. Sintaxis
de CREATE TABLE
13.1.6. Sintaxis
de DROP DATABASE
13.1.7. Sintaxis
de DROP INDEX
13.1.8. Sintaxis
de DROP TABLE
13.1.9. Sintaxis
de RENAME TABLE
13.2. Sentencias
de manipulación de datos (Data Manipulation
Statements) [+/-]
Sentencias de
manipulación de datos (Data Manipulation Statements)
[+/-]
13.2.1. Sintaxis
de DELETE
13.2.2. Sintaxis
de DO
13.2.3. Sintaxis
de HANDLER
13.2.4. Sintaxis
de INSERT
13.2.5. Sintaxis
de LOAD DATA INFILE
13.2.6. Sintaxis
de REPLACE
13.2.7. Sintaxis
de SELECT
13.2.8. Sintaxis
de subconsultas
13.2.9. Sintaxis
de TRUNCATE
13.2.10.
Sintaxis de UPDATE
13.3. Sentencias
útiles de MySQL [+/-]
Sentencias
útiles de MySQL [+/-]
Sentencias
útiles de MySQL
[+/-]
13.3.1. Sintaxis
de DESCRIBE (Información acerca de las columnas)
13.3.2. Sintaxis
de USE
13.4. Comandos
transaccionales y de bloqueo de MySQL [+/-]
Comandos transaccionales
y de bloqueo de MySQL
[+/-]
13.4.1. Sintaxis
de START TRANSACTION, COMMIT y ROLLBACK
13.4.2.
Sentencias que no se pueden deshacer
13.4.3.
Sentencias que causan una ejecución (commit) implícita
13.4.4. Sintaxis
de SAVEPOINT y ROLLBACK TO SAVEPOINT
13.4.5. Sintaxis
de LOCK TABLES y UNLOCK TABLES
13.4.6. Sintaxis
de SET TRANSACTION
13.5. Sentencias
de administración de base de datos [+/-]
Sentencias de
administración de base de datos
[+/-]
13.5.1.
Sentencias para la gestión de cuentas
13.5.2.
Sentencias para el mantenimiento de tablas
13.5.3. Sintaxis
de SET
13.5.4. Sintaxis
de SHOW
13.5.5. Otras
sentencias para la administración
13.6. Sentencias
de replicación [+/-]
Sentencias de
replicación
[+/-]
13.6.1.
Sentencias SQL para el control de servidores maestros
13.6.2.
Sentencias SQL para el control de servidores esclavos
13.7. Sintaxis
SQL de sentencias preparadas
Sintaxis SQL de
sentencias preparadas
MySQL 5.0
proporciona soporte para comandos preparados en la parte del servidor. Este
soporte aprovecha del protocolo binario cliente-servidor implementado en MySQL
4.1, dado que use una interfaz de programación cliente apropiada. Las
interfaces candidatas incluyen la biblioteca de la API de C de MySQL y MySQL
Connector/NET. Por ejemplo, la API C proporciona un conjunto de llamadas de
funciones que prepararn su API de comandos preparados. Consulte
Sección 24.2.4, “Sentencias
preparadas de la API C”. Otras
interfaces de lenguajes pueden proporcionar soporte para comandos preparados
que usen el protocolo binario enlazando la biblioteca cliente C, un ejemplo es
la extensión mysqli extension in PHP 5.0.
Una interfaz SQL
alternativa para comandos preparados está disponible Su intefaz no es tan
eficiente como usar el protocolo binario mediante una API de comandos
preparados, pero no necesita programación porque está disponible directamente a
nivel SQL:
Puede usarlo
cuando no haya interfaz programable diponible
Puede usarlo
desde cualquier programa que le permita enviar comandos SQL al servidor, tales
como el programa cliente mysql .
Puede usarlo
incluso si el cliente está usando una antigua versión de la biblioteca cliente.
El único requerimiento es que sea capaz de conectar a un servidor lo
suficientemente reciente para soporta sintaxis SQL para comandos preparados.
La sintaxis SQL
para comandos preparados está pensada para usar en situaciones como la
siguiente:
Puede querer
testear cómo fucionan los comandos preparados en su aplicación antes de
codificar la aplicación. O quizás una aplicación tenga un problema ejecutando
comandos preparados y quiera determinar interactivamente cuál es el problema.
Quiere crear un
test de uso que describa un problema que está teniendo con comandos preparados,
así que puede perparar un reporte de error.
Necesita usar
comandos preparados pero no tiene acceso a la API de programación que los
soporta.
La sintaxis SQL
para comandos preparados se abasa en tres comandos SQL:
PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
{DEALLOCATE |
DROP} PREPARE stmt_name;
El comando
PREPARE prepara un comando y le asigna un nombre, stmt_name, con el que referirse al comando posteriormente. Los
nombres de comando no son sensibles a mayúsculas. preparable_stmt es una cadena literal o una variable de usuario que
contenga el texto del comando. El texto debe representar un comando SQL único,
no varios. Dentro del comando, pueden usarse caracteres '?' como marcadores de
parámetros para indicar dónde estarán los valores en la consulta posterior
cuando la ejecute. Los caracteres '?' no deben delimitarse con comillas,
incluso si pretende ligarlos con valores de cadenas.
Si un comando
preparado con ese nombre existe, se elimina implícitamente antes que se prepare
el nuevo comando. Esto significa que si el nuevo comando contiene un error y no
puede preparase, se retorna un error y no existe un comando con el nombre dado.
El alcance de
los comandos preparados es la sesión de cliente dentro de la que se crea. Otros
clintes no pueden verlos.
Tras preparar un
comando, lo ejecuta con un comando EXECUTE que se refiere al nombre de comando
preparado. Si el comando preparado contiene cualquier marcador de parámetro,
debe añadir una cláusula USING que liste las variables de usuario conteniendo
los valores a ligar con los parámetros. Los valores de parámetros pueden
proporcionarse sólo por variables de usuario, y la cláusula USING debe nombrar
exactamente tantas variables como el número de marcadores de parámetros en el
comando.
Puede ejecutar
un comando preparado dado varias veces, pasando distintas variables al mismo o
configurando las variables con distintos valores para cada ejecución.
Para eliminar un
comando preparado, use el comando DEALLOCATE PREPARE . Tratar de ejecutar un
comando preparado tras borrarlo provoca un error.
Si termina una
sesión de cliente sin borrar un comando preparado préviamente, el servidor lo
borra automáticamente.
Los siguientes
comandos SQL pueden usarse en comandos preparados: CREATE TABLE, DELETE, DO,
INSERT, REPLACE, SELECT, SET, UPDATE, y la mayoría de comandos SHOW . Otros
comandos no se soportan todavía.
Los siguientes
ejemplos muestran dos formas equivalentes de preparar un comando que calcula la
hipotenusa de un triángulo dadas las longitudes de los dos catetos.
El primer
ejemplo muestra cómo crear un comando preparado usando una cadena literal para
proporcionar el texto del comando:
mysql>
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a
= 3;
mysql> SET @b
= 4;
mysql>
EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 5 |
+------------+
mysql>
DEALLOCATE PREPARE stmt1;
El segundo
ejemplo es similar, pero proporciona el texto del comando como variable de
usuario:
mysql> SET @s
= 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql>
PREPARE stmt2 FROM @s;
mysql> SET @a
= 6;
mysql> SET @b
= 8;
mysql>
EXECUTE stmt2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 10 |
+------------+
mysql>
DEALLOCATE PREPARE stmt2;
La sintaxis SQL
para comandos preparados no puede usarse anidada. Esto es, un comando pasado a
PREPARE no puede ser el mismo un comando PREPARE, EXECUTE, o DEALLOCATE PREPARE
.
Además, la
sintaxis SQL para comandos preparados es distinta de la usada en llamadas a la
API de comandos preparados. Por ejemplo, use la función mysql_stmt_prepare() de
la API C para preparar un comando PREPARE, EXECUTE, o DEALLOCATE PREPARE .
La sintaxis SQL
para comandos preparados no puede usarse dentro de procedimientos almacenados y
funciones.
Ésta es una
traducción del manual de referencia de MySQL, que puede encontrarse en
dev.mysql.com. El manual de referencia original de MySQL está escrito en
inglés, y esta traducción no necesariamente está tan actualizada como la
versión original.
Este capítulo
describe la sintaxis para los comandos SQL soportados en MySQL.
Ésta es una
traducción del manual de referencia de MySQL, que puede encontrarse en
dev.mysql.com. El manual de referencia original de MySQL está escrito en
inglés, y esta traducción no necesariamente está tan actualizada como la
versión original.
Restricciones de
las tablas InnoDB
Una tabla no
puede contener más de 1000 columnas.
La longitud
máxima interna de una clave es 3500 bytes, pero MySQL la restringe a 1024
bytes.
La longitud
máxima de fila, excepto para columnas VARCHAR, BLOB y TEXT, es ligeramente
inferior a la mitad de una página de base de datos. Es decir, cerca de 8000
bytes. Las columnas LONGBLOB y LONGTEXT deben ser de menos de 4GB, y la
longitud total de la fila, incluyendo las columnas BLOB y TEXT, debe ser de
menos de 4GB. InnoDB almacena los primeros 768 bytes de una columna VARCHAR,
BLOB, o TEXT en la fila, y el resto, en páginas separadas.
En algunos
sistemas operativos antiguos, los ficheros de datos deben ser de menos de 2GB.
El tamaño
combinado de los ficheros de log de InnoDB debe ser inferior a 4GB.
El tamaño mínimo
del espacio de tablas es de 10MB. El tamaño máximo es de cuatrocientos mil millones
de páginas de base de datos (64TB). Este es también el tamaño máximo para una
tabla.
Las tablas
InnoDB no admiten índices FULLTEXT.
Las tablas
InnoDB no admiten tipos de columna espaciales.
ANALYZE TABLE
determina la cardinalidad efectuando 10 accesos al azar en cada uno de los
árboles de índices y actualizando la cardinalidad del índice con una estimación
acorde. Dado que son solamente estimaciones, distintas ejecuciones de ANALYZE
TABLE pueden producir resultados diferentes. Esto convierte a ANALYZE TABLE en
una herramienta rápida sobre tablas InnoDB, pero no con el mismo nivel de
exactitud que si considerara todas las filas al hacer el recuento.
MySQL emplea las
estimaciones de cardinalidad de los índices solamente para la optimización de
uniones. Si una unión no se optimiza en la manera adecuada, se puede intentar
el uso de ANALYZE TABLE. En los pocos casso en que ANALYZE TABLE no produce
valores suficientemente buenos para las tablas, se puede emplear FORCE INDEX en
las consultas para forzar el uso de un índice en particular, o establecer el
valor de max_seeks_for_key para asegurarse de que MySQL dará preferencia a las
búsquedas en índices por sobre el examen de las tablas. Consulte
Sección 5.3.3, “Variables
de sistema del servidor”. Consulte
Sección A.6, “Cuestiones
relacionadas con el optimizador”.
En Windows,
InnoDB siempre almacena internamente en minúsculas los nombres de tablas y
bases de datos. Para mover bases de datos en formato binario desde Unix a
Windows o a la inversa, se deberán haber escrito en minúsculas todos los
nombres de tablas y bases de datos.
Advertencia: ¡No
deben convertirse las tablas de sistema de MySQL de la base de datos mysql
desde su formato original MyISAM a InnoDB! Esta es una operación no admitida.
Si se lleva a cabo, MySQL no se podrá ejecutar hasta que se recuperen las
tablas de sistema anteriores desde una copia de respaldo o se las regenere con
el script mysql_install_db.
InnoDB no lleva
una cuenta interna de las filas en una tabla. (Esto sería realmente complicado
a causa de la multiversión). Para procesar una sentencia SELECT COUNT(*) FROM
T, InnoDB debe examinar un índice de la tabla, lo cual lleva algún tiempo si el
índice no está completamente dentro del pool de buffer. Para disponer de un recuento
más rápido, se debe crear una tabla de recuento y hacer que la aplicación la
actualice a medida que se producen inserciones y eliminaciones. Si una tabla no
se modifica a menudo, utilizar el cache de consultas (query cache) de MySQL es
una buena solución. También puede emplearse SHOW TABLE STATUS si es suficiente
un recuento aproximado de filas. Consulte Sección 15.11, “Consejos de afinamiento del rendimiento
de InnoDB”.
Para una columna
AUTO_INCREMENT, siempre se debe definir un índice para la tabla, el cual debe
contener solamente a la columna AUTO_INCREMENT. En tablas MyISAM, la columna
AUTO_INCREMENT puede formar parte de un índice junto a otras columnas.
InnoDB no admite
la opción AUTO_INCREMENT en sentencias CREATE TABLE o ALTER TABLE, la cual
sirve para establecer el valor inicial de la secuencia. Para especificar este
valor en InnoDB, debe insertarse una fila con un valor que sea uno menos que el
deseado, y luego borrarla, o insertar la primera fila especificando un valor
determinado.
Luego de
reiniciar el servidor MySQL, InnoDB puede reutilizar un valor antiguo para una
columna AUTO_INCREMENT (esto es, un valor que se hubiese asignado a una
transacción finalmente cancelada).
Cuando una
columna AUTO_INCREMENT sobrepasa el máximo valor que es capaz de almacenar,
InnoDB coloca la columna en -9223372036854775808 (si es BIGINT) o en 1 (si es
BIGINT UNSIGNED). Sin embargo, como los valores BIGINT tienen 64 bits, hay que
notar que si se insertara un millón de filas por segundo, se demoraría cerca de
trescientos mil años en agotar los números disponibles. Con otros tipos de
columnas enteros, ocurre un error de clave duplicada. Esto es similar al
funcionamiento de MyISAM, ya que es en mayor medida el comportamiento general
de MySQL y no pertenece a ningún motor de almacenamiento en particular.
DELETE FROM nom_tabla no regenera la tabla sino que elimina todas sus
filas, una por una.
TRUNCATE tbl_name se implementa en InnoDB como DELETE FROM tbl_name y no inicializa el contador de AUTO_INCREMENT.
SHOW TABLE
STATUS no proporciona estadísticas precisas en tablas InnoDB, excepto para el
tamaño físico reservado por la tabla. El recuento de filas es solamente una
estimación utilizada para la optimización SQL.
En MySQL 5.0, la
operación LOCK TABLES establece dos bloqueos en cada tabla si
innodb_table_locks=1, que es el valor por defecto. Adicionalmente al bloqueo de
tabla en la capa MySQL, también se establece un bloqueo de tabla en InnoDB. En
versiones antiguas de MySQL no se establecía el bloqueo en InnoDB, para volver
a este comportamiento debe especificarse innodb_table_locks=0. Si no se
establece el bloqueo InnoDB, LOCK TABLES se completa aún cuando algunos
registros de las tablas estén bloqueados por otras transacciones.
Todos los
bloqueos InnoDB efectuados por una transacción se liberan cuando la transacción
se confirma o se cancela. Por lo tanto, no tiene mucho sentido invocar LOCK
TABLES en tablas InnoDB cuando se está en el modoAUTOCOMMIT=1, porque los
bloqueos establecidos sobre una tabla InnoDB se liberarán inmediatamente.
Algunas veces
sería útil bloquear tablas extensas en el curso de una trasacción.
Desafortunadamente, LOCK TABLES, en MySQL, emite implícitamente un COMMIT y un
UNLOCK TABLES. Está planeada una variante para InnoDB de LOCK TABLES que puede
ejecutarse dentro de una transacción.
La sentencia
LOAD TABLE FROM MASTER empleada para la replicación de servidores esclavos no
funciona aún con tablas InnoDB. Una solución temporal es cambiar a MyISAMla
tabla en el servidor amo (master), efectuar la carga, y volver a cambiar la
tabla en el amo (master) a su motor original InnoDB.
El tamaño por
defecto de cada página de base de datos en InnoDB es de 16KB. Se puede
establecer en valores entre 8KB y 64KB recompilando el código. Se deben
modificar los valores de UNIV_PAGE_SIZE y UNIV_PAGE_SIZE_SHIFT en el fichero
fuente univ.i.
En MySQL 5.0,
los disparadores (triggers) aún no son activados por modificaciones efectuadas
en cascada a través de claves foráneas.
