tables - MySQL: ¿VARCHAR grande vs. TEXTO?

show table values mysql (6)

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

¿Hay una razón para ir con VARCHAR (3000) o TEXTO? Hay algo acerca de solo escribir VARCHAR (3000) que se siente contraintuitivo. He estado en otras publicaciones similares sobre Desbordamiento de pila, pero sería bueno obtener vistas específicas para este tipo de almacenamiento de mensajes comunes.

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

VARCHAR (X)
Caso: nombre de usuario, correo electrónico, país, asunto, contraseña
TEXTO
Caso: mensajes, correos electrónicos, comentarios, texto con formato, html, código, imágenes, enlaces
MEDIUMTEXTO
Estuche: cuerpos grandes de json, libros de longitud corta a mediana, cuerdas csv
TEXTO LARGO
Caso: libros de texto, programas, años de archivos de logs, harry potter y la copa de fuego, registro de investigación científica

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 VARCHAR se almacena como parte de la fila. Hay una longitud máxima de fila para las filas de mysql ... así que puede limitar la cantidad de datos que puede almacenar en una fila usando VARCHAR.

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

Las respuestas anteriores no insisten lo suficiente en el problema principal: incluso en consultas muy simples como

(SELECT t2.* FROM t1, t2 WHERE t2.id = t1.id ORDER BY t1.id)

puede requerirse una tabla temporal, y si un campo VARCHAR está involucrado, se convierte en un campo CHAR en la tabla temporal. Por lo tanto, si tiene en su tabla 500 000 líneas con un campo VARCHAR(65000) , solo esta columna utilizará 6.5 * 5 * 10 ^ 9 bytes. Estas 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 vs VARCHAR en el motor de almacenamiento MyISAM "estándar" (?). Puede ser diferente en otros, por ejemplo, InnoDB.)

Solo para aclarar las mejores prácticas:

Los mensajes en formato de texto casi siempre deben almacenarse como TEXTO (terminan siendo arbitrariamente largos)
Los atributos de cadena deben almacenarse como VARCHAR (el nombre de usuario de destino, el asunto, etc.).

Entiendo que tienes un límite frontal, lo cual es genial hasta que no lo sea. * grin * El truco es pensar que la base de datos está separada de las aplicaciones que se conectan a ella. Solo porque una aplicación pone un límite a los datos, no significa que los datos estén intrínsecamente limitados.

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

Respuesta corta: No hay práctica, rendimiento o almacenamiento, diferencia.

Respuesta larga:

Básicamente, no hay diferencia (en MySQL) entre VARCHAR(3000) (o cualquier otro límite grande) y TEXT . El primero se truncará en 3000 caracteres ; el último se truncará a 65535 bytes . (Hago una distinción entre bytes y caracteres porque un carácter 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, el contexto y el CHARACTER SET .
INDEXes están limitados en cuanto al tamaño de una columna puede ser indexada. (767 o 3072 bytes ; esto depende de la versión y la configuración)
Las tablas intermedias creadas por SELECTs complejos se manejan de dos maneras diferentes: MEMORY (más rápido) o MyISAM (más lento). Cuando se involucran columnas ''grandes'', la técnica más lenta se selecciona automáticamente. (Cambios significativos en la versión 8.0; por lo tanto, este artículo con viñeta está sujeto a cambios).
En relación con el elemento anterior, todos los tipos de datos TEXT (a diferencia de VARCHAR ) saltan 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 hizo una cosa (qué tipo de datos usar); La respuesta aceptada responde a otra cosa (almacenamiento fuera de registro). Esa respuesta ya no está actualizada.

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

La ubicación de almacenamiento para TEXT y VARCHAR() se basa en el tamaño , no en el nombre del tipo de datos . Para una discusión actualizada sobre el almacenamiento en / fuera de registro de columnas grandes de texto / blob, vea this .

TEXT y BLOB se almacenan fuera de la tabla y la tabla solo tiene 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 lo que sería más rápido depende de sus datos y su hardware, usted querría comparar un escenario del mundo real con sus datos.

Actualización Si VARCHAR o TEXT se almacenan en línea, o fuera de registro depende del tamaño de los datos, el tamaño de las columnas, el formato de fila y la versión de MySQL. No depende de "texto" vs "varchar".