MySQL/Types de données

Alias

Manipulation de base

Alias

Manipulation de base

Types de données principaux

Voici les valeurs acceptées pour chaque type de variable en octets, au-delà desquelles un stockage provoque un débordement^[1] :

 TINYINT (1o : -127+128)
 SMALLINT (2o : +-65 000)
 MEDIUMINT (3o : +-16 000 000)
 INT (4o : +- 2 000 000 000) 
 BIGINT (8o : +- 9 trillions)
   Intervalle précis : -(2^(8*N-1)) -> (2^8*N)-1
   /!\ INT(2) = "2 chiffres affichés" -- ET NON PAS "nombre à 2 chiffres"

FLOAT(M,D) DOUBLE(M,D) FLOAT(D=0->53) 
   /!\ 8,3 -> 12345,678 -- PAS 12345678,123!

TIME (HH:MM) 
YEAR (AAAA) 
DATE (AAAA-MM-JJ) 
DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
TIMESTAMP (comme date, mais 1970->2038, compatible Unix)

VARCHAR(ligne) 
TEXT (multi-lignes; taille max=65535) 
BLOB (binaire; taille max=65535)

Variantes : 
TINY (max=255) 
MEDIUM (max=~16000) 
LONG (max=4Go)
   Ex : TINYTEXT, LONGBLOB, MEDIUMTEXT

ENUM ('valeur1', 'valeur2', ...) -- (default NULL, ou '' si NOT NULL)

Il faut préférer DECIMAL(10,2) à FLOAT car ce dernier peut se révéler imprécis. Ex : 39.99 x 1 = 39.9900016784668.

Pour gagner en performances et en espace disque, il est très important de restreindre au maximum les types. Par exemple pour une liste de pays, utiliser SMALLINT au lieu de INT s'avère payant.

VARCHAR

VARCHAR est l'abréviation de CHARACTER VARYING (caractère variant en anglais). 'n' représente la taille maximum de colonne (jusqu'à 65 535 caractères). Par exemple, une colonne de type VARCHAR(10) peut contenir 10 caractères maximum. La taille du stockage correspondant en fait à la taille du texte contenu (L), plus un ou deux octets (un si la taille est inférieure à 255).

Par exemple pour la chaîne "abcd", L = 4 et le stockage = 5.

CHAR(n) est similaire à VARCHAR(n) sauf qu'il occupe une taille fixe, il ne tient pas compte de son contenu. Un champ CHAR prend donc plus de place de stockage, est 20 % plus performant pour les recherches s'il est index^[2].

TEXT et BLOB

Les types TEXT et BLOB (binary large object) ont une taille maximum de 65 535 caractères. L'espace requis est la taille réelle des données stockées, plus un ou deux octets (un si < 255). Comme elles ne sont pas stockées dans le fichier de données, toutes les opérations (INSERT / UPDATE / DELETE / SELECT) les concernant sont plus lentes, mais cela a l'avantage de rendre celles qui ne les touchent pas plus rapides.

Toutefois le BLOB possède plusieurs déclinaisons^[3] :

TINYBLOB : 255 o.
MEDIUMBLOB : 16 Mo.
LONGBLOB : 4,29 Go.

INTEGER

Spécifier une valeur "n" entre parenthèses n'a aucun effet. De toute façon, la taille maximum des données stockées est de 429 fois 10⁷.

Pour les nombres uniquement positifs (comme les index qui représentent les numéros de ligne), utiliser UNSIGNED, sinon SIGNED.

En rentrant un nombre supérieur à la limite (ex : 1234567890123456789), le logiciel dira qu'une ligne a été affectée mais en fait il ne modifie pas le champ. Pour pallier cela, modifier le type en BIGINT.

Les booléens sont des tinyint(1).

Le nombre entre parenthèses après les types entiers indique sur combien de chiffres l'entier stocké est prévu pour être affiché^[4]. Toutefois s'il est plus long que cela, cela n'empêchera pas son stockage.

