6. Types de données

MIRAJ range chaque valeur d'une colonne dans un vecteur typé, avec un bitmap de NULL séparé (aucune valeur réservée ne représente NULL). Le dialecte des types, leurs plages et leurs conversions sont alignés sur les serveurs SQL les plus répandus, afin que les applications et outils existants (connecteurs, ORM, outils d'administration) fonctionnent sans modification.

Sommaire des familles de types#

Chaque type est reconnu quelle que soit la casse de son mot-clé. Quelques synonymes historiques sont acceptés à l'analyse et convertis vers le type MIRAJ correspondant :

SHOW CREATE TABLE, DESCRIBE et information_schema.COLUMNS montrent toujours le type retenu. L'attribut SERIAL DEFAULT VALUE d'une colonne entière équivaut à NOT NULL AUTO_INCREMENT UNIQUE. Les attributs ASCII et UNICODE d'une colonne texte équivalent à CHARACTER SET latin1 et CHARACTER SET ucs2 : comme ces clauses écrites en toutes lettres, ils sont acceptés sans changer le stockage, qui reste en UTF-8 (utf8mb4). Hors d'une colonne texte, ASCII, UNICODE et BYTE sont des erreurs de syntaxe (1064). Ces écritures appartiennent au langage MIRAJ ; le langage PostgreSQL garde son propre serial. Les adresses IP se rangent en VARCHAR ou en VARBINARY(16) avec INET_ATON, INET6_ATON et leurs réciproques.

CREATE TABLE ty (a INT1, b MIDDLEINT, c FLOAT8, d LONG, e NVARCHAR(5), f CHAR(4) BYTE, g SERIAL);
SHOW CREATE TABLE ty;

CREATE TABLE `ty` (
  `a` tinyint(4) DEFAULT NULL,
  `b` mediumint(9) DEFAULT NULL,
  `c` double DEFAULT NULL,
  `d` mediumtext DEFAULT NULL,
  `e` varchar(5) DEFAULT NULL,
  `f` binary(4) DEFAULT NULL,
  `g` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `g` (`g`)
)

Entiers#

CREATE TABLE mesure (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    quantite    SMALLINT UNSIGNED NOT NULL,
    total       BIGINT,
    actif       BOOLEAN NOT NULL DEFAULT TRUE
);

Une valeur hors plage à l'écriture rend l'erreur 1264 (ER_WARN_DATA_OUT_OF_RANGE) en mode strict ; hors mode strict, elle est ramenée à la borne la plus proche avec un avertissement.

BIGINT UNSIGNED couvre toute la plage 0 à 2⁶⁴ − 1, écrite en littéral (18446744073709551615), en chaîne, en paramètre de requête préparée ou par LOAD DATA. Un calcul entier dont un opérande est BIGINT UNSIGNED est sans signe : un résultat négatif ou au-delà de 2⁶⁴ − 1 rend l'erreur 1690 (BIGINT UNSIGNED value is out of range), par exemple CAST(1 AS UNSIGNED) - 2. CAST(-1 AS UNSIGNED) vaut 18 446 744 073 709 551 615. SUM d'une colonne BIGINT UNSIGNED reste un BIGINT UNSIGNED (erreur 1690 au-delà de 2⁶⁴ − 1) et AVG un DOUBLE.

BOOL / BOOLEAN est un alias de TINYINT(1) : la colonne est un entier d'un octet, affichée comme telle par DESCRIBE et SHOW CREATE TABLE, mais reconnue distinctement par les applications qui interrogent information_schema.COLUMNS (COLUMN_TYPE = tinyint(1)).

