Detailed explanation of the difference between tinyint and int in MySQL

Question: What is the difference between int(1) and tinyint(1)?

With a design like this, I would never write int(1) anyway.

I checked and found that after setting the storage type in MySQL, the storage is fixed-length, that is, int(1) 和int(4) 在硬盤中所占的字節數都是一樣的.

We know that the int type occupies 4 bytes and tinyint occupies 1 byte. int(1) and int(4) are the same in terms of their length and storage method. The only difference is the displayed length. However, a parameter must be set: if列制定了zerofill 就會用0填充顯示. In this case, int(4) will be displayed as 0002.

int(1)和tinyint(4) 相比，肯定int 大.

Note that the number in parentheses after the numeric type does not indicate the length but the display width, which is different from the meaning of the number after varchar and char.

That is to say不管int 后面的數字是多少，它存儲的范圍始終是-2^31 到2^31 - 1 .

In summary, no matter how many numbers are in the brackets of the integer data type, the storage space occupied is the same.

tinyint 1 byte smallint 2 bytes MEDIUMINT 3 bytes

Obviously, int(1) 和tinyint(1) ,選擇tinyint(1) when designing a database.占的儲存空間越少越好，當然要夠用才行. To store a single-digit field like this, it is better to use tinyint(1).

Summarize:

• 1. After the type is specified, the storage is fixed-length. Int(1) and int(4) are the same in terms of their length and storage method. In MySQL, the difference between int(1) and int(4) is the displayed length, but a parameter must be set: if the column is specified with zerofill, it will be filled with 0s. For example, if 2 int(3) is specified, it will be displayed as 002.
• 2.int takes up 4 bytes for storage, tinyint takes up 1 byte for storage, and the storage length determines the different ranges of numbers they represent. The range of numbers represented by int is: integer data (all numbers) from -2^31 (-2,147,483,648) to 2^31 – 1 (2,147,483,647). tinyint represents numbers between 0 and 255.
• 3. Tinyint(1) is no different from tinyint(3). It can store 123. However, if tinyint(3) is zerofilled, when the value 12 is inserted, 012 will be stored. Zerofill automatically pads the left side with zeros, which limits the display length.

The above summary is a bit messy. Here is a brief summary:

There is no difference between tinyint(1) and tinyint(3). They both occupy one byte and have the same storage range.

tinyint(3) zerofill , when the inserted data is less than 3 digits,左邊自動補零, which is to limit the display length

Int(1) and tinyint(1), if sufficient,優先選擇tinyint(1) because占字節少、節省空間.

tinyint 1 byte smallint 2 bytes MEDIUMINT 3 bytes int 4 bytes BIGINT 8 bytes.

However, the 5 in varchar(5) limits the number of characters that can be stored, regardless of their value (regardless of Chinese, English, numbers, etc.).

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.