Table des matières
MySQL supporte un grand nombre de types de colonnes, qui peuvent être rassemblées en trois catégories : les types numériques, temporels et chaînes. Cette section vous donne un aper¸u des types disponibles, et résume les besoin de stockage de chaque colonne, puis fournit une description détaillée des propriétés de chaque type de données. Cette présentation est volontairement courte. Les descriptions détaillées peuvent être consultées pour plus d'informations sur chaque type, comme les formats autorisés.
MySQL 4.1 et plus récente supporte des extensions pour gérer les données géographiques. Des informations sur ces types sont disponibles dans la section Chapitre 18, Données spatiales avec MySQL.
Plusieurs définitions de colonnes partagent la même convention :
Indique la taille maximale d'affichage. La taille maximale légale est de 255.
S'applique aux types à virgule flottante, et indique le nombre
de chiffres qui suivent la virgule décimale. Le nombre maximal
est de 30, mais ne devrait pas dépasser M-2.
Les crochets (‘[’ et
‘]’) indiquent des
spécifications qui sont optionnelles.
Un résumé des colonnes numériques suit. Pour plus de détails sur les types numériques, voyez la section Section 11.2, « Types numériques ». La taille des colonnes sont dans la section Section 11.5, « Capacités des colonnes ».
Si vous spécifiez l'option ZEROFILL pour une
valeur numérique, MySQL va automatiquement ajouter l'attribut
UNSIGNED à la colonne.
Attention : soyez conscient
que lorsque vous utilisez la soustraction entre deux entier,
dont l'un est de type UNSIGNED, le résultat
sera sans signe! See Section 12.7, « Fonctions de transtypage ».
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
Un très petit entier. L'intervalle de validité pour les
entiers signés est de -128 à
127. L'intervalle de validité pour les
entiers non-signés est 0 à
255.
Ce sont des synonymes de TINYINT(1). Le
synonyme BOOLEAN a été ajouté en
version 4.1.0
Un type booléen complet, qui sera introduit pour être en accord avec la norme SQL-99.
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
Un petit entier. L'intervalle de validité pour les entiers
signés est de -32768 à
32767. L'intervalle de validité pour les
entiers non-signés est 0 à
65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
Un entier. L'intervalle de validité pour les entiers
signés est de -8388608 à
8388607. L'intervalle de validité pour
les entiers non-signés est 0 à
16777215.
INT[(M)] [UNSIGNED] [ZEROFILL]
Un grand entier. L'intervalle de validité pour les entiers
signés est de -2147483648 à
2147483647. L'intervalle de validité
pour les entiers non-signés est 0 à
4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
Ceci est un synonyme INT.
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
Un très grand entier. L'intervalle de validité pour les
entiers signés est de
-9223372036854775808 à
9223372036854775807. L'intervalle de
validité pour les entiers non-signés est
0 à
18446744073709551615.
Quelques conseils à suivre avec les colonnes de type
BIGINT :
Tous les calculs arithmétiques sont fait en utilisant
des BIGINT signés ou des valeurs
DOUBLE. Il est donc recommandé de ne
pas utiliser de grands entiers non-signés dont la
taille dépasse 9223372036854775807
(63 bits), hormis avec les fonctions sur les bits! Si
vous faîtes cela, les derniers chiffres du résultats
risquent d'être faux, à cause des erreurs d'arrondis
lors de la conversion de BIGINT en
DOUBLE.
MySQL 4.0 peut gérer des BIGINT dans
les cas suivants :
Utiliser des entiers pour stocker des grandes
valeurs entières non signées, dans une colonne de
type BIGINT.
Avec MIN(big_int_column) et
MAX(big_int_column).
Avec les opérateurs (+,
-, *, etc.)
où tous les opérandes sont des entiers.
Vous pouvez toujours stocker une valeur entière exacte
BIGINT dans une colonne de type
chaîne. Dans ce cas, MySQL fera des conversions chaîne
/ nombre, qui n'utilisera pas de représentation
intermédiaire en nombre réels.
‘-’,
‘+’ et
‘*’ utiliseront
l'arithmétique entière des BIGINT
lorsque les deux arguments sont des entiers. Cela
signifie que si vous multipliez deux entiers (ou des
résultats de fonctions qui retournent des entiers),
vous pourriez rencontrer des résultats inattendus
lorsque le résultat est plus grand que
9223372036854775807.
FLOAT(precision) [UNSIGNED] [ZEROFILL]
Un nombre à virgule flottante. precision
peut valoir <=24 pour une précision
simple, et entre 25 et 53 pour une précision double. Ces
types sont identiques aux types FLOAT et
DOUBLE, décrit ci-dessous.
FLOAT(X) a le même intervalle de
validité que FLOAT et
DOUBLE, mais la taille d'affichage et le
nombre de décimales est indéfini.
En MySQL version 3.23, c'est un véritable nombre à virgule
flottante. Dans les versions antérieures,
FLOAT(precision) avait toujours 2
décimales.
Notez qu'utiliser FLOAT peut vous donner
des résultats inattendus, car tous les calculs de MySQL
sont fait en double précision. See
Section A.5.7, « Résoudre les problèmes des lignes non retournées ».
Cette syntaxe est fournie pour assurer la compatibilité avec ODBC.
Utiliser des FLOAT peut vous donner des
résultats inattendus, car les calculs sont fait en
précision double. See Section A.5.7, « Résoudre les problèmes des lignes non retournées ».
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
Un petit nombre à virgule flottante, en précision simple.
Les valeurs possibles vont de
-3.402823466E+38 à
-1.175494351E-38, 0,
et 1.175494351E-38 à
3.402823466E+38. Si
UNSIGNED est spécifié, les valeurs
négatives sont interdites. L'attribut M
indique la taille de l'affichage, et D
est le nombre de décimales. FLOAT sans
argument et FLOAT(X) (où
X est dans l'intervalle 0 à 24)
représente les nombres à virgule flottante en précision
simple.
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
Un nombre à virgule flottante, en précision double. Les
valeurs possibles vont de
-1.7976931348623157E+308 à
-2.2250738585072014E-308,
0, et
2.2250738585072014E-308 à
1.7976931348623157E+308. Si
UNSIGNED est spécifié, les valeurs
négatives sont interdites. L'attribut M
indique la taille de l'affichage, et D
est le nombre de décimales. DOUBLE sans
argument et FLOAT(X) (où
X est dans l'intervale 25 to 53)
représente les nombres à virgule flottante en précision
double.
DOUBLE PRECISION[(M,D)] [UNSIGNED]
[ZEROFILL], REAL[(M,D)] [UNSIGNED]
[ZEROFILL]
Ce sont des synonymes pour DOUBLE.
Exception : si le serveur SQL utilise l'option
REAL_AS_FLOAT, REAL
est alors un synonyme de FLOAT plutôt
que DOUBLE.
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
Un nombre à virgule flottante littéral. Il se comporte
comme une colonne de type CHAR:
``littéral'' (``unpacked'') signifie que
le nombre est stocké sous forme de chaîne : chaque
caractère représente un chiffre. La virgule décimale et
le signe moins ‘-’ des
nombres négatifs ne sont pas comptés dans
M (mais de l'espace leur est réservé).
Si D vaut 0, les valeurs n'auront pas de
virgule décimale ou de partie décimale. L'intervale de
validité du type DECIMAL est le même
que DOUBLE, mais le vrai intervalle de
validité de DECIMAL peut être restreint
par le choix de la valeur de M et
D. Si UNSIGNED est
spécifié, les valeurs négatives sont interdites.
Si D est omis, la valeur par défaut est
0. Si M est omis, la valeur par défaut
est 10.
Avant MySQL Version 3.23, l'argument M
devait inclure l'espace nécessaire pour la virgule et le
signe moins.
DEC[(M[,D])] [UNSIGNED] [ZEROFILL],
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL],
FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
Ce sont des synonymes pour DECIMAL.
L'alias FIXED a été ajouté en version
4.1.0 pour assurer la compatibilité avec les autres
serveurs.
Une description succincte des types de données temporel suit. Pour plus d'informations, voyez la section Section 11.3, « Les types date et heure ». La taille de stockage des valeurs est présenté dans la section Section 11.5, « Capacités des colonnes ».
Une date. L'intervalle supporté va de
'1000-01-01' à
'9999-12-31'. MySQL affiche les valeurs
de type DATE au format
'YYYY-MM-DD', mais vous permet d'assigner
des valeurs DATE en utilisant plusieurs
formats de chaînes et nombres.
Une combinaison de date et heure. L'intervalle de validité
va de '1000-01-01 00:00:00' à
'9999-12-31 23:59:59'. MySQL affiche les
valeurs de type DATE au format
'YYYY-MM-DD HH:MM:SS', mais vous permet
d'assigner des valeurs DATE en utilisant
plusieurs formats de chaînes et nombres.
Un timestamp. L'intervalle de validité va de
'1970-01-01 00:00:00' à quelque part
durant l'année 2037.
En MySQL 4.0 et plus récent, les valeurs
TIMESTAMP sont affichées au format
YYYYMMDDHHMMSS,
YYMMDDHHMMSS, YYYYMMDD
ou YYMMDD, suivant que la valeur de
M est 14 (ou absente),
12, 8 ou
6, respectivement, mais vous permet
d'assigner des valeurs aux colonnes
TIMESTAMP en utilisant des nombres ou des
chaînes.
Depuis MySQL 4.1, TIMESTAMP est
retournée comme une chaîne, au format 'YYYY-MM-DD
HH:MM:SS'. Si vous voulez que MySQL vous retourne
un nombre, ajoutez +0 à la colonne. Les différentes
tailles de timestamp ne sont pas supportées. Depuis la
version 4.0.12, l'option --new peut être
utilisée pour que le serveur adopte le comportement de la
version 4.1.
Une colonne TIMESTAMP est utile pour
enregistrer les dates et heures des opérations
INSERT et UPDATE, car
elle prend automatiquement date actuellement si vous ne lui
assignez pas de valeur par vous-même. Vous pouvez aussi lui
donner la valeur courante en lui donnant la valeur
NULL.
L'argument M affecte l'affichage des
colonnes de type TIMESTAMP. ses valeurs
sont toujours stockées sur 4 octets.
Notez que les colonnes TIMESTAMP(M) où
M vaut 8 ou 14 sont indiquée comme
étant des nombres, alors que les colonnes
TIMESTAMP(M) sont indiquées comme étant
des chaînes. Cela est fait pour s'assurer que l'ont peut
enregistrer et lire correctement les tables ayant ce type.
Une heure. L'intervalle va de
'-838:59:59' à
'838:59:59'. MySQL affiche les valeurs
TIME au format
'HH:MM:SS', mais vous permet d'assigner
des valeurs TIME en utilisant des nombres
ou des chaînes.
Une année, au format 2 ou 4 chiffres (par défaut, c'est 4
chiffres). Les valeurs possibles vont de
1901 à 2155 plus
0000 pour le format à 4 chiffres, et de
1970 à 2069 si vous
utilisez le format à 2 chiffres. MySQL affiche les valeurs
YEAR au format YYYY
mais vous permet d'assigner des valeurs en utilisant des
nombres ou des chaînes. Le type YEAR
n'est pas disponible avant la version 3.22.
Voici une présentation sommaire des types chaînes de caractères. Pour plus d'informations, voyez Section 11.4, « Les types chaînes ». Le tailles de stockage des lignes sont donnés dans Section 11.5, « Capacités des colonnes ».
Dans certains cas, MySQL change le type d'une colonne en un
autre, lors de l'utilisation des commandes CREATE
TABLE et ALTER TABLE. See
Section 13.2.5.1, « Modification automatique du type de colonnes ».
Une modification qui affecte de nombreux types de colonnes est
que depuis MySQL version 4.1.1, les définitions de colonnes
peuvent inclure l'attribut CHARACTER SET pour
spécifier le jeu de caractères, et, éventuellement, la
collation de la colonne. Cela s'applique à
CHAR, VARCHAR, les types
TEXT types, ENUM et
SET. Par exemple :
CREATE TABLE t
(
c1 CHAR(20) CHARACTER SET utf8,
c2 CHAR(20) CHARACTER SET latin1 COLLATE latin1_bin
);
Cette définition de table crée une colonne appelée
c1 dont le jeu de caractères est
utf8 avec la collation par défaut de ce jeu
de caractères, et une colonne appelée c2
qui a le jeu de caractères latin1 et la
collation binaire du jeu de caractères. La collation binaire
n'est pas sensible à la casse.
Le tri et les comparaisons de colonnes sont basés sur le jeu de
caractères de la colonne. Avant MySQL 4.1, les tris et
comparaisons étaient fait avec la collation du jeu de
caractères du serveur. Pour les colonnes
CHAR et VARCHAR, vous
pouvez déclarer la colonne avec l'attribut
BINARY pour que le tri et la recherche soient
insensibles à la casse, utilisant le jeu de caractère
sous-jacent, plutôt qu'un ordre lexical.
Pour plus de détails, voyez Chapitre 10, Jeux de caractères et Unicode.
De plus, depuis la version 4.1, MySQL interprète les spécifications de taille d'une colonne en terme de nombre de caractères. Les versions précédentes interprétaient les tailles en nombre d'octets.
[NATIONAL] CHAR(M) [BINARY | ASCII |
UNICODE]
Une chaîne de caractère de taille fixe, toujours
complété à droite par des espaces pour remplir l'espace
de stockage. L'intervalle de M va de 0 à
255 (1 à 255 pour les versions antérieure à la version
3.23). Les espaces terminaux sont supprimés lorsque la
valeur est relue. Les valeurs CHAR sont
triées et comparées sans tenir compte de la casse, en
utilisant le jeu de caractères par défaut, à moins que le
mot clé BINARY ne soit utilisé.
Note : les espaces terminaux sont supprimées lorsque la valeur est stockée.
Depuis la version 4.1.0, si la valeur M
est supérieure à 255, Une colonne de type
TEXT est créée. Ceci est une
fonctionnalité de compatibilité.
NATIONAL CHAR (sous son équivalent
raccourciNCHAR) est le nom SQL-99 pour
définir une colonne de type CHAR qui
utilise le jeu de caractère par défaut. C'est le
comportement par défaut de MySQL.
CHAR est un raccourci pour
CHARACTER.
Depuis la version 4.1.0, l'attribut ASCII
peut être spécifiée avec, pour assigner le jeu de
caractère latin1 à une colonne de type
CHAR.
Depuis la version 4.1.1, l'attribut
UNICODE peut être spécifié pour
assigner le jeu de caractères ucs2 à
une colonne CHAR.
MySQL permet la création d'une colonne de type
CHAR(0). Ceci est principalement utile
dans de vieille application, qui ont besoin de la colonne,
mais n'ont pas besoin de la valeur. C'est aussi pratique
pour avoir une colonne à deux valeurs : un
CHAR(0), qui n'est pas défini comme
NOT NULL, va occuper un bit, et prendre
deux valeurs : NULL ou
"".
CHAR
Ceci est un synonyme de CHAR(1).
[NATIONAL] VARCHAR(M) [BINARY]
Une chaîne de taille dynamique. M
représente la taille maximale de la valeur dans une
colonne. L'intervalle de M va de 0 à 255
caractères (1 à 255 avant MySQL 4.0.2).
Note : les espaces terminaux sont supprimées lorsque la valeur est stockée (cela diffère des spécifications de SQL-99).
Depuis la version 4.1.0, si la valeur M
est supérieure à 255, Une colonne de type
TEXT est créée. Ceci est une
fonctionnalité de compatibilité. Par exemple une colonne
VARCHAR(500) est convertie en
TEXT, et
VARCHAR(200000) est convertie en
MEDIUMTEXT. Attention, cette conversion
affecte la suppression des espaces finaux...
VARCHAR est un raccourci pour
CHARACTER VARYING.
Une colonne TINYBLOB ou
TINYTEXT peut contenir au maximum 255
(2^8 − 1) caractères.
Une colonne TEXT ou
BLOB peut contenir au maximum 65535 (2^16
− 1) caractères.
Une colonne MEDIUMTEXT ou
MEDIUMBLOB peut contenir au maximum
16777215 (2^24 − 1) caractères.
Une colonne LONGTEXT ou
LONGBLOB peut contenir au maximum
4294967295 ou 4 Go (2^32 − 1) caractères. Jusqu'en
version 3.23 le protocole client/serveur et les tables
MyISAM avait une limite de 16 Mo par paquet de communication
pour une ligne de table. Depuis les versions 4.x, la taille
maximale d'un LONGTEXT ou
LONGBLOB dépend de la taille maximal de
paquet de communication pour le protocole de communication,
et de la mémoire disponible.
Une énumération. Un objet chaîne qui peut prendre une
valeur, choisie parmi une liste de valeurs
'valeur1', 'valeur2',
..., NULL ou la valeur
spéciale d'erreur "". Une valeur
ENUM peut avoir un maximum de 65535
valeurs distinctes.
Un ensemble. Un objet chaîne, qui peut prendre zéro, une
ou plusieurs valeurs, choisies parmi une liste de valeurs
'valeur1', 'valeur2',
... Une valeur SET
peut avoir un maximum de 64 membres.
MySQL supporte tous les types numériques de la norme ANSI/ISO
SQL92. Ceux-ci représentent les types numériques exacts
(NUMERIC, DECIMAL,
INTEGER, et SMALLINT), ainsi
que les types approchés (FLOAT,
REAL, et DOUBLE PRECISION).
Le mot clef INT est un synonyme de
INTEGER, et le mot clef DEC
est un synonyme de DECIMAL.
Les types NUMERIC et DECIMAL
sont considérés comme identiques par MySQL, comme l'autorise le
standard SQL92. Ils sont utilisées par des valeurs dont il est
primordial de conserver la précision exacte, comme pour des
données financières. Lorsque vous déclarez des colonnes avec
l'un de ces types, vous pouvez indiquer la précision et
l'échelle comme ceci :
salaire DECIMAL(5,2)
Dans cet exemple, 5
(précision) représente le nombre de
décimales signifiantes qui seront stockées pour les valeurs, et
2 (échelle) représente le
nombre de chiffres qui seront stockés après le point des
décimales.
Les valeurs de type DECIMAL et
NUMERIC sont stockées sous forme de chaînes
de caractères, plutôt que comme des nombres à virgule
flottante, afin de préserver la précision décimale des valeurs.
Un caractère est donc nécessaire pour chaque chiffre, plus la
virgule (si scale > 0), et le signe moins
‘-’ (pour les nombres négatifs).
Si scale vaut 0, les valeurs de type
DECIMAL et NUMERIC ne
comporteront pas de valeur décimale, ni de virgule.
Les standards SQL requièrent que la colonne
salary soit capable de stocker toute valeur de
5 chiffres et 2 décimales. Dans ce cas, l'intervalle de valeur
qui peut être stockée dans la colonne salary
va de -999.99 et 999.99.
MySQL dévie de cette spécification de deux manières :
A la limite supérieure de l'intervalle, la colonne peut
stocker les nombres jusqu'à 9999.99. Pour
les nombres positifs, MySQL utilise l'octet réservé au signe
pour étendre la limite supérieure.
Les colonnes DECIMAL de MySQL avant 3.23
sont stockés différemment et ne peuvent pas représenter
toutes les valeurs requises par le standard SQL. Ceci est dû
au fait que pour DECIMAL(M,D), la valeur de
M inclut les octets pour le signe et le
point décimal. L'intervalle de la colonne
salary avant MySQL 3.23 serait de
-9.99 à 99.99.
Avec les standards SQL, la syntaxe DECIMAL(p)
est équivalente à DECIMAL(p,0). De manière
similaire, la syntaxe DECIMAL est équivalente
à DECIMAL(p,0), où l'implémentation est
autorisée à choisir la valeur de p. Depuis
MySQL 3.23.6, ces deux variantes de DECIMAL et
NUMERIC sont supportées. La valeur par défaut
de M est 10. Avant la version 3.23.6,
M et D
devaient être spécifié explicitement.
L'intervalle de validité maximal des valeurs de type
DECIMAL et NUMERIC est le
même que pour le type DOUBLE, mais
l'intervalle réel peut être limité par le choix des paramètres
précision et scale.
Lorsqu'une valeur ayant trop de décimales est affectée à une
colonne, la valeur est arrondie à scale
décimales. Lorsqu'une valeur est hors des limites de validité de
la colonne DECIMAL ou
NUMERIC, MySQL enregistre la plus grande valeur
qu'il peut à la place.
En extension de la norme ANSI/ISO SQL92, MySQL supporte aussi les
types entiers TINYINT,
MEDIUMINT, et BIGINT, comme
présenté ci-dessus. Un autre extension supportée par MySQL
permet de spécifier optionnellement la taille d'affichage, sous
la forme d'une valeur entière entre parenthèses, juste après le
mot clé spécifiant le type (par exemple, INT(4)). Cette
spécification de taille est utilisée pour remplir à gauche,
avec le caractère de remplissage par défaut, les nombres dont la
taille est inférieure à celle spécifiée mais uniquement à
l'affichage : cela ne réduit pas l'intervalle de validité des
valeurs qui peuvent être stockées dans la colonne.
Lorsqu'elle est utilisée avec l'attribut de colonne optionnel
ZEROFILL, le caractère de remplissage par
défaut est remplacé par le caractère zéro. Par exemple, pour
une colonne dont le type est INT(5) ZEROFILL,
la valeur 4 sera lue 00004.
Notez que si vous stockez des nombres plus grands que la taille maximale d'affichage, vous pouvez rencontrer des problèmes lors de jointures de tables particulièrement compliquées, surtout si MySQL génére des tables temporaires : dans ce cas, MySQL pense que les données étaient limitées par l'affichage.
Tous les types entiers ont un attribut optionnel (non-standard)
UNSIGNED (non-signé, en fran¸ais). Les
valeurs non-signées peuvent être utilisées pour n'autoriser que
des valeurs positives dans une colonne, ou bien pour exploiter un
intervalle de validité plus haut. Depuis la version 4.0.2 de
MySQL, les nombres à virgule flottante peuvent aussi être
UNSIGNED Comme avec les types entiers, cet
attribut interdit les valeurs négatives dans la colonne, mais
n'élève pas l'intervalle de validité.
Le type FLOAT est utilisé pour représenter des données
numériques approchées. La norme ANSI/ISO SQL92 permet la
spécification optionnelle de la précision (mais pas de
l'intervalle de validité) en fournissant le nombre de décimales
voulues après la spécification de type, et entre parenthèses.
L'implémentation de MySQL supporte aussi le paramétrage de la
précision. Si le mot clé FLOAT est utilisé
pour une colonne sans précision supplémentaire, MySQL utilise
quatre octets pour stocker les valeurs. Une syntaxe alternative
existe aussi, elle utilise deux paramètre optionnel après le mot
clé FLOAT. Avec cette option, le premier
nombre représente toujours la taille de stockage nécessaire pour
la valeur, et le second nombre représente le nombre de chiffres
à stocker et afficher, après la virgule décimale (comme pour
les types DECIMAL et
NUMERIC). Lorsque MySQL stocke un nombre pour
une telle colonne, et que cette valeur a plus de décimale que
requis, la valeur est arrondie pour éliminer les chiffres
surnuméraires.
Les types REAL et DOUBLE
PRECISION n'acceptent pas de paramétrage de la
précision. En extension du standard ANSI/ISO SQL92, MySQL
reconnaît DOUBLE comme un synonyme du type
DOUBLE PRECISION. Contrairement à la norme qui
requiert que REAL soit plus petit que
DOUBLE PRECISION, MySQL implémente ces deux
types comme des nombres à virgule flottante de 8 octets, en
double précision (lorsque le mode ``ANSI'' n'est pas activé).
Pour une portabilité maximale, les applications réclamant le
stockage de nombres approché doivent utiliser les types
FLOAT ou DOUBLE PRECISION
sans spécification de précision ou de nombre de décimales.
Lorsque MySQL doit stocker une valeur qui est hors de l'intervalle
de validité d'une colonne, il ramène la valeur à la plus proche
possible, et stocke cette valeur. Par exemple, l'intervalle de
validité d'une colonne d'entiers INT va de
-2147483648 à 2147483647.
Si vous essayez d'insérer -9999999999 dans une
colonne de ce type, la valeur sera ramenée à la plus proche
possible, c'est à dire -2147483648. De même,
si vous essayez d'insérer 9999999999,
2147483647 sera stocké à la place.
Si la colonne INT possède l'attribut
UNSIGNED, l'intervalle de validité est aussi
large, mais les valeurs extrêmes se décalent vers
0 et 4294967295. Si vous
essayez de stocker -9999999999 et
9999999999 dans cette colonne, vous obtiendrez
respectivement 0 et
4294967296.
Les dépassements de capacité entraînant des troncatures sont
affichés comme des alertes (``warnings'') lors
de l'utilisation des commandes ALTER TABLE,
LOAD DATA INFILE, UPDATE, et
les insertions INSERT multiples.
| Type | Octets | De | A |
TINYINT | 1 | -128 | 127 |
SMALLINT | 2 | -32768 | 32767 |
MEDIUMINT | 3 | -8388608 | 8388607 |
INT | 4 | -2147483648 | 2147483647 |
BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
Les types dates et heures sont DATETIME,
DATE, TIMESTAMP,
TIME, et YEAR. Chacun d'eux
à une échelle de valeurs légales, de même que la valeur
``zéro'' quand vous spécifiez une valeur illégale. A noter que
MySQL vous permet d'enregistrer certaines dates qui ne sont pas
strictement légales, par exemple 1999-11-31.
La raison est que nous pensons que la vérification des dates est
à faire niveau application. Pour accélérer les tests, MySQL
vérifie juste que le mois est entre 0 et 12 et que le jour est
entre 0 et 31. Les intervalles précédentes sont définies de
cette fa¸on car MySQL vous permet d'enregistrer dans une colonne
DATE ou DATETIME, des dates
où le jour de la semaine ou le jour du mois est zéro. C'est
extrêmement utile pour les applications où vous avez besoin
d'enregistrer une date d'anniversaire pour laquelle vous n'avez
pas la date exacte. Dans ce cas, vous enregistrez simplement la
date comme 1999-00-00 ou
1999-01-00. (Vous ne devez pas vous attendre à
obtenir de valeurs correctes de fonctions tel que
DATE_SUB() ou DATE_ADD pour
des dates comme cela.)
Voici quelques considérations à garder à l'esprit quand vous manipulerez ce type de champs :
MySQL extrait les valeurs d'un champ date ou heure dans un format standard, mais essaye d'interpréter une grande variétés de format pour les valeurs que vous donnez (par exemple, quand vous essayez de comparer ou attribuer une valeur à un champ de type date ou heure). Néanmoins, seul les formats décrits dans les sections suivantes sont disponibles. Il est attendu que vous fournissiez des valeurs légales et des erreurs imprévues peuvent survenir si vous utilisez d'autre formats.
Même si MySQL essaye d'interpreter les valeurs sous
différents formats, il s'attend toujours à ce que l'année
soit dans la partie gauche de la valeur. Les dates doivent
êtres données sous la forme année-mois-jour (exemple :
98-09-04), au lieu de mois-jour-année ou
jour-mois-année qui sont très utilisés ailleurs (comme
09-04-98 ou '04-09-98').
Les dates représentées par deux chiffres pour les années sont ambigues, car le siècle n'est pas connu. MySQL interprète les années sur deux chiffres suivant les règles suivantes :
Si l'année est dans l'intervalle
00-69, elle est convertie en
2000-2069.
Si l'année est dans l'intervalle
70-99, elle est convertie en
1970-1999.
MySQL convertit automatiquement une date ou heure en nombre si la valeur est utilisée dans un contexte numérique et vice versa.
Lorsque MySQL rencontre une valeur hors d'intervalle pour un
type date ou heure qui est donc illégale pour ce type (voir
le début de cette section), il la convertit à la valeur
``zéro'' de ce type. (L'exception est que les valeurs hors
intervalles pour les champs TIME sont
coupées à la limite appropriée des valeurs de
TIME.) Le tableau suivant présente le
format de la valeur ``zéro'' de chaque type :
| Column type | valeur du ``zéro'' |
DATETIME | '0000-00-00 00:00:00' |
DATE | '0000-00-00' |
TIMESTAMP | 00000000000000 (la longueur dépend de la taille de
l'affichage) |
TIME | '00:00:00' |
YEAR | 0000 |
La valeur ``zéro'' est spéciale, mais vous pouvez
l'enregistrer ou vous y référer explicitement en utilisant
les valeurs contenues dans le tableau ci dessus. Vous pouvez
aussi le faire en utilisant la valeur '0'
ou 0 qui est plus facile à manipuler.
La date ou le temps ``Zéro'' utilisé avec
MyODBC est automatiquement convertie en
NULL à partir de la version 2.50.12 de
MyODBC, car ODBC ne peut manipuler de
telles valeurs.
Les types DATETIME, DATE,
et TIMESTAMP sont liés. Cette section
décrit leurs caractéristiques, leur similarités et leurs
différences.
Le type DATETIME est prévu lorsque vous
souhaitez stocker une date et une heure. MySQL affiche les
valeurs de type DATETIME au format
‘AAAA-MM-JJ HH:MM:SS’.
L'intervalle de validité va de ‘1000-01-01
00:00:00’ à ‘9999-12-31
23:59:59’. (``validité'' signifie que même si
d'autres valeurs plus anciennes peuvent être manipulées, il
n'est pas garantit qu'elles le seront).
Le type DATE est prévu lorsque vous
souhaitez stocker une date. MySQL affiche les valeurs de type
DATE au format
‘AAAA-MM-JJ’. L'intervalle de
validité va de '1000-01-01' à
'9999-12-31'.
La colonne TIMESTAMP a vu ses propriétés et
comportements évoluer avec les versions de MySQL et le mode SQL
du serveur.
Vous pouvez spécifier les valeurs des colonnes
DATETIME, DATE et
TIMESTAMP, avec les formats communs
suivants :
Une chaîne au format 'AAAA-MM-JJ
HH:MM:SS' ou 'AA-MM-JJ
HH:MM:SS'. Une syntaxe plus souple est permise :
tout caractère de ponctuation peut être utilisé comme
délimiteur entre les parties de temps ou heure. 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 au format
‘AAAA-MM-JJ’ ou
‘AA-MM-JJ’. Une syntaxe plus
flexible est aussi acceptée ici. Par exemple,
‘98-12-31’,
‘98.12.31’,
‘98/12/31’, et
‘98@12@31’ sont équivalent.
Une chaîne sans aucun délimiteurs sous la forme
‘AAAAMMJJHHMMSS’ ou
‘AAMMJJHHMMSS’, en supposant
qu'une telle chaîne ait un sens en terme de date. Par
exemple ‘19970523091528’ et
‘970523091528’ sont
interprétés comme ‘1997-05-23
09:15:28’, mais
‘971122129015’ est invalide
(les minutes ne sont pas valides) et devient alors
'0000-00-00 00:00:00'.
Une chaîne sans aucun délimiteurs sous la forme
‘AAAAMMJJ’ ou
‘AAMMJJ’, en supposant qu'une
telle chaîne ait un sens en terme de date. Par exemple,
‘19970523’ et
‘970523’ sont interprétés
comme ‘1997-05-23’, mais
‘971332’ est invalide (les
mois ne sont pas valides) et devient alors
‘0000-00-00’.
Un nombre au format AAAAMMJJHHMMSS ou
AAMMJJHHMMSS, en supposant qu'un tel
nombre ait un sens en terme de date. Par exemple,
19830905132800 et
830905132800 sont interprétés comme
‘1983-09-05 13:28:00’.
Un nombre au format AAAAMMJJ ou
AAMMJJ en supposant qu'un tel nombre ait
un sens en terme de date. Par exemple,
19830905 et 830905
sont interprétés comme
‘1983-09-05’.
Un résultat de fonction qui retourne une valeur acceptable
dans une colonne de type DATETIME,
DATE, ou TIMESTAMP,
tels que NOW() ou
CURRENT_DATE.
Les valeurs invalides DATETIME,
DATE, ou TIMESTAMP sont
remplacées par la date ``zéro'' du type approprié
(respectivement ‘0000-00-00
00:00:00’,
‘0000-00-00’, ou
00000000000000).
Pour la valeurs spécifiées sous forme de chaînes avec des
délimiteurs de date, il n'est pas nécessaire de spécifier les
deux chiffres pour les mois ou les dates qui sont inférieurs à
10. Par exemple,
‘1979-6-9’ est valide et est
équivalent à ‘1979-06-09’.
Similairement, pour les valeurs spécifiées sous forme de
chaîne avec des délimiteurs d'heure, il n'est pas obligatoire
de spécifier les deux chiffres des heures, minutes et secondes
qui sont inférieures à 10.
‘1979-10-30 1:2:3’ est valide et
est équivalent à '1979-10-30 01:02:03'.
Les valeurs spécifiées sous forme de nombres doivent avoir 6,
8, 12, ou 14 chiffres de long. Si le nombre a 8 ou 14 chiffres,
MySQL suppose que le format est AAAAMMJJ ou
AAAAMMJJHHMMSS (respectivement) et que
l'année est représentées par les 4 premiers chiffres. Si le
nombre a 6 ou 12 chiffres, MySQL suppose que le format est
AAMMJJ ou AAMMJJHHMMSS
(respectivement) et format et que l'année est représentées
par les 2 premiers chiffres. Les nombres qui ne sont pas d'une
taille valide, sont complétés avec des 0 jusqu'à la taille
lisible la plus proche.
Les valeurs spécifiées sous forme de chaînes sans
délimiteurs sont interprétés en fonction de leur taille. Si
la chaîne à 8 ou 14 caractères de long, l'année est
supposée avoir 4 chiffres. Sinon, l'année est supposée avoir
2 chiffres. La chaîne est interprétée de gauche à droite, en
lisant successivement l'année, le mois, la date, l'heure, les
minutes et les secondes, tant qu'il y a des valeurs dans la
chaîne. Cela signifie que vous ne devez pas utiliser de
chaînes qui ont moins de 6 caractères. Par exemple, si vous
spécifiez ‘9903’, en pensant
qu'il représente Mars 1999, vous vous apercevrez que MySQL
insère à la place la date ``zéro'' dans votre table. Cela est
dû au fait que si l'année et le mois sont 99 et 03, la date
est 0, ce qui en fait une date invalide, qui est rejetée par
MySQL.
Dans une certaines mesure, vous pouvez assigner des valeurs d'une colonne à une autre colonne d'un autre type. Cependant, vous devez vous attendre à quelques altération ou pertes de valeurs durant la conversion :
Si vous assignez une valeur DATE à une
colonne de type DATETIME ou
TIMESTAMP, la partie représentant les
heures vaudra ‘00:00:00’, car
les colonnes de type DATE ne contiennent
pas d'information d'heure.
Si vous assignez une valeur DATETIME ou
TIMESTAMP à une colonne de type
DATE, la composante heure sera perdue,
car les colonnes de type DATE ne
contiennent pas d'information d'heure.
N'oubliez pas que même si les valeurs
DATETIME, DATE, et
TIMESTAMP peuvent être spécifiée avec
différents formats, ces types n'ont pas les mêmes
intervalle de validité. Par exemple, les valeurs de type
TIMESTAMP ne peuvent pas prendre de
valeur antérieure à 1970 ou postérieure à 2037. Cela
signifie qu'une date telle que
‘1968-01-01’, est légale
dans les colonnes de type DATETIME, mais
n'est pas valide pour les TIMESTAMP, et
sera convertie en date zéro (0) si elle
est assignée à une telle colonne.
Attention à certains pièges concernant les spécifications de dates :
La syntaxe à délimiteur libre peut être une source de
problème. Par exemple, une valeur telle que
‘10:11:12’ ressemble à une
heure, à cause du délimiteur
`‘:’', mais avec une colonne
de date, elle sera interprétée comme la date
‘2010-11-12’. La valeur
‘10:45:15’ sera convertie en
‘0000-00-00’ car
‘45’ n'est pas un mois
valide.
Le serveur MySQL effectue seulement la vérification de base
la validité d'une date : jours 00-31,
mois 00-12, années
1000-9999. N'importe quelle date qui
n'est pas dans cette marge retournera
0000-00-00. Veuillez noter que ceci vous
permet toujours de stocker les dates inadmissibles telles
que 2002-04-31. Il permet à des
applications web de stocker des données d'une forme sans
vérifier plus loin. Pour s'assurer qu'une date est valide,
vous devrez effectuer un test dans votre application.
Les années spécifiée avec deux chiffres seulement sont ambiguÏs, car il manque le siècle. MySQL interprète les années à deux chiffres suivant ces règles :
Les années de l'intervalle 00-69
sont converties en 2000-2069.
Les années de l'intervalle 70-99
sont converties en 1970-1999.
Le type TIMESTAMP est prévu pour stocker
automatiquement l'heure courante lors d'une commande
INSERT ou UPDATE. Si
vous avez plusieurs colonnes de type
TIMESTAMP, seule la première colonne sera
mise à jour automatiquement.
La modification automatique de la première colonne de type
TIMESTAMP survient si l'une des conditions
suivantes est remplie :
Vous insérez explicitement la valeur
NULL dans la colonne.
La colonne n'est pas spécifiée explicitement dans la
commande INSERT ou LOAD DATA
INFILE.
La colonne n'est pas spécifiée explicitement dans la
commande UPDATE et d'autres colonnes
changent de valeurs (Notez qu'une commande
UPDATE qui affecte une valeur qui est
déjà celle de la colonne sera ignorée, et la colonne
TIMESTAMP ne sera pas modifiée, car la
ligne n'est pas à proprement parlée modifiée. MySQL
ignore alors ces modifications pour des raisons
d'efficacité).
Les autres colonnes de type TIMESTAMP,
hormis la première, peuvent aussi prendre la valeur courante.
Affectez-lui alors la valeur NULL ou la
fonction NOW().
Vous pouvez affecter à n'importe quelle colonne de type
TIMESTAMP une valeur différente de l'heure
et la date courant en fournissant une valeur explicite. Cela
s'applique aussi à la première colonne de type
TIMESTAMP. Par exemple, si vous voulez
affecter la date de création d'une ligne à une colonne de
type TIMESTAMP, mais ne plus y toucher
ultérieurement :
Laissez MySQL donner la valeur de la colonne lors de la création de la ligne. Cela va initialiser la colonne à la date et heure courante.
Lorsque vous faites des modifications ultérieures,
affectez explicitement à la colonne
TIMESTAMP sa propre valeur.
UPDATE tbl_name
SET timestamp_col = timestamp_col,
other_col1 = new_value1,
other_col2 = new_value2, ...
D'un autre coté, vous pouvez aussi facilement initialiser la
colonne TIMESTAMP avec
NOW() lors de sa création, puis ne plus la
modifier ultérieurement.
L'intervalle de validité des valeurs
TIMESTAMP va du début de l'année 1970
jusque quelque part durant l'année 2037, avec une précision
d'une seconde. Les valeurs sont affichés comme des nombres
entiers.
Le format d'affichage des valeurs TIMESTAMP
dépend de la taille d'affichage, comme illustré ci-dessous.
Le format total TIMESTAMP a 14 chiffres,
mais les colonnes TIMESTAMP peuvent être
créées avec des formats plus courts :
| Type de colonne | Format d'affichage |
TIMESTAMP(14) | YYYYMMDDHHMMSS |
TIMESTAMP(12) | YYMMDDHHMMSS |
TIMESTAMP(10) | YYMMDDHHMM |
TIMESTAMP(8) | YYYYMMDD |
TIMESTAMP(6) | YYMMDD |
TIMESTAMP(4) | YYMM |
TIMESTAMP(2) | YY |
Toutes les colonnes de type TIMESTAMP ont
la même taille de stockage, indépendamment de la taille
d'affichage. Les formats les plus courants sont 6, 8, 12, et
14. Vous pouvez spécifier une taille arbitraire lors de la
création de la table, mais 0 et les valeurs supérieures à
14 sont ramenées à 14. Les valeurs impaires sont aussi
ramenées au nombre pair supérieur.
Les colonnes TIMESTAMP stockent une date
valide, en utilisant la totalité de l'espace de stockage,
quelque soit la valeur de l'affichage. Cela a les implication
suivantes :
Spécifiez toujours l'année, le mois et le jour, même si
le type de colonne est TIMESTAMP(4) ou
TIMESTAMP(2). Sinon, la valeur ne sera
pas légale et 0 sera stockée.
Si vous utilisez la commande ALTER
TABLE pour réduire la largeur d'une colonne
TIMESTAMP, les informations qui
étaient affichées sont désormais ``cachées'', mais pas
détruites.
Similairement, réduire une colonne de type
TIMESTAMP ne cause aucune perte
d'information, en dehors du fait que ces informations ne
sont plus affichées.
Bien que les valeurs TIMESTAMP soient
stockées avec une précision d'une seconde, la seule
fonction qui travaille directement avec ces valeurs est la
fonction UNIX_TIMESTAMP(). Les autres
fonctions opèrent sur des valeurs lues et formatées.
Cela signifie que vous ne pouvez pas utiliser de fonctions
telles que HOUR() ou
SECOND() a moins que le format
d'affichage de la valeur TIMESTAMP ne
présente cette valeur. Par exemple, les heures ne sont
jamais affichées dans une colonne de type
TIMESTAMP à moins que la taille
d'affichage de la colonne ne soit d'au moins 10.
L'utilisation de la fonction HOUR() sur
une valeur ayant un format d'affichage plus court que
10 retournera un résultat
inutilisable.
Depuis MySQL 4.1.0, les propriétés des colonnes
TIMESTAMP diffèrent des versions
prédécentes de MySQL :
Les colonnes TIMESTAMP sont affichées
dans le même format que les valeurs des colonnes
DATETIME.
Les tailles d'affichage ne sont plus supportées comme
décrit dans la section précédente. En d'autres termes,
vous ne pouvez pas utiliser
TIMESTAMP(2),
TIMESTAMP(4), etc.
De plus, si le serveur MySQL est en mode
MAXDB, TIMESTAMP est
identique à DATETIME. C'est à dire que si
le serveur fonctionne en mode MAXDB au
moment où la table est créée, toutes les colonnes
TIMESTAMP créées sont en fait de type
DATETIME. En conséquence, ces colonnes
utilisent le format d'affichage DATETIME,
ont le même intervalle de validité et aucune mise à jour
automatique n'intervient.
MySQL peut fonctionner en mode MAXDB depuis
la version 4.1.1. Pour activer ce mode, lancez le serveur avec
le mode MAXDB au démarrage avec l'option
--sql-mode=MAXDB, ou en modifiant la variable
sql_mode durant l'exécution :
mysql> SET GLOBAL sql_mode=MAXDB;
Un client peut mettre le serveur en mode
MAXDB pour sa propre connexion avec la
commande suivante :
mysql> SET SESSION sql_mode=MAXDB;
MySQL lit et affiche les colonnes de type
TIME au format 'HH:MM:SS'
(ou 'HHH:MM:SS' pour les grandes quantités
d'heures). Les valeurs de TIME vont de
'-838:59:59' à
'838:59:59'. La raison de cet intervalle de
validité si large est que les colonnes de type
TIME peuvent être utilisés pour
représenter non seulement des heures du jour, mais aussi des
durées entre deux événements (ce qui peut dépasser largement
les 24 heures, ou même, être négatif).
Vous pouvez spécifier une valeur de type
TIME avec différents formats :
Une chaîne au format 'D
HH:MM:SS.fraction'. (Notez que MySQL ne stockera
pas la fraction d'une valeur TIME.)
Vous pouvez aussi utiliser l'une des syntaxes alternatives
suivantes : HH:MM:SS.fraction,
HH:MM:SS, HH:MM,
D HH:MM:SS, D HH:MM,
D HH ou SS. Ici,
D peut prendre des valeurs entre 0 et 33.
Une chaîne sans délimiteur au format
'HHMMSS', en supposant que cela puisse
avoir un sens en terme de date. Par exemple,
'101112' est interprété comme
'10:11:12', mais
'109712' est invalide (le nombre de
minutes n'a pas de sens), et devient la date zéro :
'00:00:00'.
Un nombre au format HHMMSS, en supposant
que cela puisse avoir un sens en terme de date. Par exemple,
101112 est interprété comme
'10:11:12'. Les formats alternatifs sont
aussi compris : SS,
MMSS, HHMMSS et
HHMMSS.fraction. Notez que MySQL ne
stocke pas encore les fractions de secondes.
Le résultat d'une fonction qui retourne une valeur
acceptable dans un contexte de valeurs
TIME, comme
CURRENT_TIME.
Pour les valeurs TIME spécifiées avec des
délimiteurs, il n'est pas nécessaire de préciser deux
chiffres pour les valeurs inférieurs à 10
pour les heures, minutes et secondes. '8:3:2'
est la même chose que '08:03:02'.
Soyez soigneux lors de l'utilisation de valeurs ``courtes'' à
une colonne de type TIME. MySQL interprète
les valeurs en supposant que les chiffres de droite
représentent les secondes (MySQL interprète les valeurs
TIME comme des durées et non comme des
heures d'une journée). Par exemple, vous pouvez penser que les
valeurs '11:12', '1112' et
1112 représentent
'11:12:00' (12 minutes après 11 heures),
mais MySQL les interprétera comme '00:11:12'
(11 minutes, 12 secondes). Similairement,
'12' et 12 représentent
'00:00:12'. Les valeurs de
TIME déclarées avec des
:, au contraire, sont toujours traités comme
des heures de journée. '11:12' signifiera
'11:12:00' et non pas
00:11:12
Les valeurs hors de l'intervalle de validité de
TIME mais qui sont valides sont ramenées à
la valeur maximale stockable la plus proche. Par exemple,
'-850:00:00' et
'850:00:00' sont respectivement converties en
'-838:59:59' et
'838:59:59'.
Les valeurs TIME non valides sont
transformées en date zéro '00:00:00'. Notez
que comme '00:00:00' est elle-même une
valeur TIME valide, vous n'aurez pas le moyen
de faire la différence entre une valeur
'00:00:00' stockée en connaissance de cause,
et '00:00:00' stockée à cause d'une erreur.
Le type YEAR est un type d'1 octet utilisé
pour représenter les années.
MySQL extrait et affiche la valeur de YEAR au
format YYYY. L'échelle va de
1901 à 2155.
Vous pouvez spécifier la valeur de YEAR en
plusieurs formats :
Une chaîne de quatre chiffres entre
'1901' et '2155'.
Un nombre à quatre chiffres entre 1901
et 2155.
Une chaîne de deux chiffres entre '00'
et '99'. Les valeurs entre
'00' et '69' et entre
'70' et '99' sont
respectivement converties en valeurs YEAR
comprises entre 2000 et
2069 d'une part, et
1970 et 1999 de
l'autre.
Une nombre de deux chiffres entre 1 et
99. Les valeurs entre
1 et 69 et entre
70 et 99 sont
respectivement converties en valeurs YEAR
comprises entre 2001 et
2069 d'une part, et
1970 et 1999 d'autre
part. Notez que le rang de valeurs pour les nombres à deux
chiffres est totalement différent du rang pour les chaînes
à deux chiffres parce que vous ne pouvez pas spécifier
deux zéro directement en tant que nombre et le faire
interpréter en tant que 2000. Vous devez
le spécifier comme chaîne '0' ou
'00' sinon il sera interprété comme
0000.
En tant que résultat d'une fonction retournant une valeur
acceptable dans le contexte de YEAR,
comme as NOW().
Les valeurs illégales pour YEAR sont
converties en 0000.
MySQL lui même est compatible an 2000. (see Section 1.2.5, « Compatibilité an 2000 »), mais les valeurs manipulées par MySQL peuvent ne pas l'être. N'importe quelle valeur n'ayant que deux chiffres pour représenter l'année est ambigu, car le siècle n'est pas précisé. Ces valeurs doivent être interprétées comme des valeurs à 4 chiffres, car MySQL stocke les années en interne en utilisant 4 chiffres.
Pour les types DATETIME,
DATE, TIMESTAMP, et
YEAR, MySQL interprète les dates ambigus en
se basant sur les règles suivantes :
Les valeurs d'années comprises dans l'intervalle
00-69 sont converties en
2000-2069.
Les valeurs d'années comprises dans l'intervalle
70-99 sont converties en
1970-1999.
Gardez bien à l'esprit que ces règles ne sont que la meilleure approximation possible d'une valeur. Si l'heuristique proposée par MySQL ne fournit pas les valeurs attendues, vous devrez fournir une valeur sans ambiguïté. (à 4 chiffres)
ORDER BY ordonnera correctement les types
YEAR/DATE/DATETIME à deux chiffres.
Notez aussi que quelques fonctions comme
MIN() et MAX()
convertiront un TIMESTAMP/DATE en nombre.
Cela signifie qu'un timestamp avec une année à deux chiffres
ne donneront pas de résultats corrects avec ces fonctions. Une
solution dans ce cas est de convertir le
TIMESTAMP/DATE en une année à 4 chiffres ou
d'utiliser quelque chose comme
MIN(DATE_ADD(timestamp,INTERVAL 0 DAYS)).
Les types chaînes sont CHAR,
VARCHAR, BLOB,
TEXT, ENUM, et
SET. Cette section décrit comment ces types
fonctionnent, leur besoins en espace et comment les utiliser dans
vos requêtes.
Les types CHAR et VARCHAR
sont similaires, mais diffèrent dans la manière dont ils sont
stockés et récupérés.
La longueur d'une colonne CHAR est fixée à
la longueur que vous avez défini lors de la création de la
table. La longueur peut être n'importe quelle valeur entre 1 et
255. (Dans la version 3.23 de MySQL, la longueur est comprise
entre 0 et 255.) Quand une valeur CHAR est
enregistrée, elle est complété à droite avec des espaces
jusqu'à atteindre la valeur fixée. Quand une valeur de
CHAR est lue, les espaces en trop sont
retirés.
Les valeurs contenues dans les colonnes de type
VARCHAR sont de tailles variables. Vous
pouvez déclarer une colonne VARCHAR pour que
sa taille soit comprise entre 1 et 255, exactement comme pour
les colonnes CHAR. Par contre, contrairement
à CHAR, les valeurs de
VARCHAR sont stockées en utilisant autant de
caractères que nécessaire, plus un octet pour mémoriser la
longueur. Les valeurs ne sont pas complétées. Au contraire,
les espaces finaux sont supprimés avant stockage (ce qui ne
fait pas partie des spécifications ANSI SQL).
Si vous assignez une chaîne de caractères qui dépasse la
capacité de la colonne CHAR ou
VARCHAR, celle ci est tronquée jusqu'à la
taille maximale du champ.
Le tableau suivant illustre les différences entre les deux
types de colonnes en montrant les différences entre
l'enregistrement dans une colonne CHAR(4) ou
VARCHAR(4) :
| Valeur | CHAR(4) | Espace requis | VARCHAR(4) | Espace requis |
'' | ' ' | 4 octets | '' | 1 octet |
'ab' | 'ab ' | 4 octets | 'ab' | 3 octets |
'abcd' | 'abcd' | 4 octets | 'abcd' | 5 octets |
'abcdefgh' | 'abcd' | 4 octets | 'abcd' | 5 octets |
Les valeurs lues dans les colonnes de type
CHAR(4) et VARCHAR(4)
seront les mêmes dans tous les cas, car les espaces finaux sont
retirés des valeurs issues de colonnes de type
CHAR lors de la lecture.
Les valeurs dans les colonnes CHAR et
VARCHAR sont classées et comparées sans
tenir compte de la casse, à moins que l'attribut
BINARY n'ai été spécifié lors de la
création de la table. L'attribut BINARY
signifie que les valeurs sont classées et triées en tenant
compte de la casse, suivant l'ordre des caractères ASCII de la
machine ou est installé le serveur MySQL.
BINARY n'affecte pas les méthodes de lecture
et de stockage des valeurs.
L'attribut BINARY se propage dans une
expression : il suffit qu'une seule colonne, utilisée dans une
expression, ait l'attribut BINARY pour que
toute l'expression ne tienne plus compte de la casse.
MySQL peut changer automatiquement le type d'une colonne
CHAR ou VARCHAR lors de la
création de la table. See
Section 13.2.5.1, « Modification automatique du type de colonnes ».
Les types BINARY et
VARBINARY sont similaires à
CHAR et VARCHAR, hormis le
fait qu'ils contiennent des chaînes binaires, plutôt que des
chaînes de texte. C'est à dire, qu'ils contiennent des
chaînes d'octets, plutôt que des chaînes de caractères. Cela
signifie qu'ils n'ont pas de jeu de caractères associé, et les
tris et comparaisons sont basées sur la valeur numérique de
l'octet.
La taille maximale pour les types BINARY et
VARBINARY, est la même que celles de
CHAR and VARCHAR, hormis
le fait que la taille de BINARY et
VARBINARY est une taille en octets, et non
pas en caractères.
La gestion des espaces finaux est la même pour
BINARY et VARBINARY que
pour CHAR et VARCHAR.
Lorsqu'une valeur BINARY est stockée, elle
est complétée à droite avec des espaces. Lorsque les valeurs
BINARY sont lues, les espaces finaux sont
supprimés. Pour VARBINARY, les espaces
finaux sont supprimés lorsque la valeur est stockée. Depuis
MySQL 5.0.3, les espaces finaux sont conservés. Gardez ces
caractéristiques en tête si vous envisagez d'utiliser ces
types de données, et que les valeurs risques de se terminer par
des espaces.
Avant MySQL 4.1.2,
BINARY( et
M)VARBINARY(
étaient traités comme des
M)CHAR( et
M) BINARYVARCHAR(.
Depuis MySQL 4.1.2, M) BINARYBINARY et
VARBINARY sont disponibles en tant que types
de données distincts, et pour
CHAR( et
M) BINARYVARCHAR(,
l'attribut M) BINARYBINARY n'active pas le traitement
binaire des colonnes. A la place, la collation binaire de la
colonne sera utilisée, mais la colonne elle-même contiendra
des caractères, plutôt que des octets. Par exemple, en version
4.1 et plus récent, CHAR(5) BINARY est
traité comme CHAR(5) CHARACTER SET latin1 COLLATE
latin1_bin, en supposant que le jeu de caractères par
défaut est latin1.
Une valeur de type BLOB est un objet binaire
de grande taille, qui peut contenir une quantité variable de
données. Les quatre types BLOB
(TINYBLOB, BLOB,
MEDIUMBLOB, et LONGBLOB)
ne différent que par la taille maximale de données qu'ils
peuvent stocker. See Section 11.5, « Capacités des colonnes ».
Les quatre types TEXT
(TINYTEXT, TEXT,
MEDIUMTEXT, et LONGTEXT
correspondent aux types BLOB équivalents, et
ont les mêmes contraintes de stockage. Les seules différences
entre les colonnes de type BLOB et celles de
type TEXT se situent aux niveau des tris et
comparaisons : Les tris, faits sur les BLOB,
contrairement à ceux faits sur les TEXT,
tiennent compte de la casse. En d'autres termes, une valeur
TEXT est une valeur BLOB
insensible à la casse.
Si vous assignez une valeur trop grande à une colonne de type
BLOB ou TEXT, la valeur
sera tronquée à la taille maximale possible.
Dans la majorité des cas, vous pouvez considérer une colonne
de type TEXT comme une colonne de type
VARCHAR, aussi grande que vous le souhaitez.
De même, vous pouvez considérer une colonne de type
BLOB comme une colonne de type
VARCHAR BINARY. Les seules différences
sont :
Vous pouvez indexer les colonnes de type
BLOB ou TEXT à partir
de la version 3.23.2 de MySQL. Les versions plus anciennes
ne peuvent pas indexer ces colonnes.
Pour les index des colonnes BLOB et
TEXT, vous devez spécifier une taille
d'index. Pour les colonnes de type CHAR
et VARCHAR, la taille du préfixe est
optionnelle.
Il n'y a pas de suppression des espaces finaux lors du
stockage de valeur dans des colonnes de type
BLOB et TEXT, ce qui
est le cas dans pour les colonnes de type
VARCHAR.
Les colonnes BLOB et
TEXT ne peuvent avoir de valeur par
défaut. (DEFAULT)
MyODBC considère les valeurs
BLOB comme des
LONGVARBINARY et les valeurs
TEXT comme des
LONGVARCHAR.
Vous pouvez rencontrer les problèmes suivants, à cause de la
grande taille des colonnes de type BLOB et
TEXT, lors de leur utilisation :
Si vous voulez utiliser les commandes GROUP
BY ou ORDER BY sur une colonne
de type BLOB ou TEXT,
vous devez d'abord la convertir en un objet de taille fixe.
Le meilleur moyen est d'utiliser la fonction
SUBSTRING. Par exemple :
mysql>SELECT comment FROM nom_de_table,SUBSTRING(comment,20) AS substr->ORDER BY substr;
Si vous le ne faites pas, seuls les
max_sort_length premiers octets de la
colonne seront utilisés pour le tri. La valeur par défaut
de max_sort_length est 1024. Cette valeur
peut être modifiée en utilisant l'option
-O au démarrage du serveur
mysqld. Vous pouvez utiliser la commande
GROUP BY sur une colonne de type
BLOB ou TEXT en
spécifiant la position de la colonne, ou avec un alias :
mysql>SELECT id,SUBSTRING(blob_col,1,100) FROM nom_de_table GROUP BY 2;mysql>SELECT id,SUBSTRING(blob_col,1,100) AS b FROM nom_de_table GROUP BY b;
La taille maximale d'un objet BLOB ou
TEXT est déterminée par son type, mais
la valeur la plus grande que vous pouvez transmettre au
programme client est déterminée par la quantité de
mémoire disponible sur le serveur et par les tailles des
buffers de communication. Vous pouvez changer la taille des
buffers de communication, mais vous devez le faire sur le
serveur et le client en même temps. See
Section 7.5.2, « Réglage des paramètres du serveur ».
Par exemple, mysql et
mysqldump vous autorises tous les deux à
modifier la valeur cliente de
max_allowed_packet. Voyez
Section 7.5.2, « Réglage des paramètres du serveur », Section 8.3, « mysql, l'outil en ligne de commande »
et Section 8.8, « mysqldump, sauvegarde des structures de tables et les
données ».
Notez que chaque valeur BLOB ou
TEXT est représentée en interne par un
objet alloué séparément, contrairement à tous les autres
types de colonne, pour lesquels la place de stockage est
allouée une fois pour chaque colonne, lorsque la table est
ouverte.
Une énumération ENUM est une chaîne dont
la valeur est choisie parmi une liste de valeurs autorisées
lors de la création de la table.
Cette chaîne peut aussi être la chaîne vide
("") ou NULL dans
certaines circonstances :
Si vous insérez une valeur illégale dans une énumération
ENUM (c'est à dire, une chaîne qui
n'est pas dans la liste de valeurs autorisées), la chaîne
vide est insérée pour représenter une erreur. Cette
chaîne peut être distinguée d'une chaîne vide 'normale'
par le fait que cette chaîne à la valeur numérique 0.
Nous reviendrons sur ce point plus tard.
Si une colonne d'énumération est déclarée
NULL, NULL devient
aussi une valeur autorisée, et la valeur par défaut est
alors NULL. Si une colonne
d'énumération est déclarée NOT NULL,
la valeur par défaut est le premier élément de la liste
des valeurs autorisées.
Chaque élément de l'énumération dispose d'un index :
Les valeurs de la liste des valeurs autorisées sont indexées à partir de 1.
L'index de la chaîne vide (cas d'erreur) est 0. Cela signifie que vous pouvez utiliser la sélection suivante pour repérer les valeurs d'énumération invalides :
mysql> SELECT * FROM nom_de_table WHERE enum_col=0;
L'index de la valeur NULL est
NULL.
Par exemple, une colonne créée comme ENUM("un",
"deux", "trois") peut prendre n'importe quelle valeur
ci-dessous. L'index de chaque valeur est aussi présenté :
| Valeur | Index |
NULL | NULL |
"" | 0 |
"un" | 1 |
"deux" | 2 |
"trois" | 3 |
Une énumération peut avoir un maximum de 65535 éléments.
A partir de la version 3.23.51, les espaces en début et fin de
chaîne sont automatiquement supprimés des éléments de
l'énumération ENUM lorsque la table est
créée.
La casse des lettres est sans importance lors de l'assignation de valeurs dans une énumération. Cependant, les valeurs lues dans la base auront la même casse que celle spécifiée lors de la création de la table.
Si vous lisez le contenu d'une énumération dans un contexte
numérique, l'index de la valeur ENUM sera
retournée. Par exemple, vous pouvez lire des valeurs
numériques comme ceci :
mysql> SELECT enum_col+0 FROM nom_de_table;
Si vous stockez un nombre dans une colonne de type
ENUM, le nombre sera traité comme un index,
et la valeur stockée sera celle de l'élément ayant cet index
(Attention, cela ne fonctionnera pas avec les commandes
LOAD DATA, car cette dernière traite toutes
les valeurs comme des chaînes). Il est déconseillé de stocker
des valeurs numériques dans un ENUM car cela
engendre des confusions. Par exemple, la colonne suivante est
une énumération de chaînes contenant les valeurs
'0', '1' et
'2', mais leur valeur numérique est
1, 2 et
3 :
numbers ENUM('0','1','2')
Les valeurs de type ENUM sont triées en
fonction de l'ordre des éléments, fixé à la création de la
table (en d'autres termes, les valeurs ENUM
sont stockées en fonction de leur index). Par exemple,
"a" précède "b" dans
l'énumération ENUM("a", "b"), mais
"b" précède "a" dans
l'énumération ENUM("b", "a"). La chaîne
vide précède toujours les chaînes non vides, et
NULL précède toutes les valeurs.
Si vous voulez connaître toutes les valeurs possibles d'une
colonne de type ENUM, pensez à utiliser
cette commande : SHOW COLUMNS FROM nom_de_table LIKE
enum_column_name, puis analysez la définition de la
colonne de type ENUM (deuxième colonne dans
le résultat).
Un SET est une chaîne qui peut avoir zéro
ou plusieurs valeurs, chacune doit être choisie dans une liste
de valeurs définies lors de la création de la table. Les
valeurs des colonnes SET composées de
plusieurs membres sont définies en séparant celles-ci avec des
virgules (‘,’). Ce qui fait que
la valeur d'un membre de SET ne peut contenir
lui même de virgule.
Par exemple, une colonne définie en tant que SET("un",
"deux") NOT NULL peut avoir l'une de ces valeurs :
"" "un" "deux" "un,deux"
Un SET peut avoir au plus 64 membres.
A partir de la version 3.23.51, les espaces en trop sont
automatiquement effacés des membres de SET
lorsque la table est créée.
MySQL enregistre les valeurs de SET
numériquement. Le bit de poids faible de la valeur correspond
alors au premier élément de la liste. Si vous utilisez une
valeur SET dans un contexte numérique, les
bits des éléments dans cet ensemble seront mis à un, et les
autres à zéro. Par exemple, vous pouvez obtenir un entier à
partir d'un ensemble comme ceci :
mysql> SELECT col_set+0 FROM nom_de_table;
Si un nombre est enregistré dans une colonne
SET, les bits un à un de ce nombre
représenteront les éléments placés dans cet ensemble.
Supposons qu'une colonne est spécifiée en tant que
SET("a","b","c","d"), les membres ont alors
les valeurs suivantes :
SET membre | Valeur décimale | Valeur binaire |
a | 1 | 0001 |
b | 2 | 0010 |
c | 4 | 0100 |
d | 8 | 1000 |
Si vous assignez 9 à cette colonne, cela
donne 1001 en binaire, ce qui fait que les
valeurs du premier et quatrième membres "a"
et "d" sont sélectionnés et la valeur
résultante est "a,d".
Pour les valeurs se composant de plus d'un membre du
SET, l'ordre des membres n'a pas d'importance
lors des insertions. Le nombre d'occurrence d'un élément
n'importe pas non plus. Lorsque la valeur sera lue
ultérieurement, chaque élément n'apparaîtra qu'une seule
fois, et dans l'ordre donné à la déclaration de la colonne.
Par exemple, si une colonne est spécifiée comme
SET("a","b","c","d"), alors
"a,d", "d,a", et
"d,a,a,d,d" seront tous représentés par
"a,d".
Si vous spécifiez une valeur incorrecte dans une colonne
SET, la valeur sera ignorée.
Les valeurs de SET sont triées
numériquement. La valeur NULL précède
toutes les autres.
Normalement, vous exécuterez un SELECT sur
une colonne SET en utilisant l'opérateur
LIKE ou la fonction
FIND_IN_SET() :
mysql>SELECT * FROM nom_de_table WHERE set_col LIKE '%value%';mysql>SELECT * FROM nom_de_table WHERE FIND_IN_SET('value',set_col)>0;
Mais ce qui suit fonctionnera aussi :
mysql>SELECT * FROM nom_de_table WHERE set_col = 'val1,val2';mysql>SELECT * FROM nom_de_table WHERE set_col & 1;
La première requête cherche les lignes qui correspondent exactement. La seconde ne cherche que les lignes contenant le premier membre du set.
Si vous voulez connaître toutes les valeurs possible d'une
colonne SET, vous devez utiliser :
SHOW COLUMNS FROM nom_de_table LIKE
nom_colonne_set et étudier la définition du
SET dans la seconde colonne.
Les capacités de stockage de chaque type de colonnes de MySQL sont listés par catégories.
Capacités de stockage des colonnes numériques
| Type de colonne | Espace requis |
TINYINT | 1 octet |
SMALLINT | 2 octets |
MEDIUMINT | 3 octets |
INT,INTEGER | 4 octets |
BIGINT | 8 octets |
FLOAT(p) | 4 if X <= 24 or 8 if 25 <= X <= 53 |
FLOAT | 4 octets |
DOUBLE PRECISION, REAL | 8 octets |
DECIMAL(M,D) | M+2 octets si D > 0, M+1 octets
si D = 0 (D+2, si M <
D) |
Capacités de stockage des colonnes de temporelles
| Type de colonne | Espace requis |
DATE | 3 octets |
DATETIME | 8 octets |
TIMESTAMP | 4 octets |
TIME | 3 octets |
YEAR | 1 octet |
Capacités de stockage des colonnes de texte
| Type de colonne | Espace requis |
CHAR(M) | M octets, 1 <= M <= 255 |
VARCHAR(M) | L+1 octets, avec L <= M et
1 <= M <= 255 |
TINYBLOB, TINYTEXT | L+1 octets, avec L < 2^8 |
BLOB, TEXT | L+2 octets, avec L < 2^16 |
MEDIUMBLOB, MEDIUMTEXT | L+3 octets, avec L < 2^24 |
LONGBLOB, LONGTEXT | L+4 octets, avec L < 2^32 |
ENUM('valeur1','valeur2',...) | 1 ou 2 octets, suivant le nombre d'éléments de l'énumération (65535 au maximum) |
SET('valeur1','valeur2',...) | 1, 2, 3, 4 ou 8 octets, suivant le nombre de membres de l'ensemble (64 au maximum) |
Les types VARCHAR, BLOB et
TEXT sont de longueur variable, et l'espace
disque requis dépend de la taille réelle de la valeur présente
dans la colonne, (taille représentée par L dans le tableau
précédent) et non pas de la taille maximale de la colonne. Par
exemple une colonne VARCHAR(10) peut contenir
une chaîne de 10 caractères. L'espace requis est dans ce cas là
la longueur de la chaîne (L), plus 1 octet
pour enregistrer la longueur de celle ci. Pour la chaîne
'abcd', L est égal à 4 et
l'espace requis est de 5 octets.
Les types BLOB et TEXT
requièrent 1, 2, 3, ou 4 octets pour mémoriser la taille de la
valeur dans la colonne, suivant la longueur maximale du type. See
Section 11.4.3, « Les types BLOB et TEXT ».
Si une table inclus au moins une colonne de taille variable, la ligne sera de taille variable. Notez que lorsqu'une table est créée, MySQL peut, dans certaines circonstances, changer automatiquement une colonne de taille variable en colonne à taille fixe (et vice-versa). See Section 13.2.5.1, « Modification automatique du type de colonnes ».
La taille d'un ENUM est déterminée par le
nombre d'éléments de l'énumération. Un octet est nécessaire
pour les énumérations ayant jusqu'à 255 valeurs possibles. Deux
octets sont nécessaires pour les énumérations ayant jusqu'à
65535 valeurs possibles. See Section 11.4.4, « Le type ENUM ».
La taille d'un SET est déterminé par le
nombre de ses membres. Si il y en a N, l'objet
occupe (N+7)/8 octets, arrondis à 1, 2, 3, 4,
or 8 octets. Un SET peut avoir au plus 64
membres. See Section 11.4.5, « Le type SET ».
La taille maximale d'une ligne dans une table
MyISAM est de 65534 octets. Les colonnes
BLOB et TEXT acceptent
jusqu'à 5-9 octets en dessous de cette taille.
Pour une utilisation optimale des capacités de stockage, essayez
d'utiliser le type le plus optimal dans chaque cas. Par exemple,
si une colonnes du type entier sera utilisée pour des valeurs
entre 1 et 99999, le type
MEDIUMINT UNSIGNED sera le plus approprié.
La représentation des valeurs monétaires est un problème
commun. Avec MySQL, vous devrez utiliser le type
DECIMAL. Il est sauvegardé en tant que
chaîne, aucune perte de précision ne devrait avoir lieu. Si la
précision n'est pas très importante, vous pouvez utiliser le
type DOUBLE.
Pour une haute précision, vous pouvez toujours transcrire en
nombre décimaux, et les enregistrer dans des
BIGINT. Cela vous permettra d'effectuer tout
vos calculs avec des entiers et de convertir à nouveau en nombre
décimaux au besoin.
Pou faciliter l'importation de code SQL issu d'autres systèmes de gestion de bases de données, MySQL convertit les types de colonnes comme le montre le tableau suivant. Cette conversion facilite l'import de structures de tables :
| Autre dénomination | Type MySQL |
BINARY(M) | CHAR(M) BINARY |
CHAR VARYING(M) | VARCHAR(M) |
FLOAT4 | FLOAT |
FLOAT8 | DOUBLE |
INT1 | TINYINT |
INT2 | SMALLINT |
INT3 | MEDIUMINT |
INT4 | INT |
INT8 | BIGINT |
LONG VARBINARY | MEDIUMBLOB |
LONG VARCHAR | MEDIUMTEXT |
LONG | MEDIUMTEXT (depuis MySQL 4.1.0) |
MIDDLEINT | MEDIUMINT |
VARBINARY(M) | VARCHAR(M) BINARY |
La conversion du type de colonnes s'effectue lors de la création.
Si vous créez une table avec des types issus d'un autre SGBDR
puis que vous exécutez la commande DESCRIBE
nom_de_table, MySQL fournira la structure de la table en
utilisant les types équivalents.