BIT est stocké sur 64 bits (BIT sans longueur, ou BIT(n) — la largeur n n'est pas vérifiée : toute valeur qui tient dans 64 bits est acceptée quel que soit n déclaré).

Littéraux de bits (langage MIRAJ) : B'0101', b'0101' et 0b0101 écrivent un champ de bits. C'est une chaîne binaire dont les bits sont cadrés à droite (SELECT B'1000001' rend A, HEX(B'11111111') rend FF, HEX(b'100000001') rend 0101), lue comme un entier sans signe là où un nombre est attendu : dans un calcul (B'101' + 0 vaut 5), face à un nombre dans une comparaison, un IN (…) ou un BETWEEN (colonne = 0b11), en argument d'une fonction mathématique (ABS, BIN, OCT, CONV, ROUND, MOD…), dans un CAST numérique, et écrite par INSERT … VALUES, UPDATE … SET ou ON DUPLICATE KEY UPDATE dans une colonne numérique ou BIT(n). Le littéral hexadécimal 0x41 suit les mêmes règles (0x41 + 0 vaut 65, SELECT 0x41 rend A) ; X'41' reste une chaîne binaire. Elle sert aussi de valeur par défaut (BIT(4) DEFAULT b'1010'). Un chiffre autre que 0 ou 1 entre les apostrophes est une erreur de syntaxe (1064) ; 0b102 est lu comme un identifiant. Une valeur binaire quelconque (X'41', 0x41, colonne VARBINARY) écrite dans une colonne BIT(n) y range ses octets comme champ de bits (1406 en mode strict au-delà de huit octets significatifs). Le langage PostgreSQL garde ses propres chaînes de bits (B'101' de type bit).

Largeur d'affichage INT(n) et ZEROFILL#

CREATE TABLE compteur (
    id   INT(11) PRIMARY KEY,
    code SMALLINT(4) UNSIGNED ZEROFILL
);

Une largeur entre parenthèses (INT(11), TINYINT(1)…) est purement cosmétique : elle ne limite jamais les valeurs stockées (INT(1) accepte sans erreur la valeur 123456) et ne change rien aux calculs. Elle est néanmoins conservée dans la définition de la colonne et rendue telle quelle par DESCRIBE, SHOW COLUMNS, SHOW CREATE TABLE et information_schema.COLUMNS.COLUMN_TYPE. Une largeur non précisée prend la valeur par défaut du type (TINYINT(4), SMALLINT(6), MEDIUMINT(9), INT(11), BIGINT(20), YEAR(4) ; un chiffre de moins côté UNSIGNED, sauf pour BIGINT). Une largeur déclarée au-delà de 255 est refusée par l'erreur 1439 (ER_TOO_BIG_DISPLAYWIDTH). Modifier la largeur seule par ALTER TABLE ... MODIFY ne réécrit pas la table.

ZEROFILL implique UNSIGNED et s'applique aux entiers, à DECIMAL, FLOAT et DOUBLE. La valeur lue d'une telle colonne est complétée de zéros à gauche jusqu'à sa largeur :

Une valeur plus longue que la largeur n'est jamais coupée (123456 dans un INT(5) ZEROFILL). Le remplissage porte sur le texte : résultat envoyé au client (protocole texte, accès en texte de l'API embarquée, SELECT … INTO OUTFILE) et conversions en texte de la colonne — CONCAT(c), CAST(c AS CHAR), LENGTH(c), LPAD, LIKE, REGEXP, GROUP_CONCAT, et valeur écrite dans une colonne de texte par INSERT … SELECT ou par UPDATE … SET texte = c. ZEROFILL sur un type non numérique (VARCHAR(5) ZEROFILL) est une erreur de syntaxe (1064). La valeur reste un nombre pour les calculs (c + 0 vaut 1), les comparaisons, les tris et les regroupements ; une requête préparée la reçoit en nombre, la colonne portant le drapeau ZEROFILL du protocole et sa largeur. SHOW CREATE TABLE, DESCRIBE et information_schema.COLUMNS.COLUMN_TYPE montrent l'attribut (int(5) unsigned zerofill). Il suit la colonne reprise telle quelle par une vue, une table dérivée, une expression de table ou un CREATE TABLE … AS SELECT, mais pas une expression (c + 1, COALESCE(c), MAX(c)) ni une colonne d'union.

Nombres exacts et flottants#

CREATE TABLE prix (
    id       INT PRIMARY KEY,
    montant  DECIMAL(10, 2) NOT NULL,
    taux     FLOAT,
    poids    DOUBLE
);

DECIMAL(p, s) est stocké en arithmétique entière exacte (un entier mis à l'échelle), jamais en virgule flottante :

CREATE TABLE grand (a DECIMAL(65,30), b DECIMAL);
INSERT INTO grand VALUES (12345678901234567890123456789012345.123456789012345678901234567890, 12.6);
SELECT * FROM grand;

+--------------------------------------------------------------------+----+
| a                                                                  | b  |
+--------------------------------------------------------------------+----+
| 12345678901234567890123456789012345.123456789012345678901234567890 | 13 |
+--------------------------------------------------------------------+----+

Une valeur qui a plus de décimales que l'échelle est arrondie (13.6 devient 13 dans un DECIMAL(10,0)) ; une partie entière trop longue rend l'erreur 1264 en mode strict.

FLOAT accepte toute valeur absolue jusqu'à environ 3,402 823 466 × 10³⁸ (erreur 1264 au-delà) ; DOUBLE est un flottant IEEE 754 double précision qui refuse les valeurs non finies (NaN, infinis). Les écritures FLOAT(p), FLOAT(M,D) et DOUBLE(M,D) sont acceptées : la colonne reste un float ou un double (c'est ce que montre SHOW CREATE TABLE) et la valeur n'est pas arrondie à D décimales.

ROUND(x, n) et TRUNCATE(x, n) sur un flottant complètent le résultat à n décimales fixes (zéros compris) quand il est rendu en texte, tant que n ne dépasse pas 9 : CONCAT(ROUND(2.5e0, 3)) vaut 2.500, CONCAT(TRUNCATE(1.5e0, 2)) vaut 1.50 ; au-delà, le résultat reprend son format naturel.

Chaînes de caractères#

CREATE TABLE client (
    id     INT PRIMARY KEY AUTO_INCREMENT,
    nom    VARCHAR(80) NOT NULL,
    notes  TEXT
);

La longueur de CHAR et VARCHAR est comptée en caractères Unicode, pas en octets ; une valeur trop longue rend l'erreur 1406 (ER_DATA_TOO_LONG). VARCHAR sans longueur vaut VARCHAR(255). Une longueur nulle (CHAR(0), VARCHAR(0), BINARY(0), VARBINARY(0)) donne une colonne qui n'accepte que la chaîne vide et NULL. Une précision entre parenthèses sur TEXT et ses variantes (TEXT(100), TINYTEXT(10)…) est acceptée à l'analyse mais sans effet : ces colonnes ne sont bornées que par la limite générale de 2 Go par valeur.

LONG et LONG VARCHAR sont des synonymes acceptés de MEDIUMTEXT, LONG VARBINARY de MEDIUMBLOB.

