MySQL數字型別int與tinyint、float與decimal如何選擇

NO IMAGE

最近在準備給開發做一個mysql資料庫開發規範方面培訓,一步一步來,結合在生產環境發現的資料庫方面的問題,從幾個常用的資料型別說起。

int、tinyint與bigint

它們都是(精確)整型資料型別,但是佔用位元組數和表達的範圍不同。首先沒有這個表就說不過去了:

TypeStorageMinimum ValueMaximum Value
(Bytes)(Signed/Unsigned)(Signed/Unsigned)
TINYINT1-128127
0255
SMALLINT2-3276832767
065535
MEDIUMINT3-83886088388607
016777215
INT4-21474836482147483647
04294967295
BIGINT8-92233720368547758089223372036854775807
018446744073709551615

只需要知道對應型別佔多少位元組就能推算出範圍了,比如int佔 4 bytes,即4*8=32bits,大約10位數字,也能理解為什麼int預設顯示位數是11。

遇到比較多的是tinyint和bigint,tinyint一般用於存放status,type這種數值小的資料,不夠用時可能會用smallint。bigint一般用於自增主鍵。

為了避免資料庫被過度設計,布林、列舉型別也採用tinyint。

還有一點也是經常被提到的關於 int(M) 中M的理解,int型資料無論是int(4)還是int(11),都已經佔用了 4 bytes 儲存空間,M表示的只是顯示寬度(display width, max value 255),並不是定義int的長度。

例如:

mysql> CREATE TABLE `tc_integer` (
`f_id` bigint(20) PRIMARY KEY AUTO_INCREMENT,
`f_type` tinyint,
`f_flag` tinyint(1),
`f_num` smallint(5) unsigned ZEROFILL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> desc tc_integer;
---------------- ------------------------------- ------ ----- --------- ---------------- 
| Field          | Type                          | Null | Key | Default | Extra          |
---------------- ------------------------------- ------ ----- --------- ---------------- 
| f_id           | bigint(20)                    | NO   | PRI | NULL    | auto_increment |
| f_type         | tinyint(4)                    | YES  |     | NULL    |                |
| f_flag         | tinyint(1)                    | YES  |     | NULL    |                |
| f_num          | smallint(5) unsigned zerofill | YES  |     | NULL    |                |
---------------- ------------------------------- ------ ----- --------- ---------------- 
4 rows in set (0.01 sec)

插入幾條資料看一下:
<!– more –>

mysql> insert into tc_integer values(1, 1, 1, 1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into tc_integer values(9223372036854775808, 127, 127, 65535);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
--------- ------ ----------------------------------------------- 
| Level   | Code | Message                                       |
--------- ------ ----------------------------------------------- 
| Warning | 1264 | Out of range value for column 'f_id' at row 1 |
--------- ------ ----------------------------------------------- 
1 row in set (0.00 sec)
mysql> select i.*, length(i.f_flag) as len_flag from tc_integer i;
--------------------- -------------- --------------- ---------------- ---------- 
| f_id                | f_type       | f_flag        | f_num          | len_flag |
--------------------- -------------- --------------- ---------------- ---------- 
|                   1 |            1 |             1 |          00001 |        1 |
| 9223372036854775807 |          127 |           127 |          65535 |        3 |
--------------------- -------------- --------------- ---------------- ---------- 
2 rows in set (0.00 sec)
mysql> select * from tc_integer where f_num=' 01' and f_num=1 and f_num=f_flag;
------ -------------- --------------- ---------------- 
| f_id | f_type       | f_flag        | f_num          |
------ -------------- --------------- ---------------- 
|    1 |            1 |             1 |          00001 |
------ -------------- --------------- ---------------- 
1 row in set (0.00 sec)

上面的實驗說明了幾個問題:

f_id列插入比最大值還大的數,出現warnings,並且最終的值自動變成 9223372036854775807 。這個坑曾經在遷移到阿里RDS時遇到過,他們的遷移工具是java寫的,結果我們的主鍵值大於java INTEGER裡面的最大限制,導致 duplicate key問題。

f_flag的顯示寬度為1,但並不影響更多位數的顯示。也證實了tinyint(1)並不像char(1)那樣限制儲存長度

f_num定義成無符號的zerofill型別,能儲存的最大數值是65535,而signed才是32767。(當列上使用zerofill時,unsigned會自動加上)

zerofill的作用是在顯示檢索結果的時候,左邊用0補齊到display width,實際儲存時不補0的,僅作為返回結果meta data的一部分。查詢的條件值忽略0和空格

length()在numeric型別中作用於char_length()一樣,因為位元組數已經固定了。

zerofill的使用可能會在複雜join時因為了解不夠深入而帶來問題,所以最終的結論也很簡單:除非極端的特殊需要,儘量不用zerofill,建表時這類int無需指定 (11) 這樣的顯示寬度。

float與decimal

MySQL使用DECIMAL

可以看到float與float(10)是沒區別的,float預設能精確到6位有效數字

mysql> insert into tc_float(f_float9_2,f_decimal9_2) values(123456.78,123456.78);
mysql> insert into tc_float(f_float9_2,f_decimal9_2) values(1234567.1,1234567.125);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
------- ------ --------------------------------------------------- 
| Level | Code | Message                                           |
------- ------ --------------------------------------------------- 
| Note  | 1265 | Data truncated for column 'f_decimal9_2' at row 1 |
------- ------ --------------------------------------------------- 
1 row in set (0.00 sec)
mysql> select * from tc_float;
----- ---------- ----------- ------------ ------------ ------------ ------------- -------------- 
| fid | f_float  | f_float10 | f_float25  | f_float7_3 | f_float9_2 | f_float30_3 | f_decimal9_2 |
----- ---------- ----------- ------------ ------------ ------------ ------------- -------------- 
|   6 | NULL     | NULL      | NULL       | NULL       |  123456.78 | NULL        |    123456.78 |
|   9 | NULL     | NULL      | NULL       | NULL       | 1234567.12 | NULL        |   1234567.13 |
----- ---------- ----------- ------------ ------------ ------------ ------------- -------------- 
mysql> insert into tc_float(f_float7_3) values(12345.1);
ERROR 1264 (22003): Out of range value for column 'f_float7_3' at row 1

float(9,2)與decimal(9,2)是很像的,並沒有前面提到24位一下6位有效數字的限制

他們倆之間的差別就在精度上,f_float9_2本應該是 1234567.10,結果小數點變成 .12 。f_decimal9_2因為標度為2,所以 .125 四捨五入成 .13

將 12345.1 插入f_float7_3列,因為轉成標度3時 12345.100,整個位數大於7,所以 out of range 了

另外在程式設計中應儘量避免做浮點數的比較,否則可能會導致一些潛在的問題。

堅決不允許使用float去存money,使用decimal更加穩妥,但使用decimal做除法依舊會產生浮點型,所以特殊情況請考慮使用整型,貨幣單位使用 分 ,或者除法在最後進行。

參考

MySQL各資料型別的區別

MySQL manual Out-of-Range and Overflow Handling

MySQL FLOAT vs DEC: working with fraction and decimal

Never use floats for money


本文連結地址:http://seanlook.com/2016/04/29/mysql-numeric-int-float/