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

SHOW CREATE TABLE, DESCRIBE and information_schema.COLUMNS always show the resulting type. The SERIAL DEFAULT VALUE attribute of an integer column is equivalent to NOT NULL AUTO_INCREMENT UNIQUE. The ASCII and UNICODE attributes of a text column are equivalent to CHARACTER SET latin1 and CHARACTER SET ucs2: like those clauses written out in full, they are accepted without changing the storage, which stays in UTF-8 (utf8mb4). Outside a text column, ASCII, UNICODE and BYTE are syntax errors (1064). These forms belong to the MIRAJ language; the PostgreSQL language keeps its own serial. IP addresses are stored in VARCHAR or in VARBINARY(16) with INET_ATON, INET6_ATON and their reverses.

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

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; outside strict mode, it is brought back to the nearest bound with a warning.

BIGINT UNSIGNED covers the whole range 0 to 2⁶⁴ − 1, written as a literal (18446744073709551615), as a string, as a prepared statement parameter or by LOAD DATA. An integer calculation in which an operand is BIGINT UNSIGNED is unsigned: a negative result or one beyond 2⁶⁴ − 1 returns error 1690 (BIGINT UNSIGNED value is out of range), for example CAST(1 AS UNSIGNED) - 2. CAST(-1 AS UNSIGNED) is 18,446,744,073,709,551,615. SUM of a BIGINT UNSIGNED column stays a BIGINT UNSIGNED (error 1690 beyond 2⁶⁴ − 1) and AVG a DOUBLE.

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

Bit literals (MIRAJ language): B'0101', b'0101' and 0b0101 write a bit field. It is a binary string whose bits are right-aligned (SELECT B'1000001' returns A, HEX(B'11111111') returns FF, HEX(b'100000001') returns 0101), read as an unsigned integer where a number is expected: in a calculation (B'101' + 0 is 5), against a number in a comparison, an IN (…) or a BETWEEN (column = 0b11), as an argument of a math function (ABS, BIN, OCT, CONV, ROUND, MOD…), in a numeric CAST, and written by INSERT … VALUES, UPDATE … SET or ON DUPLICATE KEY UPDATE into a numeric column or BIT(n). The hexadecimal literal 0x41 follows the same rules (0x41 + 0 is 65, SELECT 0x41 returns A); X'41' stays a binary string. It also serves as a default value (BIT(4) DEFAULT b'1010'). A digit other than 0 or 1 between the apostrophes is a syntax error (1064); 0b102 is read as an identifier. Any binary value (X'41', 0x41, VARBINARY column) written into a BIT(n) column stores its bytes there as a bit field (1406 in strict mode beyond eight significant bytes). The PostgreSQL language keeps its own bit strings (B'101' of type bit).

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. 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). Changing only the width with ALTER TABLE ... MODIFY does not rewrite the table.

ZEROFILL implies UNSIGNED and applies to integers, DECIMAL, FLOAT and DOUBLE. The value read from such a column is left-padded with zeros up to its width:

A value longer than the width is never cut (123456 in an INT(5) ZEROFILL). The padding applies to text: results sent to the client (text protocol, text access of the embedded API, SELECT … INTO OUTFILE) and conversions of the column to text — CONCAT(c), CAST(c AS CHAR), LENGTH(c), LPAD, LIKE, REGEXP, GROUP_CONCAT, and the value written into a text column by INSERT … SELECT or by UPDATE … SET text = c. ZEROFILL on a non-numeric type (VARCHAR(5) ZEROFILL) is a syntax error (1064). The value stays a number for calculations (c + 0 is 1), comparisons, sorts and groupings; a prepared statement receives it as a number, with the column carrying the protocol's ZEROFILL flag and its width. SHOW CREATE TABLE, DESCRIBE and information_schema.COLUMNS.COLUMN_TYPE show the attribute (int(5) unsigned zerofill). It follows the column when it is taken as-is by a view, a derived table, a table expression or a CREATE TABLE … AS SELECT, but not an expression (c + 1, COALESCE(c), MAX(c)) nor a union column.

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:

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 |
+--------------------------------------------------------------------+----+

A value that has more decimals than the scale is rounded (13.6 becomes 13 in a DECIMAL(10,0)); an integer part that is too long returns error 1264 in strict mode.

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). The forms FLOAT(p), FLOAT(M,D) and DOUBLE(M,D) are accepted: the column stays a float or a double (this is what SHOW CREATE TABLE shows) and the value is not rounded to D decimals.

