Quelle taille choisir pour une colonne (n)varchar?
Dans un discussion légèrement houleuse sur le TDWTF<!-Une question s'est posée sur la taille des colonnes de varchar dans un DB.
par exemple, prenez un champ qui contient le nom d'une personne (juste Nom, pas de nom de famille). Il est assez facile de voir qu'il ne sera pas très long. La plupart des gens ont des noms avec moins de 10 caractères, et peu sont ceux de plus de 20. Si vous faisiez votre colonne, disons, varchar (50), elle contiendrait definately tous les noms que vous auriez jamais rencontrer.
Cependant pour la plupart des SGBD, il n'y a aucune différence de taille ou de vitesse que vous fassiez une varchar(50) ou une varchar(255).
Alors, pourquoi les gens essaient de rendre leurs colonnes le plus petit possible? Je comprends que dans certains cas vous pourriez en effet vouloir placer une limite sur la longueur de la chaîne, mais la plupart du temps ce n'est pas le cas. Et une marge plus grande ne sera bénéfique que s'il y a un cas rare d'une personne avec une très longue nom.
Ajouté: les gens veulent des références à l'énoncé "aucune différence de taille ou de vitesse". OK. Ici, ils sont:
Pour MSSQL: http://msdn.microsoft.com/en-us/library/ms176089.aspx
la taille de stockage est la longueur réelle des données saisies + 2 octets.
Pour MySQL: http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html
L + 1 octets si les valeurs de colonne exiger 0 – 255 octets, L + 2 octets si les valeurs peuvent nécessiter plus de 255 octets
Je ne trouve pas de documentation pour Oracle et je n'ai pas travaillé avec d'autres SGBD. Mais je n'ai aucune raison de croire qu'il est différent.
8 réponses
je ne peux parler que pour Oracle. Un VARCHAR2 (50) et un VARCHAR2(255) prennent exactement la même quantité d'espace et effectuent de manière identique, si vous entrez la valeur 'SMITH'.
cependant, la raison pour laquelle ce n'est généralement pas une bonne idée de faire le tour en déclarant toutes vos colonnes textuelles comme VARCHAR2(4000) est que la longueur des colonnes est, effectivement, une autre contrainte. Les contraintes sont la mise en place de bases de données sur les règles d'affaires, donc elles sont certainement quelque chose qui devrait être défini sur le base de données côté des choses.
Comme exemple. Vous définissez une contrainte de contrôle sur une colonne de sorte que les valeurs qu'elle peut accepter sont seulement 'Y' et 'N'. Qui enregistre votre demande d'avoir à traiter avec un 'y' et 'n' ou même '1' et '0'. La contrainte de contrôle garantit que vos données sont conformes aux normes attendues. Votre code d'application peut alors émettre des hypothèses valables sur la nature des données qu'il doit traiter.
la définition de longueur de colonne est dans le même bateau. Vous déclarez quelque chose à être un VARCHAR2(10) parce que vous ne voulez pas qu'il accepte une entrée de 'ABC123ZYX456' (pour quelque raison que ce soit!)
EN AUSTRALIE, je définis les colonnes D'état comme un varchar2 (3) parce que je ne veux pas que les gens tapent dans "Nouvelle-Galles du Sud" ou "Australie du Sud". La définition de la colonne les oblige à peu près à entrer " NSW " et "SA". En ce sens, une VARCHAR2 (3) est presque autant une contrainte de contrôle qu'un contrôle proprement dit ("NSW", "SA", "VIC", etc.) contrainte.
en bref, les longueurs de colonne appropriées sont un moyen d'encoder les règles d'affaires. Ils sont une autre forme de contrainte. Ils apportent tous les avantages des contraintes (et souffrent de beaucoup des mêmes inconvénients). Et ils assurent, dans une faible mesure, un certain degré de "propreté des données", que des contraintes "appropriées" peuvent également aider.
Je ne crois pas à l'argument, non plus, qu'il est préférable de coller ce genre de choses dans l'application client parce qu'il est plus facile de changer là-bas. Vous avez 20 000 personnes utilisant une application, ça fait 20 000 mises à jour. Vous avez une base de données, c'est une mise à jour. L'argument 'easier to change the client app', si true, signifierait potentiellement que la base de données est traitée comme un énorme seau de bits avec toute la logique intelligente étant manipulée dans le code client. C'est une grande discussion à avoir, mais puisque tous les RDBMSes vous permettent de définir des contraintes et ainsi de suite dans la base de données elle-même, il est assez clair qu'il y a au moins un cas valable à faire que cette logique fondamentale appartient dans le backend.
j'ai entendu l'optimiseur de requête tenir compte de la longueur des varchars, bien que je ne trouve pas de référence.
définir une longueur varchaire aide à communiquer l'intention. Plus les contraintes sont définies, plus les données sont fiables.
Alors, pourquoi les gens essaient de rendre leurs colonnes le plus petit possible? Je ne crois pas à les rendre aussi petits que possible, mais à les dimensionner de façon appropriée. Quelques raisons pour rendre (n)varchars plus petit plutôt que plus grand:
1) avec un champ plus grand, tous les clients qui utilisent la base de données doivent être en mesure de gérer la taille complète. Par exemple, prenez un système qui contient une adresse aux États-Unis avec 255 caractères par champ: (similaire à TDWTF que vous référencez, I croire.)
- Prénom
- Nom De Famille
- Adresse Ligne 1
- Adresse Ligne 2
- Ville
- État
- code postal
maintenant, vos écrans de saisie de données devront permettre et afficher 255 caractères par champ. Pas difficile, mais peu probable d'avoir l'air agréable avec de plus grands champs D'impression des factures, vous aurez besoin de la logique de rupture de ligne pour gérer les grands champs. Ça dépend de l'outil, pas si dur.
mais je ne voudrais pas le problème de formater l'adresse pour une enveloppe qui pourrait avoir 255 caractères pour chacun de ces champs ou juste un de ces champs. Allez-vous à tronquer si le champ est trop long? Quelqu'un de génial a L'adresse Ligne 1 de "numéro de maison straat numéro ... bla bla bla ... Appartement numéro 111."Et tu retireras le numéro important de l'appartement. Allez-vous l'envelopper? Combien? Et si tu ne pouvais pas le mettre dans la petite boîte de l'espace sur l'enveloppe? Faire une exception et demander à quelqu'un de l'écrire?
2) alors que 10 caractères de données contenues dans un varchar(50) versus varchar(255) n'ont pas d'impact sur la taille ou la vitesse, le fait de laisser 255 caractères permet de prendre plus d'espace. Et si tous les champs sont aussi grands, vous pouvez atteindre des limites de taille dans SQL Server 2000. (Je n'ai pas lu sur 2005 et 2008 pour voir s'ils peuvent gérer des lignes supérieures à une page.) Et avec Oracle vous les plus grandes tailles permet chaîne de rangée pour se produire si quelqu'un utilise tous les caractères disponibles.
3)les index ont des limites de taille plus strictes que les pages feuilles. Vous pouvez exclure les index, en particulier les index composites, si vous créez vos varchars trop grands.
en revanche, j'ai une longue ligne 1 pour mon adresse, et ont été déçus par les sites web qui ne permettent pas la chose entière d'être tapé.
une distinction importante consiste à spécifier une limite arbitrairement grande [par exemple VARCHAR(2000)], et en utilisant un type de données qui ne nécessite pas de limite [par exemple VARCHAR(MAX) ou TEXT].
PostgreSQL base toute sa longueur fixe VARCHARs sur son unlimitted TEXT tapez, et dynamiquement décide valeur comment stocker la valeur, y compris la stocker hors de la page. Le spécificateur de longueur dans ce cas n'est vraiment qu'une contrainte, et son utilisation est en fait déconseillée. (réf)
D'autres SGBD exigent que l'utilisateur sélectionne s'il a besoin de stockage "illimité", hors page, généralement avec un coût associé en commodité et/ou performance.
S'il y a un avantage à utiliser VARCHAR(<n>)VARCHAR(MAX) ou TEXT, il s'ensuit que vous devez sélectionner une valeur pour <n> lors de la conception de vos tables. En supposant qu'il y ait une certaine largeur maximale d'une rangée de tableaux, ou entrée d'index, les contraintes suivantes doivent appliquer:
<n>doit être inférieur ou égal à<max width>- si
<n> = <max width>, le tableau / index ne peut avoir qu'une colonne - en général, le tableau/index ne peut avoir que
<x>colonnes où (en moyenne)<n> = <max width> / <x>
Il est donc le cas où la valeur de <n> agit uniquement comme une contrainte, et le choix de <n> doit faire partie de la conception. (Même s'il n'y a pas de limite dans votre SGBD, il se peut qu'il y ait des raisons de performance pour maintenir la largeur dans une certaine limite.)
Vous pouvez utiliser les règles ci-dessus pour attribuer un maximum valeur <n>, fondée sur l'architecture de votre table (en tenant compte de l'impact des changements futurs). Cependant, il est plus logique de définir le minimum valeur <n>, basé sur le dans chaque colonne. Il est très probable que vous passerez au "nombre rond" le plus proche - par exemple: vous pourrez toujours utiliser VARCHAR(10),VARCHAR(50),VARCHAR(200), ou VARCHAR(1000), celle qui convient le mieux.
la réponse Simple à cela à mon avis est le fait que vous ne pouvez pas utiliser cette colonne comme une clé d'index, si vous avez besoin d'une indexation vous êtes essentiellement forcé d'utiliser fulltext... ceci concerne l'utilisation d'une colonne varchar(max). Dans tous les cas, la mise à jour des colonnes de longueur variable peut être une manœuvre coûteuse car elles ne sont pas faites en place et peuvent/causeront une certaine fragmentation.
Tous avec en ce qui concerne MS Sq-Server.
je vais répondre à votre question par une question: S'il n'y a pas de différence dans le SGBD entre un varchar(50) et un varchar(255), pourquoi le SGBD vous permettrait-il de faire une distinction? Pourquoi un SGBD ne dirait-il pas simplement "utilisez varchar pour un maximum de xxx caractères, et text/clob/etc. pour quelque chose de plus que."Bien sûr, peut-être Microsoft / Oracle / IBM pourrait garder la définition de longueur pour des raisons historiques, mais qu'en est-il DBMS' comme MySQL qui a plusieurs sauvegardes de stockage - pourquoi est-ce que chaque implémentation définissable longueur des colonnes de caractères?
si vous allez imprimer des étiquettes, vous voulez généralement que la chaîne ne soit pas plus de 35 caractères. C'est pourquoi vous voulez un peu de contrôle sur la taille de la Varchar que vous allez utiliser pour l'accepter les lignes qui vont être utilisés pour imprimer les étiquettes.
si vous permettez à la longueur des données d'être plus de 255 et quelqu'un lie aux données par L'intermédiaire de MS Access les données ne peuvent pas être utilisées pour joindre des tables (vient comme un champ de note de service). Si les données sont exportées vers excel, il sera limité à 255 caractères par champ. La compatibilité avec d'autres programmes doit être considérée lors de la création d'ensembles de données.
Le contrôle de la qualité des données consiste à contrôler les données qui entrent dans votre environnement. Que devez-vous stocker qui est plus de 255 caractères? Y les données doivent parfois dépasser 255 caractères, mais elles doivent être très espacées et servir de complément d'information pour un champ qui peut être utilisé à des fins d'analyse