7.2 Types de colonnes

  • MySQL dispose d'un grand nombre de type de colonnes. Ces types peuvent être regroupés en trois catégories : les types numériques, les types date et heure, et les types chaînes de caractères. Ce paragraphe présente les différents types disponibles et leur tailles respectives, puis il détaille les caractéristiques de chaque catégorie. La présentation des types est intentionnellement brèves : les descriptions détaillées fourniront toutes les informations nécessaires pour un type particulier, comme par exemple les formats autorisés pour chaque colonne.
  • Voici la liste des types de colonnes utilisés par MySQL. Les codes suivants sont utilisé durant les descriptions :
  • M Indique la taille maximale d'affichage. La taille maximale autorisée par défaut est 255.
  • D S'applique aux types à virgule flottante, et précise le nombre de chiffre après la virgule.

    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.

  • TINYINT[(M)] [UNSIGNED] [ZEROFILL] Un très petit entier. Signé, il couvre l'intervalle -128 à 127 ; non signé, il couvre 0 à 255.
  • SMALLINT[(M)] [UNSIGNED] [ZEROFILL] Un petit entier. Signé, il couvre l'intervalle -32768 à 32767; non signé, il couvre 0 à 65535.
  • MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] Un entier de taille intermédiaire. Signé, il couvre l'intervalle -8388608 à 8388607; non signé, il couvre 0 à 16777215.
  • INT[(M)] [UNSIGNED] [ZEROFILL] Un entier de taille normale. Signé, il couvre l'intervalle -2147483648 à 2147483647; non signé, il couvre 0 à 4294967295.
  • INTEGER[(M)] [UNSIGNED] [ZEROFILL] Un synonyme pour INT.
  • BIGINT[(M)] [UNSIGNED] [ZEROFILL] Un entier de grande taille. Signé, il couvre l'intervalle -9223372036854775808 à 9223372036854775807; non signé, il couvre 0 à 18446744073709551615. NB : toutes les opérations arithmétiques effectuée en interne, utilise des BIGINT signés ou DOUBLE , donc il ne faut pas utiliser les grands entiers non signé au delà de 9223372036854775807 (63 bits), hormis pour les fonctions sur les bits. NB : les opérations -, + et * utiliseront des BIGINT , même lorsque les arguments seront des entiers. Cela signifie que la multiplication de deux grands entiers (ou le résultat de fonction qui retourne des entiers) peut avoir des résultats surprenants, si le résultat est supérieur à 9223372036854775807
  • FLOAT(precision) [ZEROFILL] Un nombre à virgule flottante. Il est obligatoirement signé. precision peut prendre les valeurs de 4 ou 8. FLOAT(8) est un nombre à précision double. Ces types sont identiques aux types FLOAT et DOUBLE décrit ci-dessous, mais leur précision peut être paramétrée. Avec MySQL 3.23, ce sont de vrais nombres à virgule flottante, alors qu'avec les anciennes versions, FLOAT(precision) n'avait que 2 décimales. Cette syntaxe a été ajoutée pour assurer la compatibilité ODBC.
  • FLOAT[(M,D)] [ZEROFILL] Un nombre à virgule flottante, en précision simple. Il est toujours signé. Les valeurs sont comprises -3.402823466E+38 et -1.175494351E-38.
  • DOUBLE[(M,D)] [ZEROFILL] Un nombre à virgule flottante, en précision double. Il est toujours signé. Les valeurs sont comprises -1.7976931348623157E+308 et 2.2250738585072014E-308.
  • DOUBLE PRECISION[(M,D)] [ZEROFILL]
  • REAL[(M,D)] [ZEROFILL] Des synonymes pour DOUBLE.
  • DECIMAL(M,D) [ZEROFILL] Un nombre à virgule flottante. Il est toujours signé. Il se comporte comme une colonne CHAR . Il n'est pas paqué, c'est à dire que le nombre est stocké comme une chaîne de chiffre. Chaque chiffre, le signe moins, la virgule occupe un caractère. Si D vaut 0, le nombre n'aura pas de décimales, ni de virgule. La taille maximale pour les décimales est la même que pour les DOUBLE , mais en il peut être limité par le choix de of M et D. Avec MySQL 3.23, M n'inclut plus le signe moins``-'', ni la virgule des nombres décimaux (norme ANSI SQL.).
  • NUMERIC(M,D) [ZEROFILL] Un synonyme pour DECIMAL.
  • DATE Une date. L'intervalle valide de date va de '1000-01-01' à '9999-12-31'. MySQL affiche les DATE avec le format , mais il est possible d'affecter des DATE en utilisant indifféremment des chaînes ou des nombres.
  • DATETIME Une combinaison de date et d'heure. L'intervalle valide va de '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL affiche DATETIME avec le format 'YYYY-MM-DD HH:MM:SS', mais il est possible d'affecter des DATETIME en utilisant indifféremment des chaînes ou des nombres.
  • TIMESTAMP[(M)] Un timestamp : la date et l'heure, exprimée en secondes, depuis le 1er janviers 1970. Il permet de couvrir un intervalle allant de'1970-01-01 00:00:00' à quelque part, durant l'année 2037. MySQL affiche les TIMESTAMP avec les format YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD ou YYMMDD, suivant que M vaut 14 (ou absent), 12, 8 ou 6, mais il est possible d'affecter des TIMESTAMP en utilisant indifféremment des chaînes ou des nombres. Une colonne de type TIMESTAMP est très pratique pour enregistrer des dates et heures lors d'un INSERT ou UPDATE, car cette colonne sera automatiquement mis à la date et heure de l'opération la plus récente, si aucune valeur n'est précisée. Il est aussi possible d'affecter l'heure courante en assignant la valeur NULL à une colonne de type . ( 7.2.6 Types date et heure)
  • TIME Une mesure de l'heure. L'intervalle valide est '-838:59:59' à '838:59:59'. MySQL affiche TIME au format 'HH:MM:SS', mais il est possible d'affecter des TIME en utilisant indifféremment des chaînes ou des nombres.
  • YEAR Un an. L'intervalle valide est 1901 à 2155, et 0000. MySQL affiche YEAR au format YYYY, mais il est possible d'affecter des YEAR en utilisant indifféremment des chaînes ou des nombres}(Le type YEAR est nouveau en MySQL 3.22.)
  • CHAR(M) [BINARY] Une chaîne de caractère de taille fixe, et toujours complétée à droite par des espaces. M va de 1 à 255 caractères. Les espaces supplémentaires sont supprimés lorsque la valeur est retournée dans une requête. Les tris et comparaisons effectués sur des valeurs de type CHAR sont insensibles à la casse, à moins que le mot clé BINARY soit précisé.
  • VARCHAR(M) [BINARY] Une chaîne de caractère de longueur variable. Les espaces en fin de chaîne sont supprimés lorsque la chaîne est stockée (ce n'est pas conforme à la norme ANSI SQL). Va de 1 a 255 caractères . Les tris et comparaisons effectués sur des valeurs de type VARCHAR sont insensibles à la casse, à moins que le mot clé BINARY soit précisé. Voir aussi la section 7.6.1 Modifications automatiques de type de colonne.
  • TINYBLOB
  • TINYTEXT Un objet BLOB ou TEXT avec une longueur maximale de 255 (2^8 - 1). Voir aussi la section 7.6.1 Modifications automatiques de type de colonne.
  • BLOB
  • TEXT Un objet BLOB ou TEXT avec une longueur maximale de 65535 (2^16 - 1). 7.6.1 Modifications automatiques de type de colonne.
  • MEDIUMBLOB
  • MEDIUMTEXT Un objet BLOB ou TEXT avec une longueur maximale de 16777215 (2^24 - 1). 7.6.1 Modifications automatiques de type de colonne.
  • LONGBLOB
  • LONGTEXT Un objet BLOB ou TEXT avec une longueur maximale de 4294967295 (2^32 - 1). 7.6.1 Modifications automatiques de type de colonne.
  • ENUM('value1','value2',...) Une énumération. Un objet chaîne peut prendre une des valeurs contenue dans une liste de valeur 'value1', 'value2', ..., ou NULL . Une ENUM peut avoir un maximum de 65535 valeurs distinctes.
  • SET('value1','value2',...) Un ensemble. Un objet chaîne peut prendre une ou plusieurs valeurs, chacun de ces valeur devant être contenue dans une liste de valeurs 'value1', 'value2', .... Un SET peut prendre jusqu'à 64 éléments.

    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

    Column type Storage required
    TINYINT 1 byte
    SMALLINT 2 bytes
    MEDIUMINT 3 bytes
    INT 4 bytes
    INTEGER 4 bytes
    BIGINT 8 bytes
    FLOAT(4) 4 bytes
    FLOAT(8) 8 bytes
    FLOAT 4 bytes
    DOUBLE 8 bytes
    DOUBLE PRECISION 8 bytes
    REAL 8 bytes
    DECIMAL(M,D) M bytes (D+2, if M < D)
    NUMERIC(M,D) M bytes (D+2, if M < D)

    7.2.3 Types date et heure

    Column type Storage required
    DATETIME 8 bytes
    DATE 3 bytes
    TIMESTAMP 4 bytes
    TIME 3 bytes
    YEAR 1 byte

    7.2.4 Types chaîne

    Column type taille requise
    CHAR(M) M octets, 1 <= M <= 255
    VARCHAR(M) L+1 bytes, avec L <= M et 1 <= M <= 255
    TINYBLOB, TINYTEXT L+1 octets, where L < 2^8
    BLOB, TEXT L+2 octets, where L < 2^16
    MEDIUMBLOB, MEDIUMTEXT L+3 octets, where L < 2^24
    LONGBLOB, LONGTEXT L+4 octets, where L < 2^32
    ENUM('value1','value2',...) 1 ou 2 octets, suivant le nombre de valeur dans l'énumeration (65535 au maximum)
    SET('value1','value2',...) 1, 2, 3, 4 ou 8 octets, suivant le nombre de membre de l'ensemble (64 membres au maximum)

    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.

    Column type Display format
    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 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é.

    Value Index
    NULL NULL
    "" 0
    "one" 1
    "two" 2
    "three" 3

    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 :

    SET member Decimal value Binary value
    a 1 0001
    b 2 0010
    c 4 0100
    d 8 1000

    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 :

    Other vendor type MySQL type
    BINARY(NUM) CHAR(NUM) BINARY
    CHAR VARYING(NUM) VARCHAR(NUM)
    FLOAT4 FLOAT
    FLOAT8 DOUBLE
    INT1 TINYINT
    INT2 SMALLINT
    INT3 MEDIUMINT
    INT4 INT
    INT8 BIGINT
    LONG VARBINARY MEDIUMBLOB
    LONG VARCHAR MEDIUMTEXT
    MIDDLEINT MEDIUMINT
    VARBINARY(NUM) VARCHAR(NUM) BINARY

    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.