11. 列類型 · mysql5.1參考手冊

### 第11章：列類型 MySQL支持多種列類型：數值類型、日期/時間類型和字符串(字符)類型。本章首先對這些列類型進行了概述，然后更加詳細地描述了各種列的類型，以及列類型存儲需求的總結。概述很簡單。關于具體列類型的詳細信息應查閱詳細的描述，例如指定值時允許使用的格式。 MySQL支持處理空間數據的擴展名。關于空間類型的信息參見[第19章：](#)[_MySQL中的空間擴展_](# "Chapter?19.?Spatial Extensions in MySQL")。幾種列類型描述使用了下述慣例： ·???????? _M_ 表示最大顯示寬度。最大有效顯示寬度是255。 ·???????? _D_ 適用于浮點和定點類型，并表示小數點后面的位數。最大可能的值是30，但不應大于_M_-2。 ·???????? 方括號(‘[’和‘]’)表示可選部分。 ### 11.1.?列類型概述 [ 11.1.1. 數值類型概述](#)[ 11.1.2. 日期和時間類型概述](#)[ 11.1.3. 字符串類型概述](#) ### 11.1.1.?數值類型概述下面為數值列類型的概述。詳細信息參見[11.2節，“數值類型”](# "11.2.?Numeric Types")。列存儲需求參見[11.5節，“列類型存儲需求”](# "11.5.?Column Type Storage Requirements")。 _M_指示最大顯示寬度。最大有效顯示寬度是255。顯示寬度與存儲大小或類型包含的值的范圍無關，相關描述見[11.2節，“數值類型”](# "11.2.?Numeric Types")。如果為一個數值列指定ZEROFILL，MySQL自動為該列添加UNSIGNED屬性。 SERIAL是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的一個別名。在整數列定義中，SERIAL DEFAULT VALUE是NOT NULL AUTO_INCREMENT UNIQUE的一個別名。 **警告：**應當清楚，當使用在整數值（其中一個是UNSIGNED類型）之間使用減號時，結果是無符號。參見[12.8節，“Cast函數和操作符”](# "12.8.?Cast Functions and Operators")。 ·???????? 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節，“解決與不匹配行有關的問題”](# "A.5.7.?Solving Problems with No Matching Rows")。 ·???????? 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.3.?Date and Time Types")。列存儲需求參見[11.5節，“列類型存儲需求”](# "11.5.?Column Type Storage Requirements")。 ·???????? 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.4.?String Types")。列存儲需求參見[11.5節，“列類型存儲需求”](# "11.5.?Column Type Storage Requirements")。在某些情況中，MySQL可以將一個字符串列更改為不同于CREATE TABLE或ALTER TABLE語句中所給出的類型。參見[13.1.5.1節，“沉寂的列規格變更”](# "13.1.5.1.?Silent Column Specification Changes")。 MySQL 5.1字符串數據類型包括部分在MySQL 4.1之前的版本中沒有的特性： ·???????? 許多字符串數據類型的列定義可以包括指定字符集的CHARACTER SET屬性，也可能包括校對規則。(CHARSET是CHARACTER SET的一個同義詞）。這些屬性適用于CHAR、VARCHAR、TEXT類型、ENUM和SET。例如： ·??????????????? CREATE TABLE t ·??????????????? ( ·??????????????? ????c1 CHAR(20) CHARACTER SET utf8, ·??????????????? ????c2 CHAR(20) CHARACTER SET latin1 COLLATE latin1_bin ·??????????????? ); 該表定義創建了一個名為c1的列，具有一個utf8字符集和該字符集的默認校對規則，和一個名為c2的列以及latin1字符集和該字符集的二元校對規則。二元校對規則對大小寫不敏感。 ·???????? MySQL 5.1用字符單位解釋在字符列定義中的長度規范。(以前的一些MySQL版本以字節解釋長度）。 ·???????? 對于CHAR、VARCHAR和TEXT類型，BINARY屬性可以為列分配該列字符集的校對規則。 ·???????? 字符列的排序和比較基于分配給列的字符集。在以前的版本中，排序和比較基于服務器字符集的校對規則。對于CHAR和VARCHAR列，你可以用BINARY屬性聲明列讓排序和校對規則使用當前的字符代碼值而不是詞匯順序。關于MySQL 5.1中字符集的支持，參見[第10章：](#)[_字符集支持_](# "Chapter?10.?Character Set Support")。 ·???????? [NATIONAL] CHAR(_M_) [BINARY| ASCII | UNICODE] 固定長度字符串，當保存時在右側填充空格以達到指定的長度。_M_表示列長度。_M_的范圍是0到255個字符。 **注釋：**當檢索CHAR值時尾部空格被刪除。如果想要將某個CHAR的長度設為大于255，執行的CREATE TABLE或ALTER TABLE語句將失敗并提示錯誤： mysql> CREATE TABLE c1 (col1 INT, col2 CHAR(500)); ERROR 1074 (42000): Column length too big for column 'col' (max = 255); use BLOB or TEXT instead mysql> SHOW CREATE TABLE c1; ERROR 1146 (42S02): Table 'test.c1' doesn't exist 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。下面的表顯示了需要的每個整數類型的存儲和范圍。 <table border="1" cellpadding="0" id="table1"><tr><td> 類型</td> <td> 字節</td> <td> 最小值</td> <td> 最大值</td> </tr><tr><td> ?</td> <td> ?</td> <td> (帶符號的/無符號的)</td> <td> (帶符號的/無符號的)</td> </tr><tr><td> TINYINT</td> <td> 1</td> <td> -128</td> <td> 127</td> </tr><tr><td> ?</td> <td> ?</td> <td> 0</td> <td> 255</td> </tr><tr><td> SMALLINT</td> <td> 2</td> <td> -32768</td> <td> 32767</td> </tr><tr><td> ?</td> <td> ?</td> <td> 0</td> <td> 65535</td> </tr><tr><td> MEDIUMINT</td> <td> 3</td> <td> -8388608</td> <td> 8388607</td> </tr><tr><td> ?</td> <td> ?</td> <td> 0</td> <td> 16777215</td> </tr><tr><td> INT</td> <td> 4</td> <td> -2147483648</td> <td> 2147483647</td> </tr><tr><td> ?</td> <td> ?</td> <td> 0</td> <td> 4294967295</td> </tr><tr><td> BIGINT</td> <td> 8</td> <td> -9223372036854775808</td> <td> 9223372036854775807</td> </tr><tr><td> ?</td> <td> ?</td> <td> 0</td> <td> 18446744073709551615</td> </tr></table> 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中視為相同的類型。它們用于保存必須為確切精度的值，例如貨幣數據。當聲明該類型的列時，可以(并且通常要)指定精度和標度；例如： salary DECIMAL(5,2) 在該例子中，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節，“位字段值”](# "9.1.5.?Bit-Field Values")。如果為BIT(M)列分配的值的長度小于_M_位，在值的左邊用0填充。例如，為BIT(6)列分配一個值b'101'，其效果與分配b'000101'相同。當要在一個數值列內保存一個超出該列允許范圍的值時，MySQL的操作取決于此時有效的SQL模式。如果模式未設置，MySQL將值裁剪到范圍的相應端點，并保存裁減好的值。但是，如果模式設置為traditional(“嚴格模式”)，超出范圍的值將被拒絕并提示錯誤，并且根據SQL標準插入會失敗。參見[5.3.2節，“SQL服務器模式”](# "5.3.2.?The Server SQL Mode")。如果INT列是UNSIGNED，列范圍的大小相同，但其端點會變為到0和4294967295。如果你試圖保存-9999999999和9999999999，以非嚴格模式保存到列中的值是0和4294967296。如果在浮點或定點列中分配的值超過指定(或默認)精度和標度規定的范圍，MySQL以非嚴格模式保存表示范圍相應端點的值。當MySQL沒有工作在嚴格模式時，對于ALTER TABLE、LOAD DATA INFILE、UPDATE和多行INSERT語句，由于裁剪發生的轉換將報告為警告。當MySQL工作在嚴格模式時，這些語句將失敗，并且部分或全部值不會插入或更改，取決于是否表為事務表和其它因素。詳情參見[5.3.2節，“SQL服務器模式”](# "5.3.2.?The Server SQL Mode")。 ### 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服務器模式”](# "5.3.2.?The Server SQL Mode")。當使用日期和時間類型時應記住以下幾點： ·???????? 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模式，使用這些值會產生警告。 <table border="1" cellpadding="0" id="table2"><tr><td> 列類型</td> <td> “零”值</td> </tr><tr><td> DATETIME</td> <td> '0000-00-00 00:00:00'</td> </tr><tr><td> DATE</td> <td> '0000-00-00'</td> </tr><tr><td> TIMESTAMP</td> <td> 00000000000000</td> </tr><tr><td> TIME</td> <td> '00:00:00'</td> </tr><tr><td> YEAR</td> <td> 0000</td> </tr></table> ·???????? “零”值是特殊值，但你可以使用表內顯示的值顯式保存或引用它們。你也可以使用值'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服務器模式”](# "5.3.2.?The Server SQL Mode")。 ·???????? 包含兩位年值的日期會令人模糊，因為世紀不知道。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： mysql> SET GLOBAL sql_mode=MAXDB；客戶端可以按照下面方法讓服務器為它的連接以MAXDB模式運行： mysql> SET SESSION sql_mode=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，任何一個可以在另一個的前面。例子，下面這些語句是等效的： CREATE TABLE t (ts TIMESTAMP); CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ???????????????????????????? ON UPDATE CURRENT_TIMESTAMP); CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ???????????????????????????? DEFAULT CURRENT_TIMESTAMP); ·???????? 要為TIMESTAMP列而不是第1列指定自動默認或更新，必須通過將第1個TIMESTAMP列顯式分配一個常量DEFAULT值來禁用自動初始化和更新。(例如，DEFAULT 0或DEFAULT'2003-01-01 00:00:00')。然后，對于其它TIMESTAMP列，規則與第1個TIMESTAMP列相同，例外情況是不能忽略DEFAULT和ON UPDATE子句。如果這樣做，則不會自動進行初始化或更新。例如：下面這些語句是等效的： CREATE TABLE t ( ??? ts1 TIMESTAMP DEFAULT 0, ??? ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ????????????????? ON UPDATE CURRENT_TIMESTAMP); CREATE TABLE t ( ??? ts1 TIMESTAMP DEFAULT 0, ??? ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ????????????????? DEFAULT CURRENT_TIMESTAMP); 可以對每個連接設置當前的時區，相關描述參見[5.10.8節，“MySQL服務器時區支持”](# "5.10.8.?MySQL Server Time Zone Support")。TIMESTAMP值以UTC格式保存，存儲時對當前的時區進行轉換，檢索時再轉換回當前的時區。只要時區設定值為常量，便可以得到保存時的值。如果保存一個TIMESTAMP值，應更改時區然后檢索該值，它與你保存的值不同。這是因為在兩個方向的轉換中沒有使用相同的時區。當前的時區可以用作time_zone系統變量的值。可以在TIMESTAMP列的定義中包括NULL屬性以允許列包含NULL值。例如： CREATE TABLE t ( ? ts1 TIMESTAMP NULL DEFAULT NULL, ? ts2 TIMESTAMP NULL DEFAULT 0, ? ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ); 如果未指定NULL屬性，將列設置為NULL設置則會將它設置為當前的時間戳。請注意允許NULL值的TIMESTAMP列不會采用當前的時間戳，除非要么其默認值定義為CURRENT_TIMESTAMP，或者NOW()或CURRENT_TIMESTAMP被插入到該列內。換句話說，只有使用如下定義創建，定義為 NULL的TIMESTAMP列才會自動更新： CREATE TABLE t (ts NULLDEFAULT CURRENT_TIMESTAMP)；否則-也就是說，如果使用NULL而不是DEFAULT TIMESTAMP來定義TIMESTAMP列，如下所示... CREATE TABLE t1 (ts NULL DEFAULT NULL); CREATE TABLE t2 (ts NULL DEFAULT '0000-00-00 00:00:00'); ...則必須顯式插入一個對應當前日期和時間的值。例如： INSERT INTO t1 VALUES (NOW()); INSERT INTO t2 VALUES (CURRENT_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年兼容性”](# "1.4.5.?Year 2000 Compliance"))，但輸入給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服務器模式”](# "5.3.2.?The Server SQL Mode")。下面的表顯示了將各種字符串值保存到CHAR(4)和VARCHAR(4)列后的結果，說明了CHAR和VARCHAR之間的差別： <table border="1" cellpadding="0" id="table3"><tr><td> 值</td> <td> CHAR(4)</td> <td> 存儲需求</td> <td> VARCHAR(4)</td> <td> 存儲需求</td> </tr><tr><td> ''</td> <td> '????'</td> <td> 4個字節</td> <td> ''</td> <td> 1個字節</td> </tr><tr><td> 'ab'</td> <td> 'ab??'</td> <td> 4個字節</td> <td> 'ab '</td> <td> 3個字節</td> </tr><tr><td> 'abcd'</td> <td> 'abcd'</td> <td> 4個字節</td> <td> 'abcd'</td> <td> 5個字節</td> </tr><tr><td> 'abcdefgh'</td> <td> 'abcd'</td> <td> 4個字節</td> <td> 'abcd'</td> <td> 5個字節</td> </tr></table> 請注意上表中最后一行的值只適用_不使用嚴格模式_時；如果MySQL運行在嚴格模式，超過列長度不的值_不_保存__，并且會出現錯誤。從CHAR(4)和VARCHAR(4)列檢索的值并不總是相同，因為檢索時從CHAR列刪除了尾部的空格。通過下面的例子說明該差別： mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4)); Query OK, 0 rows affected (0.02 sec) ? mysql> INSERT INTO vc VALUES ('ab? ', 'ab? '); Query OK, 1 row affected (0.00 sec) ? mysql> SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc; +----------------+----------------+ | CONCAT(v, '+') | CONCAT(c, '+') | +----------------+----------------+ | ab? +????????? | ab+??????????? | +----------------+----------------+ 1 row in set (0.00 sec) 根據分配給列的字符集校對規則對CHAR和VARCHAR列中的值進行排序和比較。請注意所有MySQL校對規則屬于PADSPACE類。這說明在MySQL中的所有CHAR和VARCHAR值比較時不需要考慮任何尾部空格。例如： mysql> CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10)); Query OK, 0 rows affected (0.09 sec) ? mysql> INSERT INTO names VALUES ('Monty ', 'Monty '); Query OK, 1 row affected (0.00 sec) ? mysql> SELECT myname = 'Monty? ', yourname = 'Monty? ' FROM names; +--------------------+----------------------+ | myname = 'Monty? ' | yourname = 'Monty? ' | +--------------------+----------------------+ |????????????????? 1 |??????????????????? 1 | +--------------------+----------------------+ 1 row in set (0.00 sec) 請注意所有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?\0'。'a\0'插入時變為'a\0\0'。當選擇時兩個插入的值均不更改。對于VARBINARY，插入時不填充字符，選擇時不裁剪字節。比較時所有字節很重要，包括ORDER BY和DISTINCT操作。比較時0x00字節和空格是不同的，0x00<空格。對于尾部填充字符被裁剪掉或比較時將它們忽視掉的情形，如果列的索引需要唯一的值，在列內插入一個只是填充字符數不同的值將會造成復制鍵值錯誤。如果你計劃使用這些數據類型來保存二進制數據并且需要檢索的值與保存的值完全相同，應考慮前面所述的填充和裁剪特征。下面的例子說明了用0x00填充的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) 如果檢索的值必須與指定進行存儲而沒有填充的值相同，最好使用BLOB數據類型。創建表時，MySQL可以默默更改BINARY或VARBINARY列的類型。參見[13.1.5.1節，“沉寂的列規格變更”](# "13.1.5.1.?Silent Column Specification Changes")。 ### 11.4.3.?BLOB和TEXT類型`` BLOB是一個二進制大對象，可以容納可變數量的數據。有4種BLOB類型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們只是可容納值的最大長度不同。有4種TEXT類型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應4種BLOB類型，有相同的最大長度和存儲需求。參見[11.5節，“列類型存儲需求”](# "11.5.?Column Type Storage Requirements")。 BLOB列被視為二進制字符串(字節字符串)。TEXT列被視為非二進制字符串(字符字符串)。BLOB列沒有字符集，并且排序和比較基于列值字節的數值值。TEXT列有一個字符集，并且根據字符集的校對規則對值進行排序和比較。在TEXT或BLOB列的存儲或檢索過程中，不存在大小寫轉換。當未運行在嚴格模式時，如果你為BLOB或TEXT列分配一個超過該列類型的最大長度的值值，值被截取以保證適合。如果截掉的字符不是空格，將會產生一條警告。使用嚴格SQL模式，會產生錯誤，并且值將被拒絕而不是截取并給出警告。參見[5.3.2節，“SQL服務器模式”](# "5.3.2.?The Server SQL Mode")。在大多數方面，可以將BLOB列視為能夠足夠大的VARBINARY列。同樣，可以將TEXT列視為VARCHAR列。BLOB和TEXT在以下幾個方面不同于VARBINARY和VARCHAR： ·???????? 當保存或檢索BLOB和TEXT列的值時不刪除尾部空格。(這與VARBINARY和VARCHAR列相同）。請注意比較時將用空格對TEXT進行擴充以適合比較的對象，正如CHAR和VARCHAR。 ·???????? 對于BLOB和TEXT列的索引，必須指定索引前綴的長度。對于CHAR和VARCHAR，前綴長度是可選的。參見[7.4.3節，“列索引”](# "7.4.3.?Column Indexes")。 ·???????? 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節，“服務器系統變量”](# "5.3.3.?Server System Variables")。運行時增加max_sort_length的值可以在排序或組合時使更多的字節有意義。任何客戶端可以更改其會話max_sort_length變量的值： mysql> SET max_sort_length = 2000; mysql> SELECT id, comment FROM tbl_name ??? -> ORDER BY comment; 當你想要使超過max_sort_length的字節有意義，對含長值的BLOB或TEXT列使用GROUP BY或ORDER BY的另一種方式是將列值轉換為固定長度的對象。標準方法是使用SUBSTRING函數。例如，下面的語句對comment列的2000個字節進行排序： mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name ??? -> ORDER BY SUBSTRING(comment,1,2000); ·???????? BLOB或TEXT對象的最大大小由其類型確定，但在客戶端和服務器之間實際可以傳遞的最大值由可用內存數量和通信緩存區大小確定。你可以通過更改max_allowed_packet變量的值更改消息緩存區的大小，但必須同時修改服務器和客戶端程序。例如，可以使用 **mysql**和**mysqldump**來更改客戶端的max_allowed_packet值。參見[7.5.2節，“調節服務器參數”](# "7.5.2.?Tuning Server Parameters")、[8.3節，“mysql：MySQL命令行工具”](# "8.3.?mysql — The MySQL Command-Line Tool")和[8.8節，“mysqldump：數據庫備份程序”](# "8.8.?mysqldump — A Database Backup Program")。每個BLOB或TEXT值分別由內部分配的對象表示。這與其它列類型形成對比，后者是當打開表時為每1列分配存儲引擎。 ### 11.4.4.?ENUM類型 ENUM是一個字符串對象，其值來自表創建時在列規定中顯式枚舉的一列值。在某些情況下，ENUM值也可以為空字符串('')或NULL： ·???????? 如果你將一個非法值插入ENUM(也就是說，允許的值列之外的字符串)，將插入空字符串以作為特殊錯誤值。該字符串與“普通”空字符串不同，該字符串有數值值0。后面有詳細討論。 ·???????? 如果將ENUM列聲明為允許NULL，NULL值則為該列的一個有效值，并且默認值為NULL。如果ENUM列被聲明為NOT NULL，其默認值為允許的值列的第1個元素。每個枚舉值有一個索引： ·???????? 來自列規定的允許的值列中的值從1開始編號。 ·???????? 空字符串錯誤值的索引值是0。這說明你可以使用下面的SELECT語句來找出分配了非法ENUM值的行： ·??????????????? mysql> SELECT * FROM tbl_name WHERE enum_col=0; ·???????? NULL值的索引是NULL。例如，定義為ENUM的列('one'，'two'，'three')可以有下面所示任何值。還顯示了每個值的索引： <table border="1" cellpadding="0" id="table4"><tr><td> 值</td> <td> 索引</td> </tr><tr><td> NULL</td> <td> NULL</td> </tr><tr><td> ''</td> <td> 0</td> </tr><tr><td> 'one'</td> <td> 1</td> </tr><tr><td> 'two'</td> <td> 2</td> </tr><tr><td> 'three'</td> <td> 3</td> </tr></table> 枚舉最多可以有65,535個元素。當創建表時，ENUM成員值的尾部空格將自動被刪除。當檢索時，保存在ENUM列的值使用列定義中所使用的大小寫來顯示。請注意可以為ENUM列分配字符集和校對規則。對于二進制或大小寫敏感的校對規則，當為列分配值時應考慮大小寫。如果在數值上下文中檢索一個ENUM值，將返回列值的索引。例如，你可以這樣從ENUM列搜索數值值： mysql> SELECT enum_col+0 FROM tbl_name; 如果將一個數字保存到ENUM列，數字被視為索引，并且保存的值是該索引對應的枚舉成員。(但是，這不適合LOAD DATA，它將所有輸入視為字符串）。不建議使用類似數字的枚舉值來定義一個ENUM列，因為這很容易引起混淆。例如，下面的列含有字符串值'0'、'1'和'2'的枚舉成員，但數值索引值為1、2和3： numbers ENUM('0','1','2') 根據枚舉成員在列定義中列出的順序對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的列可以有下面的任何值： '' 'one' 'two' 'one,two' SET最多可以有64個不同的成員。當創建表時，SET成員值的尾部空格將自動被刪除。當檢索時，保存在SET列的值使用列定義中所使用的大小寫來顯示。請注意可以為SET列分配字符集和校對規則。對于二進制或大小寫敏感的校對規則，當為列分配值時應考慮大小寫。 MySQL用數字保存SET值，所保存值的低階位對應第1個SET成員。如果在數值上下文中檢索一個SET值，檢索的值的位設置對應組成列值的SET成員。例如，你可以這樣從一個SET列檢索數值值： mysql> SELECT set_col+0 FROM tbl_name; 如果將一個數字保存到SET列中，數字中二進制表示中的位確定了列值中的SET成員。對于指定為SET('a','b','c','d')的列，成員有下面的十進制和二進制值： <table border="1" cellpadding="0" id="table5"><tr><td> SET成員</td> <td> 十進制值</td> <td> 二進制值</td> </tr><tr><td> 'a'</td> <td> 1</td> <td> 0001</td> </tr><tr><td> 'b'</td> <td> 2</td> <td> 0010</td> </tr><tr><td> 'c'</td> <td> 4</td> <td> 0100</td> </tr><tr><td> 'd'</td> <td> 8</td> <td> 1000</td> </tr></table> ? 如果你為該列分配一個值9，其二進制形式為1001，因此第1個和第4個SET值成員'a'和'd'被選擇，結果值為 'a,d'。對于包含多個SET元素的值，當插入值時元素所列的順序并不重要。在值中一個給定的元素列了多少次也不重要。當以后檢索該值時，值中的每個元素出現一次，根據表創建時指定的順序列出元素。例如，假定某個列指定為SET('a','b','c','d')： mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd')); 插入值'a,d'、'd,a'、'a,d,d'、'a,d,a'和'd,a,d': mysql> INSERT INTO myset (col) VALUES -> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d'); Query OK, 5 rows affected (0.01 sec) Records: 5? Duplicates: 0? Warnings: 0 當檢索時所有這些值顯示為 'a,d'： mysql> SELECT col FROM myset; +------+ | col? | +------+ | a,d? | | a,d? | | a,d? | | a,d? | | a,d? | +------+ 5 rows in set (0.04 sec) 如果將SET列設置為一個不支持的值，則該值被忽略并發出警告： mysql> INSERT INTO myset (col) VALUES ('a,d,d,s'); Query OK, 1 row affected, 1 warning (0.03 sec) ? mysql> SHOW WARNINGS; +---------+------+------------------------------------------+ | Level?? | Code | Message????????????????????????????????? | +---------+------+------------------------------------------+ | Warning | 1265 | Data truncated for column 'col' at row 1 | +---------+------+------------------------------------------+ 1 row in set (0.04 sec) ? mysql> SELECT col FROM myset; +------+ | col? | +------+ | a,d? | | a,d? | | a,d? | | a,d? | | a,d? | | a,d? | +------+ 6 rows in set (0.01 sec) SET值按數字順序排序。NULL值排在非NULLSET值的前面。通常情況，可以使用FIND_IN_SET()函數或LIKE操作符搜索SET值： mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0; mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%'; 第1個語句找出_SET_col_包含_value_ set成員的行。第2個類似，但有所不同：它在其它地方找出_set_col_包含_value_的行，甚至是在另一個SET成員的子字符串中。下面的語句也是合法的： mysql> SELECT * FROM tbl_name WHERE set_col & 1; mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2'; 第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節，“沉寂的列規格變更”](# "13.1.5.1.?Silent Column Specification Changes")。 **數值類型存儲需求