The Ultimate Guide to High Availability with MariaDB

Numeric Data Type Overview

You are viewing an old version of this article. View the current version here.

Contents

  1. Numeric data types
  2. SIGNED, UNSIGNED and ZEROFILL
    1. Examples

Numeric data types

There are a number of numeric data types:

See the specific articles for detailed information on each.

SIGNED, UNSIGNED and ZEROFILL

Most numeric types can be defined as SIGNED, UNSIGNED or ZEROFILL, for example:

TINYINT[(M)] [SIGNED | UNSIGNED | ZEROFILL]

If SIGNED, or no option, is specified, a portion of the numeric type will be reserved for the sign (plus or minus). For example, a TINYINT SIGNED can range from -128 to 127.

If UNSIGNED is specified, no portion of the numeric type is reserved for the sign, so the range can be larger. For example, a TINYINT UNSIGNED can range from 0 to 255.

If ZEROFILL is specified, the column will be set to UNSIGNED and the spaces used by default to pad the field are replaced with zeros. ZEROFILL is a non-standard MySQL and MariaDB enhancement.

Note that although the preferred syntax indicates that the options are exclusive, more than one option can be specified.

Before MDEV-8659, any combination of the options could be used in any order, with duplicates. In this case:

  • the presence of ZEROFILL makes the column UNSIGNED ZEROFILL.
  • the presence of UNSIGNED makes the column UNSIGNED.

Since MDEV-8659, only the following combinations are supported:

  • SIGNED
  • UNSIGNED
  • ZEROFILL
  • UNSIGNED ZEROFILL
  • ZEROFILL UNSIGNED

The latter two should be replaced with simply ZEROFILL, but are still accepted by the parser.

Examples

CREATE TABLE zf (
  i1 TINYINT SIGNED,
  i2 TINYINT UNSIGNED,
  i3 TINYINT ZEROFILL
);

INSERT INTO zf VALUES (2,2,2);

SELECT * FROM zf;
+------+------+------+
| i1   | i2   | i3   |
+------+------+------+
|    2 |    2 |  002 |
+------+------+------+

Out of range:

INSERT INTO zf VALUES (128,128,128);
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'i1' at row 1 |
+---------+------+---------------------------------------------+

SELECT * FROM zf;
+------+------+------+
| i1   | i2   | i3   |
+------+------+------+
|  127 |  128 |  128 |
+------+------+------+

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.