MySQL數(shù)據(jù)類型--------整數(shù)類型實(shí)戰(zhàn)
1. 背景
* MySQL支持SQL標(biāo)準(zhǔn)整數(shù)類型整數(shù)(或INT)和SMALLINT。作為標(biāo)準(zhǔn)的擴(kuò)展，MySQL還支持整數(shù)類型TINYINT、MEDIUMINT和BIGINT
2. 整數(shù)類型所需的存儲和值范圍
| 類型 | 占用空間 | 最小值 | ***值 |
| ?。ㄗ止?jié)） | (signed/Unsigned) | (signed/Unsigned) | |
| TINYINT | １ | -128 |
127 |
| 0 | 255 | ||
| SMALLINT | 2 | -32768 | 32767 |
| 0 | 65535 | ||
| MEDIUMINT | 3 | -8388608 | 8388607 |
| 0 | 16777215 | ||
| INT | 4 | -2147483648 | 2147483647 |
| 0 | 4294967295 | ||
| BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
| 0 | 18446744073709551615 |
3. zerofill 屬性遇到整數(shù)類型應(yīng)用
* 創(chuàng)建zerofill測試表 zerofill_test
- mysql> CREATE TABLE zerofill_test(
- -> `id` INT(6) ZEROFILL
- -> )ENGINE=INNODB CHARSET=utf8mb4;
- Query OK, 0 rows affected (0.08 sec)
* 插入數(shù)值長度小于6的值
- mysql> INSERT INTO zerofill_test SELECT 11111;
- Query OK, 1 row affected (0.03 sec)
- Records: 1 Duplicates: 0 Warnings: 0
* 插入數(shù)值長度大于6的值
- mysql> INSERT INTO zerofill_test SELECT 2222222;
- Query OK, 1 row affected (0.04 sec)
- Records: 1 Duplicates: 0 Warnings: 0
* 查看zerofill_test表所有的數(shù)據(jù)
- mysql> SELECT * FROM zerofill_test;
- +---------+
- | id |
- +---------+
- | 011111 |
- | 2222222 |
- +---------+
- 2 rows in set (0.01 sec)
* zerofill總結(jié)
* 當(dāng)顯示的值長度小于定義時(shí)INT定義的長度，高位補(bǔ)0顯示
* 當(dāng)顯示的值長度大于定義時(shí)INT定義的長度，照常顯示
* zerofill顯示屬性，值不做任何修改
4. Unsigned 類型溢出現(xiàn)象
* 創(chuàng)建unsigned測試表 unsigned_test
- mysql> CREATE TABLE unsigned_test(
- -> id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
- -> data INT UNSIGNED
- -> )ENGINE=INNODB CHARSET=utf8mb4;
- Query OK, 0 rows affected (0.12 sec)
* 插入兩行一大一小的值
- mysql> INSERT INTO unsigned_test SELECT null, 1;
- Query OK, 1 row affected (0.03 sec)
- Records: 1 Duplicates: 0 Warnings: 0
- mysql> INSERT INTO unsigned_test SELECT null, 2;
- Query OK, 1 row affected (0.01 sec)
- Records: 1 Duplicates: 0 Warnings: 0
* 查看表中插入的數(shù)據(jù)值
- mysql> SELECT * FROM unsigned_test;
- +----+------+
- | id | data |
- +----+------+
- | 1 | 1 |
- | 2 | 2 |
- +----+------+
- 2 rows in set (0.00 sec)
* 利用小數(shù)值對大數(shù)值做減運(yùn)算
- mysql> SELECT data - (SELECT data FROM unsigned_test where id=2) FROM unsigned_test where id=1;
- ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`mytest`.`unsigned_test`.`data` - (select `mytest`.`unsigned_test`.`data` from `mytest`.`unsigned_test` where 1))'
5. 整數(shù)類型應(yīng)用總結(jié)
* signed/Unsigned: 是否有符號
* ZEROFILL: 顯示屬性，值不做任何修改
* 推薦不要使用UNSIGNED, 范圍本質(zhì)上沒有大的改變
* UNSIGNED可能會有溢出現(xiàn)象發(fā)生
* 自增INT 類型主鍵建議使用BIGINT
6. 總結(jié)
以需求驅(qū)動技術(shù)，技術(shù)本身沒有優(yōu)略之分，只有業(yè)務(wù)之分。