Une valeur CHAR(n) est rangée et relue sans ses espaces finaux. Sous le sql_mode PAD_CHAR_TO_FULL_LENGTH, une colonne de table CHAR(n) est lue complétée par des espaces jusqu'à n caractères : SELECT c, *, expressions (CONCAT('[', c, ']') donne [ab ] pour 'ab' dans un CHAR(5), LENGTH(c) 5), LIKE (c LIKE 'ab' devient faux), GROUP BY, DISTINCT, vues, tables dérivées, sous-requêtes et instructions préparées. Les comparaisons ignorant les espaces finaux, c = 'ab' reste vrai, index compris ; NULL reste NULL, un VARCHAR ou un BINARY(n) (complété de 0x00) ne changent pas. Le corps d'une routine, d'un déclencheur ou d'un événement suit le sql_mode gardé à sa création, pas celui de l'appelant. Une vue CACHED garde son résultat figé, ses colonnes CHAR(n) complétées à la lecture.

Chaque colonne texte (CHAR, VARCHAR, *TEXT, ENUM, SET) a un interclassement qui règle ses comparaisons, son tri et ses clés : utf8mb4_general_ci par défaut (sans casse ni accents), utf8mb4_unicode_ci (sans casse ni accents, 'ß' = 'ss') ou utf8mb4_bin (par point de code, 'A1' ≠ 'a1'), déclaré par [CHARACTER SET utf8mb4] COLLATE nom après le type ou par COLLATE= sur la table. Voir Interclassements.

Chaînes binaires et BLOB#

CREATE TABLE document (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    empreinte BINARY(32),
    contenu  LONGBLOB
);

BINARY(n) et VARBINARY(n) rangent des octets bruts sur n octets au plus (2 147 483 647 octets au maximum déclarable) ; erreur 1406 en cas de dépassement. Une valeur plus courte écrite dans un BINARY(n) est complétée par des octets 0x00 ('a' dans un BINARY(2) est relu HEX = 6100, LENGTH = 2).

TINYBLOB, BLOB, MEDIUMBLOB et LONGBLOB partagent la même limite de 2 Go par valeur. Une valeur BLOB d'au moins --lob-threshold octets (8192 par défaut) est rangée à part, dans le magasin de LOB de la table (<table>.bmrj) ; la table elle-même ne garde qu'une référence de 16 octets (les valeurs déjà stockées rejoignent le magasin à l'ouverture suivante quand le seuil est abaissé ; sur le primaire d'un cluster, au-delà de 256 Mio à déplacer par table, elles restent dans la table). Ce détail de stockage est transparent pour les requêtes.

Clés sur les colonnes longues (TEXT, BLOB, VARCHAR long) :

CREATE TABLE fiche (id INT PRIMARY KEY, slug VARCHAR(300), contenu BLOB,
                    KEY (slug(191)), UNIQUE (contenu));
SHOW CREATE TABLE fiche;

CREATE TABLE `fiche` (
  `id` int(11) NOT NULL,
  `slug` varchar(300) DEFAULT NULL,
  `contenu` blob DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `contenu` (`contenu`) USING HASH,
  KEY `slug` (`slug`(191))
)

Dates et heures#

CREATE TABLE evenement (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    jour       DATE NOT NULL,
    debut      TIME,
    cree_le    DATETIME DEFAULT CURRENT_TIMESTAMP,
    maj_le     DATETIME ON UPDATE CURRENT_TIMESTAMP
);

Une date impossible ('2026-02-30') ou une heure hors plage ('839:00:00') rend l'erreur 1366 en mode strict. Hors mode strict, une date invalide ('pas une date', '2026-02-30') est remplacée par la date zéro (0000-00-00) avec l'avertissement 1265 (« Data truncated for column 'd' at row 1 »). Une année écrite sur deux chiffres dans une colonne YEAR suit la règle de CAST(… AS YEAR) ci-dessous (69 donne 2069, 70 donne 1970, '05' donne 2005).

Fractions de seconde. TIME(n), DATETIME(n) et TIMESTAMP(n) (n de 0 à 6, 1426 au-delà) gardent n chiffres de fraction de seconde. La précision est conservée dans le schéma, rendue par SHOW CREATE TABLE, DESCRIBE et information_schema.COLUMNS (DATETIME_PRECISION), puis par le protocole. Une valeur écrite avec plus de chiffres est tronquée à la précision de la colonne (arrondie sous le sql_mode TIME_ROUND_FRACTIONAL) et rendue avec exactement n chiffres ; sans précision, la colonne est à la seconde. NOW(n), CURTIME(n), SYSDATE(n) et UTC_TIMESTAMP(n) rendent l'instant à la précision demandée, et DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP écrivent l'instant à celle de la colonne. La fraction est exacte à la microseconde pour les années 1720 à 2079. La dernière valeur représentable, '9999-12-31 23:59:59.999999', est toutefois relue à l'identique (comparaison d'égalité comprise), et l'arrondi de TIME_ROUND_FRACTIONAL ne dépasse jamais le 9999-12-31 : cette valeur rangée dans un DATETIME devient 9999-12-31 23:59:59, dans un DATETIME(3) 9999-12-31 23:59:59.999.

