MySQL BIGINT Number Ranges

This article focuses on the MySQL BIGINT data type and looks into how we can use it to store integer values. We will also learn its range, storage size, and various attributes, including signed, unsigned, and zero fill.

MySQL BIGINT

The BIGINT is a b byte or 64 bits integer value and is very useful in storing huge integer values.

The MySQL BIGINT, like any other MySQL integer data type, can be signed or unsigned. A signed data type indicates that the column can store both positive and negative integer values. This is the default type for most integer types in MySQL. Hence, unless explicitly specified, any integer type column can store both positive and negative integers.

On the other hand, unsigned data type indicates the column can only store positive integer values.

The range for the signed MySQL BIGINT type is between -9223372036854775808 and 9223372036854775807

For unsigned BIGINT type, the value ranges from 0 to 18446744073709551615.

The other attribute of the BIGINT type is the ZEROFILL. With this attribute specified on a column, the column gets automatically set to UNSIGNED.

The zerofill attribute also fills the spaces with zeros.

Examples

Let us look at few examples to illustrate how to use the BIGINT type.

CREATE DATABASE IF NOT EXISTS integers;
USE integers;

Next, let us create a table and populate it with various BIGINT columns, as shown in the query below:

CREATE TABLE examples(x BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, y BIGINT UNSIGNED, z BIGINT ZEROFILL );

Example 1
Let us first try to add all positive values to the table:

INSERT INTO examples(x,y,z) VALUES (1,2,3);

In the example query above, the values are acceptable because they are in the range of signed, unsigned, and zerofill BIGINT types.

SELECT * FROM examples;
+---+------+----------------------+
| x | y | z |
+---+------+----------------------+
| 1 | 2 | 00000000000000000003 |
+---+------+----------------------+
1 row in <strong>set</strong> (0.01 sec)

Example 2
In the next case, let us try adding all negative values. An example query is below:

INSERT INTO examples(x,y,z) VALUES (-1,-2,-3);
ERROR 1264 (22003): Out of range value for column 'y' at row 1

In this case, the query fails as the y column is unsigned. Hence, assigning a negative value to the column is out of the column range.

Example 3
We can observe a similar case as above for the third column. The zerofill attribute automatically makes the column unsigned, making adding a negative value is out of range. An example is as:

INSERT INTO examples(x,y,z) VALUES (-1,2,-3);
ERROR 1264 (22003): Out of range value for column 'z' at row 1

Example 4
Let us now try adding the maximum values for each type. An example query is:

INSERT INTO examples(x,y,z) VALUES (-9223372036854775808, 9223372036854775808, 9223372036854775808);

In the example above, since all the values are in the range, the query executes successfully.

Consider the query below:

INSERT INTO examples(x,y,z) VALUES (9223372036854775807, 9223372036854775808, 9223372036854775808);

You will notice that all the values are on the maximum values. Since the x column is set to AUTO_INCREMENT, adding a value to it will fail.

INSERT INTO examples(y,z) VALUES (9223372036854775808, 9223372036854775808);
ERROR 1062 (23000): Duplicate entry '9223372036854775807' for key 'examples.PRIMARY'

However, if strict mode is disabled in MySQL, you can insert out-of-range values.

SELECT * FROM examples;

Conclusion

In this tutorial, we discussed the MySQL BININT type and the ranges for its various attributes.

MySQL BIGINT Number Ranges

MySQL BIGINT

Examples

Conclusion

About the author

John Otieno