Crochets (``['' et ``]'') indique que cet argument est optionnel.

NB : Il est toujours possible de spécifier ZEROFILL pour une colonne. MySQL ajoutera alors automatiquement l'attribut UNSIGNED à la colonne.

7.2.1 Tailles nécessaires pour le stockage de types de colonnes

Voici la liste des espaces mémoire requis, par type.

7.2.2 Types numériques

7.2.3 Types date et heure

7.2.4 Types chaîne

Les types VARCHAR, BLOB et TEXT sont des types à longueur variable, dont taille de stockage dépend plus de la valeur qui leur est assignée que de leur taille maximale. Par exemple, une colonne de type VARCHAR(10) peut contenir une chaîne de 10 caractères aux maximum. La taille réelle nécessaire est la longueur de la chaîne, plus 1 octet, qui stockera la taille réelle de la chaîne. Par exemple, la chaîne 'abcd' occupe 5 octets.

Les types BLOB et TEXT ont besoin de 1, 2, 3 ou 4 octets pour stocker la taille de la colonne, en fonction du type.

Si une table possède au moins une colonne de longueur variable, l'enregistrement sera aussi de longueur variable. Il faut noter que lorsqu'une table est créée, MySQL peut, sous certaines conditions, changer le type d'une colonne de longueur variable en un type de colonne de longueur fixe, et vice-versa. Pour plus de détails, 7.6.1 Modifications automatiques de type de colonne.

La taille d'un objet ENUM est déterminé par le nombre d'énumération différentes. 1 octet est suffisant pour décrire une énumération qui a jusqu'à 255 valeurs différentes ; 2 octets sont nécessaires décrire une énumération qui aurait jusqu'

La taille d'un objet SET est déterminé par le nombre d'élément distinct qu'il contient. Si la taille d'un SET est N, le SET occupera (N+7)/8 octets, arrondi aux entiers 1,2,3,4, ou 8 octets. Un ensemble peut contenir jusqu'à 64 éléments.

7.2.5 Types numériques

Tous les types entiers disposent d'un attribut optionnel UNSIGNED. Les valeurs non signées sont utilisées quand les nombres utilisé sont uniquement positifs, ou bien lorsqu'il faut pouvoir manipuler des nombres un peu plus grand de normalement.

Tous les types numériques disposent d'un attribut optionnel ZEROFILL. Cette option force l'affichage de tous les zéros non significatifs. Ainsi, dans une colonne de type INT(5) ZEROFILL, 4 sera affiché : 00004.

Quand une valeur trop grande est affectée à une colonne, MySQL limitera cette valeur au maximum qu'il peut stocker dans la colonne.

Par exemple, soit une colonne de type INT qui accueille des nombres dans l'intervalle -2147483648 to 2147483647. Lorsqu'on tente d'inserer -9999999999 dans cette colonne, MySQL utilisera automatiquement la plus petite valeur possible, soit -2147483648 . De même, Lorsqu'on tente d'inserer 9999999999 dans cette colonne, MySQL utilisera automatiquement la plus grande valeur possible, soit 2147483647.

Si une colonne est de type INT UNSIGNED, la taille de la colonne est la même, mais les extrémités sont différentes. Lors d'une tentative d'insertion, -9999999999 et 9999999999 deviendront respectivement 0 et 4294967296.

Ces conversions implicites sont signalées comme des alertes (``warnings''), lors des requêtes incluant ALTER TABLE, LOAD DATA INFILE, UPDATE et INSERT multi-lignes.

La taille maximale (M) et le nombre de décimales (D) sont utilisées lors du formatage et des calculs de la taille maximale d'une colonne.

MySQL tentera d'enregistrer n'importe quelle valeur, du moment que cette dernière peut être contenue dans la colonne, et malgré le dépassement de limite d'affichage. Par exemple, une colonne de type INT(4) peut afficher 4 caractères. Il est cependant possible d'insérer une valeur qui a plus que 4 chiffres, telle que 12345. La taille d'affichage est dépassée, mais 12345 est bien dans l'intervalle autorisé pour un INT. Donc, MySQL va enregistrer la valeur de 12345 . Lors d'une requête, MySQL retournera bien la vraie valeur, c'est à dire . 12345

