TINYINT
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 -128 to 127 when signed, or from 0 to 255 when unsigned.
See also: Data Types for MariaDB Xpand 6.1, in 6.0, and in 5.3
DETAILS
SYNONYMS
The following are synonyms for TINYINT:
BOOL
BOOLEAN
INT1
EXAMPLES
SIGNED and UNSIGNED
The TINYINT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of TINYINT SIGNED (SIGNED is the default):
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);
Example of TINYINT UNSIGNED:
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);
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 tinyint_signed_example;
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_signed_example VALUES
('Underflow', -129),
('Overflow', 128);
INSERT INTO tinyint_unsigned_example VALUES
('Underflow', -1),
('Overflow', 256);
The resulting data would look like this:
SELECT * FROM tinyint_signed_example;
+-------------+---------+
| description | example |
+-------------+---------+
| Underflow | -128 |
| Overflow | 127 |
+-------------+---------+
SELECT * FROM tinyint_unsigned_example;
+-------------+---------+
| description | example |
+-------------+---------+
| Underflow | 0 |
| Overflow | 255 |
+-------------+---------+
TINYINT ZEROFILL
While Xpand accepts the TINYINT ZEROFILL type on table create, the qualifier is silently dropped.
CREATE TABLE tinyint_zerofill_example (
description VARCHAR(20),
example TINYINT ZEROFILL
);
SHOW CREATE TABLE tinyint_zerofill_example\G
*************************** 1. row ***************************
Table: tinyint_zerofill_example
Create Table: CREATE TABLE `tinyint_zerofill_example` (
`description` varchar(20) CHARACTER SET utf8,
`example` tinyint(4)
) CHARACTER SET utf8 /*$ SLICES=3 */
CHANGE HISTORY
Release Series | History |
|---|---|
6.1 |
|
6.0 |
|
5.3 |
|