MEDIUMINT
This page is part of MariaDB's MariaDB Documentation.
The parent of this page is: Data Types for MariaDB Xpand
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
EXAMPLES
SIGNED and UNSIGNED
The MEDIUMINT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of MEDIUMINT SIGNED (SIGNED is 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_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 without any warnings.
An example of non-strict out-of-range behavior:
TRUNCATE mediumint_signed_example;
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_signed_example VALUES
('Underflow', -8388609),
('Overflow', 8388608);
INSERT INTO mediumint_unsigned_example VALUES
('Underflow', -1),
('Overflow', 16777216);
The resulting data would look like this:
SELECT * FROM mediumint_signed_example;
+-------------+----------+
| description | example |
+-------------+----------+
| Underflow | -8388608 |
| Overflow | 8388607 |
+-------------+----------+
SELECT * FROM mediumint_unsigned_example;
+-------------+----------+
| description | example |
+-------------+----------+
| Underflow | 0 |
| Overflow | 16777215 |
+-------------+----------+
MEDIUMINT ZEROFILL
While Xpand accepts the MEDIUMINT ZEROFILL type on table create, the qualifier is silently dropped.
CREATE TABLE mediumint_zerofill_example (
description VARCHAR(20),
example MEDIUMINT ZEROFILL
);
SHOW CREATE TABLE mediumint_zerofill_example\G
*************************** 1. row ***************************
Table: mediumint_zerofill_example
Create Table: CREATE TABLE `mediumint_zerofill_example` (
`description` varchar(20) CHARACTER SET utf8,
`example` mediumint(9)
) CHARACTER SET utf8 /*$ SLICES=3 */