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.
| 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 |
##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: 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 above example query, these values are acceptable because they are in the range of signed, unsigned, and zerofill BIGINT types. SELECT * FROM examples;
Example 2
In the next case, let's try adding all negative values. An example query is as follows: 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 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: 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
Now Let's try adding the maximum value of each type. An example query is: INSERT INTO examples(x,y,z) VALUES (-9223372036854775808, 9223372036854775808, 9223372036854775808);
In the above example, the query executes successfully since all the values are within the range.
Consider the following query: INSERT INTO examples(x,y,z) VALUES (9223372036854775807, 9223372036854775808, 9223372036854775808);
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. 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;
[Related recommendations:
mysql video tutorial]
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!
