MySQL數字型別int與tinyint、float與decimal如何選擇
最近在準備給開發做一個mysql資料庫開發規範方面培訓,一步一步來,結合在生產環境發現的資料庫方面的問題,從幾個常用的資料型別說起。
int、tinyint與bigint
它們都是(精確)整型資料型別,但是佔用位元組數和表達的範圍不同。首先沒有這個表就說不過去了:
Type | Storage | Minimum Value | Maximum Value |
---|---|---|---|
(Bytes) | (Signed/Unsigned) | (Signed/Unsigned) | |
TINYINT | 1 | -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 |
只需要知道對應型別佔多少位元組就能推算出範圍了,比如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
型別去儲存對精度要求比較高的數值,比如金額,也叫定點數,decimal在mysql記憶體是以字串儲存的。宣告語法是DECIMAL(M,D)
,佔用位元組 M+2 bytes。M是數字最大位數(精度precision),範圍1-65;D是小數點右側數字個數(標度scale),範圍0-30,但不得超過M。
比如定義DECIMAL(7,3)
:
-
能存的數值範圍是 -9999.999 ~ 9999.999,佔用9個位元組
-
123.12 -> 123.120,因為小數點後未滿3位,補0
-
123.1245 -> 123.125,小數點只留3位,多餘的自動四捨五入截斷
-
12345.12 -> 儲存失敗,因為小數點未滿3位,補0變成12345.120,超過了7位。嚴格模式下報錯,非嚴格模式存成9999.999
MySQL使用FLOAT
和DOUBLE
來表示近似數值型別,這是因為十進位制0.1在電腦裡用二進位制是無法精確表示的,只能儘可能的接近。
單精度浮點數float佔4位元組,float標準語法允許通過FLOAT(M)
的形式指定精度,但是這個精度值M只是決定儲存大小: 0-23與預設不指定效果相同,24-53就變成雙精度的DOUBLE
了。
float還有非MySQL自己實現的非標準語法FLOAT(M,D)
,代表最多儲存M個數字長度,其中小數點後數字個數為D。效果與 DECIMAL(M,D)很相似。
double 和 float 的區別是double精度高,有效數字16位(float精度7位)。但double消耗記憶體是float的兩倍,佔8位元組,double的運算速度比float慢得多。
msyql> create table tc_float(fid int primary key auto_increment,f_float float, f_float10 float(10), f_float25 float(25), f_float7_3 float(7,3), f_float9_2 float(9,2), f_float30_3 float(30,3), f_decimal9_2 decimal(9,2));
mysql> insert into tc_float(f_float,f_float10,f_float25) values(123456,123456,123456);
mysql> insert into tc_float(f_float,f_float10,f_float25) values(1234567.89,12345.67,1234567.89);
mysql> select * from tc_float;
+-----+----------+-----------+------------+------------+------------+-------------+--------------+
| fid | f_float | f_float10 | f_float25 | f_float7_3 | f_float9_2 | f_float30_3 | f_decimal9_2 |
+-----+----------+-----------+------------+------------+------------+-------------+--------------+
| 1 | 123456 | 123456 | 123456 | NULL | NULL | NULL | NULL |
| 2 | 1234570 | 12345.7 | 1234567.89 | NULL | NULL | NULL | NULL |
+-----+----------+-----------+------------+------------+------------+-------------+--------------+
-
可以看到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中int、bigint、smallint 和 tinyint的區別與長度的含義MySql
- MySQL 五種整型資料型別的範圍與區別 tinyint smallint mediumint int bigintMySql資料型別
- BOOL,int,float,指標型別的變數a 與“零”的比較語句指標型別變數
- 關於mysql中欄位定義的型別int、tinyint區別MySql型別
- mysql中tinyint、smallint、int和bigint型別的用法區別MySql型別
- decimal,float和double的區別是什麼?Decimal
- 零值比較--BOOL,int,float,指標變數與零值比指標變數
- SQL SERVER數值型別int、bigint、smallint 和 tinyint範圍SQLServer型別
- mysql int(3)與int(10)的數值範圍相同嗎?MySql
- Mysql時間欄位格式如何選擇,TIMESTAMP,DATETIME,INT?MySql
- decimal(numeric )、float 和 real 資料型別的區別Decimal資料型別
- 如何在資料庫中儲存小數:FLOAT、DECIMAL還是BIGINT?資料庫Decimal
- int與Integer的區別
- MySQL中欄位型別與合理的選擇欄位型別;int(11)最大長度是多少?varchar最大長度是多少?MySql型別
- Mysql中tinyint(1)和tinyint(4)的區別詳析WIFRMySql
- python中分辨int和float的差別Python
- 獨享與共享頻寬有哪些區別?如何選擇?
- MySQL資料型別DECIMAL用法MySql資料型別Decimal
- object-c中NSString與int和float的相互轉換Object
- mysql中bigint、int、mediumint、smallint 和 tinyint的取值範圍MySql
- Python 四種數值型別(int,long,float,complex)區別及轉換Python型別
- mysql 中資料型別tinyint(2)括號中的數字意思MySql資料型別
- Python與Golang對比,如何選擇?PythonGolang
- Kafka 與 RabbitMQ 如何選擇使用哪個?KafkaMQ
- iOS倒數計時的探究與選擇iOS
- 選擇率(selectivity)與基數(cardinality)
- mysql 貨幣型別 選擇MySql型別
- int float double 各型別的最大值最小值型別
- CSS偽類與偽元素選擇器區別CSS
- CSS ID選擇器與CLASS選擇器CSS
- MySQL 中的共享表空間與獨立表空間如何選擇MySql
- mysql的varchar(N)和int(N)的含義及其與char區別MySql
- 個人郵箱與企業郵箱的區別有哪些?如何選擇?
- css定位中position:absolute與float的區別CSS
- c語言中int與float資料運算時,資料轉換問題C語言
- 數字型別型別
- MYSQL自動備份策略的選擇與實踐MySql
- Mysql中 int(10)和int(11)的區別MySql