The long integer type of mysql is "BIGINT". BIGINT is an ab byte or 64-bit integer value, very useful when storing extremely large integer values. BIGINT, like other integer data types, can be signed or unsigned; the signed data type means that the column can store positive and negative integer values, while the unsigned data type means that the column can only store positive integer values. The range of signed BIGINT types is between "-9223372036854775808" and "9223372036854775807".
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
Integer type is also called numerical data. Numeric data type is mainly used to store numbers.
MySQL provides a variety of numeric data types. Different data types provide different value ranges. The larger the value range that can be stored, the greater the storage space required.
The main integer types provided by MySQL are TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, and AUTO_INCREMENT auto-increment constraints can be added to their attribute fields. The following table lists the numeric types in MySQL.
As can be seen from the above table, the number of bytes required for different types of integer storage is different. The smallest number of bytes is the TINYINT type, and the largest number is the BIGINT type. The more bytes it occupies. The larger the range of values that the type can represent. Therefore, the long integer type of mysql is "BIGINT".
MySQL BIGINT
BIGINT is an ab bytes or 64-bit integer value, useful when storing large integer values.
MySQL BIGINT, like any other MySQL integer data type, can be signed or unsigned. A signed data type means that the column can store both positive and negative integer values. This is the default type for most integer types in MySQL. Therefore, any integer type column can store both positive and negative integers unless explicitly specified.
On the other hand, the unsigned data type means that the column can only store positive integer values.
The range of the signed MySQL BIGINT type is between -9223372036854775808 and 9223372036854775807
For the unsigned BIGINT type, the value range is 0 to 18446744073709551615.
Another property of the BIGINT type is ZEROFILL. When this property is specified on a column, the column is automatically set to UNSIGNED.
The zerofill attribute also fills spaces with zeros.
BIGINT Examples:
Let’s look at a few examples to illustrate how to use the BIGINT type.
CREATE DATABASE IF NOT EXISTS integers; USE integers;
Next, let’s create a table and populate it with various BIGINT columns as shown in the following query:
Example 1
Let us first try to add all positive values to the table:
In the above example query, these values are acceptable because they are in the range of signed, unsigned, and zerofill BIGINT types.
Example 2
In the next case, let's try adding all negative values. An example query is as follows:
In this case, the query fails because the y column is unsigned. Therefore, assigning a negative value to a column is outside the column range.
Example 3
We can observe that the same as above Similar situation in the third column. The zerofill attribute automatically makes the column unsigned, making adding negative values out of range. An example is:
Example 4
Now Let's try adding the maximum value of each type. An example query is:
In the above example, the query executes successfully since all the values are within the range.
Consider the following query:
You will notice that all the values are on the maximum value. Since column x 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.
[Related recommendations:
| Type | Size | Range (signed) | Range (unsigned) | Purpose |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128,127) | (0,255) | Small integer value |
| SMALLINT | 2 bytes | (-32 768, 32 767) | (0, 65 535) | Large integer value |
| MEDIUMINT | 3 bytes | (-8 388 608, 8 388 607) | (0,16 777 215) | Large integer value |
| INT or INTEGER | 4 words Section | (-2 147 483 648, 2 147 483 647) | (0, 4 294 967 295) | Large integer value |
| BIGINT | 8 bytes | (-9,223,372,036,854,775,808, 9 223 372 036 854 775 807) | (0, 18 446 744 073 709 551 615) | Great integer value |
The above is the detailed content of What is the long integer type of mysql?. For more information, please follow other related articles on the PHP Chinese website!