Differences between MySQL database int (1) and tinyint (1) && extended reading

Transferred from: http://blog.csdn.net/phpwish/article/details/7845921

Look at the database structure design of the project today and find a strange place.

' Xxx_detail ' delflag ' int (1) not NULL DEFAULT ' 0 ' COMMENT ' Remove flag ',


' Xxx_category ' delflag ' tinyint (1) Not NULL DEFAULT ' 0 ' COMMENT ' delete tag ',

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

Like this design, I'm not going to write int (1) anyway.

Checked, after the storage type is set in MySQL, the storage is fixed length, that is, int (1) and int (4) are the same number of bytes in the hard disk.

We know that the int type is 4 bytes, and the tinyint account is 1 bytes. Int (1) and int (4) are the same in terms of length or storage, except for the length of the display, but to set a parameter: If the column has a zerofill, it will be filled with 0, and Int (4) will be displayed as 0002.

Int (1) and tinyint (4) are definitely larger than Int.

Note the number in parentheses after the numeric type, not the length, which represents the display width, which is different from the meaning of the number after varchar, char.

This means that regardless of the number after the int, the range it stores is always -2^31 to 2^31-1.

The data type of a fully-integrated integer type the number of brackets in parentheses is the same as the amount of storage space

tinyint one byte smallint two bytes mediumint three bytes

It is obvious that int (1) and tinyint (1) should choose tinyint (1) when designing the database. The less storage space, the better, of course, enough to do. Storing a single-digit field like this, or using tinyint (1) is good.

Summarize:

1. After the type is specified, the storage is fixed-length, and int (1) and int (4) are the same from their own lengths or stored methods. In MySQL, the difference between int (1) and int (4) is the length of the display, but to set a parameter: If the column has a zerofill, it will be displayed with 0 padding, such as 2 int (3), which will be displayed as 002.

2.int storage accounts for 4 bytes, tinyint storage accounts for 1 bytes, and the storage length determines the range 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). The range represented by tinyint is a number between 0-255.

3.tinyint (1), and tinyint (3) No difference, save 123 can be saved, and if tinyint (3) Zerofill, insert value 12, will be stored 012,zerofill auto left 0, this is the limit display length.

The above summary is a bit messy. The following concise summary:

tinyint (1) and tinyint (3) are no different, taking up bytes is one, storage range is the same

tinyint (3) Zerofill, when the inserted data is less than 3 bits, the left automatically 0, this is the limit display length

Int (1) and tinyint (1), if sufficient, preference is given to tinyint (1), since the footprint is small and space-saving.

tinyint a byte smallint two bytes mediumint three bytes int 4 bytes BIGINT 8 bytes.

However, varchar (5) Here the 5 limit is the number of characters stored, the characters are not divided into Chinese, English, digital ... ）。

Differences between MySQL database int (1) and tinyint (1) && extended reading