# Numeric Data Type Overview There are a number of numeric data types: * [BOOLEAN](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/boolean) - Synonym for TINYINT(1) * [INT1](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/int1) - Synonym for TINYINT * [INT2](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/int2) - Synonym for SMALLINT * [INT3](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/int3) - Synonym for MEDIUMINT * [INT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/int), INTEGER * [INT4](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/int4) - Synonym for INT * [INT8](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/int8) - Synonym for BIGINT * [TINYINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/tinyint) * [SMALLINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/smallint) * [MEDIUMINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/mediumint) * [BIGINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/bigint) * [DECIMAL](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/decimal), DEC, NUMERIC, FIXED * [FLOAT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/float) * [DOUBLE](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/double), DOUBLE PRECISION, REAL * [BIT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/bit) * [VECTOR](https://mariadb.com/docs/server/reference/sql-structure/vectors/vector) See the specific articles for detailed information on each. ## SIGNED, UNSIGNED and ZEROFILL Most numeric types can be defined as `SIGNED`, `UNSIGNED` or `ZEROFILL`, for example: ```sql TINYINT[(M)] [SIGNED | UNSIGNED | ZEROFILL] ``` If `SIGNED`, or no attribute, is specified, a portion of the numeric type will be reserved for the sign (plus or minus). For example, a `TINYINT SIGNED` can range from -128 to 127. If `UNSIGNED` is specified, no portion of the numeric type is reserved for the sign, so for integer types range can be larger. For example, a `TINYINT UNSIGNED` can range from 0 to 255. Floating point and fixed-point types also can be `UNSIGNED`, but this only prevents negative values from being stored and doesn't alter the range. If `ZEROFILL` is specified, the column will be set to `UNSIGNED` and the spaces used by default to pad the field are replaced with zeros. `ZEROFILL` is ignored in expressions or as part of a [UNION](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/set-operations/union). `ZEROFILL` is a non-standard MySQL and MariaDB enhancement. Note that although the preferred syntax indicates that the attributes are exclusive, more than one attribute can be specified. Only the following combinations are supported: * `SIGNED` * `UNSIGNED` * `ZEROFILL` * `UNSIGNED ZEROFILL` * `ZEROFILL UNSIGNED` The latter two should be replaced with simply `ZEROFILL`, but are still accepted by the parser. ### Examples ```sql CREATE TABLE zf ( i1 TINYINT SIGNED, i2 TINYINT UNSIGNED, i3 TINYINT ZEROFILL ); INSERT INTO zf VALUES (2,2,2); SELECT * FROM zf; +------+------+------+ | i1 | i2 | i3 | +------+------+------+ | 2 | 2 | 002 | +------+------+------+ ``` ## Range When attempting to add a value that is out of the valid range for the numeric type, MariaDB will react depending on the [strict SQL\_MODE](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#strict-mode) setting. If [strict\_mode](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#strict-mode) has been set (the default), MariaDB will return an error. If [strict\_mode](https://mariadb.com/docs/server/server-management/variables-and-modes/sql_mode#strict-mode) has not been set, MariaDB will adjust the number to fit in the field, returning a warning. ### Examples With `strict_mode` set: ```sql SHOW VARIABLES LIKE 'sql_mode'; +---------------+-------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+-------------------------------------------------------------------------------------------+ CREATE TABLE ranges (i1 TINYINT, i2 SMALLINT, i3 TINYINT UNSIGNED); INSERT INTO ranges VALUES (257,257,257); ERROR 1264 (22003): Out of range value for column 'i1' at row 1 SELECT * FROM ranges; Empty set (0.10 sec) ``` With `strict_mode` unset: ```sql SHOW VARIABLES LIKE 'sql_mode%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_mode | | +---------------+-------+ CREATE TABLE ranges (i1 TINYINT, i2 SMALLINT, i3 TINYINT UNSIGNED); INSERT INTO ranges VALUES (257,257,257); Query OK, 1 row affected, 2 warnings (0.00 sec) SHOW WARNINGS; +---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 1264 | Out of range value for column 'i1' at row 1 | | Warning | 1264 | Out of range value for column 'i3' at row 1 | +---------+------+---------------------------------------------+ 2 rows in set (0.00 sec) SELECT * FROM ranges; +------+------+------+ | i1 | i2 | i3 | +------+------+------+ | 127 | 257 | 255 | +------+------+------+ ``` ## AUTO\_INCREMENT The `AUTO_INCREMENT` attribute can be used to generate a unique identity for new rows. For more details, see [auto\_increment](https://mariadb.com/docs/server/reference/data-types/auto_increment). *This page is licensed: CC BY-SA / Gnu FDL* {% @marketo/form formId="4316" %}