MySQL資料類型介紹

標籤：32位   效率   row   否則   最小   指標   div   取數   數字   

一 選擇資料類型的幾個簡單原則

1. 越小越好，越簡單越好

一般情況下，應該盡量使用可以正確儲存資料的最小最簡單的資料類型。因為它們佔用更小的磁碟，記憶體和CPU緩衝，處理時需要的CPU周期也更少。但同時，在確定選擇時也務必注意不要低估需要儲存的值的範圍，否則會給後期帶來麻煩。

2. 盡量避免NULL

NULL是列的預設屬性，但通常情況下最好指定列為NOT NULL，除非真的需要儲存NULL。因為含有NULL的列使得索引、索引統計和值比較都更加複雜，而且當可為NULL的列被索引時，每個索引記錄需要一個額外的位元組，在myisam表裡甚至還可能導致固定大小的索引變成可變大小的索引。

將可為null的列改為not null帶來的效能提升比較小，所謂除非必要，否則不必特意去修改，但，如果計劃在列上建索引，就應該盡量避免涉及成可為null的列。

InnoDB使用單獨的位（bit）儲存NULL值，所以對於稀疏資料（很多值為NULL，只有少數行的列有非NULL值）有很好的空間效率，但這一點不適用於myisam。

3. IP的儲存

IP並不是字串，而是32位不帶正負號的整數，用小數點將地址分為四段的表示方法只是為了讓人們閱讀容易，所以應該用不帶正負號的整數儲存IP地址，MySQL提供了INET_ATON()和INET_NTOA()函數在這兩種表示方法之間轉換

mysql> show create table test;+-------+-----------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                    |+-------+-----------------------------------------------------------------------------------------------------------------+| test  | CREATE TABLE `test` (  `a` int(10) unsigned DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |+-------+-----------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec) mysql> insert into test values(inet_aton(‘192.168.1.200‘)), (inet_aton(‘200.100.30.241‘));Query OK, 2 rows affected (0.01 sec) mysql> select * from test;+------------+| a          |+------------+| 3232235976 || 3362004721 |+------------+ mysql> select inet_ntoa(a) from test;+----------------+| inet_ntoa(a)   |+----------------+| 192.168.1.200  || 200.100.30.241 |+----------------+

二 具體資料類型介紹

1. 數字類型

（1）整數類型   

基本使用：整數類型（顯示寬度）

註：顯示寬度對於數值的大小並無影響，只是當設定了zerofill的時候，在顯示的時候補0而已。建議不要設顯示寬度及zerofill

（2） 浮點數類型

基本使用：資料類型（M,D）

注1：M是指精度，總的數字長度；D是指標度，小數點後的數字長度，如DECIMAL(5,2) 可以儲存5個數字和兩個小數，即儲存範圍為-999.99到999.99。

注2：分為三類，DECIMAL（定點數）、FLOAT（單精確度浮點型）、DOUBLE（雙精確度浮點型），一般用DECIMAL來儲存精確資料，如工資等，但除非必須，否則最好不要用浮點數類型，可以考慮使用bigint代替DECIMAL，將需要儲存的貨幣單位根據小數的位元乘以相應的倍數即可。

（3）超出範圍和溢出處理

當MySQL儲存了一個超出了指定範圍的數值，其結果依賴於SQL mode的設定。

• 如果sql mode為strict 模式，MySQL會報錯，資料插入失敗
• 如果為非限制模式，MySQL會將數值轉化為範圍允許內的最大或最小值進行儲存，如資料類型為TINYINT 或TINYINT UNSIGNED 時，儲存數值256進去後會被轉化為127或255。

2. 時間類型

 （1）date

範圍：1000-01-01 to 9999-12-31

（2）datetime(fsp)    fsp代表小數點後的位元

範圍：1000-01-01 00:00:00.000000  to  9999-12-31 23:59:59.999999  

eg:  CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

（3）timestamp(fsp)    fsp代表小數點後的位元

範圍：1970-01-01 00:00:01.000000  UTC to  2038-01-19 03:14:07. 999999  UTC

特性：時區轉化 ，儲存時對當前的時區進行轉換，檢索時再轉換回當前的時區

定義時加上DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 會設定目前時間為預設值並在後續更新該行中的任意值時更新時間為目前時間。定義時只加上DEFAULT 而不加 ON UPDATE CURRENT_TIMESTAMP 會設定目前時間為預設值，後續更新該行內容時不再更新該時間值。DEFAULT有兩種選擇，一種是DEFAULT CURRENT_TIMESTAMP ，一種是可以具體指定一個時間。CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,dt DATETIME DEFAULT CURRENT_TIMESTAMP);CREATE TABLE t1 (ts TIMESTAMP DEFAULT 0,dt DATETIME DEFAULT 0);

（4）time

範圍：-838:59:59.000000   to  838:59:59.000000  

（5）year

範圍：year(4) : 1901 to 2155  

          year(1/2):1 to 99   1-69會被轉換為2001 to 2069 ，70-99會被轉換為1970 to 1999   MySQL5.7.5之後已被去掉  

3. 字元類型

（1） char和varchar

