Los tipos de cadenas de carácteres son CHAR,
VARCHAR, BINARY,
VARBINARY, BLOB,
TEXT, ENUM, y
SET. Esta sección describe cómo funcionan
estos tipos y cómo usarlo en sus consultas.
Los tipos CHAR y VARCHAR
son similares, pero difieren en cómo se almacenan y recuperan.
Desde MySQL 5.0.3, también difieren en la longitud máxima y en
cómo se tratan los espacios finales.
Los tipos CHAR y VARCHAR
se declaran con una longitud que indica el máximo número de
carácteres que quiere almacenar. Por ejemplo,
CHAR(30) puede almacenar hasta 30
carácteres.
La longitud de una columna CHAR se fija a la
longitud que se declara al crear la tabla. La longitud puede ser
cualquier valor de 0 a 255. Cuando los valores
CHAR se almacenan, se añaden espacios a la
derecha hasta las longitud específica. Cuando los valores
CHAR se recuperan, estos espacios se borran.
Los valores en columnas VARCHAR son cadenas
de carácteres de longitud variable. En MySQL 5.0, la longitud
puede especficarse de 0 a 255 antes de MySQL 5.0.3, y de 0 a
65,535 en 5.0.3 y versiones posteriores. (La máxima longitud
efectiva de un VARCHAR en MySQL 5.0 se
determina por el tamaño de registro máximo y el conjunto de
carácteres usados. La longitud máxima total es de 65,532
bytes.)
En contraste con CHAR,
VARCHAR almacena los valores usando sólo los
carácteres necesarios, más un byte adicional para la longitud
(dos bytes para columnas que se declaran con una longitud
superior a 255).
Los valores VARCHAR no se cortan al
almacenarse. El tratamiento de espacios al final depende de la
versión. Desde MySQL 5.0.3, los espacios finales se almacenan
con el valor y se retornan, según el estándard SQL. Antes de
MySQL 5.0.3, los espacios finales se eliminan de los valores
cuando se almacenan en una columna VARCHAR,
esto significa que los espacios también están ausentes de los
valores retornados.
Durante el almacenamiento y la recuperación de valores no hace ninguna conversión de mayúsculas y minúsculas.
Si asigna un valor a una columna CHAR o
VARCHAR que exceda la longitud máxima de la
columna, el valor se trunca. Si los carácteres truncados no son
espacios, se genera una advertencia. Puede hacer que aparezca un
error en lugar de una advertencia usando modo SQL estricto.
Consulte Sección 5.3.2, “El modo SQL del servidor”.
Antes de MySQL 5.0.3, si necesita un tipo de datos para el que
no se borren los espacios finales, considere usar un tipo
BLOB o TEXT . También, si
quiere almacenar valores binarios como resultados de
encriptación o compresión que puedan contener valores byte
arbitrarios, use una columna BLOB en lugar de
CHAR o VARCHAR, para
evitar problemas potenciales con eliminación de espacios
finales que puedan cambiar los valores de los datos.
La siguiente tabla ilustra las diferencias entre los dos tipos
de columnas mostrando el resultado de almacenar varios valores
de cadenas de carácteres en columnas CHAR(4)
y VARCHAR(4) :
| Valor |
CHAR(4)
|
Almacenamiento necesario |
VARCHAR(4)
|
Almacenamiento necesario |
''
|
' '
|
4 bytes |
''
|
1 byte |
'ab'
|
'ab '
|
4 bytes |
'ab'
|
3 bytes |
'abcd'
|
'abcd'
|
4 bytes |
'abcd'
|
5 bytes |
'abcdefgh'
|
'abcd'
|
4 bytes |
'abcd'
|
5 bytes |
Los valores retornados de las columnas
CHAR(4) y VARCHAR(4) son
los mismos en cada caso, ya que los espacios finales se eliminan
en la recuperación de valores CHAR.
En MySQL 5.0, los valores en columnas CHAR y
VARCHAR se almacenan y comparan según la
colación del conjunto de carácteres asignado a la columna.
CHAR BYTE es un alias para CHAR
BINARY. Existe por cuestión de compatibilidad.
El atributo ASCII asigna el conjunto de
carácteres latin1 a una columna
CHAR . El atributo UNICODE
asigna el conjunto de carácteres ucs2 .
MySQL puede cambiar silenciosamente el tipo de una columna
CHAR o VARCHAR en tiempo
de creación. Consulte Sección 13.1.5.1, “Cambios tácitos en la especificación de columnas”.
Los tipos BINARY y
VARBINARY son similares a
CHAR y VARCHAR, excepto
que contienen cadenas de carácteres binarias en lugar de
cadenas de carácteres no binarias. Esto es, contienen cadenas
de bytes en lugar de cadenas de carácteres. Esto significa que
no tienen conjunto de carácteres asignado, y la comparación y
ordenación se basa en los valores numéricos de los valores de
los bytes.
La longitud máxima disponible es la máxima para
BINARY t VARBINARY como
para CHAR y VARCHAR,
excepto que la longitud para BINARY y
VARBINARY es una longitud en bytes en lugar
de en carácteres.
El tratamiento de los espacios finales es el mismo para
BINARY y VARBINARY como lo
es para CHAR y VARCHAR.
Cuando se almacenan los valores BINARY, se
rellenan con espacios a la derecha hasta la longitud
especificada. Cuando los valores BINARY se
recuperan, los espacios finales se eliminan. Para
VARBINARY, los espacios finales se eliminan
cuando los valores se almacenan. Desde MySQL 5.0.3, los espacios
finales se mantienen. Debe considerar estas características si
planea usar estos tipos de datos para almacenar datos binarios
que deban acabar con espacios.
En MySQL 5.0, BINARY y
VARBINARY son tipos de datos distintos. Para
CHAR(M) BINARY y
VARCHAR(M) BINARY,
el atributo BINARY hace que se use la
colación binaria para la columna, pero la columna no contiene
cadenas de carácteres no binarios en lugar de cadenas binarias
de bytes. Por ejemplo CHAR(5) BINARY se trata
como CHAR(5) CHARACTER SET latin1 COLLATE
latin1_bin, asumiendo que el conjunto de carácteres
por defecto es latin1.
Un BLOB es un objeto binario que puede tratar
una cantidad de datos variables. Los cuatro tipos
BLOB son TINYBLOB,
BLOB, MEDIUMBLOB, y
LONGBLOB. Difieren sólo en la longitud
máxima de los valores que pueden tratar.
Los cuatro tipos TEXT son
TINYTEXT, TEXT,
MEDIUMTEXT, y LONGTEXT. Se
corresponden a los cuatro tipos BLOB y tienen
las mismas longitudes y requerimientos de almacenamiento.
Consulte Sección 11.5, “Requisitos de almacenamiento según el tipo de columna”.
Las columnas BLOB se tratan como cadenas de
carácteres binarias (de bytes). Las columnas
TEXT se tratan como cadenas de carácteres no
binarias (de carácateres). Las columnas BLOB
no tienen conjunto de carácteres, y la ordenación y la
comparación se basan en los valores numéricos de los bytes.
Las columnas TEXT tienen un conjunto de
carácteres y se ordenan y comparan en base de la colación del
conjunto de carácteres asignada a la columna desde MySQL 4.1.
No hay conversión de mayúsculas/minúsculas para columnas
TEXT o BLOB durante el
almacenamiento o la recuperación.
Si asiguna un valor a una columna BLOB o
TEXT que exceda la longitud máxima del tipo
de la columna, el valor se trunca. Si los carácteres truncados
no son espacios, aparece una advertencia. Puede hacer que
aparezca un error en lugar de una advertencia usando el modo SQL
estricto. Consulte Sección 5.3.2, “El modo SQL del servidor”.
En la mayoría de aspectos, puede tratar una columna
BLOB como VARBINARY que
puede ser tan grande como desee. Similarmente, puede tratar
columnas TEXT como
VARCHAR. BLOB y
TEXT difieren de VARBINARY
y VARCHAR en los siguientes aspectos::
-
No se eliminan espacios al final para columnas
BLOByTEXTcuando los valores se almacenan o recuperan. Antes de MySQL 5.0.3, esto difiere deVARBINARYyVARCHAR, para los que se eliminaban los epacios al final cuando se almacenaban.Tenga en cuenta que
TEXTrealiza comparación espacial extendida para coincidir con el objeto comparado, exactamente comoCHARyVARCHAR. -
Para índices en columnas
BLOByTEXT, debe especificar una longitud de prefijo para el índice. ParaCHARyVARCHAR, la longitud de prefijo es opciona. Consulte Sección 7.4.3, “Índices de columna”. -
BLOByTEXTno pueden tener valoresDEFAULT.
En MySQL 5.0, LONG y LONG
VARCHAR se mapean con el tipo de datos
MEDIUMTEXT. Esto existe por compatibilidad.
Si usa el atributo BINARY con el tipo de
columna TEXT, se asigna la colación binaria
del conjunto de carácteres a la columna.
MySQL Connector/ODBC define los valores BLOB
como LONGVARBINARY y valores
TEXT como LONGVARCHAR.
Como los valores BLOB y
TEXT pueden ser extremadamente grandes, puede
encontrar algunas restricciones al usarlos:
-
Sólo los primeros
max_sort_lengthbytes de la columna se usan al ordenar. El valor por defecto demax_sort_lengthes 1024; este valor puede cambiarse usando la opción--max_sort_lengthal arrancar el servidor mysqld . Consulte Sección 5.3.3, “Variables de sistema del servidor”.Puede hacer que haya más bytes significativos al ordenar o agrupar incrementando el valor de
max_sort_lengthen tiempo de ejecución. Cualquier cliente puede cambiar el valor de su variable de sesiónmax_sort_length:mysql> SET max_sort_length = 2000; mysql> SELECT id, comment FROM
tbl_name-> ORDER BY comment;Otra forma de usar
GROUP BYoORDER BYen una columnaBLOBoTEXTconteniendo valores grandes cuando quiere que más demax_sort_lengthbytes sean significativos es convertir el valor de la columna en un objeto de longitud fija. La forma estándard de hacerlo es con la funciónSUBSTRING. Por ejemplo, el siguiente comando causa que 2000 bytes de la columnacommentse tengan en cuenta para ordenación:mysql> SELECT id, SUBSTRING(comment,1,2000) FROM
tbl_name-> ORDER BY SUBSTRING(comment,1,2000); -
El tamaño máximo de un objeto
BLOBoTEXTse determina por su tipo, pero el valor máximo que puede transmitir entre el cliente y el servidor viene determinado por la cantidad de memoria disponible y el tamaño de los buffers de comunicación. Puede cambiar el tamaño de los buffers de comunicación cambiando el valor de la variablemax_allowed_packet, pero debe hacerlo para el servidor y los clientes . Por ejemplo, mysql y mysqldump le permite cambiar el valor de la variable del clientemax_allowed_packet. Consulte Sección 7.5.2, “Afinar parámetros del servidor”, Sección 8.3, “La herramienta intérprete de comandos mysql”, y Sección 8.7, “El programa de copia de seguridad de base de datos mysqldump”.
Cada valor BLOB o TEXT se
representa internamente como un objeto a parte. Esto se hace en
contraste con todos los otros tipos de columnas, para los que el
almacenamiento se hace una vez por columna cuando se abre la
tabla.
Un ENUM es un objeto de cadenas de
carácteres con un valor elegido de una lista de valores
permitidos que se enumeran explícitamente en la especificación
de columna en tiempo de creación de la tabla.
El valor puede ser la cadena vacía ('') o
NULL bajo ciertas circunstancias:
-
Si inserta un valor inválido en un
ENUM(esto es, una cadena de carácteres no presente en la lista de valores permitidos), la cadena vacía se inserta en lugar de un valor especial de error. Esta cadena puede distinguirse de una cadena vacía “normal” por el hecho que esta cadena tiene un valor numérico 0. Más información posteriormente. -
Si se declara una columna
ENUMpara permitirNULL, el valorNULLes un valor legal para la columna, y el valor por defecto esNULL. Si una columnaENUMse declaraNOT NULL, su valor por defecto es el primer elemento de la lista de valores permitidos.
Cada valor de la enumeración tiene un índice:
-
Los valores de la lista de elementos permitidos en la especificación de la columna se numeran empezando por 1.
-
El valor de índice de la cadena errónea es 0. Esto significa que puede usar el siguiente comando
SELECTpara encontrar registros con el valor inválidoENUMasignado:mysql> SELECT * FROM
tbl_nameWHEREenum_col=0; -
El índice del valor
NULLesNULL.
Por ejemplo, una columna especificada como ENUM('one',
'two', 'three') puede tener cualquiera de los valores
mostrados aquí. El índice de cada valor se muestra:
| Valor | Índice |
NULL
|
NULL
|
''
|
0 |
'one'
|
1 |
'two'
|
2 |
'three'
|
3 |
Una enumeración puede tener un máximo de 65,535 elementos.
Los espacios finales se borran automáticamente para valores
ENUM miembros cuando se crea la tabla.
Cuando se reciben, los valores almacenados en una columna
ENUM se muestran usando el formato de
mayúsculas/minúsculas usado en la definición de la columna.
En MySQL 4.1.1, las columnas ENUM pueden
recibir un conjunto de carácteres y colación. Para colaciones
binarias o sensibles a mayúsculas/minúsculas, el formato se
tiene en cuenta al asignar valores a la columna.
Si recibe un valor ENUM en contexto
numérico, se retorna el índice del valor. Por ejemplo, puede
recibir valores numéricos de una columna
ENUM así:
mysql> SELECTenum_col+0 FROMtbl_name;
Si almacena un número en una columna ENUM,
el número se trata como índice, y el valor almacenado es el
miembro de la enumeración con ese índice. (Sin embargo, esto
no funciona con LOAD DATA, que trata toda la
entrada como cadenas de carácteres.) No es recomendable definir
una columna ENUM con valores de enumeración
que parezcan números, ya que esto puede causar confusión. Por
ejemplo, la siguiente columna tiene miembros de enumeración con
valores de '0', '1', y
'2', pero valores de índice
1, 2, y
3:
numbers ENUM('0','1','2')
Los valores ENUM se ordenan según el order
en que se enumeran los mienbros en la especificación de la
columna. (En otras palabras, los valores ENUM
se ordenan según sus números de índice.) Por ejemplo,
'a' se ordena antes que
'b' para ENUM('a', 'b'),
pero 'b' se ordena antes de
'a' para ENUM('b', 'a').
La cadena vacía se ordena antes de las cadenas no vacías, y
los valores NULL se ordenan antes de todos
los otros valores de la enumeración. Para evitar resultados
inesperados, especifique la lista ENUM en
orden alfabético. También puede usar GROUP BY
CAST(col AS VARCHAR) o GROUP BY
CONCAT(col) para asegurarse que la columna se ordena
léxicamente en lugar de por número de índice.
Si quiere determinar todos los valores posibles para una columna
ENUM, use SHOW COLUMNS FROM
tbl_name LIKE
enum_col y parsee la
definición de ENUM en la segunda columna de
la salida.
Un SET es un objeto de cadenas de carácteres
que tiene cero o más valores, cada uno de ellos debe elegirse
de una lista de valores posibles especificada cuando se crea la
tabla. Los valores de columnas SET que
consisten de múltiples miembros del conjunto se especifican con
los miembros separados por comas (','). Una
consecuencia de esto es que los miembros de
SET no pueden contener comas ellos mismos.
Por ejemplo, una columna especificada como SET('one',
'two') NOT NULL puede tener cualquiera de estos
valores:
'' 'one' 'two' 'one,two'
Un SET puede tener un máximo de 64 miembros
distintos.
Los espacios finales se borran automáticamente de los miembros
de un SET cuando se crea la tabla.
Cuando se recuperan, los valors almacenados en una columna
SET se muestran usando la sensibilidad de
mayúsculas/minúsculas usando en la definición de la columna.
En MySQL 5.0, las columnas SET pueden tener
un conjunto de carácteres y colación. Para colaciones binarias
o sensibles a mayúsculas/minúsculas, esta sensibilidad se
tiene en cuenta al asignar valores a la columna.
MySQL almacena valores SET numéricamente,
con el bit de menos peso del valor almacenado correspondiente al
primer miembro del conjunto. Si recibe un valor
SET en un contexto numérico, el valor
recibido tiene los bits asignados correspondientes a los
miembros que coinciden con el valor de la columna. Por ejemplo,
puede recuperar los valores numéricos de una columna
SET así:
mysql> SELECTset_col+0 FROMtbl_name;
Si se almacena un número en una columna SET,
los bits que se asignan en la representación binaria del
número determinan los miembros del conjunto en el valor de la
columna. Para una columna especificada como
SET('a','b','c','d'), los miembros tienen los
siguientes valores decimales y binarios:
SET Miembro |
Valor decimal | Valor binario |
'a'
|
1
|
0001
|
'b'
|
2
|
0010
|
'c'
|
4
|
0100
|
'd'
|
8
|
1000
|
Si asigna un valor de 9 a esta columna, esto
es 1001 en binario, de forma que el primer y
cuarto miembro delSET 'a'
y 'd' se seleccionan y el valor resultante es
'a,d'.
Para un valor que contenga más de un elemento
SET, no importa el orden en que se listen los
elementos cuando inserte el valor. Tampoco no importa cuántas
veces se lista un elemento dado para el valor. Cuando el valor
se recupera posteriormente, cada elemento en el valor aparece
una vez, con los elementos listados según el orden en que se
especificaron al crear la tabla. Si una columna se especifica
como SET('a','b','c','d'),
'a,d', 'd,a', y
'd,a,a,d,d' aparecen como
'a,d' al recuperarse.
Si asigna un valor no soportado a una columna
SET, el valor se ignora.
Los valores SET se ordenan numéricamente.
Los valores NULL se ordenan antes de los no
NULL.
Normalmente, busca valores SET usando la
función FIND_IN_SET() o el operador
LIKE :
mysql> SELECT * FROMtbl_nameWHERE FIND_IN_SET('value',set_col)>0; mysql> SELECT * FROMtbl_nameWHEREset_colLIKE '%value%';
El primer comando encuentra registros cuando
set_col contiene el miembro
value del conjunto. El segundo es
similar, pero no igual: encuentra registros cuando
set_col contengan el valor
value en cualquier sitio, incluso
cuando es una subcadena de otro miembro del conjunto.
Los siguientes comandos también son legales:
mysql> SELECT * FROMtbl_nameWHEREset_col& 1; mysql> SELECT * FROMtbl_nameWHEREset_col= 'val1,val2';
El primero de estos comandos busca valores que contengan el
primer miembro del conjunto. El segundo busca una coincidencia
exacta. Tenga cuidado con las comparaciones del segundo tipo.
Comparar valores del conjunto
'val1,val2'
retorna distintos resultados que comparar valores de
'val2,val1'.
Debe especificar los valores en el mismo orden en que se listan
en la definición de la columna.
Si desea determinar todos los valores posibles para una columna
SET, use SHOW COLUMNS FROM
tbl_name LIKE
set_col y parsee la
definición de SET en la segunda columna de
la salida.