MySQL: Large VARCHAR vs TEXT?

J’ai un tableau de messages dans MySQL qui enregistre les messages entre les utilisateurs. Mis à part les identifiants et les types de message typiques (tous les types entiers), je dois enregistrer le texte du message en tant que VARCHAR ou TEXT. Je définis une limite frontale de 3000 caractères, ce qui signifie que les messages ne seront jamais insérés dans la firebase database plus longtemps.

Existe-t-il une justification pour utiliser VARCHAR (3000) ou TEXT? Il y a quelque chose à propos de l’écriture de VARCHAR (3000) qui semble quelque peu contre-intuitive. Je suis passé par d’autres publications similaires sur Stack Overflow, mais ce serait bien d’avoir des vues spécifiques à ce type de stockage de messages commun.

  • TEXT et BLOB sont stockés sur la table, la table ayant simplement un pointeur sur l’emplacement du stockage réel.

  • VARCHAR est stocké en ligne avec la table. VARCHAR est plus rapide lorsque la taille est raisonnable, le compromis le plus rapide dépendant de vos données et de votre matériel, vous souhaitez comparer un scénario réel avec vos données.

Mettre à jour Que VARCHAR ou TEXT soit stocké en ligne ou hors enregistrement dépend de la taille des données, de la taille des colonnes, du format de ligne et de la version de MySQL. Cela ne dépend pas de “text” vs “varchar”.

Pouvez-vous prédire combien de temps l’utilisateur entrerait?

VARCHAR (X)

Cas: nom d’utilisateur, email, pays, sujet, mot de passe


TEXTE

Cas: messages, courriels, commentaires, texte formaté, html, code, images, liens


MEDIUMTEXT

Cas: gros corps json, livres courts à moyens, cordes csv


LONGTEXT

Cas: manuels scolaires, programmes, années de fichiers journaux, Harry Potter et la coupe de feu, la recherche scientifique

Juste pour clarifier la meilleure pratique:

  1. Les messages au format texte doivent presque toujours être stockés sous forme de texte (ils sont arbitrairement longs)

  2. Les atsortingbuts de chaîne doivent être stockés en tant que VARCHAR (le nom d’utilisateur de destination, le sujet, etc.).

Je comprends que vous avez une limite frontale, ce qui est génial jusqu’à ce que ce ne soit pas le cas. * Grin * L’astuce consiste à considérer la firebase database comme distincte des applications qui s’y connectent. Le fait qu’une application limite les données ne signifie pas que les données sont insortingnsèquement limitées.

Qu’en est-il des messages eux-mêmes qui les obligent à ne jamais être plus de 3000 personnages? Si c’est juste une contrainte d’application arbitraire (par exemple, pour une zone de texte ou quelque chose), utilisez un champ de texte au niveau de la couche de données.

Disclaimer: Je ne suis pas un expert MySQL … mais c’est ma compréhension des problèmes.

Je pense que TEXT est stocké en dehors de la ligne mysql, alors que je pense que VARCHAR est stocké dans la ligne. Il y a une longueur de ligne maximale pour les lignes mysql .. vous pouvez donc limiter le volume des autres données que vous pouvez stocker en utilisant VARCHAR.

En outre, VARCHAR faisant partie de la ligne, je suppose que les requêtes concernant ce champ seront légèrement plus rapides que celles utilisant un bloc TEXT.

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

Longue réponse:

Il n’y a essentiellement aucune différence (en MySQL) entre VARCHAR(3000) (ou toute autre grande limite) et TEXT . Le premier tronquera à 3000 caractères ; ce dernier sera tronqué à 65535 octets . (Je fais une distinction entre octets et caractères car un caractère peut prendre plusieurs octets.)

Pour les limites plus petites dans VARCHAR , il existe des avantages par rapport à TEXT .

  • “plus petit” signifie 191, 255, 512, 767 ou 3072, etc., en fonction de la version, du contexte et de CHARACTER SET .
  • INDEXes sont limités par la taille à laquelle une colonne peut être indexée. (767 ou 3072 octets , dépend de la version et des parameters)
  • Les tables intermédiaires créées par des SELECTs complexes sont traitées de deux manières différentes: MEMORY (plus rapide) ou MyISAM (plus lent). Lorsque de “grandes” colonnes sont impliquées, la technique la plus lente est automatiquement sélectionnée. (Modifications importantes apscopes dans la version 8.0; cet élément de la puce peut donc être modifié.)
  • En relation avec l’élément précédent, tous les types de données TEXT (par opposition à VARCHAR ) VARCHAR directement à MyISAM. C’est-à-dire que TINYTEXT est automatiquement pire pour les tables temporaires générées que VARCHAR équivalent. (Mais cela prend la discussion dans une troisième direction!)
  • VARBINARY est comme VARCHAR ; BLOB est comme TEXT .

Réfutation à d’autres réponses

La question initiale demandait une chose (quel type de données utiliser); la réponse acceptée a répondu à autre chose (stockage hors enregistrement). Cette réponse est maintenant dépassée.

Lorsque ce thread a été démarré et répondu, il n’y avait que deux “formats de lignes” dans InnoDB. Peu après, deux autres formats ( DYNAMIC et COMPRESSES ) ont été introduits.

L’emplacement de stockage pour TEXT et VARCHAR() est basé sur la taille et non sur le nom du type de données . Pour une discussion à jour sur le stockage on / off des grandes colonnes text / blob, voir ceci .

Les réponses précédentes n’insistent pas assez sur le problème principal: même dans des requêtes très simples comme

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

une table temporaire peut être requirejse et si un champ VARCHAR est impliqué, il est converti en un champ CHAR dans la table temporaire. Donc, si vous avez dans votre table 500 000 lignes avec un champ VARCHAR(65000) , cette colonne seule utilisera 6,5 * 5 * 10 ^ 9 octets. De telles tables temporaires ne peuvent pas être manipulées en mémoire et sont écrites sur le disque. L’impact peut être catastrophique.

Source (avec mésortingques): https://nicj.net/mysql-text-vs-varchar-performance/ (Ceci fait référence à la gestion de TEXT vs VARCHAR dans le moteur de stockage MyISAM “standard” (?). Il peut être différent dans d’autres, par exemple, InnoDB.)