BIGINT

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 -9223372036854775808 to 9223372036854775807 when signed, or from 0 to 18446744073709551615 when unsigned.

USAGE

DETAILS

SYNONYMS

The following are synonyms for BIGINT:

INT8

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

EXAMPLES

`SIGNED` and `UNSIGNED`

The BIGINT data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).

Example of BIGINT SIGNED (the default):

CREATE TABLE bigint_signed_example (
   description VARCHAR(20),
   example BIGINT SIGNED
);

INSERT INTO bigint_signed_example VALUES
   ('Zero', 0),
   ('Forty-Two', 42),
   ('Minimum', -9223372036854775808),
   ('Maximum', 9223372036854775807);

Example of BIGINT UNSIGNED:

CREATE TABLE bigint_unsigned_example (
   description VARCHAR(20),
   example BIGINT UNSIGNED
);

INSERT INTO bigint_unsigned_example VALUES
   ('Zero', 0),
   ('Forty-Two', 42),
   ('Minimum', 0),
   ('Maximum', 18446744073709551615);

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=STRICT_TRANS_TABLES (the default) is set, an out-of-range value generates an error. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).

An example of non-strict out-of-range behavior:

TRUNCATE bigint_signed_example;

-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));

INSERT INTO bigint_signed_example VALUES
   ('Underflow', -9223372036854775809),
   ('Overflow', 9223372036854775808);

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 bigint_signed_example;

+-------------+----------------------+
| description | example              |
+-------------+----------------------+
| Underflow   | -9223372036854775808 |
| Overflow    |  9223372036854775807 |
+-------------+----------------------+

TRUNCATE bigint_unsigned_example;

-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));

INSERT INTO bigint_unsigned_example VALUES
   ('Underflow', -1),
   ('Overflow', 18446744073709551616);

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 bigint_unsigned_example;

+-------------+----------------------+
| description | example              |
+-------------+----------------------+
| Underflow   |                    0 |
| Overflow    | 18446744073709551615 |
+-------------+----------------------+

`BIGINT ZEROFILL`

A special type of BIGINT UNSIGNED is BIGINT 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 BIGINT ZEROFILL works the same way as BIGINT UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:

CREATE TABLE bigint_zerofill_example (
   description VARCHAR(20),
   example BIGINT ZEROFILL
);

INSERT INTO bigint_zerofill_example VALUES
   ('Zero', 0),
   ('Forty-Two', 42),
   ('Minimum', 0),
   ('Maximum', 18446744073709551615);

-- Turn off strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));

INSERT INTO bigint_zerofill_example VALUES
   ('Underflow', -1),
   ('Overflow', 18446744073709551616);

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 bigint_zerofill_example;

+-------------+----------------------+----------------------+
| description | example              | example + 0          |
+-------------+----------------------+----------------------+
| Zero        | 00000000000000000000 |                    0 |
| Forty-Two   | 00000000000000000042 |                   42 |
| Minimum     | 00000000000000000000 |                    0 |
| Maximum     | 18446744073709551615 | 18446744073709551615 |
| Underflow   | 00000000000000000000 |                    0 |
| Overflow    | 18446744073709551615 | 18446744073709551615 |
+-------------+----------------------+----------------------+

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series	History
10.6 Enterprise	Present starting in MariaDB Enterprise Server 10.6.4-1.
10.6 Community	Present starting in MariaDB Community Server 10.6.0.
10.5 Enterprise	Present starting in MariaDB Enterprise Server 10.5.3-1.
10.5 Community	Present starting in MariaDB Community Server 10.5.0.
10.4 Enterprise	Present starting in MariaDB Enterprise Server 10.4.6-1.
10.4 Community	Present starting in MariaDB Community Server 10.4.0.
10.3 Enterprise	Present starting in MariaDB Enterprise Server 10.3.16-1.
10.3 Community	Present starting in MariaDB Community Server 10.3.0.
10.2 Enterprise	Present starting in MariaDB Enterprise Server 10.2.25-1.
10.2 Community	Present starting in MariaDB Community Server 10.2.0.

Release Series	History
10.6 Enterprise	Present starting in MariaDB Enterprise Server 10.6.4-1.
10.5 Enterprise	Present starting in MariaDB Enterprise Server 10.5.3-1.
10.4 Enterprise	Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES

Additional information on this topic may be found in the MariaDB Public Knowledge Base.