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.

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

Example 1

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

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

Example 2

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

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:

Example 4

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

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

Consider the query below:

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.

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

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/mysql-bigint-number-ranges-01.png" data-lazy- height="216" src="data:image/svg xml,” width=”974″>

Conclusion

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