MEDIUMINT
This page is part of MariaDB's Documentation.
The parent of this page is: Data Types for MariaDB Enterprise Server
Topics on this page:
Overview
Integer from -8388608 to 8388607 when signed, or from 0 to 16777215 when unsigned.
DETAILS
SYNONYMS
The following are synonyms for MEDIUMINT:
INT3
MIDDLEINT
EXAMPLES
SIGNED and UNSIGNED
The MEDIUMINT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of MEDIUMINT SIGNED (the default):
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);
Example of MEDIUMINT UNSIGNED:
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);
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_
An example of non-strict out-of-range behavior:
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 (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 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 (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 mediumint_unsigned_example;
+-------------+----------+
| description | example |
+-------------+----------+
| Underflow | 0 |
| Overflow | 16777215 |
+-------------+----------+
MEDIUMINT ZEROFILL
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:
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 (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 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 |
+-------------+----------+-------------+
CHANGE HISTORY
EXTERNAL REFERENCES
Additional information on this topic may be found in the MariaDB Public Knowledge Base.