《Mysql必讀mysql整數(shù)數(shù)據(jù)類型深入解析》要點(diǎn)：
本文介紹了Mysql必讀mysql整數(shù)數(shù)據(jù)類型深入解析，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。
此處我們給int char沒(méi)有給出他們的寬度,系統(tǒng)默認(rèn)會(huì)給它分配一個(gè)寬度.
M指示最大顯示寬度.最大有效顯示寬度是255.顯示寬度與存儲(chǔ)大小或類型包含的值的范圍無(wú)關(guān)
我們來(lái)進(jìn)行下試驗(yàn)
代碼如下:
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)
那么我們可以看到這里,系統(tǒng)會(huì)自動(dòng)為我們的數(shù)據(jù)類型給出一個(gè)默認(rèn)的寬帶值,這里這個(gè)寬度值其實(shí)只有在zerofill的作用下才能起到一定的作用.在下面我們看下其他的默認(rèn)值是多少,
代碼如下:
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)
這里我們?cè)賮?lái)看下當(dāng)插入值大于數(shù)據(jù)類型的取值范圍的情況：
代碼如下:
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 |
+------+------+
| 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 |
+------+------+
| 127 | c |
| 127 | c |
+------+------+
2 rows in set (0.00 sec)
這里的tinyint是占有一個(gè)字節(jié),就是可以表示從0-255這個(gè)范圍的整數(shù),可是這里為什么直到127呢,
原因是我們沒(méi)有給他設(shè)定無(wú)符號(hào)類型的.
轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/5922.html
