¶Data Types

¶Integer widths and UNSIGNED

Every integer width is stored as 64 bits: TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT occupy the same space here. The declared width is still a constraint, though, and a value too wide for it is refused exactly as MySQL refuses it:

CREATE TABLE t (a TINYINT);
INSERT INTO t VALUES (300);   -- ERROR 1264 (22003): value 300 is out of range

UNSIGNED is enforced on every integer type as well. A negative value is refused with the same error:

CREATE TABLE t (a INT UNSIGNED);
INSERT INTO t VALUES (-1);    -- ERROR 1264 (22003): invalid UNSIGNED value: -1

¶Character length

CHAR(n) and VARCHAR(n) keep their declared length, and a longer string is refused in strict mode rather than stored:

CREATE TABLE t (a VARCHAR(32));
INSERT INTO t VALUES (REPEAT('x', 40));   -- ERROR 1406 (22001): data too long

Like integer widths, this is recorded per table when the table is created, so a table created before 1.9.2 has no declared length stored and is not retroactively constrained.

¶Literals and coercion

Values are written as string or numeric literals and coerced to the column type on insert:

CREATE TABLE t (
  id     BIGINT PRIMARY KEY,
  price  DECIMAL(10,2),
  d      DATE,
  ts     DATETIME,
  clock  TIME,
  doc    JSON
);

INSERT INTO t VALUES
  (1, 19.99, '2024-01-15', '2024-01-15 09:30:00', '09:30:00', '{"a":1}');

• DECIMAL keeps its declared scale exactly: 19.9 stored in DECIMAL(10,2) reads back as 19.90, and SUM over decimals is exact.
• DATE/DATETIME/TIME accept string literals and compare correctly against strings (WHERE d >= '2024-01-01').
• JSON must be structurally valid; invalid JSON is rejected. Nesting is bounded (200 levels) so a pathological document can't exhaust the stack.
• VECTOR accepts a '[a,b,c]' string literal of the declared dimension.
• ENUM and SET are accepted and stored as their string value; a value outside the declared member list is rejected.

¶Arithmetic semantics

Arithmetic follows MySQL:

• Signed integer overflow raises an error rather than silently wrapping or saturating: 9223372036854775807 + 1 returns ERROR 1690 (22003): BIGINT value is out of range, in both scalar expressions and computed writes (UPDATE ... SET v = v + 1). Integer +, -, * are computed exactly (no f64 precision loss for large values).
• Division or modulo by zero returns NULL: 1 / 0, 1 % 0 and MOD(1, 0) are all NULL.
• DOUBLE overflow returns NULL rather than inf/NaN (e.g. POW(10, 308) * 10).
• Values above the signed range use BIGINT UNSIGNED (exact 64-bit unsigned), and DECIMAL arithmetic is exact to its scale.

¶JSON access

Extract values from JSON columns with the -> / ->> operators or JSON_EXTRACT, using MySQL-style paths ($, $.key, $[0], chained):

SELECT doc->'$.name'        AS name_json,   -- returns JSON (quoted)
       doc->>'$.name'       AS name_text,   -- returns unquoted text
       doc->>'$.addr.city'  AS city,
       doc->>'$.tags[0]'    AS first_tag,
       JSON_EXTRACT(doc, '$.age') AS age
FROM docs;

JSON_UNQUOTE returns the raw scalar of a JSON value. A missing path yields NULL.

¶JSON functions

SELECT JSON_SET('{"a":1}', '$.a', 10, '$.b', 2);   -- {"a": 10, "b": 2}
UPDATE docs SET doc = JSON_SET(doc, '$.seen', 1) WHERE id = 5;
SELECT id FROM docs WHERE JSON_LENGTH(doc, '$.tags') >= 2;

Nested paths ($.a.b, $.a[0]) are supported for setting, removing, and inspecting.

SELECT id FROM docs WHERE (doc->>'$.addr.city') = 'Bergen';

¶Comparison semantics

Cross-type comparisons are coerced (date vs. text, decimal vs. numeric). NULL compares as unknown and sorts first under ORDER BY.