ROUND(x, n) and TRUNCATE(x, n) on a float pad the result to n fixed decimal places (zeros included) when it is returned as text, as long as n does not exceed 9: CONCAT(ROUND(2.5e0, 3)) is 2.500, CONCAT(TRUNCATE(1.5e0, 2)) is 1.50; beyond that, the result reverts to its natural format.

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). VARCHAR without a length is VARCHAR(255). A zero length (CHAR(0), VARCHAR(0), BINARY(0), VARBINARY(0)) gives a column that accepts only the empty string and NULL. 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 and LONG VARCHAR are accepted synonyms for MEDIUMTEXT, LONG VARBINARY for MEDIUMBLOB.

A CHAR(n) value is stored and read back without its trailing spaces. Under the PAD_CHAR_TO_FULL_LENGTH sql_mode, a CHAR(n) table column is read padded with spaces up to n characters: SELECT c, *, expressions (CONCAT('[', c, ']') gives [ab ] for 'ab' in a CHAR(5), LENGTH(c) 5), LIKE (c LIKE 'ab' becomes false), GROUP BY, DISTINCT, views, derived tables, subqueries and prepared statements. Since comparisons ignore trailing spaces, c = 'ab' stays true, indexes included; NULL stays NULL, a VARCHAR or a BINARY(n) (padded with 0x00) do not change. The body of a routine, a trigger or an event follows the sql_mode kept at its creation, not the caller's. A CACHED view keeps its result frozen, its CHAR(n) columns padded when read.

Each text column (CHAR, VARCHAR, *TEXT, ENUM, SET) has a collation that governs its comparisons, sorting and keys: utf8mb4_general_ci by default (case- and accent-insensitive), utf8mb4_unicode_ci (case- and accent-insensitive, 'ß' = 'ss') or utf8mb4_bin (by code point, 'A1' ≠ 'a1'), declared with [CHARACTER SET utf8mb4] COLLATE name after the type or with COLLATE= on the table. See Collations.

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. A shorter value written into a BINARY(n) is padded with 0x00 bytes ('a' in a BINARY(2) is read back as HEX = 6100, LENGTH = 2).

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.

Keys on long columns (TEXT, BLOB, long VARCHAR):

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

An impossible date ('2026-02-30') or an out-of-range time ('839:00:00') returns error 1366 in strict mode. Outside strict mode, an invalid date ('pas une date', '2026-02-30') is replaced by the zero date (0000-00-00) with warning 1265 ("Data truncated for column 'd' at row 1"). A year written with two digits in a YEAR column follows the rule of CAST(… AS YEAR) below (69 gives 2069, 70 gives 1970, '05' gives 2005).

Fractional seconds. TIME(n), DATETIME(n) and TIMESTAMP(n) (n from 0 to 6, 1426 beyond) keep n fractional-second digits. The precision is kept in the schema, returned by SHOW CREATE TABLE, DESCRIBE and information_schema.COLUMNS (DATETIME_PRECISION), then by the protocol. A value written with more digits is truncated to the column's precision (rounded under the TIME_ROUND_FRACTIONAL sql_mode) and returned with exactly n digits; without a precision, the column is to the second. NOW(n), CURTIME(n), SYSDATE(n) and UTC_TIMESTAMP(n) return the instant at the requested precision, and DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP write the instant at the column's precision. The fraction is exact to the microsecond for the years 1720 to 2079. The last representable value, '9999-12-31 23:59:59.999999', is nevertheless read back identically (equality comparison included), and the rounding of TIME_ROUND_FRACTIONAL never goes beyond 9999-12-31: this value stored in a DATETIME becomes 9999-12-31 23:59:59, and in a 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));
-- ERROR 1426 (42000): Too big precision specified for 't'. Maximum is 6

