MySQL : Grand VARCHAR vs. TEXT ?

Question

J'ai une table de messages dans MySQL qui enregistre des messages entre utilisateurs. En dehors des ids et des types de messages typiques (tous des types entiers), j'ai besoin de sauvegarder le texte du message réel en tant que VARCHAR ou TEXT. Je fixe une limite côté front-end de 3000 caractères, ce qui signifie que les messages ne seront jamais insérés dans la base de données avec une longueur supérieure à cela.

Y a-t-il une justification pour choisir entre VARCHAR(3000) ou TEXT ? Il y a quelque chose à propos d'écrire simplement VARCHAR(3000) qui semble quelque peu contre-intuitif. J'ai parcouru d'autres publications similaires sur Stack Overflow mais ce serait bien d'obtenir des points de vue spécifiques à ce type de stockage de messages courant.

Answer 1

• TEXTE et BLOB peuvent être stockés en dehors de la table, la table contenant simplement un pointeur vers l'emplacement du stockage réel. L'endroit où il est stocké dépend de nombreuses choses comme la taille des données, la taille des colonnes, le format de ligne et la version de MySQL.

• VARCHAR est stocké en ligne avec la table. VARCHAR est plus rapide lorsque la taille est raisonnable, le compromis en matière de vitesse dépend de vos données et de votre matériel, vous voudriez faire des tests de performance avec un scénario réel avec vos données.

Answer 2

Pouvez-vous prédire quelle serait la longueur de l'entrée utilisateur?

VARCHAR(X)

Longueur maximale: variable, jusqu'à 65 535 octets (64 Ko)
Exemple: nom d'utilisateur, email, pays, sujet, mot de passe

---

TEXT

Longueur maximale: 65 535 octets (64 Ko)
Exemple: messages, emails, commentaires, texte formaté, html, code, images, liens

---

MEDIUMTEXT

Longueur maximale: 16 777 215 octets (16 Mo)
Exemple: grands documents json, livres de longueur courte à moyenne, chaînes csv

---

LONGTEXT

Longueur maximale: 4,294,967,29 octets (4 Go)
Exemple: manuels, programmes, années de fichiers journaux, harry potter et la coupe de feu, enregistrement de recherches scientifiques

Il y a plus d'informations sur cette question.

Answer 3

Juste pour clarifier la meilleure pratique :

1. Les messages au format texte doivent presque toujours être stockés en tant que TEXT (ils finissent par être arbitrairement longs)

2. Les attributs de type string doivent être stockés en tant que VARCHAR (le nom de l'utilisateur destinataire, le sujet, etc...)

Je comprends que vous avez une limite côté front end, ce qui est génial jusqu'à ce que ça ne le soit pas. *sourire* L'astuce est de considérer la base de données comme distincte des applications qui s'y connectent. Juste parce qu'une application met une limite sur les données, ne signifie pas que les données sont intrinsèquement limitées.

Qu'est-ce qui force les messages eux-mêmes à ne jamais dépasser 3000 caractères ? S'il s'agit simplement d'une contrainte d'application arbitraire (par exemple, pour une zone de texte ou quelque chose du genre), utilisez un champ TEXT au niveau des données.

Answer 4

Réponse courte : Aucune différence pratique, de performance, ou de stockage.

Réponse longue :

Essentiellement, il n'y a pas de différence (dans MySQL) entre VARCHAR(3000) (ou toute autre limite importante) et TEXT. Le premier tronquera à 3000 caractères ; le second tronquera à 65535 octets. (Je fais une distinction entre les octets et les caractères parce qu'un caractère peut prendre plusieurs octets.)

Pour des limites plus petites en VARCHAR, il y a quelques avantages par rapport à TEXT.

• "plus petit" signifie 191, 255, 512, 767, ou 3072, etc, selon la version, le contexte, et le JEU DE CARACTÈRES.
• Les INDEXes sont limités en fonction de la taille d'une colonne pouvant être indexée. (767 or 3072 octets ; cela dépend de la version et des paramètres)
• Les tables intermédiaires créées par des SELECTs complexes sont gérées de deux manières différentes - MEMORY (plus rapide) ou MyISAM (plus lent). Lorsque des colonnes 'larges' sont impliquées, la méthode la plus lente est automatiquement choisie. (Des changements significatifs sont prévus dans la version 8.0 ; donc cet élément à puce est sujet à modification.)
• En rapport avec l'élément précédent, tous les datatypes TEXT (par opposition à VARCHAR) passent directement à MyISAM. Autrement dit, TINYTEXT est automatiquement moins bon pour les tables temporaires générées que l'équivalent VARCHAR. (Mais cela amène la discussion dans une troisième direction !)
• VARBINARY est similaire à VARCHAR ; BLOB est similaire à TEXT.
• Une table avec plusieurs VARCHARs 'larges' pourrait atteindre une limite de 64 Ko pour l'ensemble de la définition de la table ; passer à TEXT est une solution simple et pratique. (Exemple : (42000) Row size too large, from an Oracle dump to a MySQL dump)

Réponse aux autres réponses

La question initiale portait sur une chose (quel datatype utiliser) ; la réponse acceptée concernait autre chose (stockage hors ligne). Cette réponse est maintenant obsolète.

Lorsque ce fil de discussion a été commencé et répondu, il n'y avait que deux "formats de lignes" dans InnoDB. Peu de temps après, deux autres formats (DYNAMIC et COMPRESSED) ont été introduits.

L'emplacement de stockage pour TEXT et VARCHAR() est basé sur la taille, pas sur le nom du datatype. Pour une discussion mise à jour sur le stockage hors ligne/en ligne des grandes colonnes de texte/blob, voir ceci.

Answer 5

Avertissement: Je ne suis pas un expert MySQL ... mais cela est ma compréhension des problèmes.

Je pense que TEXTE est stocké en dehors de la ligne mysql, tandis que je pense que VARCHAR est stocké en tant que partie de la ligne. Il y a une longueur maximale de la ligne pour les lignes mysql .. donc vous pouvez limiter la quantité d'autres données que vous pouvez stocker dans une ligne en utilisant le VARCHAR.

Aussi, en raison du VARCHAR faisant partie de la ligne, je soupçonne que les requêtes regardant ce champ seront légèrement plus rapides que celles utilisant un morceau de TEXTE.