Le type DECIMAL peut être considéré comme un type numérique (puisqu'il est synonyme de NUMERIC ), mais ce type est en fait enregistré comme une chaîne. Un caractère est utilisé pour chaque chiffre, pour la virgule et pour le signe moins``-''. Si D vaut 0, DECIMAL et NUMERIC ne contiennent ni virgule, ni partie décimale.

La taille maximale d'une valeur DECIMAL est la même que celle d'un DOUBLE, mais elle peut dépendre des choix de M et D. Par exemple, un DECIMAL déclaré tel que indique DECIMAL(4,2) que la valeur maximale aura 2 chiffres après la virgule. Etant donné la façon avec laquelle le type DECIMAL est enregistré, ce DECIMAL sera compris entre-.99 to 9.99, ce qui est nettement moins que les valeurs accessibles avec un DOUBLE.

Pour éviter certains problèmes d'arrondissement, MySQL ajuste toujours les valeurs qu'il enregistre au nombre de décimale de la colonne. Ainsi, pour une colonne de type FLOAT(8,2)., le nombre de décimale est 2. Donc, un nombre tel que 2.333 sera arrondi à 2.33, puis enregistré.

7.2.6 Types date et heure

Les types date et heure sont DATETIME, DATE, TIMESTAMP, TIME et YEAR. Chacun dispose d'un intervalle de validité, et une valeur ``zéro'', qui peut être utilisé pour indiquer une valeur illégale.

Voici quelques considérations générales à garder à l'esprit quand on travaille avec les types date et heure :

• MySQL retourne les valeurs de date et d'heure dans un format standard unique, mais il est capable d'interpréter un grand nombre de format d'entrée. Néanmoins, seuls les formats décrit dans les sections suivantes sont supportés. MySQL attends des dates valides, et des effets imprévisibles peuvent résulter de l'utilisation d'autres formats.
• Bien que MySQL tente d'interpréter un grand nombre de format de date, l'année devra toujours être placée à gauche. Les dates doivent être données dans l'ordre année-mois-jour (ie : '98-09-04'), plutôt que dans l'ordre mois-jour-année ou l'ordre jour-mois-année utilisés habituellement. (ie '09-04-98', '04-09-98').
• MySQL convertit automatiquement une date ou une heure en un nombre, si cette valeur est utilisé dans un contexte numérique, et vice-versa.
• Quand MySQL rencontre une valeur pour une date ou une heure qui n'est pas valide, il la convertit en valeur ``zéro''. Les problèmes de dépassement de capacités sont réglés comme pour les types numériques, en ramenant la valeur au maximum ou au minimum de l'intervalle autorisé. La table suivante montre les formats des valeurs ``zéro''.

  Column type	``Zero'' value
  DATETIME	'0000-00-00 00:00:00'
  DATE	'0000-00-00'
  TIMESTAMP	00000000000000 (length depends on display size)
  TIME	'00:00:00'
  YEAR	0000

• Les valeurs ``zéro'' sont des valeurs particulières, mais il est parfaitement possible de les utiliser pour les enregistrer ou y faire référence. Il est aussi possible de les écrire '0' ou 0,qui sont plus facile à écrire.
• Les dates ``Zéro'' utilisées via MyODBC sont automatiquement converties à NULL par MyODBC 2.50.12 et mieux, car ODBC ne peut pas manipuler de telle valeur.

7.2.6.1 Bug de l'an 2000 et données de types date

Les types DATETIME, DATE et TIMESTAMP sont proches. Cette section décrit leur caractéristiques, et montre en quoi ils sont similaires, et en quoi ils sont différents.

Le type DATETIME est utile pour manipuler en même temps une date et une heure. . MySQL retourne et affiche les valeurs de type DATETIME au format 'YYYY-MM-DD HH:MM:SS'. L'intervalle valide pour le type DATETIME est '1000-01-01 00:00:00' à '9999-12-31 23:59:59'. (``valide`` signifie que des valeurs anciennes pourrait fonctionner, mais qu'il n'y a aucune garantie).

Le type DATE est utilisé pour manipuler simplement une date, sans l'heure. MySQL retourne et affiche les valeurs de type DATE au format 'YYYY-MM-DD' L'intervalle valide pour le type DATE est '1000-01-01' à '9999-12-31'

Le type TIMESTAMP est utilisé automatiquement lors de requête , avec la valeur courante de date et d'heure. Si il y a plusieurs colonnes de type TIMESTAMP , seule la première sera automatiquement mise à jour.

La datation automatique intervient sur la première colonne de type TIMESTAMP , et dans les conditions suivantes :

• La colonne n'est pas expressément nommée dans la requête INSERT ou LOAD DATA
• La colonne n'est pas expressément nommée dans une requête , et au moins une autre colonne change UPDATE de valeur (Il est important de noter qu'une requête UPDATE qui affecterait la même valeur que celle qui est déjà affectée dans la colonne, alors la colonne TIMESTAMP ne sera pas mise à jour, car MySQL va ignorer la requête, par souci d'efficacité.)
• La colonne TIMESTAMP est explicitement mise à NULL.

Dans le cas où il y a plusieurs colonnes de type TIMESTAMP, il est possible d'affecter la valeur courante de date et d'heure, en affectant la valeur NULL ou NOW().

Il est possible d'affecter n'importe quelle date à une colonne de type TIMESTAMP, en lui affectant explicitement la valeur désirée. Cela est vrai pour toutes les colonnes de type TIMESTAMP, y compris la première. Par exemple, il est possible d'utiliser une colonne de type pour enregistrer le moment de création de la ligne, mais qui ne sera plus changé par la suite.:

• Il suffit de laisser MySQL affecter la valeur automatiquement lors de la création de la ligne. L'initalisation sera faite au jour et heure courante.
• Lors des modifications ultérieurs, il suffit d'affecter à la colonne sa propre valeur.

Ou alors, il est aussi simple d'utiliser une colonne de type DATETIME, qui sera initialisé à l'aide de la commande NOW() , et qui ne sera plus jamais modifiée.

TIMESTAMP couvre un espace de temps qui commence en 1970 et se termine quelques par en 2037, avec un précision d'une seconde. Ses valeurs sont affichée comme des nombres.

Le format utilisé par pour retourner les valeurs de type dépendent de la taille de l'affichage, comme présenté ci dessous. Un complet affiche 14 chiffres, mais il est possible de n'afficher qu'une partie.

Toutes les colonnes de type ont besoin de la même quantité de mémoire, quelque soit le format d'affichage. Les formats d'affichage les plus courants sont le 6, le 8, le 12 et le 14. Il est possible d'imposer une taille arbitraire au format d'affichage au moment de la création de la table, mais 0 et toutes les valeurs supérieures à 14 seront ramenées à 14. Les valeurs impaires entre 1 et 13 sont arrondies au nombre entier pair supérieur.

Il est possible d'affecter des valeurs de type DATETIME, DATE et TIMESTAMP en utilisant n'importe lequel des formats suivants :

• Une chaîne, avec le format 'YYYY-MM-DD HH:MM:SS' ou 'YY-MM-DD HH:MM:SS'. Les '-' et ' :' ne sont pas obligatoires, et n'importe quel caractère non numérique. Par exemple, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45' et '98@12@31 11^30^45' sont équivalents.
• Une chaîne, avec le format 'YYYY-MM-DD' ou 'YY-MM-DD'. Les '-' et ' :' ne sont pas obligatoires, et ils peuvent être remplacé par quel caractère non numérique. Par exemple, '98-12-31', '98.12.31', '98/12/31' et '98@12@31' sont équivalents.
• Une chaîne sans aucun délimiteur, avec le format 'YYYYMMDDHHMMSS' ou 'YYMMDDHHMMSS', en supposant que cette chaîne a bien un sens en tant que date. Par exemple, '19970523091528' et '970523091528 seront comprises comme '1997-05-23 09:15:28'.Au contraire, '971122459015' n'est pas valide (le nombre de seconde n'est pas valide), et sera remplacé par : '0000-00-00 00:00:00'.
• Une chaîne sans aucun délimiteur, avec le format 'YYYYMMDD' or 'YYMMDD', en supposant que cette chaîne a bien un sens en tant que date. Par exemple, '19970523' et '970523' seront comprises comme '1997-05-23'.Au contraire, '971332' n'est pas valide (le nombre de mois et de jour ne sont pas valides), et sera remplacé par : '0000-00-00'.
• Un nombre avec le format YYYYMMDDHHMMSS or YYMMDDHHMMSS, en supposant que cette chaîne a bien un sens en tant que date. Par exemple, 19830905132800 et 830905132800 seront comprises comme '1983-09-05 13:28:00'
• Un nombre avec le format YYYYMMDD or YYMMDD, en supposant que cette chaîne a bien un sens en tant que date. Par exemple, 19830905132800 et 830905132800 seront comprises comme '1983-09-05 13:28:00'
• Le résultat d'une fonction qui retourne une valeur acceptable en dans un contexte de DATETIME, DATE or TIMESTAMP, comme NOW() or CURRENT_DATE.

Toutes les valeurs de type DATETIME, DATE ou TIMESTAMP sont converties automatiquement en ``zero'' du même type ('0000-00-00 00:00:00', '0000-00-00' ou 00000000000000).

Pour les valeurs spécifiées au format chaîne avec des délimiteurs, il n'est pas nécessaire de préciser les deux chiffres pour les mois ou les jours. Par exemple, '1979-6-9' et '1979-06-09' sont identiques. De la même façon, pour les valeurs spécifiées au format chaîne avec des délimiteurs, il n'est pas nécessaire de préciser les deux chiffres pour les heures, minutes ou secondes. Ainsi, '1979-10-30 1:2:3' et '1979-10-30 01:02:03' sont identiques.

Les valeurs spécifiée au format nombre doivent avoir 6, 8, 12 ou 14 chiffres. Si le nombre a 8 ou 14 chiffres, MySQL utilisera respectivement le format YYYYMMDD ou YYYYMMDDHHMMSS, où l'année sera représentée par les quatre premiers chiffres. Si le nombre a 6 ou 12 chiffres, MySQL utilisera respectivement le format YYMMDD or YYMMDDHHMMSS, où l'année sera représentée par les deux premiers chiffres. Si le nombre n'a pas le bon nombre de chiffre, il sera complété avec des zéros non significatif (placés avant le premier chiffre), jusqu'à correspondre à une taille analysable.

Les valeurs non délimitées, sont interprétées en fonction de leur longueur. Si la chaîne fait 8 ou 14 caractères, l'année sera considérée comme ayant 4 chiffres. Sinon, l'année n'aura que les deux premiers chiffres. La chaîne est interprétée de gauche à droite, en lisant successivement l'année, le mois, le jour, l'heure, la minute et la seconde, dans la mesure où la chaîne est suffisamment longue. Cela implique qu'il ne faut pas utiliser de chaîne de moins de 6 caractères. Par exemple, la chaîne '9903', qui pourrait s'interpréter mars 1999, sera remplacée par la date ``zéro''. En effet, est capable de trouver l'année (99) et le mois (03) mais pas le jour (00), et donc cette valeur n'est pas valide.

Les colonnes de type TIMESTAMP enregistre les dates avec la précision maximum, quelque soit la taille d'affichage. Cela a plusieurs implications :

• Il faut toujours préciser l'année, le mois et le jour, même si le type de la colonne est TIMESTAMP(4) ou TIMESTAMP(2). Sinon, la valeur ne sera pas valide, et donc, remplacée par ``zéro''.
• Si la structure de la table est modifié avec , pour agrandir la colonne, l'affichage présentera des données qui étaient précédemment cachées.
• De la même façon, réduire la taille d'affichage d'une colonne TIMESTAMP ne causera pas de perte d'information : elles seront simplement cachées.
• Bien que les valeurs TIMESTAMP soient en précision maximale, la seul fonction qui opère directement sur la valeur enregistrée dans la base est UNIX_TIMESTAMP(). Les autres fonctions utilisent la valeur formatée. Cela signifie qu'il n'est pas possible d'utiliser une fonction telle que HOUR() ou SECOND() à moins que la partie significative de TIMESTAMP soit inclus dans la valeur formatée. Par exemple, la partie HH d'une valeur de type TIMESTAMP ne sera pas affichée, à moins que la taille de l'affichage soit au moins de 10. Ainsi, utiliser la fonction sur une valeur de type de taille inférieure à 10 risque de retourner une valeur incohérente.

Il est possible d'affecter des valeurs de type date dans une variable d'un autre type date. Cependant, cela peut engendrer des altérations ou des pertes d'informations. :

• Lors de l'affectation d'une DATE dans une variable de type DATETIME ou TIMESTAMP, la partie heure du résultat est mis à '00:00:00', car la valeur DATE ne contient aucune information d'heure.
• Lors de l'affectation d'une DATETIME ou TIMESTAMP dans une variable de type DATE, la partie heure du résultat est perdue , car la valeur DATE ne contient aucune information d'heure.
• Il ne faut pas confondre les différents formats de spécifications des DATETIME, DATE et TIMESTAMP . En effet, pour le même format d'acquisition, les différents types n'ont pas le même intervalle de validité. Par exemple, TIMESTAMP ne peut contenir de valeur antérieure à 1970 ou postérieure à 2037. Cela signifie qu'une date telle que as '1968-01-01', tout en étant valide pour les types DATETIME or DATE, n'est pas valide pour le type TIMESTAMP et sera transformée en 0, lors de l'affectation à un tel objet.

Attention aussi aux erreurs de spécifications :

• En utilisant la forme de spécification avec délimiteurs, comme les délimiteurs ne sont pas imposé, il est possible d'être induit en erreur par la forme : Ainsi, '10:11:12' ressemble à une heure, à cause des délimiteurs " : ". Mais, utilisé dans un contexte de date, il peut aussi être interprété comme la date'2010-11-12'. De même, la valeur '10:45:15' sera convertie en 0, car 45 ,'est pas un mois valide.
• Les années spécifiées sur deux chiffres sont ambiguës :
  • Les années qui sont dans l'intervalle 00-69 sont considéré comme 2000-2069.
  • Les années qui sont dans l'intervalle 70-99 sont considéré comme 1970-1999.

7.2.6.2 Le type TIME

MySQL retourne et affiche les valeurs de type TIME aux formats 'HH:MM:SS' ou 'HHH:MM:SS'. Les valeurs de type TIME vont de '-838:59:59' à '838:59:59'. Le nombre d'heure peut être rendu aussi grand afin de pouvoir représenter les heures du jour, mais aussi de faire des calculs de différence d'heure entre deux jours, ce qui conduit à des durée très supérieures à 24h, voire même des valeurs négatives.

Les valeurs de type TIME peuvent être définies de nombreuses manières différentes :

• Une chaîne de format 'HH:MM:SS'. Les :' ne sont pas obligatoires, et ils peuvent être remplacé par n'importe quel caractère non numérique. Par exemple, , '10:11:12' et '10.11.12'sont équivalents..
• Une chaîne sans délimiteurs, au format'HHMMSS', en supposant qu'elle est un sens en tant que date. Par exemple, '101112' sera interpreté comme '10:11:12', mais '109712' n'est pas valide et deviendra '00:00:00'.
• Un nombre au format HHMMSS, en supposant que cela ait un sens. Par exemple, 101112 vaudra . '10:11:12'
• Le résultat d'une fonction qui retourne une valeur acceptable en dans un contexte de DATETIME, DATE or TIMESTAMP, comme NOW() or CURRENT_DATE.

Pour les valeurs de type TIME spécifiées au format chaîne avec des délimiteurs, il n'est pas nécessaire de préciser les deux chiffres pour les heures, minutes ou secondes. Ainsi, '8:3:2' et '08:03:02'.sont identiques.

Attention aux affections de valeurs courtes dans une colonne de type TIME. MySQL interprète les valeurs en supposant que les chiffres de gauche sont les secondes (MySQL interprète les valeurs de type TIME comme des intervalles de temps, plutôt qu'une date). Par exemple, '11:12', '1112' et 1112 pourraient être confondues avec '11:12:00' (12 minutes après 11 heures), mais MySQL le comprend comme '00:11:12' (11 minutes, 12 seconds). De même, '12' et 12 representent '00:00:12'.

Toutes les valeurs de TIME, qui sont hors de l'intervalle de validité sont ramenées à la valeur valide la plus proche. Ainsi, '-850:00:00' et '850:00:00' sont respectivement converties en'-838:59:59' et '838:59:59'.

Toutes les valeurs invalides de TIME sont converties en '00:00:00'. Il faut bien savoir que '00:00:00'est une valeur de TIME valide. Ainsi, si est stocké dans une table, il est impossible de dire si cela provient d'une erreur, ou si il a été affecté à cette valeur.

7.2.6.3 Le type YEAR

Le type YEAR sert à représenter les années sur un octet.

MySQL retourne et affiche les YEAR au format YYYY: L'intervalle de validité est de 1901 à 2155.

Les valeurs de type YEAR peuvent être définies de nombreuses manières différentes :

• Une chaîne de 4 chiffres, dans l'intervalle de 1901 à 2155.
• Un nombre de 4 chiffres, dans l'intervalle de 1901 à 2155.
• Une chaîne de 2 chiffres, dans l'intervalle de '00' à '99'. Les valeurs de '00' à '69' et de'70' à '99' seront converties en valeurs de type YEAR, dans les intervalles respectifs de 2000 à 2069 et de 1970 à 1999.
• Un nombre de 2 chiffres, dans l'intervalle de 1 à 99. Les valeurs de 1 à 69 et de70 à 99 seront converties en valeurs de type YEAR, dans les intervalles respectifs de 2000 à 2069 et de 1970 à 1999. Il faut bien noter que ce format diffère légèrement du précédent, car il n'est pas possible de passer un nombre égal à 0, pour obtenir l'année 2000. Il faut spécifier une chaîne, '0' ou '00', sinon MySQL retournera 0000
• Le résultat d'une fonction qui retourne une valeur acceptable en dans un contexte de DATETIME, DATE or TIMESTAMP, comme NOW().

Toutes les valeurs invalides de YEAR sont converties en 0000.

7.2.7 Types chaîne

Les types chaînes sont CHAR, VARCHAR, BLOB, TEXT, ENUM et SET.

7.2.7.1 Les types CHAR et VARCHAR

Les types CHAR et VARCHAR sont similaires, mais ils diffèrent par la manière dont ils sont stockés.

Les valeurs de type CHAR sont de longueur fixée. La longueur est déclarée lors de la création de la table. Cette longueur peut aller de 1 à 255. Quand une valeur de type CHAR est enregistrée, elle est complétée à gauche par des espaces. Lorsque MySQL retourne cette valeur, ces espaces sont effacés.

Les valeurs de type VARCHAR sont de longueur variable. Une longueur maximum est déclarée lors de la création de la table. Cette longueur peut aller de 1 à 255. Quand une valeur de type CHAR est enregistrée, seul les caractères utiles sont enregistrés, plus un octet pour enregistrer la taille de la chaîne. Les ne sont pas complétée à gauche par des espaces, mais les espaces en début de valeurs sont effacés lors de l'enregistrement (cette fonction d'effacement des espaces à l'enregistrement n'est pas spécifiée dans ANSI SQL).

Lors de l'affectation d'une valeur de type CHAR ou VARCHAR dans une colonne trop petite, la valeur est tronquée à la taille de la colonne.

La table ci dessous illustre les différences de taille entre les deux types :

Value	CHAR(4)	Storage required	VARCHAR(4)	Storage required
''	' '	4 bytes	''	1 byte
'ab'	'ab '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefgh'	'abcd'	4 bytes	'abcd'	5 bytes

Les valeurs retournées seront les mêmes, car dans tous les cas, les espaces situés en début de chaîne seront effacées

Les valeurs de type CHAR et VARCHAR sont triées et comparées sans tenir compte de la casse, à moins que l'option BINARY ai été activée lors de la création de la table. Cette option signifie que cette colonne sera triée et comparée en tenant compte de la casse, et en fonction de l'ordre de la table ASCII de la machine qui supporte MySQL.

Le mode est contagieux : Cela signifie que si une colonne de type binaire est utilisée dans une expression, la comparaison tiendra compte de la casse.

MySQL peut changer spontanément les types de colonne lors de la création d'une table.. 7.6.1 Modifications automatiques de type de colonne.

7.2.7.2 Les types BLOB et TEXT

Un BLOB est un binary long object, c'est à dire un objet binaire long, qui peut contenir une certaine quantité d'information. Les quatre types TINYBLOB, BLOB, MEDIUMBLOB et LONGBLOB ne diffèrent que par leur taille maximum. Voir 7.2.1 Tailles nécessaires pour le stockage de types de colonnes.

Les quatre types TINYTEXT, TEXT, MEDIUMTEXT t LONGTEXT correspondent aux quatre types BLOB et ont les mêmes tailles maximum, et les mêmes conditions de stockage. La seule différence entre les types BLOB et TEXT tient aux tris et aux comparaisons : les tris et comparaisons tiennent compte de la casse, dans le cas des TEXT, et n'en tiennent pas compte, dans le cas des BLOB. En d'autres termes, un TEXT est un BLOB insensible à la casse.

Lors de l'affectation d'une valeur de type BLOB ou TEXT dans une colonne trop petite, la valeur est tronquée à la taille de la colonne.

En général, on peut considérer qu'une colonne de type TEXT est une colonne de type VARCHAR, aussi grande que désiré. De la même manière, on peut considérer qu'une colonne de type BLOB est une colonne de type VARCHAR BINARY, aussi grande que possible. Les différences sont :

• Il est possible d'indexer les variables de type BLOB et TEXT pour les version de MySQL 3.23.2 ou plus récentes. Les anciennes versions ne le supporte pas.
• Les espaces situés en début de chaîne ne sont pas effacés lors de l'enregistrement dans un BLOB t TEXT, contrairement aux colonnes de type VARCHAR. BLOB and TEXT columns cannot have DEFAULT values.
• Les valeurs de type BLOB t TEXT n'ont pas d'option DEFAULT.

MyODBC utilise le type LONGVARBINARY pour le type BLOB et LONGVARBINARY pour TEXT.

Parce que les types peuvent être extrêmement grands, il y a certaines contraintes à leur utilisation :

• Pour utiliser la clause GROUP BY ou ORDER BY sur une colonne de type BLOB ou TEXT, il faut commencer par convertir la colonne dans un type à longueur fixe. Pour cela, on utilise la fonction SUBSTRING. Par exemple :

mysql> SELECT commentaires FROM Nom_table , SUBSTRING(commentaires,20) as 
souschaîne ORDER BY souschaîne;

Si SUBSTRING n'est pas utilisé, le tri portera uniquement sur les max_sort_longueur premiers octets de la colonne. Par défaut, max_sort_longueur vaut 1024, et cette valeur peut être changée en utilisant l'option , lors du démarrage de MySQL. Il est possible d'utiliser la clause GROUP sur un BLOB or TEXT , en spécifiant la position de la colonne, et en utilisant un alias. Par exemple :

mysql> select id,substring(blob_col,1,100) FROM Nom_table
           GROUP BY 2;
mysql> select id,substring(blob_col,1,100) as b FROM Nom_table
           GROUP BY b;

• La taille maximale d'un objet de type est déterminée par son type, mais la quantité d'information qu'il est possible de transmettre entre le serveur et le client dépend de la quantité de mémoire vive et de la taille des buffers de communications. Il est possible de changer la taille des buffers, mais il faut le faire sur le serveur et sur le client.

Il faut bien noter que chaque valeur de type est représentée de manière interne par un objet alloué. Contrairement aux autres objets, pour qui l'espace mémoire est réservé à la création de la table.

7.2.7.3 Le type ENUM

Le type est ENUM une chaîne, dont la valeur est choisi dans une liste de valeurs autorisées, et spécifiées à la création de la table.

Cette valeur peut prendre les valeurs ("") ou NULL sous certaines conditions :

• Lors de l'insertion d'une valeur invalide dans un ENUM ( par exemple, une chaîne qui ne serait pas dans la liste des valeurs autorisées), une chaîne vide est insérée à la place, afin d'indiquer une erreur.
• Si une ENUM est déclarée NULL, NULL sera alors une valeur valide pour cette colonne, et la valeur par défaut sera aussi NULL. Si une ENUM est déclarée NOT NULL, la valeur par défaut sera le premier élément de la liste de valeurs autorisées.

Chaque énumération a un index.

• Les valeurs autorisées sont ordonnées et indexées à partir de 1.
• L'index de la valeur erreur est 0. Cela signifie qu'il est possible d'utiliser la clause SELECT pour rechercher des lignes qui auraient une valeur ENUM invalide :

mysql> SELECT * FROM Nom_table WHERE enum_col=0;

• L'index de la valeur NULL est NULL.

Par exemple, une colonne de type ENUM("un", "deux", "trois") peut prendre chacune des valeurs ci-dessous. L'index de la valeur est aussi indiqué.

Une énumération peut avoir au maximum 65535 éléments.

La casse des lettres est sans importance pour l'affection de valeur dans une colonne de type ENUM. Cependant, lorsque ces valeurs sont retournées, elles tiennent compte de la casse des lettres tels qu'elle a été spécifiées à la création de la table.

Lire une valeur de type ENUM dans un contexte numérique permet d'accèder à l'index de la valeur. De même, lors de l'affectation d'un nombre dans une valeur ENUM, le nombre sera traité comme un index, et la valeur enregistrée sera celle de l'énumération, à l'index précisé.

Les valeurs d'une énumération sont triée en fonction de l'ordre dans lequel les éléments de l'énumération sont enregistrés lors de la création de la colonne (en d'autres termes, les valeurs d'une énumération sont triées en fonction de leur index). Par exemple, "a" sera placé avant "b" pour ENUM("a", "b"), mais "b" sera placé avant "a" pour ENUM("b", "a").les chaînes vides sont placées avant les chaînes non vides, et la valeur NULL passe avant toutes les autres.

Pour visualiser toutes les valeurs d'une colonne de type, il faut utiliser SHOW COLUMNS FROM Nom_table LIKE Nom_col_enum et détailler les valeurs de la deuxième colonne.

7.2.7.4 Le type SET

Un ensemble est une chaîne qui peut avoir aucune, une ou plusieurs valeurs, chaque valeur étant choisie dans une liste de valeur autorisées lors de la création de la table. Les valeurs de type SET qui ont plusieurs valeurs sont spécifiées en séparant les membres par des virgules(``,'').. Par conséquent, les valeurs de type SET ne peuvent pas contenir de virgule.

Par exemple, une colonne de type SET("un", "deux") NOT NULL peut prendre les valeurs suivantes :

""
"un"
"deux"
"un, deux"

Un ensemble peut avoir un maximum de 64 membres distincts.

MySQL enregistre les valeurs de type SET values numériquement, avec le premier élément situé au bit de poids faible. Lorsqu'une valeur de type SET est retournée dans un contexte numérique, les bits à 1 de cette valeurs correspondent à un membre du SET qui appartienne à cette valeur. Si un nombre est enregistré dans une valeur de type SET, alors les bits mis à un de ce nombre détermineront les membres du SET qui appartiennent à la valeur. Par exemple, une colonne a été spécifiée par SET("a","b","c","d").Alors, les membres prennent la valeur suivante :

Pour les valeurs qui contiennent plus d'un membre, l'ordre d'insertion n'importe pas. Une valeur peut être insérée plusieurs fois, elle n'apparaîtra plus qu'une seule fois dans le SET, et sera placé dans l'ordre des membres, à la création du SET. Par exemple, dans une colonne de type SET("a","b","c","d"), alors les valeurs "a, d", "d, a" et "d,a,a,d,d" seront devenues "a, d", lorsqu'elle seront retournées par la base.

Les valeurs de type s SET ont triées par ordre numérique. La valeur NULL est placée avant toutes les autres valeurs non- NULL.

En général, il est possible d'utiliser la clause SELECT sur une colonne de type SET, en utilisant l'opérateur LIKE ou la fonction FIND_IN_SET().

mysql> SELECT * FROM Nom_table WHERE Nom_col LIKE '%valeur%';
mysql> SELECT * FROM Nom_table WHERE FIND_IN_SET(valeur, Nom_col)>0;

Mais les exemples suivants sont aussi corrects

mysql> SELECT * FROM Nom_table WHERE Nom_col = 'val1,val2';
mysql> SELECT * FROM Nom_table WHERE Nom_col & 1;

le premier exemple recherche une valeur exacte ('val1,val2'). Le second exemple recherche les valeurs qui contiennent le premier élément.

Pour visualiser toutes les valeurs d'une colonne de type, il faut utiliser SHOW COLUMNS FROM Nom_table LIKE Nom_col_enum et détailler les valeurs de la deuxième colonne.

7.2.8 Choisir le bon type de colonne

Pour une utilisation aussi efficace de l'espace mémoire, il faut utiliser le type de colonne le plus précis possible. Par exemple, pour stocker un entier dont la valeur va de 1 t 99999, MEDIUMINT UNSIGNED est le meilleur type.

La représentation des valeurs monétaires est un problème commun. Avec MySQL, le meilleur choix est le type DECIMAL. Il est enregistré comme une chaîne, ce qui n'entraîne aucune perte de données. Si la précision n'est pas primordiale, le type DOUBLE peut être un bon choix.

Pour une meilleure précision, il est toujours possible de convertir les nombres à virgule fixe en BIGINT. Cela autorise la manipulation d'entier pour les calculs, et il suffit alors de les reconvertir en valeur à virgule flottante au moment de l'affichage. 10.17 Quel sont les différents formats de lignes? Quand utiliser VARCHAR/CHAR?.

7.2.9 Index de colonne

Avec MySQL, tous les types de colonnes peuvent être indexés, à l'exception des types BLOB et TEXT. L'utilisation d'index est le meilleur moyen d'accélérer les performances des clauses SELECT.

Une table peut avoir jusqu'à 16 index. La taille maximale d'un index est de 256 bytes, et cette valeur peut être choisie à la compilation de MySQL.

Il n'est pas possible d'indexer une colonne qui contient des valeurs NULL, donc une colonne indexée doit être déclarée NOT NULL.

Pour les colonnes de type CHAR et VARCHAR, il est possible de préfixer la colonne. C'est un moyen beaucoup plus rapide et qui requiert moins d'espace disque qu'indexer une colonne complète. La syntaxe pour créer une telle colonne est la suivante :

KEY Nom_index(Nom_col(longueur))

L'exemple suivant créer un index pour les 10 premiers caractères de la colonne Nom_col.

mysql> CREATE TABLE test (
           nom  CHAR(200) NOT NULL,
           KEY Nom_index(nom(10)));

7.2.10 Index multi-colonnes

MySQL peut créer des indexes sur plusieurs colonnes en même temps. Un index peut contenir jusqu'à 15 colonnes (Avec les colonnes de type CHAR t VARCHAR , il est aussi possible d'utiliser un préfixe lors de l'indexation).

Un index de plusieurs colonnes peut être considéré comme un tableau trié contenant les lignes obtenues en concaténant les valeurs des colonnes indexées.

MySQL gère les index sur plusieurs colonnes de manière à ce que les requêtes qui recherche une valeur connue dans la première colonne (avec une clause WHERE), soient rapides, même si les valeurs pour les autres colonnes ne sont pas précisées.

Par exemple, soit la table suivante :

mysql> CREATE TABLE test (
           id INT NOT NULL,
           nom CHAR(30) NOT NULL,
           prenom CHAR(30) NOT NULL,
           PRIMARY KEY (id),
           INDEX nom_complet (nom, prenom));

Ainsi, l'index nom_complet est un index sur les deux colonnes nom et prénom. L'index sera utilisé lors des requêtes qui recherchent un nom, ou un nom et un prénom. L'index sera donc utilisé lors des requêtes suivantes :

mysql> SELECT * FROM test WHERE nom="Dupont";
mysql> SELECT * FROM test WHERE nom ="Dupont"
                          AND prenom="Michel";
mysql> SELECT * FROM test WHERE nom ="Dupont"
                          AND (prenom ="Michel" OR prenom ="Marie");
mysql> SELECT * FROM test WHERE nom ="Dupont"
                          AND prenom >="M" AND prenom < "N";

Cependant, l'index ne sera pas utilisé lors des requêtes suivantes :

mysql> SELECT * FROM test WHERE prenom ="Michel";
mysql> SELECT * FROM test WHERE nom="Dupont"
                          OR prenom ="Michel";

7.2.11 Utiliser des types de colonnes d'autres bases de données

Afin de simplifier le portage d'applications écrite en SQL sur d'autres base de données, MySQL remplace automatiquement les types présentés ci-dessous par les siens :

Ce remplacement intervient à la création de la table. Après la création d'une table, les types retournés par une commande DESCRIBE Nom_table seront les types équivalent de MySQL.