CREATE TABLE mesure_t (id INT PRIMARY KEY, t DATETIME(3));
INSERT INTO mesure_t VALUES (1, '2026-01-01 10:00:00.1234');
SELECT t FROM mesure_t;        -- 2026-01-01 10:00:00.123
CREATE TABLE trop_fin (t TIME(7));
-- ERREUR 1426 (42000) : Too big precision specified for 't'. Maximum is 6

Fuseau horaire. TIMESTAMP est un instant : il est lu et affiché dans le time_zone de la session, alors que DATETIME est une date et une heure sans fuseau, rendues telles qu'écrites.

CREATE TABLE t (ts TIMESTAMP, dt DATETIME);
SET time_zone = '+00:00';
INSERT INTO t (ts, dt) VALUES ('2026-06-01 12:00:00', '2026-06-01 12:00:00');
SET time_zone = '+05:00';
SELECT ts, dt FROM t;          -- 2026-06-01 17:00:00 | 2026-06-01 12:00:00

time_zone vaut SYSTEM (fuseau du serveur) par défaut ; elle se fixe pour la session ou, avec SET GLOBAL, pour les sessions suivantes, par un décalage ('+02:00', de -13:59 à +14:00) ou un nom IANA ('Europe/Paris', 'UTC'). Une valeur inconnue rend l'erreur 1298. Les comparaisons, CAST(ts AS DATETIME), UNIX_TIMESTAMP, FROM_UNIXTIME et les fonctions d'extraction (HOUR(ts)…) suivent le fuseau de la session ; CONVERT_TZ est décrit au chapitre 10.

CAST(expr AS YEAR) rend une année de 1901 à 2155 (ou 0) : 69 donne 2069, 70 donne 1970, une valeur hors plage rend NULL.

Année zéro. Une colonne YEAR lue telle quelle s'écrit toujours sur quatre chiffres : l'année zéro y vaut 0000 (SELECT y, CONCAT(y), CAST(y AS CHAR), GROUP_CONCAT, table dérivée, union) ; une expression de type YEAR (MIN(y), COALESCE(y), IF(c, y1, y2), sous-requête, CAST(0 AS YEAR)) la rend 0. COALESCE / IF d'années restent du type year(4) (CREATE TABLE … AS SELECT). Comparé à une colonne YEAR, un texte constant est lu comme l'année qu'il donnerait écrit dans la colonne : y = '0' et y = '00' désignent 2000 (et sont faux pour l'année zéro), y = '0000' l'année zéro ; un texte que l'écriture refuserait ('abc', '1800') est comparé en nombre. Un entier de forme AAAAMMJJhhmmss se convertit par CAST(… AS DATETIME) (CAST(20260601120000 AS DATETIME) donne 2026-06-01 12:00:00).

Dates écrites sous une forme libre. Un texte est lu comme une date ou une date-heure avec une ponctuation quelconque (2020.01.01, 2020/01/01T10.00.00, 2020_01_01 10_00_00), une année sur deux chiffres (20-01-01) ou une forme compacte (200101, 20200101, 200101100000, 20200101100000, avec ou sans fraction de seconde). Cette lecture vaut à l'écriture (INSERT, UPDATE, LOAD DATA, colonnes DATE, DATETIME et TIMESTAMP), pour CAST(texte AS DATE | DATETIME) et pour les arguments texte des fonctions de date (DATE_ADD, DATEDIFF, DAYNAME, YEAR, TO_DAYS…), sans avertissement. Un texte illisible, ou suivi d'autre chose qu'une date, reste refusé à l'écriture (erreur 1366 en mode strict) et CAST rend NULL. Les fonctions d'heure (HOUR, CAST(… AS TIME)) ne suivent pas cette lecture : '200101' y est une heure, pas une date.

INSERT INTO evenement (jour, cree_le) VALUES ('200101', '2020.01.01 10.00.00');
SELECT CAST('20200101100000' AS DATETIME);   -- 2020-01-01 10:00:00
SELECT DATE_ADD('200101', INTERVAL 1 DAY);   -- 2020-01-02

Comparaison d'une date avec un texte. Face à une colonne ou une valeur DATE / DATETIME / TIMESTAMP, le texte est lu comme une date selon les mêmes règles : jour = '200101' et jour = '2020.01.01' sont vrais pour le 1er janvier 2020, avec ou sans index sur la colonne. Un texte qui ne se lit pas entièrement garde son plus long début lisible ('2020-01-01x' vaut le 1er janvier 2020) ; sans début lisible il vaut 0000-00-00 00:00:00. Dans les deux cas la requête réussit et un avertissement 1292 est enregistré (visible par SHOW WARNINGS) : « Truncated incorrect datetime value: 'x' » pour une constante, « Incorrect datetime value: 'x' for column … at row 1 » pour une colonne de texte comparée à une date. Face à un TIME, une colonne de texte illisible donne de même « Incorrect time value: 'x' for column base.table.s at row 1 ».

