[1] The difference between int(3) and int(11) (cognitive error of thinking inertia)

Here, 3 or 11 represents the specific length stored in the database, always thinking that int(3) can only store 3 numbers of length, and int(11) can only store 11 numbers of length.

[2] Mysql Basics

When learning Mysql basic data types, we know:

From the moment when the declaration field is of type int, the field of type int occupies four bytes, one byte is 8 bits, that is, 4 * 8 = 32 bits.

Therefore, the number of numbers that can be represented is the 32 power of 2 (2 ^ 32 = 4294 9672 96 numbers)

And 4294 9672 96 numbers are [0, 4294967295]. Of course, if we distinguish the sign, the value stored will be smaller.

In addition, the zerofill property has the following functions:

(1) When inserting data, when the length of the value of the field is less than the defined length, a 0 of the corresponding length will be filled in front of the value

(2) zerofill defaults to int(10)

(3) When zerofill is used, the unsigned attribute is automatically added by default. When the unsigned attribute is used, the value range is twice the original value.

For example, signed is [- 21474836482147483647], unsigned is [0, 4294967295].

[3] SQL learning instance

(1) SQL statement

-- local Mysql edition V8.0.12
-- --------------------------BEGIN ZEROFILL-----------------
-- [1]Existing delete first
DROP TABLE students;
-- [2]Create table
CREATE TABLE `students` (
  `sId` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `sName` VARCHAR(100) NOT NULL,
  `sAge` INT(2) ZEROFILL NOT NULL,
  `sAddr` VARCHAR(200) DEFAULT NULL,
  `sGrade` INT(2) DEFAULT NULL,
  `sStuId` INT(8) DEFAULT NULL,
  `sSex` INT(1) UNSIGNED DEFAULT NULL,
  `sReverse_00` INT(10) ZEROFILL DEFAULT NULL,
  `sReverse_01` INT(11) UNSIGNED DEFAULT NULL,
  `sReverse_02` INT(11) ZEROFILL UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`sId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
-- [3]Insert source data
INSERT INTO 
students(sName, sAge, sAddr, sGrade, sStuId, sSex, sReverse_00, sReverse_01, sReverse_02) 
VALUES 
('wangchao', 8, 'heyang', 3, 1803111, 0, 4294967296, 4294967296, 4294967296),
('suntao', 9, 'weinan', 6, 1806110, 1, 4294967296, 4294967296, 4294967296),
('liuyan', 16, 'hancheng', 8, 20190811, 0, 4294967296, 4294967296, 4294967296),
('xuhui', 22, 'hancheng', 12, 201912107, 1, 42949672969, 4294967296, 4294967296);
-- [4]Query data information
SELECT sId, sName, sAge, LENGTH(sAge) AS len_Age, sAddr,
sGrade, LENGTH(sGrade) AS len_Grade,
sStuId, LENGTH(sStuId) AS len_StuId, 
sSex,
sReverse_00, LENGTH(sReverse_00) AS len_Reverse00,
sReverse_01, LENGTH(sReverse_01) AS len_Reverse01,
sReverse_02, LENGTH(sReverse_02) AS len_Reverse02
FROM students;
-- [5]Query data length
SELECT sId, sName, sAge, LENGTH(sAge) AS len_Age FROM students;
SELECT sId, sName, sGrade, LENGTH(sGrade) AS len_Grade FROM students;
SELECT sId, sName, sStuId, LENGTH(sStuId) AS len_StuId FROM students;
SELECT sId, sName, sReverse_02, LENGTH(sReverse_02) AS len_Reverse02 FROM students;
-- --------------------------END ZEROFILL-----------------

 

(2) query result data set

(3) Result analysis

In fact, when we choose to use int type, whether int(3) or int(11), it stores 4 bytes in the database.

But when int(3) is used and zerofill is added, if the value you enter is 17, the system will default to the value you store as 017. What do you mean?

That is to say, this 3 represents only a default length. When the length is less than 3 bits, the system will complement with zero. When the length is more than 3 bits, there will be no impact or side effect.

There is no difference between the two except for a slight difference in storage, when we use data.

In practice, to see the different effects of the two, you need to add the property of zerofill after the field type when creating the table, which means filling with 0, otherwise you will not see the effect.

Because we usually do not add this property when creating database table fields, we can say that there is no difference between the two.

To sum up: only data storage may be different.

 

You can also refer to the short book: https://www.jianshu.com/p/a3731f5b4347