MySQL中定義數據字段的類型對你數據庫的優化是非常重要的;
MySQL支持多種類型,大致可以分為三類:數值、日期/時間和字符串(字符)類型;
整數類型
Int(N)中N的涵義
定義了 init(5)zerofill 當和int(10)join時顯示寬度對不上有可能出現臨時表
N表示顯示寬度為N,但仍占4字節存儲,存儲范圍不變;
>create?table?int_test(a?int?zerofill?NOT?NULL?auto_increment,?PRIMARY?KEY?(a)); >createtable?int_test_4(a?int(4)?zerofill?NOT?NULL?auto_increment,?PRIMARY?KEY?(a)); >select?*?from?int_test_4; +------------+ |a??????????| +------------+ |???????0001?| |???????0002?| |2147483648?| +------------+ >select?*?from?int_test; +------------+ |a??????????| +------------+ |0000000001?| |0000000002?| |2147483648?| +------------+
關于浮點數類型:1)能不用到盡量不要用,2)浮點數不能用在等號比較的場景
建議TINYINT替換enum
日期和時間類型
表示時間值的日期和時間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。
MySQL5.6不支持year(2)
日期類型注意事項
Timestamp,datatime從MySQL5.6.5支持自動更新為當前的時間:current timestamp
日期轉換:cast(datatime_col as DATE)
>select now()+0;
5.6的us支持
>select?now(4),MICROSECOND(now(4)); +--------------------------+---------------------+ |now(4)???????????????????|MICROSECOND(now(4))?| +--------------------------+---------------------+ |2016-04-16?08:50:01.6589?|?????????????658900?| +--------------------------+---------------------+
timestamp5.6.6之后支持 null
建議datetime?來替換timestamp?
?字符串類型
字符串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節描述了這些類型如何工作以及如何在查詢中使用這些類型;
字符型
varchar與char的區別
char是一種固定長度的類型,varchar則是一種可變長度的類型,它們的區別是: char(M)類型的數據列里,每個值都占用M個字節,如果某個長度小于M,MySQL就會在它的右邊用空格字符補足.(在檢索操作中那些填補出來的空格字符將被去掉)在varchar(M)類型的數據列里,每個值只占用剛好夠用的字節再加上一個用來記錄其長度的字節(即總長度為L+1字節)varchar存儲可變長字符串,小于255字節時需要1個額外字節(大于255需要2個額外字節)存儲長度,最大長度為65532字節(所有列總和);
char存儲定長,讀取時會截斷末尾空格,長度最大為255字符;
1)CHAR(M)的涵義:
實際分配的長度是:M*字符編碼長度=存儲空間
例如:存儲了255個字符?中文漢字是占用3個字節
255*3?=?765??一共是765字節
2)varchar(N)中N的涵義
最大存儲N個字符;varchar(5)和varchar(200)存儲hello所占空間一樣,但后者在排序時會消耗更多內存,因為order by col采用fixed_length計算col長度(memory引擎也一樣)
例如:
varchar(200)utf8下面占用多少字節
200*3+ 2
varchar(64)??utf8?
64*3=192192+1=193
建議:
通常使用MySQL是innodb引擎 innodb引擎本來就是變長存儲
行存儲著:
trx_id,?row-id,rollback,?filed_pointer,?null-flag,?filed1,….
innodb存儲引擎推薦varchar
char更快是針對MyISAM這種堆表 char 定長分配會更快一點
計算例子
舉兩個例說明一下實際長度的計算。
a)若一個表只有一個varchar類型,如定義為
createtable t4(c varchar(N)) charset=gbk;
則此處N的最大值為(65535-1-2)/2= 32766。
減1的原因是實際行存儲從第二個字節開始’;
減2的原因是varchar頭部的2個字節表示長度;
除2的原因是字符編碼是gbk。
b)若一個表定義為
createtable t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;
則此處N的最大值為 (65535-1-2-4-30*3)/3=21812
減1和減2與上例相同;
減4的原因是int類型的c占4個字節;
減30*3的原因是char(30)占用90個字節,編碼是utf8。