Shulou(Shulou.com)05/31 Report--

Editor to share with you what int (11) in MYSQL refers to. I hope you will get something after reading this article. Let's discuss it together.

I often have to deal with mysql at work, but I have always had little knowledge of the field types of mysql. What does int (11) in MYSQL represent? You may not be very clear, so write a summary of this article.

In fact, some basic knowledge of int type has been clearly stated in the figure above. Here I would like to discuss what the commonly used int (11) means. For a long time, I thought this meant limiting the length of int to 11 bits. I didn't understand until I read an article that 11 represents not the length, but the display width of the character. When the field type is int, no matter how wide you set the display width. The maximum and minimum values that can be stored by int types are always fixed. Here are some original 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 affects 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.

So as mentioned in the article, no matter how you set the display width of the int type, the maximum and minimum values that int can store are fixed, so what is the use of this display width?

When the int field type is set to unsigned and filled with 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? because the ZEROFILL property implicitly converts the value to unsigned, so negative values cannot be stored.

Explain it in the following code.

First create a table:

CREATE TABLE int_demo (id INT (11) NOT NULL AUTO_INCREMENT, an 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); INSERT INTO int_demo (a, b, c, d, e) VALUES (1234567890, 1234567890, 1234567890, 1234567890,1234567890); select * from int_demo;idabcde110000000000110000112123456789001234567890123456789012345678901234567890

Note: if you query it with navicate software, it will not show the 0 on the left, but you can see the real data when you export the data, guess that the software has processed the data format?

From the previous example, we can draw the following conclusions:

If a field has unsigned and filled zero attributes, the length of the value will be consistent with the set display width no matter what value the field stores. For example, field b in the above example, the inserted value 1 is displayed as 00000000001. Ten zeros are added to the left until the length reaches 11 digits.

Setting the display width of a field does not limit the range of stored values for the field, for example, field d is set to int (5), but the 10-digit number 1234567890 can still be stored.

The character width set is valid only when the numeric length does not meet the width, such as d field int (5), insert 1, the length is less than 5, so add 4 zeros to the left until 5 bits, but insert 1234567890 when more than 5 bits, then the display width will not work.

After reading this article, I believe you have a certain understanding of "what does int (11) in MYSQL refer to". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.