Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Synonym for FLOAT. This keyword declares a single-precision floating-point column.
See FLOAT.
CREATE TABLE float4_example (
example FLOAT4
);SHOW CREATE TABLE float4_example\G*************************** 1. row ***************************
Table: float4_example
Create Table: CREATE TABLE `float4_example` (
`example` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
General introduction to numeric data types. This page summarizes the available integer, fixed-point, and floating-point types and their storage characteristics.
There are a number of numeric data types:
- Synonym for TINYINT(1)
- Synonym for TINYINT
- Synonym for SMALLINT
INT3 - Synonym for MEDIUMINT
INT, INTEGER
INT4 - Synonym for INT
INT8 - Synonym for BIGINT
DECIMAL, DEC, NUMERIC, FIXED
DOUBLE, DOUBLE PRECISION, REAL
See the specific articles for detailed information on each.
Most numeric types can be defined as SIGNED, UNSIGNED or ZEROFILL, for example:
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. 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.
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 setting.
If strict_mode has been set (the default), MariaDB will return an error.
If strict_mode has not been set, MariaDB will adjust the number to fit in the field, returning a warning.
With strict_mode set:
With strict_mode unset:
The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows. For more details, see auto_increment.
This page is licensed: CC BY-SA / Gnu FDL
CREATE TABLE bool_example (
example BOOL
) DEFAULT CHARSET=latin1;SHOW CREATE TABLE bool_example\G*************************** 1. row ***************************
Table: bool_example
Create Table: CREATE TABLE `bool_example` (
`example` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1-1.7976931348623157E+308 to -2.2250738585072014E-308
0
2.2250738585072014E-308 to 1.7976931348623157E+308
These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.
UNSIGNED, if specified, disallows negative values.
ZEROFILL, if specified, pads the number with zeros, up to the total number of digits specified by M.
REAL and DOUBLE PRECISION are synonyms, unless the REAL_AS_FLOAT SQL mode is enabled, in which case REAL is a synonym for FLOAT rather than DOUBLE.
See Floating Point Accuracy for issues when using floating-point numbers.
For more details on the attributes, see Numeric Data Type Overview.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Exception: If the REAL_AS_FLOAT SQL mode is enabled, REAL is a synonym for FLOAT rather than DOUBLE.
This page is licensed: GPLv2, originally from fill_help_tables.sql
DOUBLE PRECISION[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]
REAL[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]This page is licensed: GPLv2, originally from fill_help_tables.sql
DEC[(M[,D])] [SIGNED | UNSIGNED | ZEROFILL]
NUMERIC[(M[,D])] [SIGNED | UNSIGNED | ZEROFILL]
FIXED[(M[,D])] [SIGNED | UNSIGNED | ZEROFILL]This page is licensed: GPLv2, originally from fill_help_tables.sql
INTEGER[(M)] [SIGNED | UNSIGNED | ZEROFILL]CREATE TABLE integer_example (
example INTEGER
);Synonym for DOUBLE. This keyword declares a double-precision floating-point column.
See DOUBLE.
CREATE TABLE float8_example (
example FLOAT8
);SHOW CREATE TABLE float8_example\G
*************************** 1. row ***************************
Table: float8_example
Create Table: CREATE TABLE `float8_example` (
`example` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
Synonym for SMALLINT. This type uses 2 bytes of storage.
INT2 is a synonym for SMALLINT.
CREATE TABLE t1 (x INT2);
DESC t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| x | smallint(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+CREATE TABLE int2_example (
example INT2
);SHOW CREATE TABLE int2_example\G
*************************** 1. row ***************************
Table: int2_example
Create Table: CREATE TABLE `int2_example` (
`example` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is licensed: CC BY-SA / Gnu FDL
TINYINT[(M)] [SIGNED | UNSIGNED | ZEROFILL]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 |
+------+------+------+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)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 |
+------+------+------+DOUBLE[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]
DOUBLE PRECISION[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]
REAL[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]CREATE TABLE t1 (d DOUBLE(5,0) zerofill);
INSERT INTO t1 VALUES (1),(2),(3),(4);
SELECT * FROM t1;
+-------+
| d |
+-------+
| 00001 |
| 00002 |
| 00003 |
| 00004 |
+-------+CREATE TABLE double_precision_example (
example DOUBLE PRECISION
);SHOW CREATE TABLE double_precision_example\G
<</code>>
<<sql>>
*************************** 1. row ***************************
Table: double_precision_example
Create Table: CREATE TABLE `double_precision_example` (
`example` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1SHOW CREATE TABLE integer_example\G
*************************** 1. row ***************************
Table: integer_example
Create Table: CREATE TABLE `integer_example` (
`example` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE t1 (x INT3);
DESC t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x | mediumint(9) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+CREATE TABLE int3_example (
example INT3
);SHOW CREATE TABLE int3_example\G
*************************** 1. row ***************************
Table: int3_example
Create Table: CREATE TABLE `int3_example` (
`example` mediumint(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1Synonym for TINYINT(1). Like BOOL, this type is used for boolean logic, storing 0 for false and 1 (or other non-zero numbers) for true.
BOOL, BOOLEANThese types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.
However, the values TRUE and FALSE are merely aliases for 1 and 0. See , as well as the for testing values against a boolean.
TRUE and FALSE as aliases for 1 and 0:
The last two statements display the results shown because 2 is equal to neither 1 nor 0.
This page is licensed: GPLv2, originally from
Synonym for DECIMAL. This keyword is used to define columns that require exact numeric precision, such as currency.
See DECIMAL.
CREATE TABLE fixed_example (
example FIXED
);SHOW CREATE TABLE fixed_example\G
*************************** 1. row ***************************
Table: fixed_example
Create Table: CREATE TABLE `fixed_example` (
`example` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
Synonym for TINYINT. This type uses 1 byte of storage.
INT1 is a synonym for TINYINT.
CREATE TABLE t1 (x INT1);
DESC t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| x | tinyint(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+CREATE TABLE int1_example (
example INT1
);SHOW CREATE TABLE int1_example\G*************************** 1. row ***************************
Table: int1_example
Create Table: CREATE TABLE `int1_example` (
`example` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is licensed: CC BY-SA / Gnu FDL
CREATE TABLE middleint_example (
example MIDDLEINT
);SHOW CREATE TABLE middleint_example\G*************************** 1. row ***************************
Table: middleint_example
Create Table: CREATE TABLE `middleint_example` (
`example` mediumint(9) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE numeric_example (
example NUMERIC
);SHOW CREATE TABLE numeric_example\G*************************** 1. row ***************************
Table: numeric_example
Create Table: CREATE TABLE `numeric_example` (
`example` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE dec_example (
example DEC
);SHOW CREATE TABLE dec_example\G*************************** 1. row ***************************
Table: dec_example
Create Table: CREATE TABLE `dec_example` (
`example` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE boolean_example (
example BOOLEAN
);SHOW CREATE TABLE boolean_example\G*************************** 1. row ***************************
Table: boolean_example
Create Table: CREATE TABLE `boolean_example` (
`example` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE boo (i BOOLEAN);
DESC boo;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| i | tinyint(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+SELECT IF(0, 'true', 'false');
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false |
+------------------------+
SELECT IF(1, 'true', 'false');
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true |
+------------------------+
SELECT IF(2, 'true', 'false');
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true |
+------------------------+SELECT IF(0 = FALSE, 'true', 'false');
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true |
+--------------------------------+
SELECT IF(1 = TRUE, 'true', 'false');
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true |
+-------------------------------+
SELECT IF(2 = TRUE, 'true', 'false');
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false |
+-------------------------------+
SELECT IF(2 = FALSE, 'true', 'false');
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false |
+--------------------------------+NUMBER[(M[,D])] [SIGNED | UNSIGNED | ZEROFILL]SET sql_mode='oracle';CREATE TABLE number_example (
example NUMBER
);SHOW CREATE TABLE number_example\GLarge integer type. A BIGINT uses 8 bytes and can store values from -9223372036854775808 to 9223372036854775807 (signed) or 0 to 18446744073709551615 (unsigned).
A large integer. The signed range is -9223372036854775808 to9223372036854775807. The unsigned range is 0 to18446744073709551615.
If a column has been set to ZEROFILL, all values will be prepended by zeros so that the BIGINT value contains a number of M digits.
Note: If the ZEROFILL attribute has been specified, the column will automatically become UNSIGNED.
For more details on the attributes, see .
SERIAL is an alias for:
INT8 is a synonym for BIGINT.
The BIGINT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of BIGINT SIGNED (the default):
Example of BIGINT UNSIGNED:
A value is considered "out of range" when it is too small or too large to be stored in a data type. When sql_mode=STRICT_TRANS_TABLES (the default) is set, an out-of-range value generates an error. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).
An example of non-strict out-of-range behavior:
A special type of BIGINT UNSIGNED is BIGINT ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros are just enough to pad the field out to the length of the type's maximum unsigned value, but the zeros are not included in an expression result or in a UNION SELECT column.
Using BIGINT ZEROFILL works the same way as BIGINT UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:
The resulting data would look like this:
This page is licensed: GPLv2, originally from
*************************** 1. row ***************************
Table: number_example
Create Table: CREATE TABLE "number_example" (
"example" double DEFAULT NULL
)BIGINT[(M)] [SIGNED | UNSIGNED | ZEROFILL]Store numerical data efficiently. This section details integer, fixed-point, and floating-point types like INT, DECIMAL, and FLOAT.
CREATE TABLE t1 (x INT8);
DESC t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| x | bigint(20) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+CREATE TABLE int8_example (
example INT8
);SHOW CREATE TABLE int8_example\G
*************************** 1. row ***************************
Table: int8_example
Create Table: CREATE TABLE `int8_example` (
`example` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUECREATE TABLE bigints (a BIGINT,b BIGINT UNSIGNED,c BIGINT ZEROFILL);
INSERT INTO bigints VALUES (-10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'b' at row 1
INSERT INTO bigints VALUES (-10,10,-10);
ERROR 1264 (22003): Out of range value for column 'c' at row 1
INSERT INTO bigints VALUES (-10,10,10);
INSERT INTO bigints VALUES (9223372036854775808,9223372036854775808,9223372036854775808);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
INSERT INTO bigints VALUES (9223372036854775807,9223372036854775808,9223372036854775808);
SELECT * FROM bigints;
+---------------------+---------------------+----------------------+
| a | b | c |
+---------------------+---------------------+----------------------+
| -10 | 10 | 00000000000000000010 |
| 9223372036854775807 | 9223372036854775808 | 09223372036854775808 |
+---------------------+---------------------+----------------------+CREATE TABLE bigint_signed_example (
description VARCHAR(20),
example BIGINT SIGNED
);INSERT INTO bigint_signed_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', -9223372036854775808),
('Maximum', 9223372036854775807);CREATE TABLE bigint_unsigned_example (
description VARCHAR(20),
example BIGINT UNSIGNED
);INSERT INTO bigint_unsigned_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 18446744073709551615);TRUNCATE bigint_signed_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO bigint_signed_example VALUES
('Underflow', -9223372036854775809),
('Overflow', 9223372036854775808);
Warning (Code 1264): Out of range value for column 'example' at row 1
Warning (Code 1264): Out of range value for column 'example' at row 2
SELECT * FROM bigint_signed_example;
+-------------+----------------------+
| description | example |
+-------------+----------------------+
| Underflow | -9223372036854775808 |
| Overflow | 9223372036854775807 |
+-------------+----------------------+
TRUNCATE bigint_unsigned_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO bigint_unsigned_example VALUES
('Underflow', -1),
('Overflow', 18446744073709551616);
Warning (Code 1264): Out of range value for column 'example' at row 1
Warning (Code 1264): Out of range value for column 'example' at row 2
SELECT * FROM bigint_unsigned_example;
+-------------+----------------------+
| description | example |
+-------------+----------------------+
| Underflow | 0 |
| Overflow | 18446744073709551615 |
+-------------+----------------------+CREATE TABLE bigint_zerofill_example (
description VARCHAR(20),
example BIGINT ZEROFILL
);
INSERT INTO bigint_zerofill_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 18446744073709551615);
-- Turn off strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO bigint_zerofill_example VALUES
('Underflow', -1),
('Overflow', 18446744073709551616);
Warning (Code 1264): Out of range value for column 'example' at row 1
Warning (Code 1264): Out of range value for column 'example' at row 2SELECT *, example + 0 FROM bigint_zerofill_example;
+-------------+----------------------+----------------------+
| description | example | example + 0 |
+-------------+----------------------+----------------------+
| Zero | 00000000000000000000 | 0 |
| Forty-Two | 00000000000000000042 | 42 |
| Minimum | 00000000000000000000 | 0 |
| Maximum | 18446744073709551615 | 18446744073709551615 |
| Underflow | 00000000000000000000 | 0 |
| Overflow | 18446744073709551615 | 18446744073709551615 |
+-------------+----------------------+----------------------+Standard integer type. An INT column uses 4 bytes and stores values from -2147483648 to 2147483647 (signed) or 0 to 4294967295 (unsigned).
A normal-size integer. When marked UNSIGNED, it ranges from 0 to 4294967295, otherwise its range is -2147483648 to 2147483647 (SIGNED is the default). If a column has been set to ZEROFILL, all values will be prepended by zeros so that the INT value contains a number of M digits. INTEGER is a synonym for INT.
INT4 is a synonym for INT.
For details on the attributes, see .
The INT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of INT SIGNED (the default):
Example of INT UNSIGNED:
A value is considered "out-of-range" when it is too small or too large to be stored in a data type. When sql_mode=STRICT_TRANS_TABLES (the default) is set, an out-of-range value generates an error. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).
An example of non-strict out-of-range behavior:
A special type of INT UNSIGNED is INT ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros are just enough to pad the field out to the length of the type's maximum unsigned value, but the zeros are not included in an expression result or in a UNION SELECT column.
Using INT ZEROFILL works the same way as INT UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:
The resulting data would look like this:
This page is licensed: CC BY-SA / Gnu FDL
Medium-sized integer. A MEDIUMINT column uses 3 bytes and stores values from -8388608 to 8388607 (signed) or 0 to 16777215 (unsigned).
A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
ZEROFILL pads the integer with zeroes and assumes UNSIGNED (even if UNSIGNED is not specified).
INT3 is a synonym for MEDIUMINT.
For details on the attributes, see .
The MEDIUMINT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of MEDIUMINT SIGNED (the default):
Example of MEDIUMINT UNSIGNED:
A value is considered "out of range" when it is too small or too large to be stored in a data type. When sql_mode=STRICT_TRANS_TABLES (the default) is set, an out-of-range value generates an error. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).
An example of non-strict out-of-range behavior:
A special type of MEDIUMINT UNSIGNED is MEDIUMINT ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros are just enough to pad the field out to the length of the type's maximum unsigned value, but the zeros are not included in an expression result or in a UNION SELECT column.
Using MEDIUMINT ZEROFILL works the same way as MEDIUMINT UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:
The resulting data would look like this:
This page is licensed: GPLv2, originally from
INT[(M)] [SIGNED | UNSIGNED | ZEROFILL]
INTEGER[(M)] [SIGNED | UNSIGNED | ZEROFILL]MEDIUMINT[(M)] [SIGNED | UNSIGNED | ZEROFILL]-3.402823466E+38 to -1.175494351E-38
0
1.175494351E-38 to 3.402823466E+38.
These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.
UNSIGNED, if specified, disallows negative values.
Using FLOAT might give you some unexpected problems because all calculations in MariaDB are done with double precision. See Floating Point Accuracy.
For more details on the attributes, see Numeric Data Type Overview.
The FLOAT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of FLOAT SIGNED (SIGNED is the default):
Example of FLOAT UNSIGNED:
A value is considered "out of range" when it is too small or too large to be stored in a data type. The size specified when creating the column is the limit for what values can be represented. The limits can also vary based on your hardware and operating system. When SQL_MODE is strict (the default) an out-of-range value generates an error and the operation fails. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).
A value whose significant digits must be rounded to fit only generates a warning note about data truncation, since it is only an out-of-range value if the rounding causes the value to overflow. A somewhat strange exception happens when the decimal places are 16 digits or larger: at that point the value can round up to be one digit larger than you would expect to be accepted, but only for the next larger power of 10. For instance, a FLOAT(17,16) should max out at 9.9999999999999999, but that value rounds up to being equivalent to 10 (and 11 overflows).
A special type of FLOAT UNSIGNED is FLOAT ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros are just enough to pad the field out to the length of the type's field size (counting the decimal point), but the zeros are not included in an expression result or in a UNION SELECT column.
Using FLOAT ZEROFILL works the same way as FLOAT UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:
This page is licensed: GPLv2, originally from fill_help_tables.sql
CREATE TABLE ints (a INT,b INT UNSIGNED,c INT ZEROFILL);INSERT INTO ints VALUES (-10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'b' at row 1
INSERT INTO ints VALUES (-10,10,-10);
ERROR 1264 (22003): Out of range value for column 'c' at row 1
INSERT INTO ints VALUES (-10,10,10);
INSERT INTO ints VALUES (2147483648,2147483648,2147483648);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
INSERT INTO ints VALUES (2147483647,2147483648,2147483648);
SELECT * FROM ints;
+------------+------------+------------+
| a | b | c |
+------------+------------+------------+
| -10 | 10 | 0000000010 |
| 2147483647 | 2147483648 | 2147483648 |
+------------+------------+------------+CREATE TABLE int_signed_example (
description VARCHAR(20),
example INT SIGNED
);INSERT INTO int_signed_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', -2147483648),
('Maximum', 2147483647);CREATE TABLE int_unsigned_example (
description VARCHAR(20),
example INT UNSIGNED
);INSERT INTO int_unsigned_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 4294967295);TRUNCATE int_signed_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO int_signed_example VALUES
('Underflow', -2147483649),
('Overflow', 2147483648);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT * FROM int_signed_example;
+-------------+-------------+
| description | example |
+-------------+-------------+
| Underflow | -2147483648 |
| Overflow | 2147483647 |
+-------------+-------------+TRUNCATE int_unsigned_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO int_unsigned_example VALUES
('Underflow', -1),
('Overflow', 4294967296);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT * FROM int_unsigned_example;
+-------------+------------+
| description | example |
+-------------+------------+
| Underflow | 0 |
| Overflow | 4294967295 |
+-------------+------------+CREATE TABLE int_zerofill_example (
description VARCHAR(20),
example INT ZEROFILL
);INSERT INTO int_zerofill_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 4294967295);
-- Turn off strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO int_zerofill_example VALUES
('Underflow', -1),
('Overflow', 4294967296);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT *, example + 0 FROM int_zerofill_example;
+-------------+------------+-------------+
| description | example | example + 0 |
+-------------+------------+-------------+
| Zero | 0000000000 | 0 |
| Forty-Two | 0000000042 | 42 |
| Minimum | 0000000000 | 0 |
| Maximum | 4294967295 | 4294967295 |
| Underflow | 0000000000 | 0 |
| Overflow | 4294967295 | 4294967295 |
+-------------+------------+-------------+CREATE TABLE mediumints (a MEDIUMINT,b MEDIUMINT UNSIGNED,c MEDIUMINT ZEROFILL);
DESCRIBE mediumints;
+-------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------------+------+-----+---------+-------+
| a | mediumint(9) | YES | | NULL | |
| b | mediumint(8) unsigned | YES | | NULL | |
| c | mediumint(8) unsigned zerofill | YES | | NULL | |
+-------+--------------------------------+------+-----+---------+-------+INSERT INTO mediumints VALUES (-10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'b' at row 1
INSERT INTO mediumints VALUES (-10,10,-10);
ERROR 1264 (22003): Out of range value for column 'c' at row 1
INSERT INTO mediumints VALUES (-10,10,10);
INSERT INTO mediumints VALUES (8388608,8388608,8388608);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
INSERT INTO mediumints VALUES (8388607,8388608,8388608);
SELECT * FROM mediumints;
+---------+---------+----------+
| a | b | c |
+---------+---------+----------+
| -10 | 10 | 00000010 |
| 8388607 | 8388608 | 08388608 |
+---------+---------+----------+CREATE TABLE mediumint_signed_example (
description VARCHAR(20),
example MEDIUMINT SIGNED
);INSERT INTO mediumint_signed_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', -8388608),
('Maximum', 8388607);CREATE TABLE mediumint_unsigned_example (
description VARCHAR(20),
example MEDIUMINT UNSIGNED
);INSERT INTO mediumint_unsigned_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 16777215);TRUNCATE mediumint_signed_example;-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));INSERT INTO mediumint_signed_example VALUES
('Underflow', -8388609),
('Overflow', 8388608);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT * FROM mediumint_signed_example;+-------------+----------+
| description | example |
+-------------+----------+
| Underflow | -8388608 |
| Overflow | 8388607 |
+-------------+----------+TRUNCATE mediumint_unsigned_example;-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));INSERT INTO mediumint_unsigned_example VALUES
('Underflow', -1),
('Overflow', 16777216);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT * FROM mediumint_unsigned_example;+-------------+----------+
| description | example |
+-------------+----------+
| Underflow | 0 |
| Overflow | 16777215 |
+-------------+----------+CREATE TABLE mediumint_zerofill_example (
description VARCHAR(20),
example MEDIUMINT ZEROFILL
);INSERT INTO mediumint_zerofill_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 16777215);-- Turn off strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));INSERT INTO mediumint_zerofill_example VALUES
('Underflow', -1),
('Overflow', 16777216);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT *, example + 0 FROM mediumint_zerofill_example;+-------------+----------+-------------+
| description | example | example + 0 |
+-------------+----------+-------------+
| Zero | 00000000 | 0 |
| Forty-Two | 00000042 | 42 |
| Minimum | 00000000 | 0 |
| Maximum | 16777215 | 16777215 |
| Underflow | 00000000 | 0 |
| Overflow | 16777215 | 16777215 |
+-------------+----------+-------------+FLOAT[(M,D)] [SIGNED | UNSIGNED | ZEROFILL]CREATE TABLE float_signed_example (
description VARCHAR(20),
example FLOAT,
sz6_2 FLOAT(6,2),
sz20_19 FLOAT(20,19) SIGNED
);SET @pi = 3.1415926535897932384626433832795;
INSERT INTO float_signed_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Negative', -1234.567890123, -1234.567890123, -1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);SELECT * FROM float_signed_example;
+-------------+------------+----------+------------------------+
| description | example | sz6_2 | sz20_19 |
+-------------+------------+----------+------------------------+
| Pi | 3.14159 | 3.14 | 3.1415927410125732000 |
| Series | 1234.57 | 1234.57 | 1.2345678806304932000 |
| Negative | -1234.57 | -1234.57 | -1.2345678806304932000 |
| Various | 1234570000 | 9999.99 | 10.0000000000000000000 |
+-------------+------------+----------+------------------------+CREATE TABLE float_unsigned_example (
description VARCHAR(20),
example FLOAT UNSIGNED,
sz6_2 FLOAT(6,2) UNSIGNED,
sz20_19 FLOAT(20,19) UNSIGNED
);SET @pi = 3.1415926535897932384626433832795;INSERT INTO float_unsigned_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);SELECT * FROM float_unsigned_example;
+-------------+------------+---------+------------------------+
| description | example | sz6_2 | sz20_19 |
+-------------+------------+---------+------------------------+
| Pi | 3.14159 | 3.14 | 3.1415927410125732000 |
| Series | 1234.57 | 1234.57 | 1.2345678806304932000 |
| Various | 1234570000 | 9999.99 | 10.0000000000000000000 |
+-------------+------------+---------+------------------------+TRUNCATE float_signed_example;
-- Disable strict mode
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO float_signed_example VALUES
('OK', 3.402823466e38, NULL, NULL),
('Overflow', 4.0e38, NULL, NULL),
('OK', -3.402823466e38, NULL, NULL),
('Underflow', -4.0e38, NULL, NULL),
('OK', NULL, 9999.99, NULL),
('Rounded overflow', NULL, 9999.995, NULL),
('Overflow', NULL, 10000, NULL),
('OK', NULL, -9999.99, NULL),
('Rounded underflow', NULL, -9999.995, NULL),
('Underflow', NULL, -10000, NULL),
('OK', NULL, NULL, 9.9999999999999999),
('Rounded OK', NULL, NULL, 9.99999999999999995),
('Overflow', NULL, NULL, 11),
('OK', NULL, NULL, -9.9999999999999999),
('Rounded OK', NULL, NULL, -9.99999999999999995),
('Underflow', NULL, NULL, -11);Warning (sql 1264): Out of range value for column 'example' at row 2
Warning (sql 1264): Out of range value for column 'example' at row 4
Warning (sql 1264): Out of range value for column 'sz6_2' at row 6
Warning (sql 1264): Out of range value for column 'sz6_2' at row 7
Warning (sql 1264): Out of range value for column 'sz6_2' at row 9
Warning (sql 1264): Out of range value for column 'sz6_2' at row 10
Warning (sql 1264): Out of range value for column 'sz20_19' at row 13
Warning (sql 1264): Out of range value for column 'sz20_19' at row 16SELECT * FROM float_signed_example;
+-------------------+-------------+----------+-------------------------+
| description | example | sz6_2 | sz20_19 |
+-------------------+-------------+----------+-------------------------+
| OK | 3.40282e38 | NULL | NULL |
| Overflow | 3.40282e38 | NULL | NULL |
| OK | -3.40282e38 | NULL | NULL |
| Underflow | -3.40282e38 | NULL | NULL |
| OK | NULL | 9999.99 | NULL |
| Rounded overflow | NULL | 9999.99 | NULL |
| Overflow | NULL | 9999.99 | NULL |
| OK | NULL | -9999.99 | NULL |
| Rounded underflow | NULL | -9999.99 | NULL |
| Underflow | NULL | -9999.99 | NULL |
| OK | NULL | NULL | 10.0000000000000000000 |
| Rounded OK | NULL | NULL | 10.0000000000000000000 |
| Overflow | NULL | NULL | 10.0000000000000000000 |
| OK | NULL | NULL | -10.0000000000000000000 |
| Rounded OK | NULL | NULL | -10.0000000000000000000 |
| Underflow | NULL | NULL | -10.0000000000000000000 |
+-------------------+-------------+----------+-------------------------+TRUNCATE float_unsigned_example;
-- Disable strict mode
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO float_unsigned_example VALUES
('OK', 3.402823466e38, NULL, NULL),
('Overflow', 4.0e38, NULL, NULL),
('Underflow', -1, NULL, NULL);Warning (sql 1264): Out of range value for column 'example' at row 2
Warning (sql 1264): Out of range value for column 'example' at row 3SELECT * FROM float_unsigned_example;
+-------------+------------+-------+---------+
| description | example | sz6_2 | sz20_19 |
+-------------+------------+-------+---------+
| OK | 3.40282e38 | NULL | NULL |
| Overflow | 3.40282e38 | NULL | NULL |
| Underflow | 0 | NULL | NULL |
+-------------+------------+-------+---------+CREATE TABLE float_zerofill_example (
description VARCHAR(20),
example FLOAT ZEROFILL,
sz6_2 FLOAT(6,2) ZEROFILL,
sz20_19 FLOAT(20,19) ZEROFILL
);SET @pi = 3.1415926535897932384626433832795;INSERT INTO float_zerofill_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);SELECT * FROM float_zerofill_example;
+-------------+--------------+---------+------------------------+
| description | example | sz6_2 | sz20_19 |
+-------------+--------------+---------+------------------------+
| Pi | 000003.14159 | 003.14 | 3.1415927410125732000 |
| Series | 000001234.57 | 1234.57 | 1.2345678806304932000 |
| Various | 001234570000 | 9999.99 | 10.0000000000000000000 |
+-------------+--------------+---------+------------------------+The TINYINT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of TINYINT SIGNED (the default):
Example of TINYINT UNSIGNED:
A value is considered "out of range" when it is too small or too large to be stored in a data type. When sql_mode=STRICT_TRANS_TABLES (the default) is set, an out-of-range value generates an error. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).
An example of non-strict out-of-range behavior:
A special type of TINYINT UNSIGNED is TINYINT ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros are just enough to pad the field out to the length of the type's maximum unsigned value, but the zeros are not included in an expression result or in a UNION SELECT column.
Using TINYINT ZEROFILL works the same way as TINYINT UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:
The resulting data would look like this:
This page is licensed: GPLv2, originally from fill_help_tables.sql
Exact fixed-point number. DECIMAL(M,D) stores values with M total digits and D decimal places, preventing rounding errors common with floating-point types.
A packed "exact" fixed-point number. M is the total number of digits (the precision), and D is the number of digits after the decimal point (the scale).
The decimal point and (for negative numbers) the "-" sign are not counted in M.
If D is 0, values have no decimal point or fractional part, and on , the value will be rounded to the nearest DECIMAL.
The maximum number of digits (M
UNSIGNED, if specified, disallows negative values.
ZEROFILL, if specified, pads the number with zeros, up to the total number of digits specified by M.
All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 65 digits.
For more details on the attributes, see .
DEC, NUMERIC, and FIXED are synonyms, as well as NUMBER in .
The DECIMAL data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of DECIMAL SIGNED (SIGNED is the default):
Example of DECIMAL UNSIGNED:
A value is considered "out of range" when it is too small or too large to be stored in a data type. The size specified when creating the column is the strict limit for what values can be represented. When SQL_MODE is strict (the default), an out-of-range value generates an error, and the operation fails. If strict mode is not in effect, the value is rounded to the nearest valid value, and a warning is generated (which might be hidden, depending on your warning settings).
A value whose significant digits must be rounded to fit only generates a warning note about data truncation, since it is only an out-of-range value if the rounding causes the value to overflow.
A special type of DECIMAL UNSIGNED is DECIMAL ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros is just enough to pad the field out to the length of the type's field size (not counting the decimal place), but the zeros are not included in an expression result or in a UNION SELECT column.
Using DECIMAL ZEROFILL works the same way as DECIMAL UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:
This page is licensed: GPLv2, originally from
Small integer type. A SMALLINT column uses 2 bytes and stores values from -32768 to 32767 (signed) or 0 to 65535 (unsigned).
A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
If a column has been set to ZEROFILL, all values will be prepended by zeros so that the SMALLINT value contains a number of M digits.
If the ZEROFILL attribute has been specified, the column will automatically become UNSIGNED.
INT2 is a synonym for SMALLINT.
For more details on the attributes, see .
The SMALLINT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of SMALLINT SIGNED (the default):
Example of SMALLINT UNSIGNED:
A value is considered "out of range" when it is too small or too large to be stored in a data type. When sql_mode=STRICT_TRANS_TABLES (the default) is set, an out-of-range value generates an error. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).
An example of non-strict out-of-range behavior:
A special type of SMALLINT UNSIGNED is SMALLINT ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros are just enough to pad the field out to the length of the type's maximum unsigned value, but the zeros are not included in an expression result or in a UNION SELECT column.
Using SMALLINT ZEROFILL works the same way as SMALLINT UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:
The resulting data would look like this:
This page is licensed: GPLv2, originally from
TINYINT[(M)] [SIGNED | UNSIGNED | ZEROFILL]CREATE TABLE tinyints (a TINYINT,b TINYINT UNSIGNED,c TINYINT ZEROFILL);INSERT INTO tinyints VALUES (-10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'b' at row 1
INSERT INTO tinyints VALUES (-10,10,-10);
ERROR 1264 (22003): Out of range value for column 'c' at row 1
INSERT INTO tinyints VALUES (-10,10,10);
SELECT * FROM tinyints;
+------+------+------+
| a | b | c |
+------+------+------+
| -10 | 10 | 010 |
+------+------+------+
INSERT INTO tinyints VALUES (128,128,128);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
INSERT INTO tinyints VALUES (127,128,128);
SELECT * FROM tinyints;
+------+------+------+
| a | b | c |
+------+------+------+
| -10 | 10 | 010 |
| 127 | 128 | 128 |
+------+------+------+CREATE TABLE tinyint_signed_example (
description VARCHAR(20),
example TINYINT SIGNED
);INSERT INTO tinyint_signed_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', -128),
('Maximum', 127);CREATE TABLE tinyint_unsigned_example (
description VARCHAR(20),
example TINYINT UNSIGNED
);INSERT INTO tinyint_unsigned_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 255);TRUNCATE tinyint_signed_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO tinyint_signed_example VALUES
('Underflow', -129),
('Overflow', 128);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT * FROM tinyint_signed_example;+-------------+---------+
| description | example |
+-------------+---------+
| Underflow | -128 |
| Overflow | 127 |
+-------------+---------+TRUNCATE tinyint_unsigned_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO tinyint_unsigned_example VALUES
('Underflow', -1),
('Overflow', 256);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT * FROM tinyint_unsigned_example;+-------------+---------+
| description | example |
+-------------+---------+
| Underflow | 0 |
| Overflow | 255 |
+-------------+---------+CREATE TABLE tinyint_zerofill_example (
description VARCHAR(20),
example TINYINT ZEROFILL
);
INSERT INTO tinyint_zerofill_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 255);-- Turn off strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));INSERT INTO tinyint_zerofill_example VALUES
('Underflow', -1),
('Overflow', 256);Warning (sql 1264): Out of range value for column 'example' at row 1
Warning (sql 1264): Out of range value for column 'example' at row 2SELECT *, example + 0 FROM tinyint_zerofill_example;+-------------+---------+-------------+
| description | example | example + 0 |
+-------------+---------+-------------+
| Zero | 000 | 0 |
| Forty-Two | 042 | 42 |
| Minimum | 000 | 0 |
| Maximum | 255 | 255 |
| Underflow | 000 | 0 |
| Overflow | 255 | 255 |
+-------------+---------+-------------+DECIMAL[(M[,D])] [SIGNED | UNSIGNED | ZEROFILL]SMALLINT[(M)] [SIGNED | UNSIGNED | ZEROFILL]CREATE TABLE t1 (x INT4);
DESC t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+CREATE TABLE int4_example (
example INT4
);SHOW CREATE TABLE int4_example\G
*************************** 1. row ***************************
Table: int4_example
Create Table: CREATE TABLE `int4_example` (
`example` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE smallints (a SMALLINT,b SMALLINT UNSIGNED,c SMALLINT ZEROFILL);INSERT INTO smallints VALUES (-10,-10,-10);
ERROR 1264 (22003): Out of range value for column 'b' at row 1
INSERT INTO smallints VALUES (-10,10,-10);
ERROR 1264 (22003): Out of range value for column 'c' at row 1
INSERT INTO smallints VALUES (-10,10,10);
INSERT INTO smallints VALUES (32768,32768,32768);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
INSERT INTO smallints VALUES (32767,32768,32768);
SELECT * FROM smallints;
+-------+-------+-------+
| a | b | c |
+-------+-------+-------+
| -10 | 10 | 00010 |
| 32767 | 32768 | 32768 |
+-------+-------+-------+CREATE TABLE smallint_signed_example (
description VARCHAR(20),
example SMALLINT SIGNED
);INSERT INTO smallint_signed_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', -32768),
('Maximum', 32767);CREATE TABLE smallint_unsigned_example (
description VARCHAR(20),
example SMALLINT UNSIGNED
);INSERT INTO smallint_unsigned_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 65535);TRUNCATE smallint_signed_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO smallint_signed_example VALUES
('Underflow', -32769),
('Overflow', 32768);Warning (Code 1264): Out of range value for column 'example' at row 1
Warning (Code 1264): Out of range value for column 'example' at row 2SELECT * FROM smallint_signed_example;
+-------------+---------+
| description | example |
+-------------+---------+
| Underflow | -32768 |
| Overflow | 32767 |
+-------------+---------+TRUNCATE smallint_unsigned_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO smallint_unsigned_example VALUES
('Underflow', -1),
('Overflow', 65536);Warning (Code 1264): Out of range value for column 'example' at row 1
Warning (Code 1264): Out of range value for column 'example' at row 2SELECT * FROM smallint_unsigned_example;+-------------+---------+
| description | example |
+-------------+---------+
| Underflow | 0 |
| Overflow | 65535 |
+-------------+---------+CREATE TABLE smallint_zerofill_example (
description VARCHAR(20),
example SMALLINT ZEROFILL
);INSERT INTO smallint_zerofill_example VALUES
('Zero', 0),
('Forty-Two', 42),
('Minimum', 0),
('Maximum', 65535);
-- Turn off strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO smallint_zerofill_example VALUES
('Underflow', -1),
('Overflow', 65536);Warning (Code 1264): Out of range value for column 'example' at row 1
Warning (Code 1264): Out of range value for column 'example' at row 2SELECT *, example + 0 FROM smallint_zerofill_example;+-------------+---------+-------------+
| description | example | example + 0 |
+-------------+---------+-------------+
| Zero | 00000 | 0 |
| Forty-Two | 00042 | 42 |
| Minimum | 00000 | 0 |
| Maximum | 65535 | 65535 |
| Underflow | 00000 | 0 |
| Overflow | 65535 | 65535 |
+-------------+---------+-------------+DECIMALThe maximum number of supported decimals (D) is 30 before and 38 afterwards.
If D is omitted, the default is 0. If M is omitted, the default is 10.
CREATE TABLE t1 (d DECIMAL UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES (1),(2),(3),(4.0),(5.2),(5.7);
Query OK, 6 rows affected, 2 warnings (0.16 sec)
Records: 6 Duplicates: 0 Warnings: 2
Note (sql 1265): Data truncated for column 'd' at row 5
Note (sql 1265): Data truncated for column 'd' at row 6
SELECT * FROM t1;
+------------+
| d |
+------------+
| 0000000001 |
| 0000000002 |
| 0000000003 |
| 0000000004 |
| 0000000005 |
| 0000000006 |
+------------+INSERT INTO t1 VALUES (-7);
ERROR 1264 (22003): Out of range value for column 'd' at row 1CREATE TABLE decimal_signed_example (
description VARCHAR(20),
sz10_0 DECIMAL,
sz6_2 DECIMAL(6,2),
sz20_19 DECIMAL(20,19) SIGNED
);SET @pi = 3.1415926535897932384626433832795;INSERT INTO decimal_signed_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Negative', -1234.567890123, -1234.567890123, -1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);SELECT * FROM decimal_signed_example;
+-------------+------------+----------+------------------------+
| description | sz10_0 | sz6_2 | sz20_19 |
+-------------+------------+----------+------------------------+
| Pi | 3 | 3.14 | 3.1415926535897932385 |
| Series | 1235 | 1234.57 | 1.2345678901230000000 |
| Negative | -1235 | -1234.57 | -1.2345678901230000000 |
| Various | 1234567890 | 9999.99 | 9.9999999999999999999 |
+-------------+------------+----------+------------------------+CREATE TABLE decimal_unsigned_example (
description VARCHAR(20),
sz10_0 DECIMAL UNSIGNED,
sz6_2 DECIMAL(6,2) UNSIGNED,
sz20_19 DECIMAL(20,19) UNSIGNED
);SET @pi = 3.1415926535897932384626433832795;INSERT INTO decimal_unsigned_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);SELECT * FROM decimal_unsigned_example;
+-------------+------------+---------+-----------------------+
| description | sz10_0 | sz6_2 | sz20_19 |
+-------------+------------+---------+-----------------------+
| Pi | 3 | 3.14 | 3.1415926535897932385 |
| Series | 1235 | 1234.57 | 1.2345678901230000000 |
| Various | 1234567890 | 9999.99 | 9.9999999999999999999 |
+-------------+------------+---------+-----------------------+TRUNCATE decimal_signed_example;
-- Disable strict mode
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO decimal_signed_example VALUES
('OK', 9999999999.4, NULL, NULL),
('Rounded overflow', 9999999999.5, NULL, NULL),
('Overflow', 10000000000, NULL, NULL),
('OK', -9999999999.4, NULL, NULL),
('Rounded underflow', -9999999999.5, NULL, NULL),
('Underflow', -10000000000, NULL, NULL),
('OK', NULL, 9999.994, NULL),
('Rounded overflow', NULL, 9999.995, NULL),
('Overflow', NULL, 10000, NULL),
('OK', NULL, -9999.994, NULL),
('Rounded underflow', NULL, -9999.995, NULL),
('Underflow', NULL, -10000, NULL),
('OK', NULL, NULL, 9.99999999999999999994),
('Rounded overflow', NULL, NULL, 9.99999999999999999995),
('Overflow', NULL, NULL, 10),
('OK', NULL, NULL, -9.99999999999999999994),
('Rounded underflow', NULL, NULL, -9.99999999999999999995),
('Underflow', NULL, NULL, -10);Note (sql 1265): Data truncated for column 'sz10_0' at row 1
Warning (sql 1264): Out of range value for column 'sz10_0' at row 2
Warning (sql 1264): Out of range value for column 'sz10_0' at row 3
Note (sql 1265): Data truncated for column 'sz10_0' at row 4
Warning (sql 1264): Out of range value for column 'sz10_0' at row 5
Warning (sql 1264): Out of range value for column 'sz10_0' at row 6
Note (sql 1265): Data truncated for column 'sz6_2' at row 7
Warning (sql 1264): Out of range value for column 'sz6_2' at row 8
Warning (sql 1264): Out of range value for column 'sz6_2' at row 9
Note (sql 1265): Data truncated for column 'sz6_2' at row 10
Warning (sql 1264): Out of range value for column 'sz6_2' at row 11
Warning (sql 1264): Out of range value for column 'sz6_2' at row 12
Note (sql 1265): Data truncated for column 'sz20_19' at row 13
Warning (sql 1264): Out of range value for column 'sz20_19' at row 14
Warning (sql 1264): Out of range value for column 'sz20_19' at row 15
Note (sql 1265): Data truncated for column 'sz20_19' at row 16
Warning (sql 1264): Out of range value for column 'sz20_19' at row 17
Warning (sql 1264): Out of range value for column 'sz20_19' at row 18SELECT * FROM decimal_signed_example;+-------------------+-------------+----------+------------------------+
| description | sz10_0 | sz6_2 | sz20_19 |
+-------------------+-------------+----------+------------------------+
| OK | 9999999999 | NULL | NULL |
| Rounded overflow | 9999999999 | NULL | NULL |
| Overflow | 9999999999 | NULL | NULL |
| OK | -9999999999 | NULL | NULL |
| Rounded underflow | -9999999999 | NULL | NULL |
| Underflow | -9999999999 | NULL | NULL |
| OK | NULL | 9999.99 | NULL |
| Rounded overflow | NULL | 9999.99 | NULL |
| Overflow | NULL | 9999.99 | NULL |
| OK | NULL | -9999.99 | NULL |
| Rounded underflow | NULL | -9999.99 | NULL |
| Underflow | NULL | -9999.99 | NULL |
| OK | NULL | NULL | 9.9999999999999999999 |
| Rounded overflow | NULL | NULL | 9.9999999999999999999 |
| Overflow | NULL | NULL | 9.9999999999999999999 |
| OK | NULL | NULL | -9.9999999999999999999 |
| Rounded underflow | NULL | NULL | -9.9999999999999999999 |
| Underflow | NULL | NULL | -9.9999999999999999999 |
+-------------------+-------------+----------+------------------------+CREATE TABLE decimal_zerofill_example (
description VARCHAR(20),
sz10_0 DECIMAL ZEROFILL,
sz6_2 DECIMAL(6,2) ZEROFILL,
sz20_19 DECIMAL(20,19) ZEROFILL
);SET @pi = 3.1415926535897932384626433832795;INSERT INTO decimal_zerofill_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);SELECT * FROM decimal_zerofill_example;
+-------------+------------+---------+-----------------------+
| description | sz10_0 | sz6_2 | sz20_19 |
+-------------+------------+---------+-----------------------+
| Pi | 0000000003 | 0003.14 | 3.1415926535897932385 |
| Series | 0000001235 | 1234.57 | 1.2345678901230000000 |
| Various | 1234567890 | 9999.99 | 9.9999999999999999999 |
+-------------+------------+---------+-----------------------+Synonym for DOUBLE. In standard SQL mode, REAL is a double-precision floating-point number.
See DOUBLE.
CREATE TABLE real_example (
example REAL
);SHOW CREATE TABLE real_example\G*************************** 1. row ***************************
Table: real_example
Create Table: CREATE TABLE `real_example` (
`example` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1This page is: Copyright © 2025 MariaDB. All rights reserved.
1641MBit values can be inserted with b'value' notation, where value is the bit value in 0's and 1's.
Bit fields are automatically zero-padded from the left to the full length of the bit, so for example in a BIT(4) field, '10' is equivalent to '0010'.
Bits are returned as binary, so to display them, either add 0, or use a function such as HEX, OCT or BIN to convert them.
Example of BIT:
This page is licensed: GPLv2, originally from fill_help_tables.sql
BIT[(M)]CREATE TABLE bit_example (
description VARCHAR(20),
b1 BIT,
b4 BIT(4),
b16 BIT(16)
);INSERT INTO bit_example VALUES
('Zero', 0, 0, 0),
('One', 1, 1, 1),
('Two', 0, 2, 2),
('Eight', 0, 8, b'1000'),
('All on', 1, 15, b'1111111111111111');
SELECT description, b1+0, LPAD(BIN(b4), 4, 0) AS b4, HEX(b16)
FROM bit_example;
+-------------+------+------+----------+
| description | b1+0 | b4 | HEX(b16) |
+-------------+------+------+----------+
| Zero | 0 | 0000 | 0 |
| One | 1 | 0001 | 1 |
| Two | 0 | 0010 | 2 |
| Eight | 0 | 1000 | 8 |
| All on | 1 | 1111 | FFFF |
+-------------+------+------+----------+CREATE TABLE b ( b1 BIT(8) );INSERT INTO b VALUES (b'11111111');
INSERT INTO b VALUES (b'01010101');
INSERT INTO b VALUES (b'1111111111111');
ERROR 1406 (22001): Data too long for column 'b1' at row 1
SELECT b1+0, HEX(b1), OCT(b1), BIN(b1) FROM b;
+------+---------+---------+----------+
| b1+0 | HEX(b1) | OCT(b1) | BIN(b1) |
+------+---------+---------+----------+
| 255 | FF | 377 | 11111111 |
| 85 | 55 | 125 | 1010101 |
+------+---------+---------+----------+Explanation of floating-point precision issues. This page details why FLOAT and DOUBLE types are approximate and how rounding errors occur.
Due to their nature, not all floating-point numbers can be stored with exact precision. Hardware architecture, the CPU or even the compiler version and optimization level may affect the precision.
If you are comparing DOUBLEs or FLOATs with numeric decimals, it is not safe to use the equality operator.
Sometimes, changing a floating-point number from single-precision (FLOAT) to double-precision (DOUBLE) will fix the problem.
In the following query, f1, f2 and f3 have seemingly identical values across each row, but due to floating point accuracy, the results may be unexpected.
CREATE TABLE fpn (id INT, f1 FLOAT, f2 DOUBLE, f3 DECIMAL (10,3));
INSERT INTO fpn VALUES (1,2,2,2),(2,0.1,0.1,0.1);
SELECT * FROM fpn WHERE f1*f1 = f2*f2;
+------+------+------+-------+
| id | f1 | f2 | f3 |
+------+------+------+-------+
| 1 | 2 | 2 | 2.000 |
+------+------+------+-------+The reason why only one instead of two rows was returned becomes clear when we see how the floating point squares were evaluated.
This page is licensed: CC BY-SA / Gnu FDL
SELECT f1*f1, f2*f2, f3*f3 FROM fpn;
+----------------------+----------------------+----------+
| f1*f1 | f2*f2 | f3*f3 |
+----------------------+----------------------+----------+
| 4 | 4 | 4.000000 |
| 0.010000000298023226 | 0.010000000000000002 | 0.010000 |
+----------------------+----------------------+----------+