BIGINT

Overview

Integer from -9223372036854775808 to 9223372036854775807 when signed, or from 0 to 18446744073709551615 when unsigned.

USAGE

BIGINT[(<display_width>)] [SIGNED | UNSIGNED] [ZEROFILL]

DETAILS

Data Type

Minimum Value

Maximum Value

BIGINT SIGNED (the default)

-9223372036854775808

9223372036854775807

BIGINT UNSIGNED

0

18446744073709551615

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 without any warnings.

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);
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);
SELECT * FROM bigint_unsigned_example;
+-------------+----------------------+
| description | example              |
+-------------+----------------------+
| Underflow   |                    0 |
| Overflow    | 18446744073709551615 |
+-------------+----------------------+

BIGINT ZEROFILL

While Xpand accepts the BIGINT ZEROFILL type on table create, the qualifier is ignored and silently dropped:

CREATE TABLE bigint_zerofill_example (
   description VARCHAR(20),
   example BIGINT ZEROFILL
);
SHOW CREATE TABLE bigint_zerofill_example\G
*************************** 1. row ***************************
       Table: bigint_zerofill_example
Create Table: CREATE TABLE `bigint_zerofill_example` (
  `description` varchar(20) CHARACTER SET utf8,
  `example` bigint(20)
) CHARACTER SET utf8 /*$ SLICES=3 */

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.09

  • Present starting in MariaDB Xpand 23.09.1.

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.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES