Home  >  Article  >  Database  >  What does int(11) represent in MYSQL

What does int(11) represent in MYSQL

小云云
小云云Original
2017-12-19 16:13:262371browse

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.

What does int(11) represent in MYSQL

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;
##11000000000011000011##2## Note: If you use the navicate software to query it, the left side will not be displayed. 0, but you can see the real data when exporting the data. I guess the software has processed the data format?
id a b c d e
1234567890 01234567890 1234567890 1234567890 1234567890
We can draw the following conclusions from the previous example:

If a field is set to unsigned and filled with zero attributes, then no matter what value is stored in this field , the length of the value will be consistent with the set display width. For example, in field b in the above example, the inserted value 1 is displayed as 00000000001, and 10 zeros are added on the left until the length reaches 11 digits;
  1. 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;
  2. 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.
  3. 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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn