我的MYSQL學習心得（4） ： 資料型別

發表於2015-06-03
MySql資料型別

MYSQL裡的BLOB資料型別

BLOB是一個二進位制大物件，用來儲存可變數量的資料。BLOB型別分為4種：TinyBlob、Blob、MediumBlob、LongBlob，

這幾個型別之間的唯一區別是在儲存檔案的最大大小上不同。

MySQL的四種BLOB型別     型別 大小(單位：位元組)

TinyBlob                            最大 255
Blob                                  最大 65K
MediumBlob                       最大 16M
LongBlob                           最大 4G

BLOB列儲存的是二進位制字串（位元組字串）；TEXT列儲存的是非二進位制字串（字元字串）。

BLOB列沒有字符集，並且排序和比較基於列值位元組的數值；TEXT列有一個字符集，並且根據字符集對值進行排序和比較

BLOB是二進位制字串，TEXT是非二進位制字串，兩者均可存放大容量的資訊。BLOB主要儲存圖片、音訊資訊等，

而TEXT只能儲存文字檔案。

SQLSERVER

SQLSERVER並沒有BLOB資料型別，只有大型物件資料型別（BLOB）：

text,ntext,image,nvarchar(max),varchar(max),varbinary(max)和xml資料型別

這些資料型別的資料都存放在LOB型別的資料頁面裡

如有不對的地方，歡迎大家拍磚o(∩_∩)o 

其他型別

第11章：列型別

目錄

11.1. 列型別概述
11.1.1. 數值型別概述
11.1.2. 日期和時間型別概述
11.1.3. 字串型別概述
11.2. 數值型別
11.3. 日期和時間型別
11.3.1. DATETIME、DATE和TIMESTAMP型別
11.3.2. TIME型別
11.3.3. YEAR型別
11.3.4. Y2K事宜和日期型別
11.4. String型別
11.4.1. CHAR和VARCHAR型別
11.4.2. BINARY和VARBINARY型別
11.4.3. BLOB和TEXT型別
11.4.4. ENUM型別
11.4.5. SET型別
11.5. 列型別儲存需求
11.6. 選擇正確的列型別
11.7. 使用來自其他資料庫引擎的列型別

MySQL支援多種列型別：數值型別、日期/時間型別和字串(字元)型別。本章首先對這些列型別進行了概述，然後更加詳細地描述了各種列的型別，以及列型別儲存需求的總結。概述很簡單。關於具體列型別的詳細資訊應查閱詳細的描述，例如指定值時允許使用的格式。

MySQL支援處理空間資料的副檔名。關於空間型別的資訊參見第19章：MySQL中的空間擴充套件。

幾種列型別描述使用了下述慣例：

·         M

表示最大顯示寬度。最大有效顯示寬度是255。

·         D

適用於浮點和定點型別，並表示小數點後面的位數。最大可能的值是30，但不應大於M-2。

·         方括號(‘[’和‘]’)表示可選部分。

11.1. 列型別概述

11.1.1. 數值型別概述
11.1.2. 日期和時間型別概述
11.1.3. 字串型別概述

11.1.1. 數值型別概述

下面為數值列型別的概述。詳細資訊參見11.2節，“數值型別”。列儲存需求參見11.5節，“列型別儲存需求”。

M指示最大顯示寬度。最大有效顯示寬度是255。顯示寬度與儲存大小或型別包含的值的範圍無關，相關描述見11.2節，“數值型別”。

如果為一個數值列指定ZEROFILL，MySQL自動為該列新增UNSIGNED屬性。

SERIAL是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的一個別名。

在整數列定義中，SERIAL DEFAULT VALUE是NOT NULL AUTO_INCREMENT UNIQUE的一個別名。

警告：應當清楚，當使用在整數值（其中一個是UNSIGNED型別）之間使用減號時，結果是無符號。參見12.8節，“Cast函式和操作符”。

·         BIT[(M)]

位欄位型別。M表示每個值的位數，範圍為從1到64。如果M被省略， 預設為1。

·         TINYINT[(M)] [UNSIGNED] [ZEROFILL]

很小的整數。帶符號的範圍是-128到127。無符號的範圍是0到255。

·         BOOL，BOOLEAN

是TINYINT(1)的同義詞。zero值被視為假。非zero值視為真。

在將來，將根據標準SQL引入完全布林型別的處理。

·         SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

小的整數。帶符號的範圍是-32768到32767。無符號的範圍是0到65535。

·         MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

中等大小的整數。帶符號的範圍是-8388608到8388607。無符號的範圍是0到16777215。

·         INT[(M)] [UNSIGNED] [ZEROFILL]

普通大小的整數。帶符號的範圍是-2147483648到2147483647。無符號的範圍是0到4294967295。

·         INTEGER[(M)] [UNSIGNED] [ZEROFILL]

這是INT的同義詞。

·         BIGINT[(M)] [UNSIGNED] [ZEROFILL]

大整數。帶符號的範圍是-9223372036854775808到9223372036854775807。無符號的範圍是0到18446744073709551615。

應清楚BIGINT列的下述內容：

o        使用帶符號的BIGINT或DOUBLE值進行所有演算法，因此除了位函式，不應使用大於9223372036854775807(63位)的無符號的大整數! 如果這樣做，結果中的最後幾位可能出錯，這是由於將BIGINT值轉換為DOUBLE進行四捨五入時造成的錯誤。

MySQL可以在以下情況下處理BIGINT：

§         當使用整數在一個BIGINT列儲存大的無符號的值時。

§         在MIN(col_name)或MAX(col_name)中，其中col_name指BIGINT列。

§         使用操作符(+，-，*等等)並且兩個運算元均為整數時。

o        總是可以使用一個字串在BIGINT列中儲存嚴格整數值。在這種情況下，MySQL執行字串-數字轉換，其間不存在雙精度表示。

o        當兩個運算元均為整數值時，-、+和* 操作符使用BIGINT演算法。這說明如果乘兩個大整數(或來自返回整數的函式)，當結果大於9223372036854775807時，會得到意想不到的結果。

·         FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

小(單精度)浮點數。允許的值是-3.402823466E+38到-1.175494351E-38、0和1.175494351E-38到3.402823466E+38。這些是理論限制，基於IEEE標準。實際的範圍根據硬體或作業系統的不同可能稍微小些。

M是小數縱位數，D是小數點後面的位數。如果M和D被省略，根據硬體允許的限制來儲存值。單精度浮點數精確到大約7位小數位。

如果指定UNSIGNED，不允許負值。

使用浮點數可能會遇到意想不到的問題，因為在MySQL中的所有計算用雙精度完成。參見A.5.7節，“解決與不匹配行有關的問題”。

·         DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

普通大小(雙精度)浮點數。允許的值是-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到 1.7976931348623157E+308。這些是理論限制，基於IEEE標準。實際的範圍根據硬體或作業系統的不同可能稍微小些。

M是小數總位數，D是小數點後面的位數。如果M和D被省略，根據硬體允許的限制來儲存值。雙精度浮點數精確到大約15位小數位。

如果指定UNSIGNED，不允許負值。

·         DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]

為DOUBLE的同義詞。除了：如果SQL伺服器模式包括REAL_AS_FLOAT選項，REAL是FLOAT的同義詞而不是DOUBLE的同義詞。

·         FLOAT(p) [UNSIGNED] [ZEROFILL]

浮點數。p表示精度（以位數表示），但MySQL只使用該值來確定是否結果列的資料型別為FLOAT或DOUBLE。如果p為從0到24，資料型別變為沒有M或D值的FLOAT。如果p為從25到53，資料型別變為沒有M或D值的DOUBLE。結果列範圍與本節前面描述的單精度FLOAT或雙精度DOUBLE資料型別相同。

