What does int(11) represent in MYSQL
- 小云云Original
- 2017-12-19 16:13:262391browse
I often have to deal with mysql at work, but I have always had a vague understanding of the various field types of mysql. What does int(11) in MYSQL represent? You may not know it very well, so I wrote this article to summarize and record it.
In fact, the above picture has already made it very clear about some basic knowledge of the int type. Here I would like to discuss the commonly used int(11) representatives. What does it mean? For a long time, I thought this meant limiting the length of int to 11 digits. It wasn’t until I read an article one day that I realized that 11 does not represent the length, but the display width of the character. , when the field type is int, no matter how much you set the display width, the maximum and minimum values that the int type can store are always fixed, here are some original text snippets
The number in the parenthesis does not determines the max and min values that can be stored in the integer field. The max and min values that can be stored are always fixed.The display width of the column does not affect the maximum value that can be stored in that column. A column with INT(5) or INT(11) can store the same maximum values. Also, if you have a column INT(20) that does not means that you will be able to store 20 digit values (BIGINT values). The column still will store only till the max values of INT.
As mentioned in the article, no matter how the display width of the int type is set, the maximum value that the int can store The value and minimum value are fixed, so what is the use of this display width?
When the int field type is set to Unsigned and filled with zeros (UNSIGNED ZEROFILL), when the number of digits does not reach the set display width, zeros will be added in front of the value. Until the set display width is met, why is there an unsigned limit? It is because the ZEROFILL attribute implicitly converts the value to an unsigned type, so negative values cannot be stored.
Use the following code to explain specifically.
First create a table:
CREATE TABLE int_demo ( id INT(11) NOT NULL AUTO_INCREMENT, a INT(11) NOT NULL, b INT(11) UNSIGNED ZEROFILL NOT NULL, c INT(5) DEFAULT NULL, d INT(5) UNSIGNED ZEROFILL NOT NULL, e INT(15) DEFAULT NULL, PRIMARY KEY (`id`) )
Insert two pieces of data
INSERT INTO int_demo (a, b, c, d, e) VALUES (1, 1, 1, 1, 1); INSERT INTO int_demo (a, b, c, d, e) VALUES (1234567890, 1234567890, 1234567890, 1234567890, 1234567890);
select * from int_demo;
| id | a | b | c | d | e |
|---|---|---|---|---|---|
| 1 | 00000000001 | 1 | 00001 | 1 | |
| 1234567890 | 01234567890 | 1234567890 | 1234567890 | 1234567890 |
- Setting the display width of a field does not limit the range of field storage values. For example, field d is set to int(5), but the 10-digit number 1234567890 can still be stored;
- set characters The width is only valid when the length of the value does not meet the width. For example, in the d field int(5), when 1 is inserted, the length is less than 5, so 4 zeros are added to the left until 5 digits are reached. However, when 1234567890 is inserted, it exceeds 5 digits. At this time, Display width has no effect.
- Related recommendations:
MYSQL tutorial on how to automatically number the results of query data
How to view the storage location of MySQL data files
mysql paging performance exploration
The above is the detailed content of What does int(11) represent in MYSQL. For more information, please follow other related articles on the PHP Chinese website!