La clause ON UPDATE CURRENT_TIMESTAMP (ou l'un de ses synonymes NOW, LOCALTIME, LOCALTIMESTAMP, avec ou sans précision) est appliquée : voir la section correspondante plus bas.

ENUM et SET#

CREATE TABLE commande (
    id     INT PRIMARY KEY AUTO_INCREMENT,
    statut ENUM('nouvelle', 'expediee', 'annulee') NOT NULL DEFAULT 'nouvelle',
    options SET('urgent', 'fragile', 'retour')
);

ENUM range en texte la valeur choisie (comparaison insensible à la casse, aux accents et aux espaces finaux) ; en contexte numérique (opération arithmétique sur la colonne seule, comparaison avec un nombre, CAST numérique, ORDER BY), la colonne vaut le rang du membre choisi (1 pour le premier). Un nombre écrit dans la colonne désigne le membre de ce rang. Une valeur hors liste rend l'erreur 1265 en mode strict (chaîne vide, de rang 0, et avertissement hors mode strict). Un nombre illimité de membres est accepté ; des membres en double rendent l'erreur 1291, un DEFAULT hors liste l'erreur 1067. Une colonne ENUM … NOT NULL sans DEFAULT prend son premier membre quand l'écriture ne la fournit pas. Reprise telle quelle par une vue, une table dérivée ou une expression de table (WITH), la colonne se trie toujours par son rang et vaut ce rang en contexte numérique.

SET fonctionne de même mais range un ensemble de membres (masque de bits à l'écrit, membres relus dans l'ordre de la déclaration) ; 64 membres au maximum (au-delà : erreur 1097). Une virgule dans un membre est refusée (erreur 1367).

INSERT INTO commande (options) VALUES ('fragile,urgent');
INSERT INTO commande (statut, options) VALUES ('EXPEDIEE ', 'retour'), (3, 3);
SELECT id, statut, statut + 0, options, options + 0 FROM commande ORDER BY statut;

+----+----------+------------+----------------+-------------+
| id | statut   | statut + 0 | options        | options + 0 |
+----+----------+------------+----------------+-------------+
|  1 | nouvelle |          1 | urgent,fragile |           3 |
|  2 | expediee |          2 | retour         |           4 |
|  3 | annulee  |          3 | urgent,fragile |           3 |
+----+----------+------------+----------------+-------------+

JSON et UUID#

JSON et UUID se rangent comme des chaînes de caractères.

CREATE TABLE profil (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    identifiant UUID,
    parametres  JSON
);
SHOW CREATE TABLE profil;

CREATE TABLE `profil` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `identifiant` uuid DEFAULT NULL,
  `parametres` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`parametres`)),
  PRIMARY KEY (`id`)
)

Une colonne JSON est un texte long (longtext) en interclassement utf8mb4_bin, muni d'une contrainte implicite CHECK (json_valid(col)) : c'est ainsi que la décrivent SHOW CREATE TABLE, DESCRIBE et information_schema.COLUMNS (DATA_TYPE = longtext), et la contrainte figure dans information_schema.CHECK_CONSTRAINTS sous le nom de la colonne. Un texte qui n'est pas du JSON est refusé par l'erreur 4025, même hors mode strict (avertissement avec IGNORE), à l'INSERT, à l'UPDATE, au REPLACE, au LOAD DATA et à un ALTER TABLE … MODIFY vers JSON :

INSERT INTO profil (parametres) VALUES ('pas du json');
-- ERREUR 4025 (23000) : CONSTRAINT `profil.parametres` failed for `d`.`profil`
INSERT INTO profil (parametres) VALUES ('{"a": 1, "b": [1,2]}');
SELECT parametres->'$.b', parametres->>'$.a', JSON_ARRAY(parametres) FROM profil;

+-------------------+--------------------+------------------------+
| parametres->'$.b' | parametres->>'$.a' | JSON_ARRAY(parametres) |
+-------------------+--------------------+------------------------+
| [1, 2]            | 1                  | [{"a": 1, "b": [1,2]}] |
+-------------------+--------------------+------------------------+

Une contrainte CHECK écrite sur la colonne elle-même remplace la contrainte implicite ; celle-ci ne se retire pas par DROP CONSTRAINT (1091). CREATE TABLE … AS SELECT d'une colonne JSON donne un longtext utf8mb4_bin sans la contrainte.

Le document est rangé tel qu'écrit. Lu par une fonction JSON, il est traité comme un document (et non comme un texte à mettre entre guillemets) : JSON_ARRAY(doc) ou JSON_SET(autre, '$.x', doc) imbriquent son contenu. Ses parties se lisent par les opérateurs doc->'$.chemin' et doc->>'$.chemin', et se modifient par JSON_SET, JSON_REMOVE... (voir 10.10 Fonctions JSON). L'interclassement utf8mb4_bin distingue deux documents qui ne diffèrent que par la casse ('{"a":"x"}' ≠ '{"a":"X"}').

JSONB (nom du langage PostgreSQL, non réservé : jsonb reste un nom de colonne possible) est un synonyme exact de JSON : même stockage, mêmes fonctions et opérateurs, même interclassement, mêmes index, même contrôle json_valid (4025). Le nom écrit est toutefois gardé avec la colonne : SHOW CREATE TABLE et DESCRIBE l'affichent jsonb, information_schema.COLUMNS le donne dans COLUMN_TYPE (DATA_TYPE vaut json), et il suit la table dans un fichier de sauvegarde, une réplique du cluster (texte du DDL rejoué), CREATE TABLE … LIKE et miraj-dump. ALTER TABLE … MODIFY col JSON ou MODIFY col JSONB passe d'un nom à l'autre sans toucher aux données. En langage PostgreSQL, une colonne jsonb est annoncée jsonb (OID 3802) et une colonne json reste json (OID 114) ; voir 30.3.

Une colonne UUID range le texte de l'identifiant tel qu'écrit et le compare sans tenir compte de la casse ('123E4567-…' est trouvé par = '123e4567-…') ; information_schema.COLUMNS la décrit par son propre nom (uuid).