Time zone. TIMESTAMP is an instant: it is read and displayed in the session's time_zone, whereas DATETIME is a date and time without a time zone, returned as written.

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 is SYSTEM (the server's time zone) by default; it is set for the session or, with SET GLOBAL, for subsequent sessions, by an offset ('+02:00', from -13:59 to +14:00) or an IANA name ('Europe/Paris', 'UTC'). An unknown value returns error 1298. Comparisons, CAST(ts AS DATETIME), UNIX_TIMESTAMP, FROM_UNIXTIME and the extraction functions (HOUR(ts)…) follow the session's time zone; CONVERT_TZ is described in chapter 10.

CAST(expr AS YEAR) returns a year from 1901 to 2155 (or 0): 69 gives 2069, 70 gives 1970, an out-of-range value returns NULL.

Year zero. A YEAR column read as-is is always written with four digits: year zero is 0000 there (SELECT y, CONCAT(y), CAST(y AS CHAR), GROUP_CONCAT, derived table, union); an expression of type YEAR (MIN(y), COALESCE(y), IF(c, y1, y2), subquery, CAST(0 AS YEAR)) returns it as 0. COALESCE / IF of years stay of type year(4) (CREATE TABLE … AS SELECT). Compared with a YEAR column, a constant text is read as the year it would give if written into the column: y = '0' and y = '00' designate 2000 (and are false for year zero), y = '0000' year zero; a text that writing would reject ('abc', '1800') is compared as a number. An integer of the form YYYYMMDDhhmmss is converted with CAST(… AS DATETIME) (CAST(20260601120000 AS DATETIME) gives 2026-06-01 12:00:00).

Dates written in free form. A text is read as a date or a date-time with any punctuation (2020.01.01, 2020/01/01T10.00.00, 2020_01_01 10_00_00), a two-digit year (20-01-01) or a compact form (200101, 20200101, 200101100000, 20200101100000, with or without fractional seconds). This reading applies on write (INSERT, UPDATE, LOAD DATA, DATE, DATETIME and TIMESTAMP columns), to CAST(text AS DATE | DATETIME) and to the text arguments of the date functions (DATE_ADD, DATEDIFF, DAYNAME, YEAR, TO_DAYS…), without a warning. A text that is unreadable, or followed by anything other than a date, is still rejected on write (error 1366 in strict mode) and CAST returns NULL. The time functions (HOUR, CAST(… AS TIME)) do not follow this reading: '200101' is a time there, not a 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

Comparing a date with a text. Against a DATE / DATETIME / TIMESTAMP column or value, the text is read as a date by the same rules: jour = '200101' and jour = '2020.01.01' are true for January 1, 2020, with or without an index on the column. A text that cannot be read entirely keeps its longest readable beginning ('2020-01-01x' is January 1, 2020); with no readable beginning it is 0000-00-00 00:00:00. In both cases the query succeeds and a warning 1292 is recorded (visible with SHOW WARNINGS): "Truncated incorrect datetime value: 'x'" for a constant, "Incorrect datetime value: 'x' for column … at row 1" for a text column compared with a date. Against a TIME, an unreadable text column likewise gives "Incorrect time value: 'x' for column base.table.s at row 1".

The ON UPDATE CURRENT_TIMESTAMP clause (or one of its synonyms NOW, LOCALTIME, LOCALTIMESTAMP, with or without precision) is 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 accents and 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 number written into the column designates the member of that index. A value not in the list returns error 1265 in strict mode (empty string, of index 0, and a warning outside strict mode). An unlimited number of members is accepted; duplicate members return error 1291, a DEFAULT not in the list error 1067. An ENUM … NOT NULL column without a DEFAULT takes its first member when the write does not supply it. Taken as-is by a view, a derived table or a table expression (WITH), the column still sorts by its index and is equal to that index in a numeric context.

SET works the same way but stores a set of members (bit mask when written, members read back in declaration order); 64 members at most (beyond that: error 1097). A comma in a member is rejected (error 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 and UUID#

JSON and UUID are stored as character strings.

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

A JSON column is a long text (longtext) in utf8mb4_bin collation, with an implicit CHECK (json_valid(col)) constraint: this is how SHOW CREATE TABLE, DESCRIBE and information_schema.COLUMNS describe it (DATA_TYPE = longtext), and the constraint appears in information_schema.CHECK_CONSTRAINTS under the column's name. A text that is not JSON is rejected with error 4025, even outside strict mode (a warning with IGNORE), on INSERT, UPDATE, REPLACE, LOAD DATA and on an ALTER TABLE … MODIFY to JSON:

INSERT INTO profil (parametres) VALUES ('pas du json');
-- ERROR 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]}] |
+-------------------+--------------------+------------------------+

A CHECK constraint written on the column itself replaces the implicit constraint; the latter cannot be removed with DROP CONSTRAINT (1091). CREATE TABLE … AS SELECT of a JSON column gives a longtext utf8mb4_bin without the constraint.

The document is stored as written. Read by a JSON function, it is treated as a document (and not as a text to be quoted): JSON_ARRAY(doc) or JSON_SET(other, '$.x', doc) nest its content. Its parts are read with the doc->'$.path' and doc->>'$.path' operators, and modified with JSON_SET, JSON_REMOVE... (see 10.10 JSON functions). The utf8mb4_bin collation distinguishes two documents that differ only by case ('{"a":"x"}' ≠ '{"a":"X"}').

