Data types

ASQL accepts the MySQL and MariaDB type names. Each column is stored as one of four kinds, compressed by column: integer, double, decimal, or text (dates and binary data are text with a type attached).

Type	Stored as	Notes
`TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, `INTEGER`, `BIGINT`	integer	`UNSIGNED` and `ZEROFILL` accepted; values checked against the type's range
`BOOL`, `BOOLEAN`	integer	shown as `tinyint(1)`
`BIT(n)`	integer
`DECIMAL(p,s)`, `NUMERIC`, `DEC`, `FIXED`	exact decimal	default `(10,0)`; precision up to 65, scale up to 30
`FLOAT`, `DOUBLE`, `DOUBLE PRECISION`, `REAL`	double	`(M,D)` sets display decimals only
`SERIAL`	integer	`BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE`, as in MySQL
`CHAR(n)`, `VARCHAR(n)`, `CHARACTER VARYING(n)`, `NCHAR`, `NVARCHAR`	text	length counted in characters; `CHAR` drops trailing spaces
`TINYTEXT`, `TEXT`, `MEDIUMTEXT`, `LONGTEXT`	text
`BINARY(n)`, `VARBINARY(n)`, `TINYBLOB`, `BLOB`, `MEDIUMBLOB`, `LONGBLOB`	text, binary	`BINARY(n)` is padded with zero bytes
`DATE`	text, date	`YYYY-MM-DD`
`DATETIME[(fsp)]`, `TIMESTAMP[(fsp)]`	text, date and time
`TIME[(fsp)]`	text, time
`YEAR`	text
`ENUM('a', ...)`, `SET('a', ...)`	text	members checked in strict mode
`JSON`	text	a `CHECK (JSON_VALID(col))` is added for you
`GEOMETRY`, `LINESTRING`, `POLYGON`, `MULTILINESTRING`, `MULTIPOLYGON`, `GEOMETRYCOLLECTION`	text	WKT; see the geometry functions

PostgreSQL-style enum types also work:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE people (name TEXT, current mood);
DROP TYPE mood;

Values that do not fit

What happens depends on strict mode, which is off by default (see Support and differences).

Value	Strict mode off	Strict mode on
Text longer than `VARCHAR(n)` or `CHAR(n)`	cut to `n` characters	error "Data too long"
Integer out of the type's range	clamped to the range	error "Out of range value"
Decimal past its precision	clamped	error
Invalid date	zero date	error "Incorrect date value"
Value not in an `ENUM` or `SET`	stored as given	error "Data truncated"

Turn strict mode on for the server with AIOS_SQL_STRICT=1, or per session with SET sql_mode = 'STRICT_TRANS_TABLES'.

Storage

Integers and decimals are stored as scaled integers with the smallest width that fits the column's values. Text is dictionary-coded. Dates are stored as day or second counts. A column's encoding is chosen per column and per segment; it does not change how values behave.