781 votes

Différence entre texte et varchar (caractère variable)

Quelle est la différence entre le type de données text et les types de données character varying (varchar) ?

selon la documentation

Si character varying est utilisé sans spécificateur de longueur, le type accepte des chaînes de n'importe quelle taille. Ce dernier est une extension PostgreSQL.

et

De plus, PostgreSQL fournit le type text, qui stocke des chaînes de n'importe quelle longueur. Bien que le type text ne soit pas dans la norme SQL, plusieurs autres systèmes de gestion de base de données SQL l'ont également.

Alors quelle est la différence ?

925voto

Frank Heikens Points 29270

Il n'y a pas de différence, sous le capot c'est tout varlena (tableau de longueur variable).

Vérifiez cet article de Depesz: http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/

Quelques points importants:

Pour résumer:

  • char(n) - prend trop d'espace lorsqu'il s'agit de valeurs plus courtes que n (les remplit jusqu'à n), et peut entraîner des erreurs subtiles en ajoutant des espaces en fin de chaîne, plus il est problématique de changer la limite
  • varchar(n) - il est problématique de changer la limite dans un environnement en direct (nécessite un verrou exclusif lors de l'altération de la table)
  • varchar - tout comme text
  • text - pour moi le meilleur - surpasse les types de données (n) car il ne présente pas leurs problèmes, et surpasse varchar - car il a un nom distinct

L'article effectue des tests détaillés pour montrer que les performances des insertions et sélections pour les 4 types de données sont similaires. Il examine également de manière détaillée les façons alternatives de contraindre la longueur lorsque nécessaire. Les contraintes basées sur des fonctions ou des domaines offrent l'avantage d'augmenter instantanément la contrainte de longueur, et sur la base du fait qu'il est rare de réduire une contrainte de longueur de chaîne, Depesz conclut qu'une des deux options est généralement le meilleur choix pour une limite de longueur.

60 votes

@axiopisty C'est un excellent article. Tu pourrais simplement dire, "Pourrais-tu inclure quelques extraits au cas où l'article disparaîtrait ?" J'ai essayé de résumer brièvement le contenu/conclusions de l'article. J'espère que cela suffira à apaiser tes inquiétudes.

35 votes

@axiopisty, à proprement parler, la réponse initiale disait "sous le capot c'est tout varlena", ce qui est certainement une information utile qui distingue cette réponse d'une simple réponse ne contenant qu'un lien.

32 votes

Une chose à garder à l'esprit avec une chaîne de caractères sans limite est qu'elle ouvre la voie à des abus potentiels. Si vous permettez à un utilisateur d'avoir un nom de famille de n'importe quelle taille, vous pourriez avoir quelqu'un stockant de GRANDES quantités d'informations dans votre champ de nom de famille. Dans un article sur le développement de reddit, ils conseillent de "mettre une limite sur tout".

131voto

George Points 945

Comme le souligne la documentation dans "Types de caractères", les types varchar(n), char(n) et text sont tous stockés de la même manière. La seule différence réside dans les cycles supplémentaires nécessaires pour vérifier la longueur, si elle est précisée, et l'espace et le temps supplémentaires requis si un rembourrage est nécessaire pour char(n).

Cependant, lorsque vous avez juste besoin de stocker un seul caractère, il y a un léger avantage de performance à utiliser le type spécial "char" (gardez les guillemets doubles - ils font partie du nom du type). Vous accédez plus rapidement au champ et il n'y a pas de surcharge pour stocker la longueur.

Je viens de créer une table de 1 000 000 de "char" aléatoires choisis dans l'alphabet en minuscules. Une requête pour obtenir une distribution de fréquence (sélectionner count(*), champ ... group by champ) prend environ 650 millisecondes, contre environ 760 sur les mêmes données en utilisant un champ text.

22 votes

Techniquement les guillemets ne font pas partie du nom du type. ils sont nécessaires pour le différencier de du mot-clé char.

38 votes

Techniquement, tu as raison @Jasen... Ce qui, bien sûr, est le meilleur genre de correct

1 votes

Le type de données "char" n'est pas char ?? Est-ce valide de nos jours pour PostgreSQL 11+ ? ... Oui: "Le type "char" (notez les guillemets) est différent de char(1) en ce qu'il n'utilise qu'un octet de stockage. Il est utilisé en interne dans les catalogues système comme un type d'énumération simpliste.", guide/datatype-character.

91voto

Peter Krauss Points 1888

(cette réponse est un Wiki, vous pouvez modifier - veuillez corriger et améliorer!)

MISE À JOUR DES BENCHMARKS POUR 2016 (pg9.5+)

Et en utilisant des benchmarks "pure SQL" (sans aucun script externe)

  1. utilisez n'importe quel string_generator avec UTF8

  2. benchmarks principaux:

2.1. INSÉRER

2.2. SÉLECTION comparer et compter


CREATE FUNCTION string_generator(int DEFAULT 20,int DEFAULT 10) RETURNS text AS $f$
  SELECT array_to_string( array_agg(
    substring(md5(random()::text),1,$1)||chr( 9824 + (random()*10)::int )
  ), ' ' ) as s
  FROM generate_series(1, $2) i(x);
$f$ LANGUAGE SQL IMMUTABLE;

Préparez un test spécifique (exemples)

DROP TABLE IF EXISTS test;
-- CREATE TABLE test ( f varchar(500));
-- CREATE TABLE test ( f text); 
CREATE TABLE test ( f text  CHECK(char_length(f)<=500) );

Effectuez un test de base:

INSERT INTO test  
   SELECT string_generator(20+(random()*(i%11))::int)
   FROM generate_series(1, 99000) t(i);

Et autres tests,

CREATE INDEX q on test (f);

SELECT count(*) FROM (
  SELECT substring(f,1,1) || f FROM test WHERE f<'a0' ORDER BY 1 LIMIT 80000
) t;

... Et utilisez EXPLAIN ANALYZE.

À NOUVEAU MIS À JOUR EN 2018 (pg10)

petite édition pour ajouter les résultats de 2018 et renforcer les recommandations.


Résultats en 2016 et 2018

Mes résultats, après la moyenne, sur de nombreuses machines et de nombreux tests: tous les mêmes
(statistiquement inférieur à l'écart type).

Recommandation

  • Utilisez le type de données text,
    évitez les anciens varchar(x) car parfois ce n'est pas un standard, par exemple dans les clauses CREATE FUNCTION varchar(x)varchar(y).

  • exprimez des limites (avec les mêmes performances de varchar!) avec la clause CHECK dans le CREATE TABLE
    par exemple CHECK(char_length(x)<=10).
    Avec une perte de performance négligeable lors de l'INSERTION/MISE À JOUR, vous pouvez également contrôler les plages et la structure des chaînes de caractères
    par exemple CHECK(char_length(x)>5 AND char_length(x)<=20 AND x LIKE 'Hello%')

0 votes

Est-ce donc important que j'aie fait toutes mes colonnes varchar au lieu de text ? Je n'ai pas spécifié la longueur même si certaines ne contiennent que 4 à 5 caractères et certainement pas 255.

2 votes

@tranchée oui, cela n'a pas d'importance

2 votes

Cool, je l'ai refait pour être sûr et j'ai tout mis en texte de toute façon. Cela a bien fonctionné et c'était super facile d'ajouter des millions d'enregistrements historiques rapidement de toute façon.

49voto

Sur le manuel PostgreSQL

Il n'y a pas de différence de performance entre ces trois types, à part un espace de stockage accru lors de l'utilisation du type avec padding blanc, et quelques cycles CPU supplémentaires pour vérifier la longueur lors du stockage dans une colonne contrainte en longueur. Alors que character(n) présente des avantages de performance dans d'autres systèmes de base de données, il n'y a pas d'avantage similaire dans PostgreSQL ; en fait character(n) est généralement le plus lent des trois en raison de ses coûts de stockage supplémentaires. Dans la plupart des cas, il faudrait plutôt utiliser text ou character varying.

J'utilise généralement text

Références: http://www.postgresql.org/docs/current/static/datatype-character.html

31voto

sotn Points 1460

À mon avis, varchar(n) a ses propres avantages. Oui, ils utilisent tous le même type sous-jacent et tout ça. Mais, il convient de noter que les index dans PostgreSQL ont une limite de taille de 2712 octets par ligne.

TL;DR : Si vous utilisez le type text sans contrainte et que vous avez des index sur ces colonnes, il est tout à fait possible que vous atteigniez cette limite pour certaines de vos colonnes et obteniez une erreur lorsque vous essayez d'insérer des données, mais en utilisant varchar(n), vous pouvez éviter cela.

Quelques détails supplémentaires : Le problème ici est que PostgreSQL ne génère aucune exception lors de la création d'index pour le type text ou varchar(n)n est supérieur à 2712. Cependant, une erreur sera générée lorsqu'un enregistrement avec une taille compressée supérieure à 2712 sera tenté d'être inséré. Cela signifie que vous pouvez insérer 100 000 caractères de chaîne composée de caractères répétitifs facilement car cela sera compressé bien en dessous de 2712, mais vous pourriez ne pas être en mesure d'insérer une chaîne de 4000 caractères car la taille compressée est supérieure à 2712 octets. En utilisant varchar(n)n n'est pas trop grand par rapport à 2712, vous évitez ces erreurs.

0 votes

Ensuite, les erreurs de postgres lors de tentatives de création d'indexation pour le texte ne fonctionnent que pour varchar (version sans le (n)). Testé uniquement avec postgres intégré cependant.

2 votes

Référence à : stackoverflow.com/questions/39965834/… qui contient un lien vers le Wiki PostgreSQL : wiki.postgresql.org/wiki/… a une taille maximale de ligne de 400 Go, à partir de cela, il semble que la limite de 2712 octets par ligne indiquée est incorrecte. Taille maximale pour une base de données ? illimitée (des bases de données de 32 To existent). Taille maximale pour une table ? 32 To. Taille maximale pour une ligne ? 400 Go. Taille maximale pour un champ ? 1 Go. Nombre maximal de lignes dans une table ? illimité.

0 votes

@BillWorthington Les chiffres que vous avez publiés ne tiennent pas compte de l'incorporation des index. 2712 bytes correspond environ aux limites maximales d'un btree, c'est un détail de mise en œuvre donc vous ne le trouverez pas dans les documents. Cependant, vous pouvez facilement le tester vous-même ou simplement le rechercher sur Google en cherchant "taille de ligne d'index de postgresql dépasse le maximum de 2712 pour l'index" par exemple.

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

X