MySQL in tinyint (1) and zerofill what does it mean

Know that the 1 in tinyint (1) is only for the specified display length and does not represent the storage length, but it is useful to specify Zerofill for the field.

For example tinyint (3), if the actual value is 8, if the column specifies Zerofill, the result shown after the query is 008, and the left side is filled with 0来.

Sample code:

CREATE TABLE Test2 (
ID1 tinyint (2) Zerofill,
Id2 tinyint (3) Zerofill
);

Insert a piece of data: INSERT into test2 values (2,3);

The following figure is displayed after the select:

So now, MySQL in tinyint (1), tinyint (2).

In MySQL, 1 of Int (1) and tinyint (1) are only specified display lengths and do not represent storage lengths, only fields that specify Zerofill are useful
such as int (3), if the actual value is 2, if the column specifies Zerofill, the result of the query is 002, and the left is filled with 0来.

The limit of number of digits is basically meaningless.

Mysql> CREATE TABLE Testint (id int (1), col2 tinyint (1));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT into Testint values (256, 257);
Query OK, 1 row affected, 1 warning (0.03 sec)

Mysql> select * from Testint;
+------+------+
| ID | col2 |
+------+------+
| 256 | 127 |
+------+------+
1 row in Set (0.02 sec)

mysql> INSERT into Testint values (336, 257);
Query OK, 1 row affected, 1 Warning (0.02 sec)

Mysql> select * from Testint;
+------+------+
| ID | col2 |
+------+------+
| 256 | 127 |
| 336 | 127 |
+------+------+
2 rows in Set (0.00 sec)

mysql> INSERT into Testint values (336, 255);
Query OK, 1 row affected, 1 warning (0.03 sec)

Mysql> select * from Testint;
+------+------+
| ID | col2 |
+------+------+
| 256 | 127 |
| 336 | 127 |
| 336 | 127 |
+------+------+
3 Rows in Set (0.00 sec)

Mysql>

Focus on the warning tip above ....
----------------------------
MySQL real, fake such data should use what type of data?
Reply:tinyint (1)

After the storage type is set in MySQL, the storage is fixed-length, that is, int (1) and int (4) have the same number of bytes on the hard disk.

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

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

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

That is, regardless of the number after the int, it is stored in a range of -2^31 to 2^31-1.

Data type of integral type the number in parentheses, regardless of the amount of storage space, is the same

tinyint a byte smallint two bytes mediumint three bytes

Obviously, int (1) and tinyint (1) should choose tinyint (1) when designing the database. The less storage space is, the better, of course, is enough to do. Store a single digit field like this, or use tinyint (1) for good.

Summarize:

1. After the specified type, the storage is fixed-length, int (1) and int (4) are the same from their own lengths or storage modes. 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 filled with 0, as the 2 int (3) will appear as 002

2.int Storage occupies 4 bytes, tinyint storage is 1 bytes, and 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 the number between 0-255.

3.tinyint (1), and tinyint (3) is no different, save 123 can save the next, and if tinyint (3) Zerofill, insert a value of 12, will store 012,zerofill automatically left 0, this is the limit display length.

The summary above is a bit messy. Here's a condensed summary:

tinyint (1) and tinyint (3) are no different, occupy bytes are one, storage scope is the same
tinyint (3) Zerofill, when the inserted data is less than 3 digits, the left side automatically complements 0, which is the limit display length
Int (1) and tinyint (1), in sufficient circumstances, preferred tinyint (1), because less bytes, space saving.
tinyint a byte smallint two bytes mediumint three byte int 4 bytes BIGINT 8 bytes.

However, varchar (5) Here the 5 limit is the number of characters stored, no distinction between the noble and noble (no Chinese, English, digital ...). ）。