In-depth analysis of mysql Integer Data Types

In this example, the int char is not given their width, and the system will assign a width to it by default.
M indicates the maximum display width. The maximum valid display width is 255. The display width is irrelevant to the storage size or the range of values in the type.
Let's start the experiment.
Copy codeThe Code is as follows:
Mysql (root @ localhost: test 03:19:00)> create table c (
-> Id int not null,
-> Name char not null );
Query OK, 0 rows affected (0.25 sec)
Mysql (root @ localhost: test 03:19:34)> desc c;
+ ------- + --------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + --------- + ------ + ----- + --------- + ------- +
| Id | int (11) | NO | NULL |
| Name | char (1) | NO | NULL |
+ ------- + --------- + ------ + ----- + --------- + ------- +
2 rows in set (0.00 sec)

We can see that the system will automatically provide a default bandwidth value for our data type. Here, the width value can only play a certain role under zerofill. Next, let's take a look at the default values of others,
Copy codeThe Code is as follows:
Mysql (root @ localhost: test 03:34:53)> alter table c modify id smallint;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql (root @ localhost: test 03:39:39)> desc c;
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Id | smallint (6) | YES | NULL |
| Name | varchar (10) | YES | NULL |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
2 rows in set (0.00 sec)
Mysql (root @ localhost: test 03:39:44)> alter table c modify id bigint;
Query OK, 4 rows affected (0.23 sec)
Records: 4 Duplicates: 0 Warnings: 0
Mysql (root @ localhost: test 03:40:12)> desc c;
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
| Id | bigint (20) | YES | NULL |
| Name | varchar (10) | YES | NULL |
+ ------- + ------------- + ------ + ----- + --------- + ------- +
2 rows in set (0.01 sec)

Here we will look at the case where the inserted value is greater than the value range of the Data Type:
Copy codeThe Code is as follows:
Mysql (root @ localhost: test 03:25:58)> insert into c values (300, 'chen ');
Query OK, 1 row affected, 2 warnings (0.08 sec)
Mysql (root @ localhost: test 03:26:20)> show warnings;
+ --------- + ------ + ----------------------------------------------- +
| Level | Code | Message |
+ --------- + ------ + ----------------------------------------------- +
| Warning | 1264 | Out of range value for column 'id' at row 1 |
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+ --------- + ------ + ----------------------------------------------- +
2 rows in set (0.00 sec)
Mysql (root @ localhost: test 03:26:27)> select * from c;
+ ------ +
| Id | name |
+ ------ +
| 1, 127 | c |
+ ------ +
1 row in set (0.02 sec)
Mysql (root @ localhost: test 03:26:40)> insert into c values (320, 'chen ');
Query OK, 1 row affected, 2 warnings (0.05 sec)
Mysql (root @ localhost: test 03:26:53)> select * from c;
+ ------ +
| Id | name |
+ ------ +
| 1, 127 | c |
| 1, 127 | c |
+ ------ +
2 rows in set (0.00 sec)

Here, tinyint occupies a byte, which can represent an integer in the range from 0 to 127. Why is it,The reason is that we didn't set the unsigned type for him.