JSONB (the PostgreSQL language's name, not reserved: jsonb remains a possible column name) is an exact synonym of JSON: same storage, same functions and operators, same collation, same indexes, same json_valid check (4025). The name as written is nevertheless kept with the column: SHOW CREATE TABLE and DESCRIBE display it as jsonb, information_schema.COLUMNS gives it in COLUMN_TYPE (DATA_TYPE is json), and it follows the table into a backup file, a cluster replica (DDL text replayed), CREATE TABLE … LIKE and miraj-dump. ALTER TABLE … MODIFY col JSON or MODIFY col JSONB switches from one name to the other without touching the data. In the PostgreSQL language, a jsonb column is reported as jsonb (OID 3802) and a json column stays json (OID 114); see 30.3.

A UUID column stores the identifier's text as written and compares it without regard to case ('123E4567-…' is found by = '123e4567-…'); information_schema.COLUMNS describes it by its own name (uuid).

CAST(expr AS JSON) (or AS JSONB, identical) is available. DEFAULT UUID(), DEFAULT UUID_V4() (random UUID), DEFAULT UUID_V7() (time-ordered UUID) and DEFAULT UUID_SHORT() are accepted as a column default value (see below).

Dynamic columns#

Dynamic columns store a set of key / value pairs in a single BLOB column, in the standard binary format of dynamic columns: such a blob can be exchanged as-is with other servers that use it. Keys are numbers or names; each value has a type (INT, UINT, DOUBLE, DECIMAL(p,s), CHAR, BINARY, DATE, TIME, DATETIME, or another dynamic set).

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} |
+---------------------------------+---------------------+---------------------------+

An invalid blob or a duplicate key returns NULL.

Spatial types#

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 accepts any geometry; POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON and GEOMETRYCOLLECTION accept only their own kind. A value is stored as the SRID (4 bytes) followed by the geometry in WKB format; it is written by the constructor functions (ST_GeomFromText, ST_GeomFromWKB, ST_GeomFromGeoJSON, POINT(x, y)…) and read back by ST_AsText, ST_AsGeoJSON… (see 10.17 Spatial functions).

information_schema.COLUMNS gives the type name (point, polygon…) in DATA_TYPE. A SPATIAL index (a single NOT NULL geometry column) serves area-search predicates (MBRContains, ST_Intersects…): see 10.17, "Spatial index".

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, written in text form '[0.1, 0.2, ...]' (or by VEC_FromText) or in binary (4 bytes per component, little-endian). The declared dimension must be respected exactly on write:

Outside strict mode, a value rejected with 1292 becomes a zero vector (all components 0) with the warning 1292 Incorrect vector value: '…' for column … at row n (bytes shown as \xNN, control characters of a text as \XXXX); an assigned number gives warning 4078 (Cannot cast 'int' as 'vector' in assignment of …) followed by 1292. Only bytes (literal x'…', UNHEX, binary column) or the text […] form a vector; in LOAD DATA, a field is read as bytes with CHARACTER SET binary, otherwise as text. A default value x'…' or 0x… of a VECTOR column (as of a binary or text column) is its bytes, checked like a written value (1067 otherwise), and information_schema.COLUMNS displays it as x'…' in lowercase.

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]');
-- ERROR 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 |
+----+---------------+--------------------------+

A VECTOR column can carry a primary key, a UNIQUE constraint or an ordinary index (KEY), which compare vectors byte by byte (a v(n) prefix returns error 1089), and a vector index (VECTOR INDEX, NOT NULL column). Without a vector index, ORDER BY vector_distance(...) LIMIT k computes the exact k nearest neighbors; with it, a much faster approximate search (see chapter 11).

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[(n)], TIME[(n)], DOUBLE / FLOAT / REAL, JSON / JSONB, TEXT (converted to VARCHAR), YEAR, VECTOR[(n)], INTEGER / INT (converted to BIGINT). CHAR(n) cuts a longer text with warning 1292 (CAST(12 AS CHAR(1)) is 1); BINARY(n) pads a shorter text with 0x00 bytes (CAST(1 AS BINARY(3)) = X'310000' is true). A number converted to an integer is rounded (CAST(1.5 AS INTEGER) is 2, CAST(3.9 AS UNSIGNED) is 4). 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.

A text converted to SIGNED or UNSIGNED is read by its leading digits only: neither fraction nor exponent is taken (CAST('1.5' AS SIGNED) and CAST('1e3' AS SIGNED) are 1, with warning 1292), whereas DECIMAL and DOUBLE read the exponent (CAST('1e3' AS DECIMAL(10,2)) = 1000.00).

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:

SHOW CREATE TABLE returns each form as it was declared:

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 with an expression default value fills the existing rows with the value recomputed for each one (ADD COLUMN h CHAR(32) DEFAULT (MD5(nom)) gives each row the hash of its own nom). 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; SHOW CREATE TABLE displays it as ON UPDATE current_timestamp(n)). The column takes the statement's instant, at the column's precision, on each write that actually modifies the row without giving it a value; an unchanged row (SET n = n) 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).