Discussion :

[scamphp]

Bonjour

Je ne vais pas poser la question classique à savoir qu'elle est la différence entre CHAR / VARCHAR car le forum est plein de réponses et d'explications....

Mais ma question va en découler...

Si j'ai bien compris VARCHAR (100) va être plus léger que CHAR (100), pour des données stables mais le problème se pose en cas de modification car VARCHAR ne réserve pas de l'espace ce qui génère en cas de modification une sorte de fragmentation de la table

"Toto" -> Taille 4
modification
"Toto va à la plage" -> Taille 18 -> ce qui ne rentre pas dans 4 donc suppression de la ligne (abandon) et création d'une nouvelle ligne
modification
"Toto va à la plage ce soir" -> Taille 32 -> ce qui ne rentre pas dans 18 donc suppression de la ligne (abandon) et création d'une nouvelle ligne

Belote et rebelote....

Du coup on pourrait mettre un CHAR (100) mais cela serait lourd pour rien...


JE ME POSE DONC LA QUESTION SUIVANTE

Si j'utilise VARCHAR (100)
et que je rentre au premier enregistrement '123456789123456789....123456789123456789' 100 caractères
Puis que j'enregistre
"Toto" -> Taille 4 < 100
modification
"Toto va à la plage" -> Taille 18 < 100
modification
"Toto va à la plage ce soir" -> Taille 32 < 100


Est-ce que j'ai résolu le problème de CHAR / VARCHAR

En gros j'ai un vrai VARCHAR (100) avec réservation de caractères pour éviter la fragmentation de ma table
TOUT en étant plus léger car j'utilise en VARCHAR

Bonne IDEE ou IDEE à la con

[escartefigue]

Bonjour,

C'est une mauvaise idée, car vous cumulez les inconvénients de l'un et l'autre type : non seulement vous stockez la taille maximale inutilement comme si c'était du CHAR, mais aussi l'attribut longueur lié au varchar.
La prochaine mise à jour pour une taille inférieure ajustera l'attribut longueur, et la MàJ suivante, si elle augmente à nouveau la longueur, présentera à nouveau le risque de fragmentation.
Aucun intérêt donc.

Le VARCHAR doit être utilisé dans le cas où la taille varie fortement d'une ligne à l'autre de la table, mais est relativement stable pour une même ligne de cette même table.
Le CHAR doit être utilisé quand la taille totale est réduite (disons grosso-modo en deçà de 15 caractères, le varchar n'a pas ou très peu d'intérêt), ou que les changements de longueur pour un même attribut d'une même ligne sont très fréquents.

[CinePhil]

Si j'ai bien compris VARCHAR (100) va être plus léger que CHAR (100)

VARCHAR(100) ne sera plus "léger" que CHAR(100) que s'il y a des lignes avec moins de 99 caractères.

Pour prendre une échelle plus petite, si vous devez stocker un code sur systématiquement 4 caractères, un CHAR(4) est plus léger qu'un VARCHAR(4). Pour stocker le code postal français, il vaut mieux prendre un CHAR(5). Pour stocker un hash dont le résultat est sur 32 caractères, il vaut mieux utiliser un CHAR(32).

D'une manière générale, et surtout au delà de 4 caractères, si la taille des chaînes à stocker est VARiable, utilisez un VARCHAR. Si la taille est fixe, utilisez un CHAR.

[scamphp]

Bonjour et merci pour les réponses

et exemples précis.

Donc je vais être plus précis...

Je voulais sauvegarder un Texte compris entre 1 et 64 caractères, avec des mise à jour fréquentes (5c -> 63c -> 15c -> 12c -> 3c) et éviter la fragmentation de la table.

Si je comprend bien VARCHAR (64) reste le plus adapté, mais du coup je vais avoir je ne peux pas éviter la fragmentation


et mon astuce théorique ne fonctionne pas

La prochaine mise à jour pour une taille inférieure ajustera l'attribut longueur, et la MàJ suivante, si elle augmente à nouveau la longueur, présentera à nouveau le risque de fragmentation.

car VARCHAR va ajuster l'attribut logeur ???? tout seul automatiquement même si je définie VARCHAR(64)


J'espérais qu'n définissant VARCHAR(64) et en lui forcent un 1er enregistrement de 64 caractère il allait avoir une vrai taille 64
Puis lui passer mon vrai enregistrement et mes mise à jours toutes <64c et ainsi éviter la fragmentation

[CinePhil]

Quelle volumétrie de données cela va t-il concerner ? Combien de millions de lignes ?
La colonne est-elle indexée ?
La recherche de données va t-elle se faire sur le contenu de cette colonne ou bien sur une autre colonne indexée ?

[escartefigue]

L'attribut longueur est reconsidéré à chaque ordre update, il y aura donc fragmentation potentielle à chaque fois que la longueur sera revue à la hausse.

Autre élément à prendre en considération : l'attribut fera -t- il l'objet de tris fréquents (ORDER BY, GROUP BY, DISTINCT...)
Le type VARCHAR nécessite un ré-alignement sur une longueur fixe avant tri, c'est donc un coût supplémentaire

[scamphp]

Quelle volumétrie de données cela va t-il concerner ? Combien de millions de lignes ? -> moins de 5 000

La colonne est-elle indexée ? OUI

La recherche de données va t-elle se faire sur le contenu de cette colonne ou bien sur une autre colonne indexée ? Recherche sur cette colonne

[CinePhil]

Quelle volumétrie de données cela va t-il concerner ? Combien de millions de lignes ? -> moins de 5 000

La colonne est-elle indexée ? OUI

alors ne vous torturez pas les méninges pour ça ! C'est minuscule ! Et en plus c'est indexé donc no problem !

[Artemus24]

Salut scanphp.

