Mysql integer data type in-depth parsing bitsCN.com here we give int char not their width, the system will assign it a width 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.
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,
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:
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. BitsCN.com
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
A Free Trial That Lets You Build Big!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service