4. Data Types

MIRAJ stores each column value in a typed vector, with a separate NULL bitmap (no reserved value represents NULL). The type dialect, its ranges, and its conversions are aligned with the most widespread SQL servers, so that existing applications and tools (connectors, ORMs, administration tools) work without modification.

Overview of type families#

Each type is recognized regardless of the case of its keyword. A few legacy synonyms are accepted by the parser and converted to the corresponding MIRAJ type:

Integers#

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

An out-of-range value on write returns error 1264 (ER_WARN_DATA_OUT_OF_RANGE) in strict mode.

Known limitation — BIGINT UNSIGNED: the reference allows BIGINT UNSIGNED in theory up to 2⁶⁴ − 1. MIRAJ internally stores all integers in a signed i64: values between 2⁶³ and 2⁶⁴ − 1 are therefore rejected on write (error 1264), even in an UNSIGNED column. An application that genuinely needs this upper range cannot be ported as-is to MIRAJ 1.0.

BOOL / BOOLEAN is an alias for TINYINT(1): the column is a one-byte integer, displayed as such by DESCRIBE and SHOW CREATE TABLE, but recognized distinctly by applications that query information_schema.COLUMNS (COLUMN_TYPE = tinyint(1)).

BIT is stored on 64 bits (BIT without a length, or BIT(n) — the width n is not checked: any value that fits in 64 bits is accepted regardless of the declared n).

Display width INT(n) and ZEROFILL#

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

A width in parentheses (INT(11), TINYINT(1)…) is purely cosmetic: it never limits the stored values (INT(1) accepts the value 123456 without error) and does not change any calculation. It is nevertheless kept in the column definition and rendered as-is by DESCRIBE, SHOW COLUMNS, SHOW CREATE TABLE, and information_schema.COLUMNS.COLUMN_TYPE, as a recent reference server does. A width that is not specified takes the type's default value (TINYINT(4), SMALLINT(6), MEDIUMINT(9), INT(11), BIGINT(20), YEAR(4); one digit fewer for UNSIGNED, except for BIGINT). A declared width above 255 is rejected with error 1439 (ER_TOO_BIG_DISPLAYWIDTH). ZEROFILL is accepted but treated as a plain UNSIGNED: the zero-padding on display is not produced. Changing only the width with ALTER TABLE ... MODIFY does not rewrite the table.

Exact numbers and floating point#

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

DECIMAL(p, s) is stored using exact integer arithmetic (a scaled integer), never in floating point:

FLOAT accepts any absolute value up to approximately 3.402 823 466 × 10³⁸ (error 1264 beyond that); DOUBLE is an IEEE 754 double-precision float that rejects non-finite values (NaN, infinities).

ROUND(x, n) and TRUNCATE(x, n) on a float pad the result to n fixed decimal places (zeros included) as long as n does not exceed 9 (the same limit as the scale of a DECIMAL); beyond that, the result reverts to its natural format with no fixed decimals imposed, as the reference does beyond its own internal limit.

Character strings#

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

The length of CHAR and VARCHAR is counted in Unicode characters, not bytes; a value that is too long returns error 1406 (ER_DATA_TOO_LONG). A precision in parentheses on TEXT and its variants (TEXT(100), TINYTEXT(10)…) is accepted by the parser but has no effect: these columns are bounded only by the general limit of 2 GB per value.

LONG VARCHAR is an accepted synonym for MEDIUMTEXT.

Binary strings and BLOB#

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

BINARY(n) and VARBINARY(n) store raw bytes on at most n bytes (2,147,483,647 bytes maximum declarable); error 1406 on overflow.

TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB share the same limit of 2 GB per value. A BLOB value of at least --lob-threshold bytes (8192 by default) is moved out to the table's LOB store (<table>.bmrj); the table itself keeps only a 16-byte reference (values already stored join the store at the next open when the threshold is lowered; on the primary of a cluster, beyond 256 MiB to move per table, they stay in the table). This storage detail is transparent to queries, but it explains why a BLOB column (like TEXT, on the string side) can never be part of a key — primary key, UNIQUE, or index — which returns error 1170 (ER_BLOB_KEY_WITHOUT_LENGTH), as index prefixes (col(n)) are not supported.

Dates and times#

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 (and TIMESTAMP, which behaves identically to it) is stored internally as a double-precision floating-point number (number of days), with a physical precision of one millisecond, but rendered to the second in results.

Known limitation — fractional precision DATETIME(n) / TIMESTAMP(n): the syntax DATETIME(n) or TIMESTAMP(n) (0 to 6 fractional-second digits) is accepted by the parser, including after DEFAULT and after ON UPDATE (CURRENT_TIMESTAMP(n), NOW(n), LOCALTIME(n), LOCALTIMESTAMP(n)), but the declared precision has no effect on what is actually stored or displayed. A precision inconsistent with that of the column is accepted without a warning, where a reference server would return a syntax error. Do not expect reliable fractions of a second beyond the physically stored millisecond, regardless of the precision written in the column definition.

The ON UPDATE CURRENT_TIMESTAMP clause (or one of its synonyms NOW, LOCALTIME, LOCALTIMESTAMP, with or without precision) is, however, fully applied: see the corresponding section below.

ENUM and 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 stores the chosen value as text (comparison is case-insensitive and ignores trailing spaces); in a numeric context (arithmetic operation on the column alone, comparison with a number, numeric CAST, ORDER BY), the column takes the index of the chosen member (1 for the first). A value not in the list returns error 1265 in strict mode (empty string and a warning outside strict mode). An unlimited number of members is accepted; duplicate members return error 1291.

SET works the same way but stores a set of members (bit mask when written); 64 members at most (beyond that: error 1097). A comma in a member is rejected (error 1367).

JSON and UUID#

JSON and UUID are recognized as full-fledged column types and stored as character strings (information_schema.COLUMNS describes them by their own name).

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

CAST(expr AS JSON) is available. DEFAULT UUID() and DEFAULT UUID_SHORT() are accepted as a column default value (see below).

VECTOR(n)#

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

VECTOR(n) stores n 32-bit floats (1 ≤ n ≤ 16,383), never dictionary-compressed, in text form [0.1, 0.2, ...] or binary. The declared dimension must be respected exactly on write (error 7601); NaN and infinite values are rejected (7603, 7604). A VECTOR column cannot carry a primary key, a UNIQUE constraint or an ordinary index (error 6133), but it can carry a vector index (VECTOR INDEX, NOT NULL column). Without it, ORDER BY distance_vectorielle(...) LIMIT k computes the exact k nearest neighbors; with it, a much faster approximate search (see chapter 19).

Conversions: CAST and 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) and CONVERT(expr, type) accept the following target types: SIGNED [INTEGER], UNSIGNED [INTEGER], CHAR[(n)] (and its synonyms NCHAR, VARCHAR), BINARY[(n)], DECIMAL[(p, s)], DATE, DATETIME, TIME, DOUBLE / FLOAT / REAL, JSON, TEXT (converted to VARCHAR), VECTOR[(n)], INTEGER / INT (converted to BIGINT). CONVERT(expr USING charset) changes the logical character set of the expression. An implicit conversion takes place automatically in mixed comparisons and operations (for example a numeric string compared with an integer) following the same rules as a common SQL server.

NULL#

Every column is nullable by default (implicit NULL), except:

• a column carrying the explicit NOT NULL clause;
• a primary key column, which is implicitly NOT NULL and loses any DEFAULT NULL declared along with it.

NULL participates in three-valued logic (TRUE, FALSE, UNKNOWN) in any Boolean expression: a comparison with NULL (other than IS NULL / IS NOT NULL) returns UNKNOWN, never TRUE or 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)))
);

