781 votes

Différence entre le 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 le 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 de 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 inclus dans la norme SQL, plusieurs autres systèmes de gestion de base de données SQL le proposent é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 saillants :

Pour résumer :

  • char(n) – prend trop d'espace lorsqu'on traite des valeurs plus courtes que n (les complète jusqu'à n), et peut causer des erreurs subtiles en ajoutant des espaces de remplissage, en 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 gagnant – surpasse les types de données (n) car il ne possède 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 des sélections pour les 4 types de données sont similaires. Il examine également en détail les moyens alternatifs pour contraindre la longueur lorsque nécessaire. Les contraintes basées sur des fonctions ou des domaines offrent l'avantage d'une augmentation instantanée de la contrainte de longueur, et sur la base que la diminution d'une contrainte de longueur de chaîne est rare, depesz conclut que l'un d'eux est généralement le meilleur choix pour une limite de longueur.

60 votes

@axiopisty C'est un excellent article. Vous pourriez simplement dire, "Pourriez-vous intégrer quelques extraits au cas où l'article disparaîtrait?" J'ai essayé de résumer brièvement le contenu/les conclusions de l'article. J'espère que cela suffira à apaiser vos inquiétudes.

35 votes

@axiopisty, en termes stricts, 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 contenant un lien.

32 votes

Une chose à garder à l'esprit avec une chaîne illimitée est qu'elle ouvre la possibilité de mauvais usage. 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 donnent le conseil de "mettre une limite sur tout".

131voto

George Points 945

Comme le souligne "Types de caractères" dans la documentation, 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 donnée, et l'espace supplémentaire et le temps nécessaires en cas de rembourrage pour char(n).

Cependant, lorsque vous avez seulement 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 - ils font partie du nom du type). Vous avez un accès plus rapide au champ, et il n'y a pas de surcoût 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 avec 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 du mot-clé char.

38 votes

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

1 votes

Datatype "char" n'est pas char?? Est-ce valide de nos jours avec PostgreSQL 11+? ... Oui: "Le type "char" (notez les guillemets) est différent de char(1) en ce qu'il utilise seulement un octet de stockage. Il est utilisé en interne dans les catalogues système en tant que 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 "SQL pur" (sans aucun script externe)

  1. utilisez n'importe quel générateur de chaînes avec UTF8

  2. benchmarks principaux:

2.1. INSERT

2.2. SELECT comparaison et décompte


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 des tests spécifiques (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 d'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.

MISE À JOUR ENCORE 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 moyenne, sur de nombreuses machines et de nombreux tests : tous identiques
(statistiquement inférieur à l'écart-type).

Recommandation

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

  • exprimez des limites (avec les mêmes performances que varchar!) avec une clause CHECK dans le CREATE TABLE
    par exemple CHECK(char_length(x)<=10).
    Avec une perte de performance négligeable dans INSERT/UPDATE vous pouvez également contrôler les plages et la structure de la chaîne
    par exemple CHECK(char_length(x)>5 AND char_length(x)<=20 AND x LIKE 'Hello%')

0 votes

Alors cela ne pose aucun problème que j'aie défini toutes mes colonnes en 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

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

49voto

Sur le manuel PostgreSQL

Il n'y a aucune différence de performance entre ces trois types, à part un espace de stockage accru lors de l'utilisation du type avec des espaces vides, 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 tel avantage 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 situations, il est préférable d'utiliser text ou character varying à la place.

Je prends 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 signaler 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 avez des index sur ces colonnes, il est très probable 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 l'éviter.

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, il renverra une erreur lorsqu'un enregistrement avec une taille compressée supérieure à 2712 est 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 elle sera compressée bien en dessous de 2712, mais vous ne pourrez peut-être pas 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 supérieur à 2712, vous êtes à l'abri de ces erreurs.

0 votes

Plus tard, les erreurs de postgres lors de la 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 a un lien vers PostgreSQL Wiki: 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 prennent pas en compte la mise en index. 2712 octets est environ la limite maximale des arbres B, c'est un détail d'implémentation donc vous ne le trouverez pas dans les documents. Cependant, vous pouvez facilement le tester vous-même ou tout simplement le rechercher sur Google en tapant "taille de ligne d'index PostgreSQL dépasse le maximum 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