MySQL不同存儲(chǔ)引擎可能會(huì)有不同壹粟。下面的內(nèi)容以InnoDB為主伯铣。
選擇數(shù)據(jù)類型的步驟
- 確定合適的大類型：數(shù)字呻此、字符串、時(shí)間腔寡、二進(jìn)制
- 確定具體的類型：有無符號(hào)焚鲜、取值范圍、變長定長等放前。
整數(shù)類型
| 類型 | 字節(jié)數(shù) | 范圍 |
|---|---|---|
| TNIYINT | 1 | -128~127 |
| SMALLINT | 2 | -32767~32768 |
| MEDIUMINT | 3 | -8388608~8388607 |
| INT | 4 | -2147483648~2147483647 |
| BIGINT | 8 | -9223372036854775808~9223372036854775807 |
- 每個(gè)整數(shù)類型都有對(duì)應(yīng)的無符號(hào)（UNSIGNED）類型忿磅。
- 建議使用TINYINT代替ENUM。
- 避免使用整數(shù)的顯示寬度凭语。
實(shí)數(shù)類型
| 類型 | 字節(jié) | 備注 |
|---|---|---|
| FLOAT | 4 | 單精度浮點(diǎn)數(shù) |
| DOUBLE | 8 | 雙精度浮點(diǎn)數(shù) |
| DECIMAL | 可變 | 高精度定點(diǎn)數(shù) |
- DECIMAL只是一種存儲(chǔ)格式葱她，MySQL以二進(jìn)制的合適存儲(chǔ)DECIMAL類型的列。在計(jì)算中叽粹，DECIMAL會(huì)轉(zhuǎn)換成DOUBLE览效。
- 不建議指定浮點(diǎn)數(shù)的精度。
- 不建議使用DECIMAL虫几。
- 建議要存儲(chǔ)的實(shí)數(shù)乘以相應(yīng)的倍數(shù)锤灿，使用整數(shù)類型運(yùn)算和存儲(chǔ)。
字符串類型
VARCHAR vs CHAR
| 類型 | 最大長度 | 備注 |
|---|---|---|
| CHAR(size) | 255字節(jié) | 定長辆脸。size指定的是字符數(shù)但校，不是字節(jié)數(shù)。 |
| VARCHAR(size) | 65532字節(jié) | 變長啡氢。size指定的是字符數(shù)状囱，不是字節(jié)數(shù)。 |
-
適用VARCHAR：
- 字符串地最大長度比平均長度大很多倘是；
- 列更新很少亭枷，所以碎片不是問題；
- 使用像UTF8這樣復(fù)雜地字符集搀崭，每個(gè)字符都可能使用不同的字節(jié)數(shù)進(jìn)行存儲(chǔ)叨粘。
-
適用CHAR：
- 短字符串；
- 所有值都接近一個(gè)長度瘤睹；
- 經(jīng)常變更的列升敲，這樣不易產(chǎn)生碎片；
CHAR類型的列轰传，原字符串末尾的空格會(huì)被“干掉”驴党，再填充空格。MySQL檢索CHAR不會(huì)使用末尾的空格获茬。（列是定長的港庄，MySQL沒有存儲(chǔ)寫入的字符串有多長倔既，只好一刀切，末尾的空格都忽略掉攘轩。）
VARCHAR末尾的空格不會(huì)被“干掉”叉存，檢索的時(shí)候會(huì)用到。（MySQL存儲(chǔ)了寫入的字符串的長度度帮，這樣可以知道字符串末尾有多少各空格是你寫入的歼捏。）
VARCHAR(5)和VARCHAR(200)，如果都只存了"abc"笨篷，它們有什么不同呢瞳秽？實(shí)際上，MySQL會(huì)分配固定大小地內(nèi)存塊來保存內(nèi)部值率翅，因此VARCHAR(200)的列盡管只存了和VARCHAR(5)一樣的字符串练俐，但是分配的內(nèi)存可能會(huì)大得多。
BINARY vs VARBINARY
| 類型 | 最大長度 | 備注 |
|---|---|---|
| BINARY | 255 | 定長 |
| VARBINARY | 65535 | 變長 |
- BINARY和VARBINARY與CHAR和VARCHAR非常類似冕臭。
- BINARY和VARBINARY存儲(chǔ)的是二進(jìn)制字符串腺晾，與字符集無關(guān)。
- BINARY的末尾會(huì)被填充\0辜贵，并且會(huì)加入檢索悯蝉。
BLOB vs TEXT
L表示數(shù)據(jù)的長度。
L+x表示存儲(chǔ)需要的空間托慨。
| 類型 | 存儲(chǔ) |
|---|---|
| TINYBLOB | L+1 bytes, L < 2^8 |
| SMALLBLOB/BLOB | L+2 bytes, L < 2^16 |
| MEDIUMBLOB | L+3 bytes, L < 2^24 |
| LONGBLOB | L+4 bytes, L < 2^32 |
| TINYTEXT | L+1 bytes, L < 2^8 |
| SMALLTEXT/TEXT | L+2 bytes, L < 2^16 |
| MEDIUMTEXT | L+3 bytes, L < 2^24 |
| LONGTEXT | L+4 bytes, L < 2^32 |
- BLOB系列存儲(chǔ)二進(jìn)制字符串鼻由，與字符集無關(guān)。
- TEXT系列存儲(chǔ)非二進(jìn)制字符串厚棵，與字符集相關(guān)蕉世。
- 一般情況下，你可以認(rèn)為BLOB是一個(gè)更大的VARBINARY婆硬；TEXT是一個(gè)更大的VARCHAR狠轻。
- MySQL只能對(duì)BLOB和TEXT的前面max_sort_length各字符進(jìn)行排序和索引。
- BLOB和TEXT都不能有default value彬犯。
- 當(dāng)BLOB和TEXT的長度太大時(shí)向楼，InnoDB會(huì)使用專門的“外部”存儲(chǔ)區(qū)域來進(jìn)行存儲(chǔ)。
日期和時(shí)間類型
| 類型 | 大小 |
|---|---|
| TIMESTAMP | 4字節(jié) |
| DATETIME | 8字節(jié) |
- MySQL能存儲(chǔ)的最小時(shí)間粒度為秒躏嚎。
- TIMESTAMP是UTC時(shí)間戳，與時(shí)區(qū)相關(guān)菩貌。
- DATETIME的存儲(chǔ)格式是一個(gè)YYYYMMDDHHmmSS的整數(shù)卢佣，與時(shí)區(qū)無關(guān)，你存了什么箭阶，讀出來就是什么虚茶。
- DATETIME的存儲(chǔ)范圍大于TIMESTAMP戈鲁。
- TIMESTAMP的列可以自動(dòng)更新。
- 除非有特殊需求嘹叫，否則建議使用TIMESTAMP婆殿。
ENUM、SET罩扇、BIT
- ENUM列允許在列中存儲(chǔ)一組定義值中的單個(gè)值婆芦。
- SET列允許在列中存儲(chǔ)一組定義值中的一個(gè)或多個(gè)值。
- BIT在InnoDB中其實(shí)是一個(gè)最小的整數(shù)類型喂饥。而MySQL在檢索BIT的時(shí)候會(huì)將其當(dāng)做字符串消约，而不是整數(shù)，這可能會(huì)導(dǎo)致一些奇怪的行為员帮。
- 不建議使用這三個(gè)類型：用整數(shù)代替或粮。
一些原則
- 選擇最小的滿足需求的數(shù)據(jù)類型。
一般情況下捞高，應(yīng)該盡量使用可以正確存儲(chǔ)數(shù)據(jù)的最小數(shù)據(jù)類型氯材。
簡單就好。
比如硝岗，用MySQL的內(nèi)建類型date, time, datetime來存儲(chǔ)時(shí)間氢哮，而不是使用字符串；用INT UNSIGNED來存儲(chǔ)IPv4地址辈讶。
如何存儲(chǔ)IPv6的地址命浴？IPv6地址128bit，MySQL最大的整數(shù)類型BIGINT只有64bit贱除∩校可以將其存儲(chǔ)成定長（16字節(jié)）的二進(jìn)制字符？
- 盡量避免使用NULL月幌。