• 它們的儲存、資料恢複、最大長度和後面的空格是否保留等方面有所不同
• char(n)和varchar(m)中的n/m代表的是字元，如char(30)就可以儲存30個字元，具體佔用多少位元組與字元集有關
• char是定長字元，char(n)，n的範圍是0-255，其值被儲存時，值的右邊會被填充指定長度的空格。當值被取回時，除非PAD_CHAR_TO_FULL_LENGTH SQL mode 被指定，否則末尾的空格會被移除
  
• varchar被儲存時並不會被填充空格，如果末尾有空格的話，在儲存和取回資料時都會被按原樣保持，與標準sql一致
• varchar是可變長度的字元，varchar(m)，m的範圍是0-65535，其值的最大有效長度為最大行長度65535 bytes，如果值不大於255 bytes，該列會佔用1個位元組來儲存該值的長度（單位為bytes），如果值大於255bytes，該列會佔用2位元組來儲存該值長度。
• 對於char和varchar，如果不使用嚴格sql mode，當插入超出限定範圍的值後，該值會被切斷並產生一個警告。如果設定為strict 模式的話，一旦插入值的非空白字元會被切斷就會會被阻止並誘發一個報錯。
• varchar比定長類型更節省空間的，因為它僅使用必要的空間，但，由於行是變長的，在update時可能使行變得比原來更長，這就導致需要做額外的工作，如果當前頁內沒有更多的空間儲存，innodb還需要分裂頁來使行可以放進頁內，所以需要視使用方式來決定，當字串列的最大長度比平均長度大很多，列的更新很少，或使用了像utf8這樣的字元集，每個字元都使用不同的位元組數進行儲存等情況下可以採用varchar。

（2）binary和varbinary

• 它們與char和varchar類似，唯一不同的就是它們儲存的是位元組碼，這意味著它們沒有字元集，排序比較時是基於值中位元數的值。
• 允許的最大長度也類似，只是這個長度是位元長度，而不是字元長度。
• 對於binary，插入時值的末尾會由0x00 (the zero byte) 來填充，查取資料時末尾的填充不會被移除，在資料比較時，包括order by和distinct操作等，所有的bytes都是顯式的，都參與比較，另外，0x00 和空格在比較時是不同的，0x00 < 空格。
  
• 對於varbinary，插入時值的末尾不會被填充，查取資料時也不會被截取資料。在比較時，所有的bytes都是顯式的，同binary一樣。
• 如果資料在查取時必須與原存入時的資料完全一致的話，用varbinary或blob中的一種會更好，用binary的話其填補功能會造成一定變化，如下所示：

mysql> CREATE TABLE t (c BINARY(3));Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO t SET c = ‘a‘;Query OK, 1 row affected (0.01 sec)mysql> SELECT HEX(c), c = ‘a‘, c = ‘a\0\0‘ from t;+--------+---------+-------------+| HEX(c) | c = ‘a‘ | c = ‘a\0\0‘ |+--------+---------+-------------+| 610000 | 0       | 1           |+--------+---------+-------------+1 row in set (0.09 sec)

（3）blob和text

• blob分為TINYBLOB, BLOB, MEDIUMBLOB 和 LONGBLOB ，text分為TINYTEXT, TEXT, MEDIUMTEXT 和  LONGTEXT
  
• 在blob和text列上加索引時必須加首碼所有
• blob和text列上不能有default值
• 在排序時，只會用列值的前max_sort_length 位元的資料進行比較，max_sort_length的預設值為1024，可以線上更改。
• 查取text或blob時產生的中間結果會儲存在硬碟上的一個暫存資料表中，而不是記憶體中的暫存資料表，因為記憶體中的暫存資料表用的是memory引擎，它不支援text和blob資料類型，所以除非必要，一般不要查取text和blob列，例如要避免使用select *
• 其可儲存的資料長度決定於可用的記憶體和buffer的大小，基本可以隨便存，當其太大時，innodb會分配專門的外部地區來進行儲存，此時每個值在行內僅需要1-4個位元組來儲存一個指標，然後在外部儲存地區儲存實際的值

 （4）enum和set

• enum是枚舉類型，枚舉列可以把一些不重複的字串儲存成一個預定的集合。MySQL在儲存枚舉時會根據列表值的數量壓縮到一個或兩個位元組中，在內部將每個值在列表中的位置儲存成整數，並在表的.frm檔案中儲存“數字-字串”映射關係的“尋找表”。所以要避免用數字作為enum枚舉常量，很容易引起混亂。

  create table test(e enum(‘fish‘, ‘apple‘, ‘dog‘) not null);insert into test values(‘fish‘),(‘dogs‘),(‘apple‘);mysql> select e+0 from test;+-----+| e+0 |+-----+|   1 ||   3 ||   2 |+-----+

• enum排序時是按照內部儲存的整數而不是定義的字串進行

  mysql> select e from test;+-------+| e     |+-------+| fish  || dog   || apple |+-------+　　

• enum的缺點是字串列表是固定的，需要添加或刪除字串時必須使用alter table
• enum只能單選，但set可以多選，如enum的值是‘A,B,C’時，可選的要麼是A，要麼是B，要麼是C，如果是set，就可以是“A,B”，“A,B,C”。

 

MySQL資料類型介紹