Pregunta MySQL: ¿VARCHAR grande frente a TEXTO?


Tengo una tabla de mensajes en MySQL que registra los mensajes entre los usuarios. Además de los típicos ids y tipos de mensajes (todos los tipos enteros), debo guardar el texto del mensaje real como VARCHAR o TEXTO. Estoy configurando un límite de entrada de 3000 caracteres, lo que significa que los mensajes nunca se insertarán en la base de datos más tiempo que este.

¿Hay alguna razón para ir con VARCHAR (3000) o TEXT? Hay algo acerca de simplemente escribir VARCHAR (3000) que se siente algo contrario a la intuición. He pasado por otras publicaciones similares en Stack Overflow, pero sería bueno obtener vistas específicas para este tipo de almacenamiento de mensajes comunes.


758
2018-01-07 20:40


origen


Respuestas:


  • TEXT y BLOB se almacena fuera de la mesa con la tabla solo con un puntero a la ubicación del almacenamiento real.

  • VARCHAR se almacena en línea con la tabla. VARCHAR es más rápido cuando el tamaño es razonable, la compensación de la cual sería más rápida depende de sus datos y su hardware, le gustaría comparar un escenario real con sus datos.

Actualizar Si VARCHAR o TEXT se almacena en línea o fuera de registro depende del tamaño de los datos, el tamaño de las columnas, el formato de la fila y la versión de MySQL. Lo hace no depende de "texto" frente a "varchar".


759
2018-01-07 20:45



¿Puedes predecir cuánto tiempo será la entrada del usuario?

VARCHAR (X)

Caso: nombre de usuario, correo electrónico, país, sujeto, contraseña


TEXTO

Caso: mensajes, correos electrónicos, comentarios, texto formateado, html, código, imágenes, enlaces


MEDIUMTEXT

Caso: cuerpos json grandes, libros cortos a medianos, cuerdas csv


TEXTO LARGO

Caso: libros de texto, programas, años de archivos de registro, harry potter y el cáliz de fuego, registro de investigación científica


415
2017-11-01 17:56



Solo para aclarar la mejor práctica:

  1. Los mensajes de formato de texto casi siempre deben almacenarse como TEXTO (terminan siendo arbitrariamente largos)

  2. Los atributos de cadena deben almacenarse como VARCHAR (el nombre de usuario de destino, el sujeto, etc.).

Entiendo que tienes un límite de entrada, lo cual es genial hasta que no lo sea. * sonrisa * El truco es pensar en el DB como algo separado de las aplicaciones que se conectan a él. El hecho de que una aplicación ponga un límite a los datos no significa que los datos sean intrínsecamente limitados.

¿Qué tienen los mensajes en sí mismos que los fuerza a no tener más de 3000 caracteres? Si se trata de una restricción de aplicación arbitraria (por ejemplo, para un cuadro de texto o algo así), use un TEXT campo en la capa de datos.


210
2018-01-07 21:53



Descargo de responsabilidad: no soy un experto en MySQL ... pero esta es mi comprensión de los problemas.

Creo que TEXT se almacena fuera de la fila mysql, mientras que creo que VARCHAR se almacena como parte de la fila. Hay una longitud de fila máxima para las filas de MySQL ... por lo que puede limitar la cantidad de datos que puede almacenar en una fila utilizando VARCHAR.

También debido a que VARCHAR forma parte de la fila, sospecho que las consultas que miran ese campo serán ligeramente más rápidas que las que usan un fragmento de TEXTO.


31
2018-01-07 20:47



Respuesta corta:  Sin diferencia práctica, de rendimiento o de almacenamiento.

Respuesta larga:

No hay esencialmente ninguna diferencia (en MySQL) entre VARCHAR(3000) (o cualquier otro límite grande) y TEXT. El primero se truncará a 3000 caracteres; este último truncará a 65535 bytes. (Hago una distinción entre bytes y caracteres porque un personaje puede tomar múltiples bytes.)

Para límites más pequeños en VARCHAR, hay algunas ventajas sobre TEXT.

  • "más pequeño" significa 191, 255, 512, 767 o 3072, etc., según la versión, contexto y CHARACTER SET.
  • INDEXes están limitados en qué tan grande se puede indexar una columna. (767 o 3072 bytes; esta es la versión y la configuración depende)
  • Tablas intermedias creadas por complejos SELECTs se manejan de dos maneras diferentes: MEMORIA (más rápida) o MyISAM (más lenta). Cuando se trata de columnas 'grandes', la técnica más lenta se selecciona automáticamente. (Cambios significativos en la versión 8.0, por lo que este elemento de la viñeta está sujeto a cambios).
  • Relacionado con el artículo anterior, todo TEXT tipos de datos (a diferencia de VARCHAR) salta directamente a MyISAM. Es decir, TINYTEXT es automáticamente peor para las tablas temporales generadas que el equivalente VARCHAR. (¡Pero esto lleva la discusión en una tercera dirección!)
  • VARBINARY es como VARCHAR; BLOB es como TEXT.

Refutación a otras respuestas

La pregunta original preguntaba una cosa (qué tipo de datos usar); la respuesta aceptada respondió algo más (almacenamiento fuera de registro). Esa respuesta ahora está desactualizada.

Cuando se inició este hilo y respondió, solo había dos "formatos de fila" en InnoDB. Poco después, dos formatos más (DYNAMIC y COMPRESSES) fueron presentados.

La ubicación de almacenamiento para TEXT y VARCHAR() está basado en tamaño, no en nombre del tipo de datos. Por un actualizado discusión sobre el almacenamiento on / off-record de columnas grandes de texto / blob, ver esta .


3
2018-06-25 16:05



Las respuestas anteriores no insisten lo suficiente en el problema principal: incluso en consultas muy simples (SELECCIONE t2. * DESDE t1, t2 DONDE t2.id = t1.id ORDER BY t1.id) se puede requerir una tabla temporal, y si un campo VARCHAR está involucrado, se convierte en un campo CHAR en la tabla temporal. Entonces, si tiene en su tabla 500,000 líneas con un campo VARCHAR (65000), esta columna solo usará 6.5 * 5 * 10 ^ 9 byte. Tales tablas temporales no se pueden manejar en la memoria y se escriben en el disco. Se puede esperar que el impacto sea catastrófico.

Fuente (con métricas): https://nicj.net/mysql-text-vs-varchar-performance/ (Esto se refiere al manejo de TEXT frente a VARCHAR en el motor de almacenamiento MyISAM "estándar" (?). Puede ser diferente en otros, por ejemplo, InnoDB).


1
2018-06-30 21:43