Accepted forms for a default value:

A default value in the form of an expression is compiled on write and evaluated row by row on each insert that does not supply the column; it may reference the other columns of the same row, including columns declared later in the table. Unlike a generated column, the value thus set remains freely modifiable afterwards by an UPDATE.

The following are rejected in the definition of an expression DEFAULT:

ALTER TABLE ... ADD COLUMN with such a default value fills the existing rows with the value recomputed for each one — a convenient behavior for migrations, which the documentation of a reference server generally does not describe. ALTER TABLE ... ALTER COLUMN ... SET DEFAULT, on the other hand, never recomputes existing rows.

A primary key column is implicitly NOT NULL and loses a DEFAULT NULL carried with it; an existing row that holds NULL there causes the statement to fail with error 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
);

A DATETIME or TIMESTAMP column can carry ON UPDATE CURRENT_TIMESTAMP[(n)] (or one of the synonyms NOW, LOCALTIME, LOCALTIMESTAMP, with or without fractional precision — this precision remains, as everywhere else on these types, without real effect). The column takes the statement's instant on each write that actually modifies the row without giving it a value; an unchanged row keeps its instant. An explicit value (supplied by the SET of an UPDATE, or set by a BEFORE UPDATE trigger) always takes precedence over the clause. This clause is rejected on a type that is neither DATETIME nor TIMESTAMP (error 1294) and on a generated column (error 3106).

AUTO_INCREMENT#

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

AUTO_INCREMENT is declared on an integer column; the counter is persistent (kept in the table file) and is never restored to its previous value by a ROLLBACK. It can be set or raised explicitly:

ALTER TABLE facture AUTO_INCREMENT = 5000;

An AUTO_INCREMENT value cannot be referenced in a DEFAULT expression (error 3769) or in the expression of a generated column (error 3109).

Generated columns#

CREATE TABLE ligne_facture (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    prix_unit   DECIMAL(10,2) NOT NULL,
    quantite    INT NOT NULL,
    -- computed on each read, nothing is stored
    total_ht    DECIMAL(12,2) GENERATED ALWAYS AS (prix_unit * quantite) VIRTUAL,
    -- computed on each write, then stored
    total_ttc   DECIMAL(12,2) AS (total_ht * 1.20) STORED
);

Syntax: col type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED | PERSISTENT]. VIRTUAL is the default kind if none is written; STORED and PERSISTENT are synonyms.

A VIRTUAL column accepts a non-unique secondary index (KEY / INDEX) provided its expression is deterministic and references no BLOB column (otherwise error 3106); an expression computed on NOW() or CURDATE() stays out of the index for the same reason. On the other hand, primary key, UNIQUE, and foreign key on a VIRTUAL column are always rejected (error 3106), regardless of the expression — only a STORED / PERSISTENT column can carry this kind of constraint.

An explicit value given on write for a generated column (INSERT, UPDATE...) is silently ignored (no error, no warning): the column is recomputed anyway.

Rejected when creating a generated column:

SHOW CREATE TABLE, DESCRIBE, and information_schema.COLUMNS distinguish the two kinds in the EXTRA column (VIRTUAL GENERATED / STORED GENERATED) and return the source expression in GENERATION_EXPRESSION.

CHECK constraints#

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

A CHECK constraint can be column-level (carried by a single column, implicitly named after it) or table-level (named, or generated as CONSTRAINT_<n>). The expression is evaluated on the full row before each write; an evaluation to FALSE returns error 4025, and NULL is accepted (like an always-true CHECK). NOT ENFORCED stores it without ever evaluating it.

Rejected when creating a CHECK: subquery (3815), non-deterministic function (3814), variable (3816), AUTO_INCREMENT column (3818), column constraint that references another column (3813), aggregate (1111).

See also#

The DDL Language chapter documents the full syntax of CREATE TABLE, ALTER TABLE, and constraints (primary key, UNIQUE, foreign key).