Bonne IDEE ou IDEE à la con

Idée à la con !!!

Plusieurs questions doivent se poser concernant votre colonne chaîne de caractères.

1) est-ce que la longueur de votre colonne chaîne de caractères est variable d'une ligne à l'autre ? Le mieux serait de donner un exemple.
2) quel est la longueur maximale de cette colonne ?
3) avez-vous d'autres colonnes chaîne de caractères dans votre ligne ? Et si oui combien ?
4) la totalité des longueurs de toutes vos colonnes est-elle plus petite que la taille de votre page ?
5) modifiez-vous fréquemment le contenu de vos chaînes de caractères ?
6) quel langue dois-je utiliser pour ces chaînes de caractères ?
7) quel est le bin choix du charset ?

L'attribut longueur est reconsidéré à chaque ordre update, ...

Oui, car vous modifiez le contenu de la colonne.

... il y aura donc fragmentation potentielle à chaque fois que la longueur sera revue à la hausse.

Non, car la longueur de la ligne est calculée en fonction du type que vous utilisez.

Mais la bonne question est celle du choix du charset.
Si vous utilisez iso-8859-1 ou iso-8859-15, la longueur d'un caractère est de 1 octet, à l'inverse de l'utf8 qui peut aller de 1 à 4 octets.
A priori, si vous utilisez 1 caractère = 1 octet, il n'y aura pas de fragmentation de la ligne.
Inversement, avec l'utf-8, c'est possible.

Le type VARCHAR nécessite un ré-alignement sur une longueur fixe avant tri, c'est donc un coût supplémentaire

On n'en parle jamais sur les forums, mais il existe un alignement frontière de mot ou de demi-mot selon le type qui est utilisé.

Si la volumétrie vous pose un problème, compressez vos données lors de l'insertion dans la table.

@+

[escartefigue]

Non, car la longueur de la ligne est calculée en fonction du type que vous utilisez.

Si, car on parle ici de type varchar, donc la modification du contenu lors d'un UPDATE va recalculer la longueur.
Quand la nouvelle longueur est supérieure et que la ligne ne tient plus dans l'emplacement, alors il y a déplacement de la ligne

Mais la bonne question est celle du choix du charset.
Si vous utilisez iso-8859-1 ou iso-8859-15, la longueur d'un caractère est de 1 octet, à l'inverse de l'utf8 qui peut aller de 1 à 4 octets.
A priori, si vous utilisez 1 caractère = 1 octet, il n'y aura pas de fragmentation de la ligne.
Inversement, avec l'utf-8, c'est possible.

Non plus, pour la raison que j'ai expliquée plus haut, quelque soit la charset, si on ajoute des caractères, il faut reconsidérer la longueur et donc, potentiellement, avoir un déplacement de la ligne.
Par exemple, la requête suivante risque fort de provoquer des déplacement dans les DATA et INDEX, si la colonne "ma_colonne" est de type varchar, et ce quel que soit le jeu de caractères (charset) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
UPDATE ma_table
SET ma_colonne = 'Société Nationale des Chemins de fer Français'
WHERE ma_colonne = 'S.N.C.F'

[Artemus24]

Salut Escartefigue.

Si, car on parle ici de type varchar, donc la modification du contenu lors d'un UPDATE va recalculer la longueur.
Quand la nouvelle longueur est supérieure et que la ligne ne tient plus dans l'emplacement, alors il y a déplacement de la ligne

Pas du tout ! Il suffit de faire le test pour s'apercevoir que MySql calcul la taille maximale de sa ligne.
Si la déclarative de la colonne "string" est plus grande que la taille maximale autorisée, mysql provoque une erreur.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test1`
--------------
 
--------------
CREATE TABLE `test1`
( `id`        integer  unsigned  NOT NULL auto_increment PRIMARY KEY,
  `string`    varchar(65529)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS `test2`
--------------
 
--------------
CREATE TABLE `test2`
( `id`        integer  unsigned  NOT NULL auto_increment PRIMARY KEY,
  `string`    varchar(65530)       NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
ERROR 1118 (42000) at line 35: Ligne trop grande. Le taille maximale d'une ligne, sauf les BLOBs, est 65535. Changez le type de quelques colonnes en BLOB
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Dans cet exemple, on ne peut pas dépasser, pour une ligne, la limite de 65535.
Ce qui donne pour la table test1 : 65535 - 4 (integer) - 65529 (string) = 2.
Or ce 2 correspond, en octets, à la longueur de la zone qui détermine la longueur de la colonne varchar.

Dans le second exemple, j'ajoute simplement un octet de plus (65530) à la colonne string, et là MySql n'est pas content.

Ici, j'ai bien précisé le charset LATIN qui donne 1 caractère = 1 octet.

C'est bien ce que j'ai dit, cela dépend du type que vous utilisez.

@+

[escartefigue]

Fort bien, mais sans rapport avec l'attribut longueur lié à une colonne de type varchar (ou nvarchar) comparativement à une colonne de type char
Et sans rapport non plus avec la désorganisation liée au changement de longueur totale de la ligne

[Artemus24]

Salut Escartefigue.

Je ne comprends pas votre remarque.

Parlons-nous de l'augmentation de la taille du varchar ?

@+

[SQLpro]

L'attribut longueur est reconsidéré à chaque ordre update, il y aura donc fragmentation potentielle à chaque fois que la longueur sera revue à la hausse.

pas seulement... À la baisse aussi puisque cela laisse des octets vides !

A +

[bertiny]

Bonjour
Si ton souci est d'éviter cette fragmentation dû à la à la hausse ou à la baisse des données, CHAR te convient le mieux. Mais je pense que la taille des données n'est pas considérable. Il me semble que tu as envie de résoudre deux problèmes au même moment là.

Cordialement