TINYINT

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

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.