CAST(expr AS JSON) (ou AS JSONB, identique) est disponible. DEFAULT UUID(), DEFAULT UUID_V4() (UUID aléatoire), DEFAULT UUID_V7() (UUID ordonné dans le temps) et DEFAULT UUID_SHORT() sont acceptés comme valeur par défaut d'une colonne (voir plus bas).

Colonnes dynamiques#

Les colonnes dynamiques rangent un jeu de paires clé / valeur dans une seule colonne BLOB, au format binaire standard des colonnes dynamiques : un tel blob s'échange tel quel avec les autres serveurs qui l'emploient. Les clés sont des numéros ou des noms ; chaque valeur a un type (INT, UINT, DOUBLE, DECIMAL(p,s), CHAR, BINARY, DATE, TIME, DATETIME, ou un autre jeu dynamique).

CREATE TABLE produit (id INT PRIMARY KEY, attrs BLOB);
INSERT INTO produit VALUES (1, COLUMN_CREATE('couleur', 'rouge', 'poids', 2.5 AS DOUBLE));
UPDATE produit SET attrs = COLUMN_ADD(attrs, 'taille', 42) WHERE id = 1;
SELECT COLUMN_GET(attrs, 'poids' AS DOUBLE), COLUMN_LIST(attrs), COLUMN_JSON(attrs) FROM produit;
UPDATE produit SET attrs = COLUMN_DELETE(attrs, 'couleur') WHERE id = 1;
SELECT COLUMN_EXISTS(attrs, 'couleur'), COLUMN_CHECK(attrs), COLUMN_JSON(attrs) FROM produit;

+--------------------------------------+----------------------------+---------------------------------------------+
| COLUMN_GET(attrs, 'poids' AS DOUBLE) | COLUMN_LIST(attrs)         | COLUMN_JSON(attrs)                          |
+--------------------------------------+----------------------------+---------------------------------------------+
|                                  2.5 | `poids`,`taille`,`couleur` | {"poids":2.5,"taille":42,"couleur":"rouge"} |
+--------------------------------------+----------------------------+---------------------------------------------+
+---------------------------------+---------------------+---------------------------+
| COLUMN_EXISTS(attrs, 'couleur') | COLUMN_CHECK(attrs) | COLUMN_JSON(attrs)        |
+---------------------------------+---------------------+---------------------------+
|                               0 |                   1 | {"poids":2.5,"taille":42} |
+---------------------------------+---------------------+---------------------------+

Un blob invalide ou une clé en double rend NULL.

Types spatiaux#

CREATE TABLE lieu (
    id    INT PRIMARY KEY,
    nom   VARCHAR(40),
    pos   POINT NOT NULL,
    zone  POLYGON,
    SPATIAL INDEX sp (pos)
);
INSERT INTO lieu VALUES (1, 'Alger', ST_GeomFromText('POINT(3.06 36.75)', 4326), NULL);
INSERT INTO lieu VALUES (2, 'Oran', POINT(-0.63, 35.7), ST_GeomFromText('POLYGON((0 0,1 0,1 1,0 0))'));
SELECT id, nom, ST_AsText(pos), ST_SRID(pos) FROM lieu;

+----+-------+-------------------+--------------+
| id | nom   | ST_AsText(pos)    | ST_SRID(pos) |
+----+-------+-------------------+--------------+
|  1 | Alger | POINT(3.06 36.75) |         4326 |
|  2 | Oran  | POINT(-0.63 35.7) |            0 |
+----+-------+-------------------+--------------+

GEOMETRY accepte toute géométrie ; POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON et GEOMETRYCOLLECTION n'acceptent que leur propre sorte. Une valeur se range comme le SRID (4 octets) suivi de la géométrie au format WKB ; elle s'écrit par les fonctions de construction (ST_GeomFromText, ST_GeomFromWKB, ST_GeomFromGeoJSON, POINT(x, y)…) et se relit par ST_AsText, ST_AsGeoJSON… (voir 10.17 Fonctions spatiales).

information_schema.COLUMNS donne le nom du type (point, polygon…) dans DATA_TYPE. Un index SPATIAL (une seule colonne géométrique NOT NULL) sert les prédicats de recherche par zone (MBRContains, ST_Intersects…) : voir 10.17, « Index spatial ».

VECTOR(n)#

CREATE TABLE embedding (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    vecteur   VECTOR(384) NOT NULL
);

VECTOR(n) range n flottants 32 bits (1 ≤ n ≤ 16 383), jamais compressés par dictionnaire, écrits sous forme texte '[0.1, 0.2, ...]' (ou par VEC_FromText) ou binaire (4 octets par composante, petit-boutiste). La dimension déclarée doit être respectée exactement à l'écriture :

