INT
This page is part of MariaDB's MariaDB Documentation.
The parent of this page is: Data Types for MariaDB Enterprise Server
Topics on this page:
Overview
Integer from -2147483648 to 2147483647 when signed, or from 0 to 4294967295 when unsigned.
DETAILS
SYNONYMS
The following are synonyms for INT:
INT4
INTEGER
EXAMPLES
SIGNED and UNSIGNED
The INT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of INT SIGNED (SIGNED is the default):
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);
Example of INT UNSIGNED:
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);
Out-of-Range
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 is strict (the default) 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:
TRUNCATE int_signed_example;
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_signed_example VALUES
('Underflow', -2147483649),
('Overflow', 2147483648);
INSERT INTO int_unsigned_example VALUES
('Underflow', -1),
('Overflow', 4294967296);
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
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
The resulting data would look like this:
SELECT * FROM int_signed_example;
+-------------+-------------+
| description | example |
+-------------+-------------+
| Underflow | -2147483648 |
| Overflow | 2147483647 |
+-------------+-------------+
SELECT * FROM int_unsigned_example;
+-------------+------------+
| description | example |
+-------------+------------+
| Underflow | 0 |
| Overflow | 4294967295 |
+-------------+------------+
INT ZEROFILL
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:
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 (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
The resulting data would look like this:
SELECT *, 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 |
+-------------+------------+-------------+
CHANGE HISTORY
EXTERNAL REFERENCES
Additional information on this topic may be found in the MariaDB Public Knowledge Base.