DECIMAL

Syntaxe : DECIMAL(n,m).

Ex : DECIMAL(4,2) signifie des nombres jusqu'à 99,99 (quatre chiffres dont deux réservés aux décimales).

DATE

Il existe trois types pour stocker des dates : DATETIME, DATE, et TIMESTAMP.

MySQL récupère et affiche les dates au format "AAAA-MM-JJ" (plus pratique pour les classer de gauche à droite).

DATETIME est utilisé quand les valeurs doivent contenir l'heure en plus du jour.

La différence entre DATETIME et TIMESTAMP est que la taille des formatsTIMESTAMP est limitée aux années 1970-2037.

Le type TIME peut stocker les heures du jour (HH:MM:SS) sans date. Il peut aussi représenter une période de temps (ex : -02:00:00 pour deux heures avant). Limité entre '-838:59:59' et '838:59:59'.

YEAR peut stocker des années.

Pour manipuler des dates, il faut préciser un jour et pas seulement une heure, car pourrait interpréter "HH:MM:SS" comme une valeur "YY:MM:DD".

Les exemples suivant montrent la plage de date précise pour les temps Unix, démarrant à l'époque Unix jusqu'à 2038 ( $2^{31}-1$ ).

mysql> SET time_zone = '+00:00'; -- GMT
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT FROM_UNIXTIME(-1);
+-------------------+
| FROM_UNIXTIME(-1) |
+-------------------+
| NULL              |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(0); -- "Epoch"
+---------------------+
| FROM_UNIXTIME(0)    |
+---------------------+
| 1970-01-01 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(2145916799);
+---------------------------+
| FROM_UNIXTIME(2145916799) |
+---------------------------+
| 2037-12-31 23:59:59       |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(2145916800);
+---------------------------+
| FROM_UNIXTIME(2145916800) |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

Les fonctions de manipulation de date seront exposées dans un chapitre ultérieur.

SET et ENUM

SET est un type dont les valeurs sont prédéfinies dans une liste lors de la création de la table^[5].

ENUM est similaire mais restreint à un seul membre, alors que SET autorise le stockage de n'importe lesquelles de ses valeurs ensemble.

Exemple :

SET("madame", "monsieur")    -- autorise un champ vide, "madame", "monsieur", "madame, monsieur", ou "monsieur, madame"

ENUM("madame", "monsieur")   -- autorise un champ vide, "madame" ou  "monsieur"

Vérification

Pour connaitre le type d'un champ :

DESCRIBE MaTable MonChamp

Pour vérifier que le contenu d'un champ est un toujours un nombre entier :

SELECT MonChamp FROM MaTable WHERE NOT MonChamp REGEXP '^-?[0-9]+$';

Références

↑ https://dev.mysql.com/doc/refman/5.5/en/integer-types.html
↑ https://dba.stackexchange.com/questions/424/performance-implications-of-mysql-varchar-sizes/1915#1915
↑ Christian Soutou, Apprendre SQL avec MySQL : Avec 40 exercices corrigés, Éditions Eyrolles, 7 juillet 2011 (lire en ligne)
↑ https://dev.mysql.com/doc/refman/5.7/en/numeric-type-attributes.html
↑ http://dev.mysql.com/doc/refman/5.0/fr/set.html

[1] ttps://dev.mysql.com/doc/refman/5.5/en/integer-types.html

[2] ttps://dba.stackexchange.com/questions/424/performance-implications-of-mysql-varchar-sizes/1915#1915

[3] Christian Soutou, Apprendre SQL avec MySQL : Avec 40 exercices corrigés, Éditions Eyrolles, 7 juillet 2011 (lire en ligne)

[4] ttps://dev.mysql.com/doc/refman/5.7/en/numeric-type-attributes.html

[5] ttp://dev.mysql.com/doc/refman/5.0/fr/set.html

[1]

[2]

[3]

[4]

[5]