Hors mode strict, une valeur refusée en 1292 devient un vecteur nul (toutes composantes à 0) avec l'avertissement 1292 Incorrect vector value: '…' for column … at row n (octets montrés en \xNN, caractères de contrôle d'un texte en \XXXX) ; un nombre affecté donne l'avertissement 4078 (Cannot cast 'int' as 'vector' in assignment of …) suivi du 1292. Seuls des octets (littéral x'…', UNHEX, colonne binaire) ou le texte […] forment un vecteur ; dans LOAD DATA, un champ est lu comme octets avec CHARACTER SET binary, sinon comme texte. Une valeur par défaut x'…' ou 0x… d'une colonne VECTOR (comme d'une colonne binaire ou texte) vaut ses octets, contrôlés comme une valeur écrite (1067 sinon), et information_schema.COLUMNS l'affiche x'…' en minuscules.

CREATE TABLE emb (id INT PRIMARY KEY AUTO_INCREMENT, v VECTOR(3) NOT NULL);
INSERT INTO emb (v) VALUES (VEC_FromText('[0.1, 0.2, 0.3]'));
INSERT INTO emb (v) VALUES ('[1,2]');
-- ERREUR 7601 (22000) : expected 3 dimensions, not 2
SELECT id, VEC_ToText(v), HEX(v) FROM emb;

+----+---------------+--------------------------+
| id | VEC_ToText(v) | HEX(v)                   |
+----+---------------+--------------------------+
|  1 | [0.1,0.2,0.3] | CDCCCC3DCDCC4C3E9A99993E |
+----+---------------+--------------------------+

Une colonne VECTOR peut porter une clé primaire, une contrainte UNIQUE ou un index ordinaire (KEY), qui comparent les vecteurs octet par octet (un préfixe v(n) rend l'erreur 1089), et un index vectoriel (VECTOR INDEX, colonne NOT NULL). Sans index vectoriel, ORDER BY distance_vectorielle(...) LIMIT k calcule les k plus proches voisins exacts ; avec lui, une recherche approchée bien plus rapide (voir le chapitre 11).

Conversions : CAST et CONVERT#

SELECT CAST('42' AS SIGNED), CAST(3.7 AS DECIMAL(10,2)), CONVERT(dt, CHAR);
SELECT CONVERT('texte' USING utf8mb4);

CAST(expr AS type) et CONVERT(expr, type) acceptent les types cibles suivants : SIGNED [INTEGER], UNSIGNED [INTEGER], CHAR[(n)] (et ses synonymes NCHAR, VARCHAR), BINARY[(n)], DECIMAL[(p, s)], DATE, DATETIME[(n)], TIME[(n)], DOUBLE / FLOAT / REAL, JSON / JSONB, TEXT (converti vers VARCHAR), YEAR, VECTOR[(n)], INTEGER / INT (converti vers BIGINT). CHAR(n) coupe un texte plus long avec l'avertissement 1292 (CAST(12 AS CHAR(1)) vaut 1) ; BINARY(n) complète un texte plus court par des octets 0x00 (CAST(1 AS BINARY(3)) = X'310000' est vrai). Un nombre converti en entier est arrondi (CAST(1.5 AS INTEGER) vaut 2, CAST(3.9 AS UNSIGNED) vaut 4). CONVERT(expr USING jeu) change le jeu de caractères logique de l'expression. Une conversion implicite a lieu automatiquement dans les comparaisons et opérations mixtes (par exemple une chaîne numérique comparée à un entier) selon les mêmes règles qu'un serveur SQL courant.

Un texte converti en SIGNED ou UNSIGNED est lu par ses chiffres de tête seuls : ni fraction ni exposant ne sont pris (CAST('1.5' AS SIGNED) et CAST('1e3' AS SIGNED) valent 1, avec l'avertissement 1292), alors que DECIMAL et DOUBLE lisent l'exposant (CAST('1e3' AS DECIMAL(10,2)) = 1000.00).

NULL#

Toute colonne est nullable par défaut (NULL implicite), sauf :

• une colonne portant la clause NOT NULL explicite ;
• une colonne de clé primaire, qui est implicitement NOT NULL et perd un éventuel DEFAULT NULL déclaré avec elle.

NULL participe à la logique à trois valeurs (TRUE, FALSE, UNKNOWN) dans toute expression booléenne : une comparaison avec NULL (hors IS NULL / IS NOT NULL) rend UNKNOWN, jamais TRUE ni FALSE.

DEFAULT#

CREATE TABLE article (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    reference   VARCHAR(20) NOT NULL,
    cree_le     DATETIME DEFAULT CURRENT_TIMESTAMP,
    identifiant CHAR(36) DEFAULT (UUID()),
    remise      DECIMAL(5,2) DEFAULT 0.00,
    hachage     VARCHAR(32) DEFAULT (MD5(CONCAT(reference, remise)))
);

Formes acceptées pour une valeur par défaut :

Une valeur par défaut sous forme d'expression est compilée à l'écriture et évaluée ligne par ligne à chaque insertion qui ne fournit pas la colonne ; elle peut citer les autres colonnes de la même ligne, y compris des colonnes déclarées plus loin dans la table. Contrairement à une colonne générée, la valeur ainsi posée reste ensuite modifiable normalement par un UPDATE.

Sont refusés à la définition d'un DEFAULT expression :

SHOW CREATE TABLE rend chaque forme telle qu'elle a été déclarée :

CREATE TABLE d8 (a VARCHAR(50) DEFAULT @@version_comment, b INT DEFAULT (1+1), c DATE DEFAULT CURDATE(),
                 d TIME DEFAULT CURTIME, e DATETIME(3) DEFAULT NOW(3) ON UPDATE NOW(3),
                 f VARBINARY(4) DEFAULT X'4142', h TEXT DEFAULT 'texte');
SHOW CREATE TABLE d8;

CREATE TABLE `d8` (
  `a` varchar(50) DEFAULT @@version_comment,
  `b` int(11) DEFAULT (1+1),
  `c` date DEFAULT CURDATE(),
  `d` time DEFAULT CURTIME,
  `e` datetime(3) DEFAULT NOW(3) ON UPDATE current_timestamp(3),
  `f` varbinary(4) DEFAULT x'4142',
  `h` text DEFAULT 'texte'
)

ALTER TABLE ... ADD COLUMN avec une valeur par défaut expression remplit les lignes existantes avec la valeur recalculée sur chacune (ADD COLUMN h CHAR(32) DEFAULT (MD5(nom)) donne à chaque ligne l'empreinte de son propre nom). ALTER TABLE ... ALTER COLUMN ... SET DEFAULT en revanche ne recalcule jamais les lignes existantes.

Une colonne de clé primaire est implicitement NOT NULL et perd un DEFAULT NULL porté avec elle ; une ligne existante qui y porterait NULL fait échouer l'instruction avec l'erreur 1265.

ON UPDATE CURRENT_TIMESTAMP#

CREATE TABLE session_utilisateur (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    cree_le   DATETIME DEFAULT CURRENT_TIMESTAMP,
    maj_le    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Une colonne DATETIME ou TIMESTAMP peut porter ON UPDATE CURRENT_TIMESTAMP[(n)] (ou l'un des synonymes NOW, LOCALTIME, LOCALTIMESTAMP, avec ou sans précision fractionnaire ; SHOW CREATE TABLE l'affiche ON UPDATE current_timestamp(n)). La colonne prend l'instant de l'instruction, à la précision de la colonne, à chaque écriture qui modifie effectivement la ligne sans lui donner de valeur ; une ligne inchangée (SET n = n) garde son instant. Une valeur explicite (fournie par le SET d'un UPDATE, ou posée par un déclencheur BEFORE UPDATE) l'emporte toujours sur la clause. Cette clause est refusée sur un type qui n'est ni DATETIME ni TIMESTAMP (erreur 1294) et sur une colonne générée (erreur 3106).

AUTO_INCREMENT#

CREATE TABLE facture (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    numero   VARCHAR(20) NOT NULL
) AUTO_INCREMENT = 1000;

AUTO_INCREMENT se déclare sur une colonne entière ; le compteur est persistant (conservé dans le fichier de la table) et n'est jamais restauré à sa valeur précédente par un ROLLBACK. Il peut être fixé ou relevé explicitement :

ALTER TABLE facture AUTO_INCREMENT = 5000;

Une valeur AUTO_INCREMENT ne peut pas être citée dans une expression DEFAULT (erreur 3769) ni dans l'expression d'une colonne générée (erreur 3109).

Colonnes générées#

CREATE TABLE ligne_facture (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    prix_unit   DECIMAL(10,2) NOT NULL,
    quantite    INT NOT NULL,
    -- calculée à chaque lecture, rien n'est stocké
    total_ht    DECIMAL(12,2) GENERATED ALWAYS AS (prix_unit * quantite) VIRTUAL,
    -- calculée à chaque écriture puis stockée
    total_ttc   DECIMAL(12,2) AS (total_ht * 1.20) STORED
);

Syntaxe : col type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED | PERSISTENT]. VIRTUAL est la nature par défaut si aucune n'est écrite ; STORED et PERSISTENT sont synonymes.

Une colonne VIRTUAL accepte un index secondaire non unique (KEY / INDEX) à condition que son expression soit déterministe et ne cite aucune colonne BLOB (sinon erreur 3106) ; une expression calculée sur NOW() ou CURDATE() reste hors index pour la même raison. En revanche, clé primaire, UNIQUE et clé étrangère sur une colonne VIRTUAL restent toujours refusées (erreur 3106), quelle que soit l'expression — seule une colonne STORED / PERSISTENT peut porter ce type de contrainte.

Une valeur explicite donnée à l'écriture pour une colonne générée (INSERT, UPDATE...) est ignorée silencieusement (ni erreur, ni avertissement) : la colonne est de toute façon recalculée.

Refusés à la création d'une colonne générée :

SHOW CREATE TABLE, DESCRIBE et information_schema.COLUMNS distinguent les deux natures dans la colonne EXTRA (VIRTUAL GENERATED / STORED GENERATED) et rendent l'expression source dans GENERATION_EXPRESSION.

Contraintes CHECK#

CREATE TABLE compte (
    id      INT PRIMARY KEY,
    solde   DECIMAL(12,2) NOT NULL,
    CONSTRAINT solde_positif CHECK (solde >= 0)
);

Une contrainte CHECK peut être de colonne (portée par une seule colonne, nommée implicitement d'après elle) ou de table (nommée ou générée CONSTRAINT_<n>). L'expression est évaluée sur la ligne complète avant chaque écriture ; une évaluation à FALSE rend l'erreur 4025, NULL est accepté (comme un CHECK toujours vrai). NOT ENFORCED la range sans jamais l'évaluer.

Refusés à la création d'un CHECK : sous-requête (3815), fonction non déterministe (3814), variable (3816), colonne AUTO_INCREMENT (3818), contrainte de colonne qui cite une autre colonne (3813), agrégat (1111).

Voir aussi#

Le chapitre Langage DDL documente la syntaxe complète de CREATE TABLE, ALTER TABLE et des contraintes (clé primaire, UNIQUE, clé étrangère).