FLOAT(p)語法與ODBC相容。

·         DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

壓縮的“嚴格”定點數。M是小數位數(精度)的總數，D是小數點(標度)後面的位數。小數點和(負數)的‘-’符號不包括在M中。如果D是0，則值沒有小數點或分數部分。DECIMAL整數最大位數(M)為65。支援的十進位制數的最大位數(D)是30。如果D被省略， 預設是0。如果M被省略， 預設是10。

如果指定UNSIGNED，不允許負值。

所有DECIMAL列的基本計算(+，-，*，/)用65位精度完成。

·         DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

是DECIMAL的同義詞。FIXED同義詞適用於與其它伺服器的相容性。

11.1.2. 日期和時間型別概述

本節綜合討論了臨時列型別。詳細資訊，參見11.3節，“日期和時間型別”。列儲存需求參見11.5節，“列型別儲存需求”。

·         DATE

日期。支援的範圍為’1000-01-01’到’9999-12-31’。MySQL以’YYYY-MM-DD’格式顯示DATE值，但允許使用字串或數字為DATE列分配值。

·         DATETIME

日期和時間的組合。支援的範圍是’1000-01-01 00:00:00’到’9999-12-31 23:59:59’。MySQL以’YYYY-MM-DD HH:MM:SS’格式顯示DATETIME值，但允許使用字串或數字為DATETIME列分配值。

·         TIMESTAMP[(M)]

時間戳。範圍是’1970-01-01 00:00:00’到2037年。

TIMESTAMP列用於INSERT或UPDATE操作時記錄日期和時間。如果你不分配一個值，表中的第一個TIMESTAMP列自動設定為最近操作的日期和時間。也可以通過分配一個NULL值，將TIMESTAMP列設定為當前的日期和時間。

TIMESTAMP值返回後顯示為’YYYY-MM-DD HH:MM:SS’格式的字串，顯示寬度固定為19個字元。如果想要獲得數字值，應在TIMESTAMP 列新增+0。

註釋：MySQL 4.1以前使用的TIMESTAMP格式在MySQL 5.1中不支援；關於舊格式的資訊參見MySQL 4.1 參考手冊。

·         TIME

時間。範圍是’-838:59:59’到’838:59:59’。MySQL以’HH:MM:SS’格式顯示TIME值，但允許使用字串或數字為TIME列分配值。

·         YEAR[(2|4)]

兩位或四位格式的年。預設是四位格式。在四位格式中，允許的值是1901到2155和0000。在兩位格式中，允許的值是70到69，表示從1970年到2069年。MySQL以YYYY 格式顯示YEAR值，但允許使用字串或數字為YEAR列分配值。

11.1.3. 字串型別概述

本節綜合討論了字串列型別。詳細資訊參見11.4節，“String型別”。列儲存需求參見11.5節，“列型別儲存需求”。

在某些情況中，MySQL可以將一個字串列更改為不同於CREATE TABLE或ALTER TABLE語句中所給出的型別。參見13.1.5.1節，“沉寂的列規格變更”。

MySQL 5.1字串資料型別包括部分在MySQL 4.1之前的版本中沒有的特性：

