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

下面為數值列型別的概述。詳細資訊參見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.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.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。例如：

·                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章：字符集支援。

·         [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值在內部用整數表示。

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。

mysql> SET GLOBAL sql_mode=MAXDB；

mysql> SET SESSION sql_mode=MAXDB;

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);

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);

CREATE TABLE t

(

  ts1 TIMESTAMP NULL DEFAULT NULL,

  ts2 TIMESTAMP NULL DEFAULT 0,

  ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE t (ts NULLDEFAULT CURRENT_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);

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))。

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之間的差別：

請注意上表中最後一行的值只適用不使用嚴格模式時；如果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字符集。

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值如何影響列值比較：

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  ` from t;

+--------+---------+-------------+

| HEX(c) | c = `a` | c = `a  ` |

+--------+---------+-------------+

| 610000 |       0 |           1 |

+--------+---------+-------------+

1 row in set (0.09 sec)

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

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

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變數的值：

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節，“調節伺服器引數”、8.3節，“mysql：MySQL命令列工具”和8.8節，“mysqldump：資料庫備份程式”。

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