MYSQL入門學習之四：MYSQL的數據類型
相關鏈接：
MYSQL入門學習之一：基本操作
http://www.BkJia.com/database/201212/173868.html
MYSQL入門學習之二：使用正則表達式搜索
http://www.BkJia.com/database/201212/173869.html
MYSQL入門學習之三：全文本搜索
http://www.BkJia.com/database/201212/173873.html
一、整型 www.2cto.com
整數類型是數據庫中最基本的數據類型。標准SQL中支持INTEGER和SMALLINT這兩種整數類型。MySQL數據庫除了支持這兩種類型外，還擴展支持了TINYINT、MEDIUMINT和BIGINT。
各種整數類型的取值范圍、存儲的字節數如下：
整型 字節數 無符號數的取值范圍 有符號數的取值范圍
TINYINT 1 0~255 -128~127
SMALLINT 2 0~65535 -32768~12767
MEDIUMINT 3 0~16777215 -8388608~8388607
INT 4 0~4294967295 -2147483648~2147483647
INTEGER 4 0~4294967295 -2147483648~2147483647
BIGINT 8 0~18446744073709551615 -9223372036954775808~9223372036854775807
二、浮點數類型和定點數類型
MySQL使用浮點數類型和定點數類型來表示小數。浮點數類型包括單精度浮點數(FLOAT類型)和雙精度浮點數(DOUBLE類型)。定點數類型就是DECIMAL型。
FLOAT型、DOUBLE型、DECIMAL型的取值范圍、存儲的字節數如下：
小數類型 字節數 負數取值范圍 無符號取值范圍
FLOAT 4 -3.402823466E+38~ 0和1.175494351E-38~
-1.175494351E-38 3.402823466E+38
DOUBLE 8 1.7976931348623157E+308~ 0和2.2250738585072014E~
-2.2250738585072014E-308 1.7976931348623157E+308
DECIMAL(M,D) M+2 DOUBLE型 同DOUBLE型
或DEC(M,D)
M:最大長度（包括小數部分，但不包括小數點）
D:小數點後保留長度
對於浮點數和定點數，當插入值的精度高於實際定義的精度，系統會自動進行四捨五入處理。其目的是為了使該值的精度達到要求。浮點數進行四捨五入不會報警，定點數出現警告。
在未指定精度的情況下，浮點數和定點數有其默認的精度。FLOAT類型和DOUBLE類型默認會保存實際精度。這個精度與操作系統和硬件的精度有關。DECIMAL類型默認整數位為10, 小數位為0，即默認為整數。 www.2cto.com
在MySQL中，定點數精度比浮點數要高。而且，浮點數會出現誤差。如果要對數據的精度要求比較高，應該選擇定點數。
三、日期和時間類型
1、DATETIME類型
DATETIME類型表示同時包含日期和時間信息的值。MySQL以'YYYY-MM-DD HH:MM:SS'格式檢索和顯示DATETIME值。支持的范圍為'1000-01-01 00:00:00'～'9999-12-31 23:59:59'。
2、DATE類型
DATE類型表示只有日期值而沒有時間值的值。MySQL用'YYYY-MM-DD'格式檢索和顯示DATE值。支持的范圍為'1000-01-01'～'9999-12-31'。
3、TIME類型
TIME值可以使用多種表示格式。
'D HH:MM:SS.fraction'格式的字符串。還可以使用下面任何一種“非嚴格”語法'HH:MM:SS.fraction'、'HH:MM:SS'、'HH:MM'、'D HH:MM:SS'、'D HH:MM'、'D HH'或'SS'。這裡D表示日，可以取0～34的值。請注意MySQL不保存分數。
'HHMMSS'格式的沒有間隔符的字符串，被假定為是有意義的時間。例如，'101112'被理解為'10:11:12'，但'109712'是不合法的(它有一個沒有意義的分鐘部分)，其將變為'00:00:00'。
HHMMSS格式的數值，被假定為是有意義的時間。例如，101112被理解為'10:11:12'。下面的格式也可以理解：SS、MMSS、HHMMSS、HHMMSS.fraction。請注意MySQL不保存分數。
4、YEAR類型
YEAR類型是一個單字節類型，用於表示年。MySQL以YYYY格式檢索和顯示YEAR值。范圍為1901～2155。
可以指定各種格式的YEAR值。
四位字符串，范圍為'1901'～'2155'。
四位數字，范圍為1901～2155。
兩位字符串，范圍為'00'～'99'。'00'～'69'和'70'～'99'范圍的值分別被轉換為2000～2069和1970～1999范圍的YEAR值。
兩位整數，范圍為1～99。1～69和70～99范圍的值分別被轉換為2001～2069和1970～1999范圍的YEAR值。請注意兩位整數范圍與兩位字符串范圍稍有不同，因為不能直接將零指定為數字並將它解釋為2000。必須將它指定為一個字符串'0'或'00'或它被解釋為0000。
5、TIMESTAMP類型
TIMESTAMP類型使用４個字節來表示日期和時間。TIMESTAMP類型的范圍從1970-01-001 08:00:01到2038-01-19 11:14:07。MySQL中也是以'YYYY-MM-DD HH:MM:SS'的形式顯示TIMESTAMP類型的值。從其形式可以看出，TIMESTAMP類型與DATETIME類型顯示的格式一樣的。給TIMESTAMP類型的字段賦值的表示方法基本與DATETIME類型相同。值的注意的是，TIMESTAMP類型范圍比較小，沒有DATETIME類型的范圍大。因此，輸入值時要保證在TIMESTAMP類型時有效范圍內。
四、字符串類型
字符串類型是在數據庫中存儲字符串的數據類型。
1、CHAR類型和VARCHAR類型
CHAR類型和VARCHAR類型都是在創建表時指定最大長度，其基本形式如下：
字符串類型(M)
例如，CHAR(4)就是指數據類型為CHAR類型，其最大長度為4。
CHAR類型的長度是固定的，在創建表時就指定了。其長度可以是0~255的任意值。
例如，CHAR(100)就是指定CHAR類型的長度為100。
VARCHAR類型的長度是可變的，在創建時指定了最大長度。定義時，其最大值可以取0~65535之間的任意值。指定VARCHAR類型的最大值以後，其長度可以在0到最大長度之間。例如，VARCHAR(100)的最大長度是100。但是，不是每條記錄都要占100個位置。而是在這個最大值范圍內，使用多少分配多少。VARCHAR類型實際占用的空間為字符串的實際長度加1。這樣，可以有效的節約系統的空間。
2、TEXT類型
TEXT類型是一種特殊的字符串類型。TEXT只能保存字符數據，如文章等。TEXT類型包含TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。
類型 允許的長度 存儲空間
TINYTEXT 0~255字節 值的長度+2個字節
TEXT 0~65535字節 值的長度+2個字節
MEDIUMTEXT 0~167772150字節 值的長度+3個字節
LONGTEXT 0~4294967295字節 值的長度+4個字節
從表可以看出，各種TEXT類型的區別在於允許的長度和存儲空間不同。因此在這幾種TEXT中，根據需求選取既能滿足需要以最節約空間的類型即可。
3、ENUM類型(枚舉類型) www.2cto.com
ENUM類型又稱為枚舉類型。在創建表時，ENUM類型的取值范圍就以列表的形式指定了。
屬性名 ENUM('值1', '值2',...., '值n');
其中， '屬性名'參數指定字段名稱；'值n'參數表示列表中的第n個值，這些值末尾的空格將會被系統直接刪除。
ENUm類型的值只能列表中的一個元素。其取值列表中最多能有65535個值。列表中的每個值都有一個順序排序的編號，MySQL中存入的是這個編號，而不是列表中的值。
如果ENUm類型加上了NOT NULL屬性，其默認值為取值列表的第1個元素。如果不加NOT NULL屬性，ENUm類型將允許插入NULL，而且NULL為默認值。
CREATE TABLE IF NOT EXISTS `test`.`enum_tbl`(
`a` ENUM('male','female'),
`b` ENUM('true','false') NOT NULL
);
INSERT INTO `test`.`enum_tbl`
VALUES('male', 'true'),(NULL, 'false'), (NULL, NULL),(20, 20);
SELECT * FROM `enum_tbl`;
4、SET類型
基本形式如下：
屬性名 set('值1','值2','值3'...'值n');
其中，'屬性名'參數指定字段名稱；'值n'參數列表中的第n個值,這些值末尾的空格將會被系統直接刪除。其基本形式與ENUM類型一樣。
SET類型的值可以取列表中一個元素或者多個元素的組合。取多個元素時，不同元素之間用逗號隔開。SET類型的值最多只能是64個元素構成的組合。列表中的每一個值都有一個順序排列的編號。MySQL中存入的是這個編號，而不是列表中的值。
插入記錄時，SET字段裡的元素順序無關緊要。存入MySQL數據庫後，數據庫系統會自動按照定義時的順序顯示。 www.2cto.com
CREATE TABLE IF NOT EXISTS `test`.`set_tbl`(
`a` SET('a','b','c','d','e','f','g')
);
INSERT INTO `test`.`set_tbl` VALUES('f'),('a,b,c'),('d,e,a');
INSERT INTO `test`.`set_tbl` VALUES('h');
SELECT * FROM `set_tbl`;
五、二進制類型
二進制類型是在數據庫中存儲二進制數據的數據類型。
二進制類型 取值范圍
BINARY(M) 字節數為M，允許長度為0~M的定長二進制字符串
VARBINARY(M) 允許長度為0~M的變長二進制字符串，字節數為值的長度加一
BIT(M) M位二進制數據，M最大值為64
TINYBLOB 可變長二進制數據，最多255個字節
BLOB 可變長二進制數據，最多(2[16]-1)個字節
MEDIUMBLOB 可變長二進制數據，最多(2[24]-1)個字節
LONGBLOB 可變長二進制數據，最多(2[32]-1)個字節
1、BINARY和VARBINARY類型
BINARY和VARBINARY類型都是在創建表時指定了最大長度，其基本形式如下 ：
字符串類型(M)
這與CHAR類型和VARCHAR類型相似。
例如，BINARY(10)就是指數據類型為BINARY類型，其最大長度為10。
BINARY類型的長度是固定的，在創建表是就指定了。不足最大長度的空間由"\0"補全。例如，BINARY(50)就是指定BINARY類型的長度為50。
VARBINARY類型的長度是可變的，在創建表時指定了最大長度。指定好了VARBINARY類型的最大值以後，基長度可以在0到最大長度之間。例如，VARBINARY(50)的最大字節長度是50。但是，不是每條記錄的字節長度都是50。在這個最大范圍內，使用多少分配多少。VARBINARY類型實際占用的空間為實際長度加一。這樣，可以有效的節約系統的空間。
2、BIT類型
BIT類型也是創建表時指定了最大長度，其基本形式如下：
BIT(M)
其中，'M'指定了該二進制的數的最大字節長度為M，M的最大值為64。例如，BIT(4)就是數據類型BIT類型，長度為4.若字段的類型BIT(4)，存儲的數據是從0到15。因為，變成二進制以後，15的值為1111，其長度為4。如果插入的值為16，其二進制數為10000，長度為5，超過了最大長度。因此大於等於16的數是不能插入到BIT(4)類型的字段中的。在查詢BIT類型的數據時，要用BIN(字段名+0)來將值轉換為二進制顯示。
3、BLOB類型
BLOB類型是一種特殊的二進制類型。BLOB可以用來保存數據量很大的二進制數據，如圖片等。BLOB類型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。這幾種BLOB類型最大的區別就是能夠保存的最大長度不同。LONGBLOB的長度最大，TINYBLOB的長度最小。
BLOB類型與TEXT類型很類似。不同點在於BLOB類型用於存儲二進制數據，BLOB類型數據是根據其二進制編碼進行比較和排序。而TEXT類型是文本模式進行比較和排序的。
