4. 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 :

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.

Limite connue — BIGINT UNSIGNED : la référence autorise en théorie BIGINT UNSIGNED jusqu'à 2⁶⁴ − 1. MIRAJ range en interne tous les entiers sur un i64 signé : les valeurs comprises entre 2⁶³ et 2⁶⁴ − 1 sont donc refusées à l'écriture (erreur 1264), même en colonne UNSIGNED. Une application qui a réellement besoin de cette plage haute ne peut pas être portée telle quelle sur MIRAJ 1.0.

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é).

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, comme le fait un serveur de référence récent. 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). ZEROFILL est accepté mais traité comme un simple UNSIGNED : le remplissage de zéros à l'affichage n'est pas produit. Modifier la largeur seule par ALTER TABLE ... MODIFY ne réécrit pas la table.

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 :

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).

ROUND(x, n) et TRUNCATE(x, n) sur un flottant complètent le résultat à n décimales fixes (zéros compris) tant que n ne dépasse pas 9 (la même limite que l'échelle d'un DECIMAL) ; au-delà, le résultat reprend son format naturel sans décimales fixes imposées, comme le fait la référence au-delà de sa propre limite interne.

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). 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 VARCHAR est un synonyme accepté de MEDIUMTEXT.

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.

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 externalisée 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, mais explique pourquoi une colonne BLOB (comme TEXT, du côté chaîne) ne peut jamais faire partie d'une clé — clé primaire, UNIQUE ou index — ce qui rend l'erreur 1170 (ER_BLOB_KEY_WITHOUT_LENGTH) sans prise en charge de préfixe d'index (col(n)).

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
);

DATETIME (et TIMESTAMP, qui s'y comporte de façon identique) est stocké en interne en virgule flottante double précision (nombre de jours), avec une précision physique à la milliseconde, mais rendu à la seconde dans les résultats.

Limite connue — précision fractionnaire DATETIME(n) / TIMESTAMP(n) : la syntaxe DATETIME(n) ou TIMESTAMP(n) (0 à 6 chiffres de fraction de seconde) est acceptée par l'analyseur, y compris après DEFAULT et après ON UPDATE (CURRENT_TIMESTAMP(n), NOW(n), LOCALTIME(n), LOCALTIMESTAMP(n)), mais la précision déclarée n'a aucun effet sur ce qui est réellement stocké ou affiché. Une précision incohérente avec celle de la colonne est acceptée sans avertissement, là où un serveur de référence renverrait une erreur de syntaxe. N'attendez donc pas de fractions de seconde fiables au-delà de la milliseconde physiquement stockée, quelle que soit la précision écrite dans la définition de la colonne.

La clause ON UPDATE CURRENT_TIMESTAMP (ou l'un de ses synonymes NOW, LOCALTIME, LOCALTIMESTAMP, avec ou sans précision) est en revanche pleinement 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 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). Une valeur hors liste rend l'erreur 1265 en mode strict (chaîne vide et avertissement hors mode strict). Un nombre illimité de membres est accepté ; des membres en double rendent l'erreur 1291.

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

JSON et UUID#

JSON et UUID sont reconnus comme types de colonne à part entière et rangés comme des chaînes de caractères (information_schema.COLUMNS les décrit par leur propre nom).

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

CAST(expr AS JSON) est disponible. DEFAULT UUID() et DEFAULT UUID_SHORT() sont acceptés comme valeur par défaut d'une colonne (voir plus bas).

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, sous forme texte [0.1, 0.2, ...] ou binaire. La dimension déclarée doit être respectée exactement à l'écriture (erreur 7601) ; les valeurs NaN et infinies sont refusées (7603, 7604). Une colonne VECTOR ne peut pas porter de clé primaire, de contrainte UNIQUE ni d'index ordinaire (erreur 6133), mais un index vectoriel (VECTOR INDEX, colonne NOT NULL). Sans lui, 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 19).

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, TIME, DOUBLE / FLOAT / REAL, JSON, TEXT (converti vers VARCHAR), VECTOR[(n)], INTEGER / INT (converti vers BIGINT). 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.

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 :

ALTER TABLE ... ADD COLUMN avec une telle valeur par défaut remplit les lignes existantes avec la valeur recalculée sur chacune — un comportement pratique pour les migrations, que la documentation d'un serveur de référence ne décrit généralement pas. 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 — cette précision reste, comme partout ailleurs sur ces types, sans effet réel). La colonne prend l'instant de l'instruction à chaque écriture qui modifie effectivement la ligne sans lui donner de valeur ; une ligne inchangée 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).