Database int, what is the length after the char field

First come to the conclusion:
The length of the int field has nothing to do with the length of the data you store, but with the display.
The length of the char field is related to the length of the data you store

int field length

Start with int and look at the table below

CREATE TABLE `test10` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `num1` int(3) DEFAULT NULL,
  `num2` int(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

Novice: num1 field, which stores integers with a maximum length of 3

Sorry, the storage size of type int is 4 bytes, 8 bits per byte, or 2^32.The value range of int (-2147483648-2147483647), which can represent a number with a maximum length of 10

The ints in the database, regardless of the length of the subsequent fill-in, will be able to put your storage number in place as long as they are within the range of ints.This raises the question of what it means to set the length.

Take a look at the explanation on the official website

"MySQL also supports choosing to specify the display width of integer values (for example, INT(4)) within parentheses after keywords of this type.This optional display width specification is used to fill the width from the left when the display width is less than the specified column width value.The display width does not limit the range of values that can be saved in a column or the display of values that exceed the specified width of the column."

This is the official website explanation, meaning that when the number of digits is not satisfied, 0 is automatically added, but does not affect storage.

Practice is the only criterion for testing truth.

insert into test10 (num1,num2) values('1','10');

Look at the results

select * from test10
+----+------+------+
| id | num1 | num2 |
+----+------+------+
|  3 |    1 |   10 |
+----+------+------+
1 row in set (0.01 sec)

We found that the data we just inserted was not complemented, according to num1(3),num(6), num1=001, num2=000010, by theory.

Check online to find out that it was the zerofill attribute, without adding

CREATE TABLE `test10` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `num1` int(3) DEFAULT NULL, #Missing zerofill attribute
  `num2` int(6) DEFAULT NULL, #Missing zerofill attribute
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

After modification

`test10` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `num1` int(3) unsigned zerofill DEFAULT NULL,
  `num2` int(6) unsigned zerofill DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

query

select * from test10;
+----+------+--------+
| id | num1 | num2   |
+----+------+--------+
|  3 |  001 | 000010 |
+----+------+--------+
1 row in set (0.01 sec)

Completion was successful.

Now you really know what the length of the int field means!

char field length

Next, we'll talk about char and look at the table below

CREATE TABLE `test20` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
 `char1` char(3)  DEFAULT NULL,
  `char2` char(6)  DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 

Now the novice will say: since the length of the int field does not affect the size of the data stored, so should char.
Sorry and wrong. char ranges from 1 to 255 bytes, 3 bytes per Chinese character.

Anyway, let's try it out as before

insert into test20 (char1,char2) values ('abcd','abcd');

Look at the results

select * from test20;
+----+-------+-------+
| id | char1 | char2 |
+----+-------+-------+
|  4 | a     | b     |
+----+-------+-------+
1 row in set (0.01 sec)

You can see that we insert a length less than defined and that it can be inserted and displayed normally.So what happens if the insertion is longer than the defined length? Let's try it.

insert into test20 (char1,char2) values ('abcd','abcd');
ERROR 1406 (22001): Data too long for column 'char1' at row 1

As you can see, char1 inserts'abcd', which is longer than the data we defined, so it is wrong.
One thing to mention is that char(6) If you only have three bits of data like'abc', it still takes up six bits of storage space, and the extra three are set to empty characters, which is different from varchar.

Then you can conclude that the field length defined by char is related to the length of the data to be stored.
If you want to see a description of each field type, you can see Field data type

It happens to encounter the property zerofill, so learn it

Role of the zerofill attribute

1. When inserting data, when the length of the value of the field is less than the defined length, the corresponding 0 is added before the value.
2. zerofill defaults to int(10)
3. When zerofill is used, unsigned attributes are automatically added by default. When unsigned attributes are used, the range of values is twice the original value, for example, -128~+127 is signed, and 0~256 is unsigned.