·         許多字串資料型別的列定義可以包括指定字符集的CHARACTER SET屬性，也可能包括校對規則。(CHARSET是CHARACTER SET的一個同義詞）。這些屬性適用於CHAR、VARCHAR、TEXT型別、ENUM和SET。例如：

該表定義建立了一個名為c1的列，具有一個utf8字符集和該字符集的預設 校對規則，和一個名為c2的列以及latin1字符集和該字符集的二元 校對規則。二元校對規則對大小寫不敏感。

·         MySQL 5.1用字元單位解釋在字元列定義中的長度規範。(以前的一些MySQL版本以位元組解釋長度）。

·         對於CHAR、VARCHAR和TEXT型別，BINARY屬性可以為列分配該列字符集的 校對規則。

·         字元列的排序和比較基於分配給列的字符集。在以前的版本中，排序和比較基於伺服器字符集的校對規則。對於CHAR和VARCHAR 列，你可以用BINARY屬性宣告列讓排序和 校對規則使用當前的字元程式碼值而不是詞彙順序。

關於MySQL 5.1中字符集的支援，參見第10章：字符集支援。

·         [NATIONAL] CHAR(M) [BINARY| ASCII | UNICODE]

固定長度字串，當儲存時在右側填充空格以達到指定的長度。M表示列長度。M的範圍是0到255個字元。

註釋：當檢索CHAR值時尾部空格被刪除。

如果想要將某個CHAR的長度設為大於255，執行的CREATE TABLE或ALTER TABLE語句將失敗並提示錯誤：

CHAR是CHARACTER的簡寫。NATIONAL CHAR(或其等效短形式NCHAR)是標準的定義CHAR列應使用 預設字符集的SQL方法。這在MySQL中為預設值。

BINARY屬性是指定列字符集的二元 校對規則的簡寫。排序和比較基於數值字元值。

列型別CHAR BYTE是CHAR BINARY的一個別名。這是為了保證相容性。

可以為CHAR指定ASCII屬性。它分配latin1字符集。

可以為CHAR指定UNICODE屬性。它分配ucs2字符集。

MySQL允許建立型別CHAR(0)的列。這主要用於必須有一個列但實際上不使用值的舊版本的應用程式相相容。當你需要只能取兩個值的列時也很好：沒有定義為NOT NULL的一個CHAR(0)列只佔用一位，只可以取值NULL和”(空字串)。

·         CHAR

這是CHAR(1)的同義詞。

·         [NATIONAL] VARCHAR(M) [BINARY]

變長字串。M 表示最大列長度。M的範圍是0到65,535。(VARCHAR的最大實際長度由最長的行的大小和使用的字符集確定。最大有效長度是65,532位元組）。

註釋：MySQL 5.1遵從標準SQL規範，並且不刪除VARCHAR值的尾部空格。

VARCHAR是字元VARYING的簡寫。

BINARY屬性是指定列的字符集的二元 校對規則的簡寫。排序和比較基於數值字元值。

VARCHAR儲存時用一個位元組或兩個位元組長的字首+資料。如果VARCHAR列宣告的長度大於255，長度字首是兩個位元組。

·         BINARY(M)

BINARY型別類似於CHAR型別，但儲存二進位制位元組字串而不是非二進位制字串。

·         VARBINARY(M)

VARBINARY型別類似於VARCHAR型別，但儲存二進位制位元組字串而不是非二進位制字串。

·         TINYBLOB

最大長度為255(28–1)位元組的BLOB列。

·         TINYTEXT

最大長度為255(28–1)字元的TEXT列。

·         BLOB[(M)]

最大長度為65,535(216–1)位元組的BLOB列。

可以給出該型別的可選長度M。如果給出，則MySQL將列建立為最小的但足以容納M位元組長的值的BLOB型別。

·         TEXT[(M)]

最大長度為65,535(216–1)字元的TEXT列。

可以給出可選長度M。則MySQL將列建立為最小的但足以容納M字元長的值的TEXT型別。

·         MEDIUMBLOB

最大長度為16,777,215(224–1)位元組的BLOB列。

·         MEDIUMTEXT

最大長度為16,777,215(224–1)字元的TEXT列。

·         LONGBLOB

最大長度為4,294,967,295或4GB(232–1)位元組的BLOB列。LONGBLOB列的最大有效(允許的)長度取決於客戶端/伺服器協議中配置最大包大小和可用的記憶體。

·         LONGTEXT

最大長度為4,294,967,295或4GB(232–1)字元的TEXT列。LONGTEXT列的最大有效(允許的)長度取決於客戶端/伺服器協議中配置最大包大小和可用的記憶體。

·         ENUM(‘value1‘,’value2‘,…)

列舉型別。只能有一個值的字串，從值列’value1‘，’value2‘，…，NULL中或特殊 ”錯誤值中選出。ENUM列最多可以有65,535個截然不同的值。ENUM值在內部用整數表示。

·         SET(‘value1‘,’value2‘,…)

一個設定。字串物件可以有零個或多個值，每個值必須來自列值’value1‘，’value2‘，…SET列最多可以有64個成員。SET值在內部用整數表示。

11.2. 數值型別

MySQL支援所有標準SQL數值資料型別。這些型別包括嚴格數值資料型別(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值資料型別(FLOAT、REAL和DOUBLE PRECISION)。關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞。

BIT資料型別儲存位欄位值，並且支援MyISAM、MEMORY、InnoDB和BDB表。

作為SQL標準的擴充套件，MySQL也支援整數型別TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個整數型別的儲存和範圍。

型別 位元組 最小值 最大值
(帶符號的/無符號的) (帶符號的/無符號的)
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

MySQL還支援選擇在該型別關鍵字後面的括號內指定整數值的顯示寬度(例如，INT(4))。該可選顯示寬度規定用於顯示寬度小於指定的列寬度的值時從左側填滿寬度。

顯示寬度並不限制可以在列內儲存的值的範圍，也不限制超過列的指定寬度的值的顯示。

當結合可選擴充套件屬性ZEROFILL使用時， 預設補充的空格用零代替。例如，對於宣告為INT(5) ZEROFILL的列，值4檢索為00004。請注意如果在整數列儲存超過顯示寬度的一個值，當MySQL為複雜聯接生成臨時表時會遇到問題，因為在這些情況下MySQL相信資料適合原列寬度。

所有整數型別可以有一個可選(非標準)屬性UNSIGNED。當你想要在列內只允許非負數和該列需要較大的上限數值範圍時可以使用無符號值。

浮點和定點型別也可以為UNSIGNED。同數型別，該屬性防止負值儲存到列中。然而，與整數型別不同的是，列值的上範圍保持不變。

如果為一個數值列指定ZEROFILL，MySQL自動為該列新增UNSIGNED屬性。

對於浮點列型別，在MySQL中單精度值使用4個位元組，雙精度值使用8個位元組。

FLOAT型別用於表示近似數值資料型別。SQL標準允許在關鍵字FLOAT後面的括號內選擇用位指定精度(但不能為指數範圍)。MySQL還支援可選的只用於確定儲存大小的精度規定。0到23的精度對應FLOAT列的4位元組單精度。24到53的精度對應DOUBLE列的8位元組雙精度。

MySQL允許使用非標準語法：FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)。這裡，“(M,D)”表示該值一共顯示M位整數，其中D位位於小數點後面。例如，定義為FLOAT(7,4)的一個列可以顯示為-999.9999。MySQL儲存值時進行四捨五入，因此如果在FLOAT(7,4)列內插入999.00009，近似結果是999.0001。

MySQL將DOUBLE視為DOUBLE PRECISION(非標準擴充套件)的同義詞。MySQL還將REAL視為DOUBLE PRECISION(非標準擴充套件)的同義詞，除非SQL伺服器模式包括REAL_AS_FLOAT選項。

為了保證最大可能的可移植性，需要使用近似數值資料值儲存的程式碼應使用FLOAT或DOUBLE PRECISION，不規定精度或位數。

DECIMAL和NUMERIC型別在MySQL中視為相同的型別。它們用於儲存必須為確切精度的值，例如貨幣資料。當宣告該型別的列時，可以(並且通常要)指定精度和標度；例如：

在該例子中，5是精度，2是標度。精度表示儲存值的主要位數，標度表示小數點後面可以儲存的位數。

在MySQL 5.1中以二進位制格式儲存DECIMAL和NUMERIC值。

標準SQL要求salary列能夠用5位整數位和兩位小數儲存任何值。因此，在這種情況下可以儲存在salary列的值的範圍是從-999.99到999.99。

在標準SQL中，語法DECIMAL(M)等價於DECIMAL(M,0)。同樣，語法DECIMAL等價於DECIMAL(M,0)，可以通過計算確定M的值。在MySQL 5.1中支援DECIMAL和NUMERIC資料型別的變數形式。M預設值是10。

DECIMAL或NUMERIC的最大位數是65，但具體的DECIMAL或NUMERIC列的實際範圍受具體列的精度或標度約束。如果此類列分配的值小數點後面的位數超過指定的標度允許的範圍，值被轉換為該標度。(具體操作與作業系統有關，但一般結果均被擷取到允許的位數）。

BIT資料型別可用來儲存位欄位值。BIT(M)型別允許儲存M位值。M範圍為1到64。

要指定位值，可以使用b’value‘符。value是一個用0和1編寫的二進位制值。例如，b’111’和b’100000000’分別表示7和128。參見9.1.5節，“位欄位值”。

如果為BIT(M)列分配的值的長度小於M位，在值的左邊用0填充。例如，為BIT(6)列分配一個值b’101’，其效果與分配b’000101’相同。

當要在一個數值列內儲存一個超出該列允許範圍的值時，MySQL的操作取決於此時有效的SQL模式。如果模式未設定，MySQL將值裁剪到範圍的相應端點，並儲存裁減好的值。但是，如果模式設定為traditional(“嚴格模式”)，超出範圍的值將被拒絕並提示錯誤，並且根據SQL標準插入會失敗。參見5.3.2節，“SQL伺服器模式”。

如果INT列是UNSIGNED，列範圍的大小相同，但其端點會變為到0和4294967295。如果你試圖儲存-9999999999和9999999999，以非嚴格模式儲存到列中的值是0和4294967296。

如果在浮點或定點列中分配的值超過指定(或預設)精度和標度規定的範圍，MySQL以非嚴格模式儲存表示範圍相應端點的值。

當MySQL沒有工作在嚴格模式時，對於ALTER TABLE、LOAD DATA INFILE、UPDATE和多行INSERT語句，由於裁剪髮生的轉換將報告為警告。當MySQL工作在嚴格模式時，這些語句將失敗，並且部分或全部值不會插入或更改，取決於是否表為事務表和其它因素。詳情參見5.3.2節，“SQL伺服器模式”。

11.3. 日期和時間型別

11.3.1. DATETIME、DATE和TIMESTAMP型別
11.3.2. TIME型別
11.3.3. YEAR型別
11.3.4. Y2K事宜和日期型別

表示時間值的DATE和時間型別為DATETIME、DATE、TIMESTAMP、TIME和YEAR。每個時間型別有一個有效值範圍和一個“零”值，當指定不合法的MySQL不能表示的值時使用“零”值。TIMESTAMP型別有專有的自動更新特性，將在後面描述。

如果試圖插入一個不合法的日期，MySQL將給出警告或錯誤。可以使用ALLOW_INVALID_DATES SQL模式讓MySQL接受某些日期，例如’1999-11-31’。當你想要儲存一個“可能錯誤的”使用者已經在資料庫中指定(例如，以web形式)用於將來處理的值時很有用。在這種模式下，MySQL只驗證月範圍為從0到12，日範圍為從0到31。這些範圍可以包括零，因為MySQL允許在DATE或DATETIME列儲存日/月和日是零的日期。這在應用程式需要儲存一個你不知道確切日期的生日時非常有用。在這種情況下，只需要將日期儲存為’1999-00-00’或’1999-01-00’。如果儲存此類日期，DATE_SUB()或DATE_ADD等需要完整日期的函式不會得到正確的結果。(如果你不想在日期中出現零，可以使用NO_ZERO_IN_DATE SQL模式)。

MySQL還允許將’0000-00-00’儲存為“偽日期”(如果不使用NO_ZERO_DATE SQL模式)。這在某些情況下比使用NULL值更方便(並且資料和索引佔用的空間更小)。

將sql_mode系統變數設定為相應模式值，可以更確切你想讓MySQL支援哪種日期。參見5.3.2節，“SQL伺服器模式”。

當使用日期和時間型別時應記住以下幾點：

·         MySQL以標準輸出格式檢索給定日期或時間型別的值，但它盡力解釋你指定的各種輸入值格式(例如，當你指定一個分配給或與日期或時間型別進行比較的值時)。只支援下面章節中描述的格式。期望你能提供有效值。如果你使用其它格式的值會發生意想不到的結果。

·         包含兩位年值的日期會令人模糊，因為世紀不知道。MySQL使用以下規則解釋兩位年值：

o        70-99範圍的年值轉換為1970-1999。

o        00-69範圍的年值轉換為2000-2069。

·         儘管MySQL嘗試解釋幾種格式的值，日期總是以年-月-日順序(例如，’98-09-04′)，而不是其它地方常用的月-日-年或日-月-年順序(例如，’09-04-98’，’04-09-98′)。

·         如果值用於數值上下文中，MySQL自動將日期或時間型別的值轉換為數字，反之亦然。

·         當 MySQL遇到一個日期或時間型別的超出範圍或對於該型別不合法的值時(如本節開始所描述)，它將該值轉換為該類的“零”值。一個例外是超出範圍的TIME值被裁剪到TIME範圍的相應端點。

下面的表顯示了各類“零”值的格式。請注意如果啟用NO_ZERO_DATE SQL模式，使用這些值會產生警告。

列型別 “零”值
DATETIME ‘0000-00-00 00:00:00’
DATE ‘0000-00-00’
TIMESTAMP 00000000000000
TIME ’00:00:00′
YEAR 0000

·         “零”值是特殊值，但你可以使用表內顯示的值顯式儲存或引用它們。你也可以使用值’0’或0來儲存或引用，寫起來更容易。

·         MyODBC中使用的“零”日期或時間值在MyODBC 2.50.12和以上版本中被自動轉換為NULL，因為ODBC不能處理此類值。

11.3.1. DATETIME、DATE和TIMESTAMP型別

11.3.1.1. 自MySQL 4.1以來的TIMESTAMP屬性

DATETIME、DATE和TIMESTAMP型別是相關的。該節描述了它們的特徵，它們的相似點和不同點。

當你需要同時包含日期和時間資訊的值時則使用DATETIME型別。MySQL以’YYYY-MM-DD HH:MM:SS’格式檢索和顯示DATETIME值。支援的範圍為’1000-01-01 00:00:00’到’9999-12-31 23:59:59’。(“支援”表示儘管先前的值可能工作，但沒有保證)。

當你只需要日期值而不需要時間部分時應使用DATE型別。MySQL用’YYYY-MM-DD’格式檢索和顯示DATE值。支援的範圍是’1000-01-01’到 ‘9999-12-31’。

TIMESTAMP列型別的屬性不固定，取決於MySQL版本和伺服器執行的SQL模式。這些屬性將在本節後面描述。

可以使用任何常見格式指定DATETIME、DATE和TIMESTAMP值：

·         ‘YYYY-MM-DD HH:MM:SS’或’YY-MM-DD HH:MM:SS’格式的字串。允許“不嚴格”語法：任何標點符都可以用做日期部分或時間部分之間的間割符。例如，’98-12-31 11:30:45’、’98.12.31 11+30+45’、’98/12/31 11*30*45’和’98@12@31 11^30^45’是等價的。

·         ‘YYYY-MM-DD’或’YY-MM-DD’格式的字串。這裡也允許使用“不嚴格的”語法。例如，’98-12-31’、’98.12.31’、’98/12/31’和’98@12@31’是等價的。

·         ‘YYYYMMDDHHMMSS’或’YYMMDDHHMMSS’格式的沒有間割符的字串，假定字串對於日期型別是有意義的。例如，’19970523091528’和’970523091528’被解釋為’1997-05-23 09:15:28’，但’971122129015’是不合法的(它有一個沒有意義的分鐘部分)，將變為’0000-00-00 00:00:00’。

·         ‘YYYYMMDD’或’YYMMDD’格式的沒有間割符的字串，假定字串對於日期型別是有意義的。例如，’19970523’和’970523’被解釋為 ‘1997-05-23’，但’971332’是不合法的(它有一個沒有意義的月和日部分)，將變為’0000-00-00’。

·         YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的數字，假定數字對於日期型別是有意義的。例如，19830905132800和830905132800被解釋為 ‘1983-09-05 13:28:00’。

·         YYYYMMDD或YYMMDD格式的數字，假定數字對於日期型別是有意義的。例如，19830905和830905被解釋為’1983-09-05’。

·         函式返回的結果，其值適合DATETIME、DATE或者TIMESTAMP上下文，例如NOW()或CURRENT_DATE。

無效DATETIME、DATE或者TIMESTAMP值被轉換為相應型別的“零”值(‘0000-00-00 00:00:00’、’0000-00-00’或者00000000000000)。

對於包括日期部分間割符的字串值，如果日和月的值小於10，不需要指定兩位數。’1979-6-9’與’1979-06-09’是相同的。同樣，對於包括時間部分間割符的字串值，如果時、分和秒的值小於10，不需要指定兩位數。’1979-10-30 1:2:3’與’1979-10-30 01:02:03’相同。

數字值應為6、8、12或者14位長。如果一個數值是8或14位長，則假定為YYYYMMDD或YYYYMMDDHHMMSS格式，前4位數表示年。如果數字 是6或12位長，則假定為YYMMDD或YYMMDDHHMMSS格式，前2位數表示年。其它數字被解釋為彷彿用零填充到了最近的長度。

指定為非限定符字串的值使用給定的長度進行解釋。如果字串為8或14字元長，前4位數表示年。否則，前2位數表示年。從左向右解釋字串內出現的各部分，以發現年、月、日、小時、分和秒值。這說明不應使用少於6字元的字串。例如，如果你指定’9903’，認為它表示1999年3月，MySQL將在你的表內插入一個“零”日期值。這是因為年和月值是99和03，但日部分完全丟失，因此該值不是一個合法的日期。但是，可以明顯指定一個零值來代表缺少的月或日部分。例如，可以使用’990300’來插入值’1999-03-00’。

在一定程度上，可以將一個日期型別的值分配給一個不同的日期型別。但是，值可能會更改或丟失一些資訊：

·         如果你為一個DATETIME或TIMESTAMP物件分配一個DATE值，結果值的時間部分被設定為’00:00:00’，因為DATE值未包含時間資訊。

·         如果你為一個DATE物件分配一個DATETIME或TIMESTAMP值，結果值的時間部分被刪除，因為DATE值未包含時間資訊。

·         記住儘管可以使用相同的格式指定DATETIME、DATE和TIMESTAMP值，不同型別的值的範圍卻不同。例如，TIMESTAMP值不能早於1970或晚於2037。這說明一個日期，例如’1968-01-01’，雖然對於DATETIME或DATE值是有效的，但對於TIMESTAMP值卻無效，如果分配給這樣一個物件將被轉換為0。

當指定日期值時請注意某些缺陷：

·         指定為字串的值允許的非嚴格格式可能會欺騙。例如，值’10:11:12’由於‘:’間割符看上去可能象時間值，但如果用於日期上下文值則被解釋為年’2010-11-12’。值’10:45:15’被轉換為’0000-00-00’因為’45’不是合法月。

·         在非嚴格模式，MySQL伺服器只對日期的合法性進行基本檢查：年、月和日的範圍分別是1000到9999、00到12和00到31。任何包含超出這些範圍的部分的日期被轉換成’0000-00-00’。請注意仍然允許你儲存非法日期，例如’2002-04-31’。要想確保不使用嚴格模式時日期有效，應檢查應用程式。

在嚴格模式，非法日期不被接受，並且不轉換。

詳細資訊參見5.3.2節，“SQL伺服器模式”。

·         包含兩位年值的日期會令人模糊，因為世紀不知道。MySQL使用以下規則解釋兩位年值：

o        00-69範圍的年值轉換為2000-2069。

o        70-99範圍的年值轉換為1970-1999。

11.3.1.1. 自MySQL 4.1以來的TIMESTAMP屬性

註釋：在舊版本的MySQL中(4.1之前)，TIMESTAMP列型別的屬性在許多方面於本節所描述的大大不同。如果你需要對舊的TIMESTAMP資料進行轉化以便在MySQL 5.1中工作，詳情請參見MySQL 4.1 參考手冊。

TIMESTAMP列的顯示格式與DATETIME列相同。換句話說，顯示寬度固定在19字元，並且格式為YYYY-MM-DD HH:MM:SS。

MySQL伺服器也可以以MAXDB模式執行。當伺服器以該模式執行時，TIMESTAMP與DATETIME相等。也就是說，如果建立表時伺服器以MAXDB模式執行，TIMESTAMP列建立為DATETIME列。結果是，該列使用DATETIME顯示格式，有相同的值範圍，並且沒有自動對當前的日期和時間進行初始化或更新。

要想啟用MAXDB模式，在啟動伺服器時使用–sql-mode=MAXDB伺服器選項或在執行時通過設定全域性sql_mode變數將SQL伺服器模式設定為MAXDB：

客戶端可以按照下面方法讓伺服器為它的連線以MAXDB模式執行：

MySQL不接受在日或月列包括一個零或包含非法日期值的時間戳值。該規則的唯一例外是特殊值’0000-00-00 00:00:00’。

你可以非常靈便地確定什麼時候初始化和更新TIMESTAMP和對哪些列進行初始化和更新：

·         你可以將當前的時間戳指定為預設值和自動更新的值。但只能選擇一個，或者兩者都不選。(不可能一個列選擇一個行為而另一個列選擇另一個行為）。

·         你可以指定哪個TIMESTAMP列自動初始化或更新為當前的日期和時間。不再需要為第1個TIMESTAMP列。

請注意下面討論所資訊只適用於建立時未啟用MAXDB模式的表的TIMESTAMP列。(如上所述，MAXDB模式使列建立為DATETIME列）。控制TIMESTAMP列的初始化和更新的規則如下所示：

·         如果一個表內的第1個TIMESTAMP列指定為一個DEFAULT值，則不能忽略。 預設值可以為CURRENT_TIMESTAMP或常量日期和時間值。

·         DEFAULT NULL與第1個TIMESTAMP 列的DEFAULT CURRENT_TIMESTAMP相同。對於其它TIMESTAMP列，DEFAULT NULL被視為DEFAULT 0。

·         表內的任何一個TIMESTAMP列可以設定為自動初始化為當前時間戳和/或更新。

·         在CREATE TABLE語句中，可以用下面的任何一種方式宣告第1個TIMESTAMP列：

o        用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP子句，列為預設值使用當前的時間戳，並且自動更新。

o        不使用DEFAULT或ON UPDATE子句，與DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP相同。

o        用DEFAULT CURRENT_TIMESTAMP子句不用ON UPDATE子句，列為預設值使用當前的時間戳但是不自動更新。

o        不用DEFAULT子句但用ON UPDATE CURRENT_TIMESTAMP子句，列有預設值0並自動更新。

o        用常量DEFAULT值，列有給出的 預設值。如果列有一個ON UPDATE CURRENT_TIMESTAMP子句，它自動更新，否則不。

換句話說，你可以為初始值和自動更新的值使用當前的時間戳，或者其中一個使用，或者兩個皆不使用。(例如，你可以指定ON UPDATE來啟用自動更新而不讓列自動初始化）。

·         在DEFAULT和ON UPDATE子句中可以使用CURRENT_TIMESTAMP、CURRENT_TIMESTAMP()或者NOW()。它們均具有相同的效果。

兩個屬性的順序並不重要。如果一個TIMESTAMP列同時指定了DEFAULT和ON UPDATE，任何一個可以在另一個的前面。

例子，下面這些語句是等效的：

·         要為TIMESTAMP列而不是第1列指定自動預設或更新，必須通過將第1個TIMESTAMP列顯式分配一個常量DEFAULT值來禁用自動初始化和更新。(例如，DEFAULT 0或DEFAULT’2003-01-01 00:00:00′)。然後，對於其它TIMESTAMP列，規則與第1個TIMESTAMP列相同，例外情況是不能忽略DEFAULT和ON UPDATE子句。如果這樣做，則不會自動進行初始化或更新。

例如：下面這些語句是等效的：

可以對每個連線設定當前的時區，相關描述參見5.10.8節，“MySQL伺服器時區支援”。TIMESTAMP值以UTC格式儲存，儲存時對當前的時區進行轉換，檢索時再轉換回當前的時區。只要時區設定值為常量，便可以得到儲存時的值。如果儲存一個TIMESTAMP值，應更改時區然後檢索該值，它與你儲存的值不同。這是因為在兩個方向的轉換中沒有使用相同的時區。當前的時區可以用作time_zone系統變數的值。

可以在TIMESTAMP列的定義中包括NULL屬性以允許列包含NULL值。例如：

如果未指定NULL屬性，將列設定為NULL設定則會將它設定為當前的時間戳。請注意允許NULL值的TIMESTAMP列不會採用當前的時間戳，除非要麼其 預設值定義為CURRENT_TIMESTAMP，或者NOW()或CURRENT_TIMESTAMP被插入到該列內。換句話說，只有使用如下定義建立，定義為 NULL的TIMESTAMP列才會自動更新：

否則-也就是說，如果使用NULL而不是DEFAULT TIMESTAMP來定義TIMESTAMP列，如下所示…

…則必須顯式插入一個對應當前日期和時間的值。例如：

11.3.2. TIME型別

MySQL以’HH:MM:SS’格式檢索和顯示TIME值(或對於大的小時值採用’HHH:MM:SS’格式)。TIME值的範圍可以從’-838:59:59’到’838:59:59’。小時部分會因此大的原因是TIME型別不僅可以用於表示一天的時間(必須小於24小時)，還可能為某個事件過去的時間或兩個事件之間的時間間隔(可以大於24小時，或者甚至為負)。

你可以用各種格式指定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還不儲存分數。

·         函式返回的結果，其值適合TIME上下文，例如CURRENT_TIME。

對於指定為包括時間部分間割符的字串的TIME值，如果時、分或者秒值小於10，則不需要指定兩位數。’8:3:2’與’08:03:02’相同。

為TIME列分配簡寫值時應注意。沒有冒號，MySQL解釋值時假定最右邊的兩位表示秒。(MySQL解釋TIME值為過去的時間而不是當天的時間）。例如，你可能認為’1112’和1112表示’11:12:00′(11點過12分)，但MySQL將它們解釋為’00:11:12′(11分，12 秒)。同樣，’12’和12 被解釋為 ’00:00:12’。相反，TIME值中使用冒號則肯定被看作當天的時間。也就是說，’11:12’表示’11:12:00’，而不是’00:11:12’。

超出TIME範圍但合法的值被裁為範圍最接近的端點。例如，’-850:00:00’和’850:00:00’被轉換為’-838:59:59’和’838:59:59’。

無效TIME值被轉換為’00:00:00’。請注意由於’00:00:00’本身是一個合法TIME值，只從表內儲存的一個’00:00:00’值還不能說出原來的值是 ’00:00:00’還是不合法的值。

11.3.3. 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。

·         函式返回的結果，其值適合YEAR上下文，例如NOW()。

非法YEAR值被轉換為0000。

11.3.4. Y2K事宜和日期型別

MySQL本身對於2000年(Y2K)是安全的(參見1.4.5節，“2000年相容性”)，但輸入給MySQL的值可能不安全。任何包含兩位年值的輸入都會令人模糊，因為世紀不知道。這些值必須解釋為四位形式，因為MySQL內部使用四位來儲存年。

對於DATETIME、DATE、TIMESTAMP和YEAR型別，MySQL使用以下規則解釋含模糊年值的日期：

·         00-69範圍的年值轉換為2000-2069。

·         70-99範圍的年值轉換為1970-1999。

請記住這些規則只是合理猜測資料值表示什麼。如果MySQL使用的啟發不能產生正確的值，你應提供包含四位年值的確切輸入。

ORDER BY可以正確排序有兩位年的TIMESTAMP或YEAR值。

部分函式如MIN()和MAX()將TIMESTAMP或YEAR轉換為一個數字。這說明使用有兩位年值的值，這些函式不能工作正確。在這種情況下的修復方法是將TIMESTAMP或YEAR轉換為四位年格式或使用MIN(DATE_ADD(TIMESTAMP,INTERVAL 0 DAYS))。

11.4. String型別

11.4.1. CHAR和VARCHAR型別
11.4.2. BINARY和VARBINARY型別
11.4.3. BLOB和TEXT型別
11.4.4. ENUM型別
11.4.5. SET型別

字串型別指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節描述了這些型別如何工作以及如何在查詢中使用這些型別。

11.4.1. CHAR和VARCHAR型別

CHAR和VARCHAR型別類似，但它們儲存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在儲存或檢索過程中不進行大小寫轉換。

CHAR和VARCHAR型別宣告的長度表示你想要儲存的最大字元數。例如，CHAR(30)可以佔用30個字元。

CHAR列的長度固定為建立表時宣告的長度。長度可以為從0到255的任何值。當儲存CHAR值時，在它們的右邊填充空格以達到指定的長度。當檢索到CHAR值時，尾部的空格被刪除掉。在儲存或檢索過程中不進行大小寫轉換。

VARCHAR列中的值為可變長字串。長度可以指定為0到65,535之間的值。(VARCHAR的最大有效長度由最大行大小和使用的字符集確定。整體最大長度是65,532位元組）。

同CHAR對比，VARCHAR值儲存時只儲存需要的字元數，另加一個位元組來記錄長度(如果列宣告的長度超過255，則使用兩個位元組)。

VARCHAR值儲存時不進行填充。當值儲存和檢索時尾部的空格仍保留，符合標準SQL。

如果分配給CHAR或VARCHAR列的值超過列的最大長度，則對值進行裁剪以使其適合。如果被裁掉的字元不是空格，則會產生一條警告。如果裁剪非空格字元，則會造成錯誤(而不是警告)並通過使用嚴格SQL模式禁用值的插入。參見5.3.2節，“SQL伺服器模式”。

下面的表顯示了將各種字串值儲存到CHAR(4)和VARCHAR(4)列後的結果，說明了CHAR和VARCHAR之間的差別：

值 CHAR(4) 儲存需求 VARCHAR(4) 儲存需求
” ‘    ‘ 4個位元組 ” 1個位元組
‘ab’ ‘ab  ‘ 4個位元組 ‘ab ‘ 3個位元組
‘abcd’ ‘abcd’ 4個位元組 ‘abcd’ 5個位元組
‘abcdefgh’ ‘abcd’ 4個位元組 ‘abcd’ 5個位元組

請注意上表中最後一行的值只適用不使用嚴格模式時；如果MySQL執行在嚴格模式，超過列長度不的值不儲存，並且會出現錯誤。

從CHAR(4)和VARCHAR(4)列檢索的值並不總是相同，因為檢索時從CHAR列刪除了尾部的空格。通過下面的例子說明該差別：

根據分配給列的字符集校對規則對CHAR和VARCHAR列中的值進行排序和比較。

請注意所有MySQL校對規則屬於PADSPACE類。這說明在MySQL中的所有CHAR和VARCHAR值比較時不需要考慮任何尾部空格。例如：

請注意所有MySQL版本均如此，並且它不受SQL伺服器模式的影響。

對於尾部填充字元被裁剪掉或比較時將它們忽視掉的情形，如果列的索引需要唯一的值，在列內插入一個只是填充字元數不同的值將會造成複製鍵值錯誤。

CHAR BYTE是CHAR BINARY的別名。這是為了保證相容性。

ASCII屬性為CHAR列分配latin1字符集。UNICODE屬性分配ucs2字符集。

11.4.2. BINARY和VARBINARY型別

BINARY和VARBINARY類類似於CHAR和VARCHAR，不同的是它們包含二進位制字串而不要非二進位制字串。也就是說，它們包含位元組字串而不是字元字串。這說明它們沒有字符集，並且排序和比較基於列值位元組的數值值。

BINARY和VARBINARY允許的最大長度一樣，如同CHAR和VARCHAR，不同的是BINARY和VARBINARY的長度是位元組長度而不是字元長度。

BINARY和VARBINARY資料型別不同於CHAR BINARY和VARCHAR BINARY資料型別。對於後一種型別，BINARY屬性不會將列視為二進位制字串列。相反，它致使使用列字符集的二元 校對規則，並且列自身包含非二進位制字元字串而不是二進位制位元組字串。例如CHAR(5) BINARY被視為CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin，假定預設字符集是latin1。這不同於BINARY(5)，它儲存5位元組二進位制字串，沒有字符集或 校對規則。

當儲存BINARY值時，在它們右邊填充值以達到指定長度。填充值是0x00(零位元組)。插入值時在右側新增0x00 on，並且選擇時不刪除尾部的位元組。比較時所有位元組很重要，包括ORDER BY和DISTINCT操作。比較時0x00位元組和空格是不同的，0x00<空格。

例如：對於一個BINARY(3)列，當插入時 ‘a’ 變為 ‘a ‘。’a’插入時變為’a’。當選擇時兩個插入的值均不更改。

對於VARBINARY，插入時不填充字元，選擇時不裁剪位元組。比較時所有位元組很重要，包括ORDER BY和DISTINCT操作。比較時0x00位元組和空格是不同的，0x00<空格。

對於尾部填充字元被裁剪掉或比較時將它們忽視掉的情形，如果列的索引需要唯一的值，在列內插入一個只是填充字元數不同的值將會造成複製鍵值錯誤。

如果你計劃使用這些資料型別來儲存二進位制資料並且需要檢索的值與儲存的值完全相同，應考慮前面所述的填充和裁剪特徵。下面的例子說明了用0x00填充的BINARY值如何影響列值比較：

如果檢索的值必須與指定進行儲存而沒有填充的值相同，最好使用BLOB資料型別。

建立表時，MySQL可以默默更改BINARY或VARBINARY列的型別。參見13.1.5.1節，“沉寂的列規格變更”。

11.4.3. BLOB和TEXT型別

BLOB是一個二進位制大物件，可以容納可變數量的資料。有4種BLOB型別：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們只是可容納值的最大長度不同。

有4種TEXT型別：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應4種BLOB型別，有相同的最大長度和儲存需求。

參見11.5節，“列型別儲存需求”。

BLOB 列被視為二進位制字串(位元組字串)。TEXT列被視為非二進位制字串(字元字串)。BLOB列沒有字符集，並且排序和比較基於列值位元組的數值值。TEXT列有一個字符集，並且根據字符集的 校對規則對值進行排序和比較。

在TEXT或BLOB列的儲存或檢索過程中，不存在大小寫轉換。

當未執行在嚴格模式時，如果你為BLOB或TEXT列分配一個超過該列型別的最大長度的值值，值被擷取以保證適合。如果截掉的字元不是空格，將會產生一條警告。使用嚴格SQL模式，會產生錯誤，並且值將被拒絕而不是擷取並給出警告。參見5.3.2節，“SQL伺服器模式”。

在大多數方面，可以將BLOB列視為能夠足夠大的VARBINARY列。同樣，可以將TEXT列視為VARCHAR列。BLOB和TEXT在以下幾個方面不同於VARBINARY和VARCHAR：

·         當儲存或檢索BLOB和TEXT列的值時不刪除尾部空格。(這與VARBINARY和VARCHAR列相同）。

請注意比較時將用空格對TEXT進行擴充以適合比較的物件，正如CHAR和VARCHAR。

·         對於BLOB和TEXT列的索引，必須指定索引字首的長度。對於CHAR和VARCHAR，字首長度是可選的。參見7.4.3節，“列索引”。

·         BLOB和TEXT列不能有 預設值。

LONG和LONG VARCHAR對應MEDIUMTEXT資料型別。這是為了保證相容性。如果TEXT列型別使用BINARY屬性，將為列分配列字符集的二元 校對規則。

MySQL連線程式/ODBC將BLOB值定義為LONGVARBINARY，將TEXT值定義為LONGVARCHAR。

由於BLOB和TEXT值可能會非常長，使用它們時可能遇到一些約束：

·         當排序時只使用該列的前max_sort_length個位元組。max_sort_length的 預設值是1024；該值可以在啟動mysqld伺服器時使用–max_sort_length選項進行更改。參見5.3.3節，“伺服器系統變數”。

執行時增加max_sort_length的值可以在排序或組合時使更多的位元組有意義。任何客戶端可以更改其會話max_sort_length變數的值：

當你想要使超過max_sort_length的位元組有意義，對含長值的BLOB或TEXT列使用GROUP BY或ORDER BY的另一種方式是將列值轉換為固定長度的物件。標準方法是使用SUBSTRING函式。例如，下面的語句對comment列的2000個位元組進行排序：

·         BLOB或TEXT物件的最大大小由其型別確定，但在客戶端和伺服器之間實際可以傳遞的最大值由可用記憶體數量和通訊快取區大小確定。你可以通過更改max_allowed_packet變數的值更改訊息快取區的大小，但必須同時修改伺服器和客戶端程式。例如，可以使用 mysql和mysqldump來更改客戶端的max_allowed_packet值。參見7.5.2節，“調節伺服器引數”、8.3節，“mysql：MySQL命令列工具”和8.8節，“mysqldump：資料庫備份程式”。

每個BLOB或TEXT值分別由內部分配的物件表示。這與其它列型別形成對比，後者是當開啟表時為每1列分配儲存引擎。

11.4.4. ENUM型別

ENUM是一個字串物件，其值來自表建立時在列規定中顯式列舉的一列值。

在某些情況下，ENUM值也可以為空字串(”)或NULL：

·         如果你將一個非法值插入ENUM(也就是說，允許的值列之外的字串)，將插入空字串以作為特殊錯誤值。該字串與“普通”空字串不同，該字串有數值值0。後面有詳細討論。

·         如果將ENUM列宣告為允許NULL，NULL值則為該列的一個有效值，並且 預設值為NULL。如果ENUM列被宣告為NOT NULL，其預設值為允許的值列的第1個元素。

每個列舉值有一個索引：

·         來自列規定的允許的值列中的值從1開始編號。

·         空字串錯誤值的索引值是0。這說明你可以使用下面的SELECT語句來找出分配了非法ENUM值的行：

·         NULL值的索引是NULL。

例如，定義為ENUM的列(‘one’，’two’，’three’)可以有下面所示任何值。還顯示了每個值的索引：

值 索引
NULL NULL
” 0
‘one’ 1
‘two’ 2
‘three’ 3

列舉最多可以有65,535個元素。

當建立表時，ENUM成員值的尾部空格將自動被刪除。

當檢索時，儲存在ENUM列的值使用列定義中所使用的大小寫來顯示。請注意可以為ENUM列分配字符集和 校對規則。對於二進位制或大小寫敏感的校對規則，當為列分配值時應考慮大小寫。

如果在數值上下文中檢索一個ENUM值，將返回列值的索引。例如，你可以這樣從ENUM列搜尋數值值：

如果將一個數字儲存到ENUM列，數字被視為索引，並且儲存的值是該索引對應的列舉成員。(但是，這不適合LOAD DATA，它將所有輸入視為字串）。不建議使用類似數字的列舉值來定義一個ENUM列，因為這很容易引起混淆。例如，下面的列含有字串值’0’、’1’和’2’的列舉成員，但數值索引值為1、2和3：

根據列舉成員在列定義中列出的順序對ENUM值進行排序。(換句話說，ENUM值根據索引編號進行排序）。例如，對於ENUM(‘a’，’b’)，’a’排在’b’前面，但對於ENUM(‘b’，’a’)，’b’排在’a’前面。空字串排在非空字串前面，並且NULL值排在所有其它列舉值前面。要想防止意想不到的結果，按字母順序規定ENUM列。還可以使用GROUP BY  CAST(col AS CHAR)或GROUP BY  CONCAT(col)來確保按照詞彙對列進行排序而不是用索引數字。

如果你想要確定一個ENUM列的所有可能的值，使用SHOW COLUMNS FROM tbl_name LIKE enum_col，並解析輸出中第2列的ENUM定義。

11.4.5. SET型別

SET是一個字串物件，可以有零或多個值，其值來自表建立時規定的允許的一列值。指定包括多個SET成員的SET列值時各成員之間用逗號(‘,’)間隔開。這樣SET成員值本身不能包含逗號。

例如，指定為SET(‘one’, ‘two’) NOT NULL的列可以有下面的任何值：

SET最多可以有64個不同的成員。

當建立表時，SET成員值的尾部空格將自動被刪除。

當檢索時，儲存在SET列的值使用列定義中所使用的大小寫來顯示。請注意可以為SET列分配字符集和 校對規則。對於二進位制或大小寫敏感的校對規則，當為列分配值時應考慮大小寫。

MySQL用數字儲存SET值，所儲存值的低階位對應第1個SET成員。如果在數值上下文中檢索一個SET值，檢索的值的位設定對應組成列值的SET成員。例如，你可以這樣從一個SET列檢索數值值：

如果將一個數字儲存到SET列中，數字中二進位制表示中的位確定了列值中的SET成員。對於指定為SET(‘a’,’b’,’c’,’d’)的列，成員有下面的十進位制和二進位制值：

SET成員 十進位制值 二進位制值
‘a’ 1 0001
‘b’ 2 0010
‘c’ 4 0100
‘d’ 8 1000

如果你為該列分配一個值9，其二進位制形式為1001，因此第1個和第4個SET值成員’a’和’d’被選擇，結果值為 ‘a,d’。

對於包含多個SET元素的值，當插入值時元素所列的順序並不重要。在值中一個給定的元素列了多少次也不重要。當以後檢索該值時，值中的每個元素出現一次，根據表建立時指定的順序列出元素。例如，假定某個列指定為SET(‘a’,’b’,’c’,’d’)：

插入值’a,d’、’d,a’、’a,d,d’、’a,d,a’和’d,a,d’:

當檢索時所有這些值顯示為 ‘a,d’：

如果將SET列設定為一個不支援的值，則該值被忽略併發出警告：

SET值按數字順序排序。NULL值排在非NULL SET值的前面。

通常情況，可以使用FIND_IN_SET()函式或LIKE操作符搜尋SET值：

第1個語句找出SET_col包含value set成員的行。第2個類似，但有所不同：它在其它地方找出set_col包含value的行，甚至是在另一個SET成員的子字串中。

下面的語句也是合法的：

第1個語句尋找包含第1個set成員的值。第2個語句尋找一個確切匹配的值。應注意第2類的比較。將set值與’val1,val2‘比較返回的結果與同’val2,val1‘比較返回的結果不同。指定值時的順序應與在列定義中所列的順序相同。

如果想要為SET列確定所有可能的值，使用SHOW COLUMNS FROM tbl_name LIKE set_col並解析輸出中第2列的SET定義。

11.5. 列型別儲存需求

根據類別列出了MySQL支援的每個列型別的儲存需求。

MyISAM表中行的最大大小為65,534位元組。每個BLOB和TEXT列 賬戶只佔其中的5至9個位元組。

如果MyISAM表包括變長列型別，記錄格式也是可變長度。當建立表時，在某些條件下，MySQL可以將一個列從變長型別改為固定長度的型別或反之亦然。詳細資訊參見13.1.5.1節，“沉寂的列規格變更”。

數值型別儲存需求

列型別 儲存需求
TINYINT 1個位元組
SMALLINT 2個位元組
MEDIUMINT 3個位元組
INT, INTEGER 4個位元組
BIGINT 8個位元組
FLOAT(p) 如果0 <= p <= 24為4個位元組, 如果25 <= p <= 53為8個位元組
FLOAT 4個位元組
DOUBLE [PRECISION], item REAL 8個位元組
DECIMAL(M,D), NUMERIC(M,D) 變長；參見下面的討論
BIT(M) 大約(M+7)/8個位元組

DECIMAL(和NUMERIC)的儲存需求與具體版本有關：

使用二進位制格式將9個十進位制(基於10)數壓縮為4個位元組來表示DECIMAL列值。每個值的整數和分數部分的儲存分別確定。每個9位數的倍數需要4個位元組，並且“剩餘的”位需要4個位元組的一部分。下表給出了超出位數的儲存需求：

剩餘的 位元組
位數 數目
0 0
1 1
2 1
3 2
4 2
5 3
6 3
7 4
8 4
9 4

日期和時間型別的儲存需求

列型別 儲存需求
DATE 3個位元組
DATETIME 8個位元組
TIMESTAMP 4個位元組
TIME 3個位元組
YEAR 1個位元組

字串型別的儲存需求

列型別 儲存需求
CHAR(M) M個位元組，0 <= M <= 255
VARCHAR(M) L+1個位元組，其中L <= M 且0 <= M <= 65535(參見下面的註釋)
BINARY(M) M個位元組，0 <= M <= 255
VARBINARY(M) L+1個位元組，其中L <= M 且0 <= M <= 255
TINYBLOB, TINYTEXT L+1個位元組，其中L < 28
BLOB, TEXT L+2個位元組，其中L < 216
MEDIUMBLOB, MEDIUMTEXT L+3個位元組，其中L < 224
LONGBLOB, LONGTEXT L+4個位元組，其中L < 232
ENUM(‘value1‘,’value2‘,…) 1或2個位元組，取決於列舉值的個數(最多65,535個值)
SET(‘value1‘,’value2‘,…) 1、2、3、4或者8個位元組，取決於set成員的數目(最多64個成員)

VARCHAR、BLOB和TEXT類是變長型別。每個型別的儲存需求取決於列值的實際長度(用前面的表中的L表示)，而不是該型別的最大可能的大小。例如，VARCHAR(10)列可以容納最大長度為10的字串。實際儲存需求是字串(L)的長度，加上一個記錄字串長度的位元組。對於字串’abcd’，L是4，儲存需要5個位元組。

對於CHAR、VARCHAR和TEXT型別，前面的表中的值L和M應解釋為字元數目，並且列定義中的這些型別的長度表示字元數目。例如，要想儲存一個TINYTEXT值需要L字元+ 1個位元組。

要想計算用於儲存具體CHAR、VARCHAR或者TEXT列值的位元組數，需要考慮該列使用的字符集。在具體情況中，當使用Unicode時，必須記住所有Unicode字元使用相同的位元組數。為了細分用於不同類Unicode字元使用的儲存，參見10.5節，“Unicode支援”。

註釋：VARCHAR列的有效最大長度為65,532字元。

NDBCLUSTER引擎只支援固定寬度的列。這說明MySQL簇中的表中的VARCHAR列的行為如同型別CHAR(不同的是每個記錄仍然有一個額外位元組空間)。例如，在Cluster表中，宣告為VARCHAR(100)的列中的每個記錄儲存時將佔用101個位元組，無論實際儲存的記錄中的字串的長度為多少。

BLOB和TEXT類需要 1、2、3或者4個位元組來記錄列值的長度，取決於該類的最大可能的長度。參見11.4.3節，“BLOB和TEXT型別”。

在NDB Cluster儲存引擎中，TEXT和BLOB列的實施是不同的，其中TEXT列中的每個記錄由兩個單獨部分組成。一個是固定大小(256位元組)，並且實際上儲存在原表中。另一個包括超出256位元組的任何資料，儲存在隱含的表中。第2個表中的記錄總是2,000位元組長。這說明如果size<= 256，TEXT列的大小為256(其中size表示記錄的大小)；否則，大小是256 +size+(2000–(size–256)%2000)。

ENUM物件的大小由不同的列舉值的數目確定。列舉用一個位元組，可以有255個可能的值。當列舉的值位於256和65,535之間時，用兩個位元組。參見11.4.4節，“ENUM型別”。

SET物件的大小由不同的set成員的數量確定。如果set大小是N，物件佔(N+7)/8個位元組，四捨五入到1、2、3、4或者8個位元組。SET最多可以有64個成員。參見11.4.5節，“SET型別”。

11.6. 選擇正確的列型別

為了優化儲存，在任何情況下均應使用最精確的型別。例如，如果列的值的範圍為從1到99999，若使用整數，則MEDIUMINT UNSIGNED是好的型別。在所有可以表示該列值的型別中，該型別使用的儲存最少。

用精度為65位十進位制數(基於10)對DECIMAL 列進行所有基本計算(+、-、*、/)。參見11.1.1節，“數值型別概述”。

使用雙精度操作對DECIMAL值進行計算。如果準確度不是太重要或如果速度為最高優先順序，DOUBLE型別即足夠了。為了達到高精度，可以轉換到儲存在BIGINT中的定點型別。這樣可以用64位整數進行所有計算，根據需要將結果轉換回浮點值。

11.7. 使用來自其他資料庫引擎的列型別

為了使用由其它賣方編寫的SQL執行程式碼，MySQL按照下表所示對列型別進行對映。通過這些對映，可以很容易地從其它資料庫引擎將表定義匯入到MySQL中：

其它賣方型別 MySQL型別
BOOL, TINYINT
BOOLEAN TINYINT
CHAR VARYING(M) VARCHAR(M)
DEC DECIMAL
FIXED DECIMAL
FLOAT4 FLOAT
FLOAT8 DOUBLE
INT1 TINYINT
INT2 SMALLINT
INT3 MEDIUMINT
INT4 INT
INT8 BIGINT
LONG VARBINARY MEDIUMBLOB
LONG VARCHAR MEDIUMTEXT
LONG MEDIUMTEXT
MIDDLEINT MEDIUMINT
NUMERIC DECIMAL

在建立表時對列型別進行對映，然後原來的型別定義被丟棄。如果你使用其它賣方的型別建立一個表，然後執行DESCRIBE tbl_name語句，MySQL使用等效的MySQL型別來報告表的結構。例如：

相關文章