MySQL Reference Manual for version 4.1.0-alpha.


6.2 列類型

MySQL 支援大量的列類型,它們可以被分為 3 類:數位類型、日期和時間類型以及字串(字元)類型。這個章節首先給出可用類型的概述,並且總結各類型所需的儲存需求,然後提供各類型中的類型範疇更詳細的描述。概述有意地簡化了。更詳細的說明應該參考特寫列類型的附加資訊,例如你能為其指定值的允許格式。

MySQL 支援的列類型在下面列出。下列代碼字母用於描述中:

M
指出最大的顯示尺寸。最大的顯示尺寸長度為 255。
D
適用於浮點類型。指出跟隨在十進制小數點後的數位數量。最大可能值為 30,但不應大於 M-2。

方括號 (“[” and “]”) 指定可選的類型修飾部份。

注意,如果為一個列指定了 ZEROFILL,MySQL 將自動為這個列加入 UNSIGNED 屬性。

警告:你應該知道當在兩個整數類型值中使用減法時,如有一個為 UNSIGNED類型,那麼結果也是無符號的。查看章節 6.3.5 Cast 函數

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
一個非常小的整數。有符號的範圍是 -128127。無符號的範圍是 0255
BIT
BOOL
它們是 TINYINT(1) 的同義詞。
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
一個小整數。有符號的範圍是 -3276832767。無符號的範圍是 065535
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
一個中等大小的整數。有符號的範圍是 -83886088388607。無符號的範圍是 016777215
INT[(M)] [UNSIGNED] [ZEROFILL]
一個正常大小的整數。有符號的範圍是 -21474836482147483647。無符號的範圍是 04294967295
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
INT 的同義詞。
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
一個大的整數。有符號的範圍是 -92233720368547758089223372036854775807。無符號的範圍是 018446744073709551615。 你應該知道的有關 BIGINT 列的一些事情:
FLOAT(precision) [UNSIGNED] [ZEROFILL]
一個浮點型數位。precision 可以是 <=24 作為一個單精度的浮點數位和介於 25 和 53 之間作為一個雙精度的浮點數位。這些類型與下面描述的 FLOATDOUBLE 類型相似。 FLOAT(X) 有與相應的 FLOATDOUBLE 類型同樣的範圍,但是顯示尺寸和十進制小數位數是未定義的。 在 MySQL 3.23 中,它是一個真實的浮點值。而在 MySQL 早期的版本中,FLOAT(precision) 通常有 2 小數位。 注意,由於在 MySQL 中所有的計算都是以雙精度執行的,所以使用 FLOAT 可能帶來一些意想不到的問題。 查看章節 A.5.6 解決沒有相符行的問題 該句法是為了 ODBC 兼容而提供的。
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
一個小的(單精度) 浮點數位。允許的值是 -3.402823466E+38-1.175494351E-3801.175494351E-383.402823466E+38。如果 UNSIGNED 被指定,負值是不允許的。M 是顯示寬度,D 是小數位數。FLOAT 沒有參數或有 X <= 24 的 FLOAT(X) 代表一個單精度的浮點數位。
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
一個正常大小的(雙精度)浮上數位。允許的值是 -1.7976931348623157E+308-2.2250738585072014E-30802.2250738585072014E-3081.7976931348623157E+308。如果 UNSIGNED 被指定,負值是不允許的。M 是顯示寬度,D 是小數位數。DOUBLE 沒胡參數或有 25 <= X <= 53 的 FLOAT(X) 代表一個雙精度的浮點數位。
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]
REAL[(M,D)] [UNSIGNED] [ZEROFILL]
它們是 DOUBLE 同義詞。
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
一個未壓縮(unpacked)的浮點數。運作如同一個 CHAR 列:“unpacked” 意味著數位是以一個字串儲存的,值的每一位將使用一個字元。小數點並且對於負數,“-” 符號不在 M 中計算(但是它們的空間是被保留的)。如果 D 是 0,值將沒有小數點或小數部份。DECIMAL 值的最大範圍與 DOUBLE 一致,但是對於一個給定的 DECIMAL 列,實際的範圍可以被所選擇的 MD 限制。如果 UNSIGNED 被指定,負值是不允許的。 如果 D 被忽略,預設為 0。如果 M 被忽略,預設為 10。 在 MySQL 3.23 以前,M 參數必須包含符號與小數點所需的空間。
DEC[(M[,D])] [UNSIGNED] [ZEROFILL]
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
DECIMAL 的同義詞。
DATE
一個日期。支援的範圍是 '1000-01-01''9999-12-31'。MySQL 以 'YYYY-MM-DD' 格式顯示 DATE 值,但是允許你以字串或數位給一個 DATE 列賦值。查看章節 6.2.2.2 DATETIMEDATETIMESTAMP 類型
DATETIME
一個日期和時間的組合。支援的範圍是 '1000-01-01 00:00:00''9999-12-31 23:59:59'。MySQL 以'YYYY-MM-DD HH:MM:SS' 格式顯示 DATETIME 值,但是允許你以字串或數位給一個 DATETIME 列賦值。查看章節 6.2.2.2 DATETIMEDATETIMESTAMP 類型
TIMESTAMP[(M)]
一個時間戳。範圍是 '1970-01-01 00:00:00'2037 年間的任意時刻。 MySQL 4.0 和更早版本中,TIMESTAMP 值是以 YYYYMMDDHHMMSSYYMMDDHHMMSSYYYYMMDDYYMMDD 格式顯示的,它取決於 M 是否是 14 (或省略)、1286,但是允許你以字串或數位給一個 TIMESTAMP 列賦值。 從 MySQL 4.1 開始,TIMESTAMP'YYYY-MM-DD HH:MM:DD' 格式作為字元傳回。如果你你希望以數位形式傳回則必須在該時間戳欄位後加上 +0。不同的時間戳長度是不支援的。從 MySQL 4.0.12 開始,選項 --new 可以被用來使伺服器與 4.1 一樣運作。 TIMESTAMP 列有益於記錄一個 INSERTUPDATE 操作的日期和時間,因為如果你自己沒有給它賦值,它將被自動地設定為最近一次操作的日期和時間。也可以通過給它賦一個 NULL 而使它設定為目前的日期和時間。查看章節 6.2.2 Date 和 Time 類型。 參數 M 只影響一個 TIMESTAMP 列的顯示格式﹔它的值總是占用 4 個字節儲存。 注意,當 TIMESTAMP(M) 列的 M 是 8 或 14 時,它傳回的是數位而其它的 TIMESTAMP(M) 列傳回的是字串。這僅僅是為了可以可靠地轉儲並恢復到其它格式的表中。 查看章節 6.2.2.2 DATETIMEDATETIMESTAMP 類型
TIME
一個時間。範圍是 '-838:59:59''838:59:59'。MySQL 以 'HH:MM:SS' 格式顯示 TIME 值,但是允許你使用字串或數位來給 TIME 列賦值。查看章節 6.2.2.3 TIME 類型
YEAR[(2|4)]
一個 2 或 4 位數位格式的年(預設為 4 位)。允許的值是 190121550000(4 位年格式) 以及使用 2 位格式的 1970-2069 (70-69)。MySQL 以 YYYY 格式顯示 YEAR 值,但是允許你使用字串或數位來給 YEAR 列賦值。(YEAR 類型在 MySQL 3.22 之前不支援。) 查看章節 6.2.2.4 YEAR 類型
[NATIONAL] CHAR(M) [BINARY]
一個定長的字串,當儲存時,總是以空格填滿右邊到指定的長度。M 的範圍是 0 到 255 (在 MySQL 3.23 版本之前為 1 到 255)。當該值被檢索時,尾部空格將被刪除。CHAR 值根據預設的字元集進行忽略大小寫的排索與比較,除非指定了關鍵詞 BINARYNATIONAL CHAR (或短形式 NCHAR) 是以 ANSI SQL 方式定義一個 CHAR 列,它將使用預設的字元集。這在 MySQL 中是預設的。 CHARCHARACTER 的縮寫。 MySQL 允許以 CHAR(0) 類型建立一個列。一些老程式運行時必需一個列,卻又並不使用這個列的值,你就不得不為了適應它而建立該列,在這情況下,CHAR(0) 將是很有益的。當需要一個列僅保存兩個值時:一個為 CHAR(0)(該列沒有定義為 NOT NULL),這將僅占用一個比特位來儲存 2 個值:NULL""。查看章節 6.2.3.1 CHARVARCHAR 類型
CHAR
這是 CHAR(1) 的同義詞。
[NATIONAL] VARCHAR(M) [BINARY]
一個變長的字串。注意:尾部的空格在儲存時將會被刪除(這與 ANSI SQL 約規不同)。M 的範圍是 0 到 255 (在 MySQL 4.0.2 之前的版本中是 1 到 255)。 VARCHAR 值以大小寫忽略方式進行排索與比較,除非關鍵詞 BINARY 被指定。查看章節 6.5.3.1 隱式的列定義變化VARCHARCHARACTER VARYING 的縮寫。 查看章節 6.2.3.1 CHARVARCHAR 類型
TINYBLOB
TINYTEXT
一個 BLOBTEXT 列,最大長度為 255 (2^8 - 1) 個字元。查看章節 6.5.3.1 隱式的列定義變化。查看章節 6.2.3.2 BLOBTEXT 類型
BLOB
TEXT
一個 BLOBTEXT 列,最大長度為 65535 (2^16 - 1) 個字元。查看章節 6.5.3.1 隱式的列定義變化。查看章節 6.2.3.2 BLOBTEXT 類型
MEDIUMBLOB
MEDIUMTEXT
一個 BLOBTEXT 列,最大長度為 16777215 (2^24 - 1) 個字元。查看章節 6.5.3.1 隱式的列定義變化。查看章節 6.2.3.2 BLOBTEXT 類型
LONGBLOB
LONGTEXT
一個 BLOBTEXT 列,最大長度為 4294967295 (2^32 - 1) 個字元。查看章節 6.5.3.1 隱式的列定義變化。注意,由於伺服器/客戶端的協定以及 MyISAM 表通常有一個 16M 每通信包/表行的限制,你仍然不能使用這個類型的整個範圍。查看章節 6.2.3.2 BLOBTEXT 類型
ENUM('value1','value2',...)
一個枚舉類型。一個僅能有一個值的字串物件,這個值選自值列 'value1''value2'...NULL 或特殊的 "" 出錯值。一個 ENUM 列可以有最大 65535 不同的值。查看章節 6.2.3.3 ENUM 類型
SET('value1','value2',...)
一個集合。一個能有零個或更多個值的字串物件,其中每個值必須選自值列'value1''value2'...。一個 SET 列可以有最大 64 個成員。查看章節 6.2.3.4 SET 類型

6.2.1 數位類型

MySQL 支援所有的 ANSI/ISO SQL92 數位類型。這些類型包括準確數位的資料類型(NUMERICDECIMALINTEGERSMALLINT),也包括近似數位的資料類型(FLOATREALDOUBLE PRECISION)。關鍵詞 INTINTEGER 的同義詞,關鍵詞 DECDECIMAL 的同義詞。

NUMERICDECIMAL 類型被 MySQL 以同樣的類型實現,這在 SQL92 標準中是允許的。他們用於保存對準確精度有重要要求的值,例如與金錢有關的資料。當以它們中的之一宣告一個列時,精度和數值範圍可以(通常是)被指定﹔例如:

    salary DECIMAL(5,2)

在這個範例中,5 (精度(precision)) 代表重要的十進制數位的數目,2 (資料範圍(scale)) 代表在小數點後的數位位數。在這種情況下,因此,salary 列可以儲存的值範圍是從 -99.9999.99。(實際上 MySQL 在這個列中可以儲存的數值可以一直到 999.99,因為它沒有儲存正數的符號)。

譯者注:

M 與D 對DECIMAL(M, D) 取值範圍的影響

類型說明          取值範圍(MySQL < 3.23)     取值範圍(MySQL >= 3.23)
DECIMAL(4, 1)        -9.9 到 99.9                  -999.9 到 9999.9
DECIMAL(5, 1)       -99.9 到 999.9                -9999.9 到 99999.9
DECIMAL(6, 1)      -999.9 到 9999.9              -99999.9 到 999999.9
DECIMAL(6, 2)      -99.99 到 999.99              -9999.99 到 99999.99
DECIMAL(6, 3)      -9.999 到 99.999              -999.999 到 9999.999

# 在MySQL 3.23 及以後的版本中,DECIMAL(M, D) 的取值範圍等於早期版本中的DECIMAL(M + 2, D) 的取值範圍。
註釋結束:

在 ANSI/ISO SQL92 中,句法 DECIMAL(p) 等價於 DECIMAL(p,0)。同樣的,在執行被允許決定值 p 的地方,句法 DECIMAL 等價於 DECIMAL(p,0)。MySQL 目前還不支援 DECIMAL/NUMERIC 資料類型的這些變體形式中的任一種。一般來說這並不是一個嚴重的問題,通過明確地控制精度和數值範圍可以得到這些類型的主要功能益處。

DECIMALNUMERIC 值是作為字串儲存的,而不是作為二進位浮點數,以便保護這些值的十進制精確度。一個字元用於數值的每一位、小數點(如果 scale > 0) 和 “-” 符號(對於負值)。如果 scale 是 0,DECIMALNUMERIC 值不包含小數點或小數部分。

DECIMALNUMERIC 值的最大範圍與 DOUBLE 一致,但是對於一個給定的 DECIMALNUMERIC 列,它的實際範圍可制定該列時的 precisionscale 限制。當這樣的列被賦給了小數點的位數超過 scale 所指定的值時,該將根據 scale 進行四舍五入。當一個 DECIMALNUMERIC 列被賦與一個大小超過指定(或預設)的 precision and scale 的限止範圍時,MySQL 以該列範圍的端點值儲存該值。

範例(譯者注):
mysql> CREATE TABLE `tA` (
    ->   `id` int(4) unsigned zerofill NOT NULL,
    ->   `salary` decimal(5,2),
    ->   PRIMARY KEY  (`id`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `tA` (`id`, `salary`)
    ->  VALUES (1, -99.99),     # 以數位方式插入
    ->         (2, "99.99"),    # 以字串方式插入
    ->         (3, -999.99), 
    ->         (4, "9999.99"),
    ->         (5, -1000.00),   # decimal(5,2)最小範圍為 -999.99 該值插入時結果為 -999.99
    ->         (6, "10000.00"), # decimal(5,2)最大範圍為 9999.99 該值插入時結果為 9999.99
    ->         (7, "-99.999"),  # 小數位數超過 scale 指定值,但因以字串方式插入,結果值僅截去多餘部分
    ->         (8, "99.9999"),
    ->         (9, -99.999),    # 小數位數超過 scale 指定值,對該值進行四舍五入,結果為 -100.00
    ->         (10,99.9999);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 4

mysql> SELECT * FROM `tA`;
+------+---------+
| id   | salary  |
+------+---------+
| 0001 |  -99.99 |
| 0002 |   99.99 |
| 0003 | -999.99 |
| 0004 | 9999.99 |
| 0005 | -999.99 |
| 0006 | 9999.99 |
| 0007 |  -99.99 |
| 0008 |   99.99 |
| 0009 | -100.00 |
| 0010 |  100.00 |
+------+---------+
10 rows in set (0.00 sec)

* 以上結果在 MySQL 4.0.12 中測試
範例結束(譯者注)

作為對 ANSI/ISO SQL92 標準的一個擴展,MySQL 也支援上面的表格所列出的整型類型 TINYINTMEDIUMINTBIGINT。另外一個擴展是 MySQL 支援隨意指定一個整型數值的顯示格式,這通過在類型的基本關鍵詞後跟一個括號來實現(例如 INT(4))。這個可選的寬度規格說明是用於在數值顯示時,對某些值的寬度短於該列寬度的值進行左填補顯示的,而不是為了限制在該列中儲存值的寬度,也不是為了限制那些超過該列指定寬度的值的可被顯示的數位位數。當與可選的擴展屬性 ZEROFILL 一起使用時,預設填補用的空格被零代替。舉例來說,一個列被定義為 INT(5) ZEROFILL,插入的值 4 被檢索出來時為 00004。注意,如果在一個整型列中儲存一個超過顯示寬度的更大值時,當 MySQL 為某些復雜的聯結(join)生成臨時表時,你可能會遇到問題,因為在這種情況下,MySQL 信任地認為所有的值均適合原始的列寬度。

範例(譯者注):
mysql> CREATE TABLE `tA` (
    ->   `id` int(4) unsigned zerofill NOT NULL,
    ->     PRIMARY KEY  (`id`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `tA` (`id`)
    ->  VALUES (1),(12),(1234),(12345678);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `tA`;
+----------+
| id       |
+----------+
|     0001 |
|     0012 |
|     1234 |
| 12345678 |
+----------+
4 rows in set (0.00 sec)

* 以上結果在 MySQL 4.0.12 中測試
範例結束(譯者注)

所有的整型類型可以有一個可選(非標準的)屬性 UNSIGNED。如果希望在一個列中只允許正值並且需要一個稍大一點的數位範圍,就可以使用無符號值。

從 MySQL 4.0.2 開始,浮點類型也可以存在 UNSIGNED。 與整型類型一致的,這個屬性可以防止在該列中存在負值。而與整型類型不一致的,該列的高部範圍仍然與原範圍保持一致。

FLOAT 類型被用於表示近似數位的數值類型。ANSI/ISO SQL92 標準允許一個可選的精度說明(但不是指數的範圍),跟在 關鍵詞 FLOAT 後的括號內。MySQL 實現也支援這個可選的精度規格說明。當關鍵詞被用於一個列的類型說明而沒有精度規格說明時,MySQL 使用四個字節來儲存該欄位值。在關鍵詞 FLOAT 後的括號裡給出兩個數位,這種變形的句法也是被支援的。使用這個選項時,第一個數位繼續表示值儲存所占的字節數,第二個數位指定能被儲存和顯示的跟隨在小數點後的位數(就像 DECIMALNUMERIC)。當 MySQL 被要求為這樣的一個列儲存一個小數點後的小數位數超過指定值的數值時,該值將會被四舍五入,去除額外的位。

範例(譯者注):
mysql> CREATE TABLE `tA` (
    ->   `id` int(4) unsigned zerofill NOT NULL,
    ->   `float_date` float(4,2) unsigned NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `tA` (`id`,`float_date`)
    ->  VALUES (1, -100),        # 插入無效的負值
    ->         (2, 100.99),      # 以數位方式插入
    ->         (3, "100.99"),    # 以字串方式插入
    ->         (4, "100.99999"), # 小數位數超過指定倍數,該值進行四舍五入
    ->         (5, 100.99999),
    ->         (6, "100.9911"),
    ->         (7, 100.9911);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM `tA`;
+------+------------+
| id   | float_date |
+------+------------+
| 0001 |       0.00 |
| 0002 |     100.99 |
| 0003 |     100.99 |
| 0004 |     101.00 |
| 0005 |     101.00 |
| 0006 |     100.99 |
| 0007 |     100.99 |
+------+------------+
7 rows in set (0.00 sec)

* 以上結果在 MySQL 4.0.12 中測試
範例結束(譯者注)

REALDOUBLE PRECISION 類型不接受精度規格說明。作為對 ANSI/ISO SQL92 標準的擴展,MySQL 接受 DOUBLE 作為 DOUBLE PRECISION 類型的一個同義詞。標準要求 REAL 的精度比用於 DOUBLE PRECISION 的更小,而與之相反的,MySQL 以 8 字節的雙精度浮點值(當不以“ANSI 樣式”運行時)來實現兩者。為了得到最大的可移植性, 近似數位的數值儲存所需代碼應該使用沒有精度或小數位數規格說明的 FLOATDOUBLE PRECISION 類型。

當試圖在一個數位列中儲存一個超過該列允許範圍的值時,MySQL 會剪下該值到範圍內的適當端點值,並以結果值代替儲存。

舉例來說,一個整型列的範圍是 -21474836482147483647。如果試圖在一個 INT 列中插入值 -9999999999,該值將會被剪下到該範圍的低部端點,以 -2147483648 代替儲存。同樣的,如果試圖插入 99999999992147483647 將被代替儲存。

如果 INT 列是 UNSIGNED的,列的範圍大小是一致的,不過它的端點移動到了 04294967295。如果你試圖儲存 -99999999999999999999,而實際列中儲存的值將會變成 04294967296

對於 ALTER TABLELOAD DATA INFILEUPDATE 和多行 INSERT 語句,由於剪下發生的轉換,將以“Warnings”被報告。

類型 占用字節
TINYINT 1 -128 127
SMALLINT 2 -32768 32767
MEDIUMINT 3 -8388608 8388607
INT 4 -2147483648 2147483647
BIGINT 8 -9223372036854775808 9223372036854775807

6.2.2 Date 和 Time 類型

日期和時間類型有 DATETIMEDATETIMESTAMPTIMEYEAR。每一個類型均有合法值的範圍,當給它們賦於一個真正不合法的值時,這些值將會被“零”代替。注意,MySQL 允許儲存某個“不嚴格地”合法的日期,例如 1999-11-31。這樣做的原因是,我們認為應用程式有責任來處理日期合法性的檢查,而不是由 SQL 伺服器來處理。為了“加快”對日期的檢查,MySQL 僅檢查月份應在 0-12 範圍內,以及天在 0-31 範圍內。因為上面所述的範圍定義方式,MySQL 因而允許你在一個 DATEDATETIME 列中儲存日或月日均為 0 的日期。當一個應用程式希望儲存一個出生日期,而你並不知準確的出生日月時,這將變得非常有用。在這種情況下,你可以簡單地以 1999-00-001999-01-00 形式儲存日期。(當然你不能期望 DATE_SUB()DATE_ADD 之類的函數能正確地處理這樣的日期,並得到正確的值。)

當使用日期和時間類型工作時,這裡有一些要記住的總則:

6.2.2.1 Y2K 問題和日期類型

MySQL 自身是 Y2K 安全的(查看章節 1.2.5 2000 年兼容性),但是餘交給 MySQL 的值可能並不是。任何一個包含 2 位年份的值是存在二義性的,因為世紀值是未知的。這樣的值必須被解釋為 4 位格式,因為 MySQL 內部使用四位儲存年份。

對於 DATETIMEDATETIMESTAMPYEAR 類型,MySQL 使用下列規則解釋存在二義性的年份值:

記住,這些規則僅僅提供對於你的資料含義的合理猜測。如果 MySQL 使用的探索法無法得到正確的值,你應該以包含 4 位年份的格式提供一個明確的值。

ORDER BY 將以適當的順序對 2 位 YEAR/DATE/DATETIME 類型排序。

注意,某些像 MIN()MAX() 之類的函數會將 TIMESTAMP/DATE 轉換為一個數位。這就意味著,一個 2 位年份的時間戳將完全不能與這些函數一同工作。在這種情況下,解決的辦法是將 TIMESTAMP/DATE 轉換為 4 位年份格式或者使用諸如 MIN(DATE_ADD(timestamp,INTERVAL 0 DAYS)) 的方法

6.2.2.2 DATETIMEDATETIMESTAMP 類型

DATETIMEDATETIMESTAMP 類型是相似的。這個章節描述了它們的特性以及它們的相似點與不同點。

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 列類型提供了一種類型,通過它你可以以目前操作的日期和時間自動地標記 INSERTUPDATE 操作。如果一張表中有多個 TIMESTAMP 列,只有第一個被自動更新。

自動更新第一個 TIMESTAMP 列在下列任何條件下發生:

第一個列以外其它 TIMESTAMP 列,可以設定到目前的日期和時間,只要將該列賦值 NULLNOW()

任何 TIMESTAMP 列均可以被設定一個不同於目前操作日期與時間的值,這通過為該列明確指定一個你所期望的值來實現。這也適用於第一個 TIMESTAMP 列。這個選擇性是很有用的,舉例來說,當你希望 TIMESTAMP 列保存該記錄行被新加入時的目前的日期和時間,但該值不再發生改變,無論以後是否對該記錄行進行過更新:

另一方面,你可能發現更容易的方法,使用 DATETIME 列,當新增記錄行時以 NOW() 初始化該列,以後在對該記錄行進行更新時不再處理它。

範例(譯者注):

mysql> CREATE TABLE `tA` (
    ->   `id` int(3) unsigned NOT NULL auto_increment,
    ->     `date1` timestamp(14) NOT NULL,
    ->     `date2` timestamp(14) NOT NULL,
    ->     PRIMARY KEY  (`id`)
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `tA` SET `id` = 1; 
Query OK, 1 row affected (0.02 sec)

# 沒有明確地指定第一個 timestamp 列值,該列值被設為插入的目前時刻
# 沒有明確地指定其它的 timestamp 列值,MySQL 則認為插入的是一個非法值,而該列值被設為0

mysql> INSERT INTO `tA` VALUES (2, NOW(), NULL);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM `tA`;
+----+----------------+----------------+
| id | date1          | date2          |
+----+----------------+----------------+
|  1 | 20030503104118 | 00000000000000 |
|  2 | 20030503104254 | 20030503104254 |
+----+----------------+----------------+
2 rows in set (0.00 sec)

mysql> UPDATE `tA` SET `id` = 3 WHERE `id` = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 對某一記錄行進行了更新,第一個 timestamp 列值也將被更新

mysql> UPDATE `tA` SET `id` = 2 WHERE `id` = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

# MySQL 忽略了這次操作,第一個 timestamp 列值不會被更新

mysql> SELECT * FROM `tA`;
+----+----------------+----------------+
| id | date1          | date2          |
+----+----------------+----------------+
|  3 | 20030503104538 | 00000000000000 |
|  2 | 20030503104254 | 20030503104254 |
+----+----------------+----------------+
2 rows in set (0.00 sec)

mysql> UPDATE `tA` SET `id` = 1,`date1`=`date1` WHERE `id` = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 明確地指定了第一個 timestamp 列值為它原有值,該值將不會被更新

mysql> SELECT * FROM `tA`;
+----+----------------+----------------+
| id | date1          | date2          |
+----+----------------+----------------+
|  1 | 20030503104538 | 00000000000000 |
|  2 | 20030503104254 | 20030503104254 |
+----+----------------+----------------+
2 rows in set (0.00 sec)

* 以上結果在 MySQL 4.0.12 中測試
範例結束(譯者注)

TIMESTAMP 值可以從 1970 到 2037 之間的任一時刻,精度為一秒。其值作為數位顯示。

MySQL 檢索與顯示 TIMESTAMP 值的格式取決於它的顯示尺寸,描述如下表。“完整”的 TIMESTAMP 格式是 14 位的,但是 TIMESTAMP 列可以以一個更短的顯示尺寸建立:

列類型 顯示格式
TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YY

所有的 TIMESTAMP 列均有一個相同的儲存尺寸,而不考慮顯示尺寸的大小。最常見的顯示尺寸為 6、8、12 和 14。你可以在表建立時指定一個任意的顯示尺寸,但是值 0 或 比 14 大的值將會被強制定義為列長 14。在從 1 ~ 13 範圍內的奇數會被強制為下一個更大的偶數。

例如(譯者注):
欄位定義 強制欄位長度
TIMESTAMP(0) TIMESTAMP(14)
TIMESTAMP(15) TIMESTAMP(14)
TIMESTAMP(1) TIMESTAMP(2)
TIMESTAMP(5) TIMESTAMP(6)

注意:從 MySQL 4.1 開始,TIMESTAMP'YYYY-MM-DD HH:MM:DD' 格式作為字串傳回。不同的時間戳長度不再被支援。

譯者注:如果你你希望在 MySQL 4.1 中以數位形式傳回時間戳,則必須在該時間戳欄位後加上 +0。從 MySQL 4.0.12 開始,選項 --new 可以被用來使伺服器與 4.1 一樣運作。

你可以使用常用的格式集中的任何一個指定 DATETIMEDATETIMESTAMP 值:

非法的 DATETIMEDATETIMESTAMP 值將會被轉換到適當形式的“零”值('0000-00-00 00:00:00''0000-00-00'00000000000000)。

對於以字串格式指定的包含日期定界符的值,不必要為小於 10 的月或天的值指定 2 位數位。'1979-6-9' 等同於 '1979-06-09'。同樣的,對於以字串指定的包含時間定界符的值,不必要為小於 10 的時、分或秒指定 2 位數位。'1979-10-30 1:2:3' 等同於 '1979-10-30 01:02:03'

以數位指定的值應該是 6、8、12 或 14 位長。如果數位是 8 或 14 位長的,該值將被假定為年份是由頭 4 位數位給出的 YYYYMMDDYYYYMMDDHHMMSS 格式。如果數位是 6 或 12 數位長,它將被假定為年份是由頭 2 位數位給出的 YYMMDDYYMMDDHHMMSS 格式。不是這些長度之一的數位通過在值前補零到最接近的長度後解釋。

以沒有定界符的字串格式指定的值通過它們給出的長度來解釋。如果字串是 8 或 14 個字元長,則假定年份由前 4 個字元給出。否則,年份由前 2 個字元給出。對於字串中出現的多個部分,字串以從左到右的順序被解釋,以找出年、月、日、時、分和秒值。這就意味著,你不就使用少於 6 個字元的字串。例如,如果指定 '9903' ,你可能認為它代表 1999年3月,但你將會發現MySQL 會將一個“零”值插入到你的表中。這是因為,年份和月份分別為 9903,但是日期部分丟失(為 0),因此這個值是不合法的。

TIMESTAMP 列總是以完全精度儲存給定的合法值,而不考慮顯示尺寸的大小。這包含幾個含義:

在某種程式上,你可以將一個日期值賦給另一種日期類型的物件。然而,這可能會使值產生改變或丟失一部分資訊。

當指定日期值時,當心某些失誤:

6.2.2.3 TIME 類型

MySQL 以 'HH:MM:SS' 格式(或對大的小時值時使用 'HHH:MM:SS' 格式)檢索和顯示 TIME 值。TIME 值的範圍可以從 '-838:59:59''838:59:59'。小時部分可以這麼大的原因 是,TIME 類型不僅可以用於表示一天的時間(這一定不會超過 24 小時),而且可以用來表示所經過的時間或兩個事件之間的時間間隔(這可能比 24 小時大許多或是一個負值)。

TIME 值可以多種格式指定:

對於以字串指定的包含時間定界符的 TIME 值,不必要為小於 10 的時、分或秒指定 2 位數位。'8:3:2''08:03:02' 是一致的。

將“短”的 TIME 值賦給另一個 TIME 列時要格外小心。如果沒有冒號,MySQL 使用最右位代表秒的假設來解釋值。(MySQL 將 TIME 值解釋為經過的時間,而不是時刻)。例如,你可能會認為 '1112' and 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' 或是一個不合法的值。

6.2.2.4 YEAR 類型

YEAR 類型是一個以 1 個字節描述年份的類型。

MySQL 以 YYYY 格式檢索和顯示一個 YEAR 值。範圍是 19012155

可以以多個格式指定 YEAR 值:

不合法的 YEAR 值將被轉換為 0000

6.2.3 字串類型

字串類型有 CHARVARCHARBLOBTEXTENUMSET。這個章節描述這些類型是如何工作的,它們的儲存需求,以及在你的查詢中如何使用它們。

類型 最大尺寸 字節
TINYTEXTTINYBLOB 2^8-1 255
TEXTBLOB 2^16-1 (64K-1) 65535
MEDIUMTEXTMEDIUMBLOB 2^24-1 (16M-1) 16777215
LONGBLOB 2^32-1 (4G-1) 4294967295

6.2.3.1 CHARVARCHAR 類型

CHARVARCHAR 類型是很相似的,但是它們被儲存與檢索的方式有些不同。

一個 CHAR 列的長度被固定為你建立表進所宣告的長度。長度可以是 1 和 255 之間的任一值。(從 MySQL 3.23 之後,CHAR 的長度可以是 0 到 255。)當 CHAR 值被儲存時,他們被用空格在右邊填補到指定長度。當 CHAR 值被檢索時,尾部的空格被截除。

VARCHAR 列的值是變長的字串。你可以宣告一個 VARCHAR 列在 1 到 255, 就像對 CHAR 列一樣。然而,與 CHAR 相反的,VARCHAR 值只以所需的字元數儲存,另加一個字節儲存記錄的長度。值並不被填補﹔相反的,當被儲存時,尾部的空格被截除。(這個截除空格方式不同於 ANSI SQL 規約。)

如果將一個超過列最大長度的值賦給一個 CHARVARCHAR 列,該值將截斷以適合它。

下表通過在 CHAR(4)VARCHAR(4) 列中儲存不同的字串的結果顯示了兩種類型列的不同:

CHAR(4) 儲存需求 VARCHAR(4) 儲存需求
'' ' ' 4 字節 '' 1 字節
'ab' 'ab ' 4 字節 'ab' 3 字節
'abcd' 'abcd' 4 字節 'abcd' 5 字節
'abcdefgh' 'abcd' 4 字節 'abcd' 5 字節

在各種情況下,CHAR(4)VARCHAR(4) 列的檢索值均是一樣的,因為在 CHAR 列上檢索值的尾部空格會被截除。

CHARVARCHAR 列值以省略字母大小寫的方式進行排序和比較,除非在表建立時 BINARY 屬性被指定。BINARY 屬性意味著,該列值根據 MySQL 伺服器正在運行的機器的 ASCII 表順序進行字母大小寫敏感的方式排序和比較。BINARY 並不影響該列如何被儲存和檢索。

BINARY 屬性是有粘性的。這就意味著,如果一個被標記為 BINARY 的列被用於一個表達式中,整個表達式將作為一個 BINARY 值被比較。

在表建立時,MySQL 可能會隱式地改變一個 CHARVARCHAR 列的類型。查看章節 6.5.3.1 隱式的列定義變化

6.2.3.2 BLOBTEXT 類型

一個 BLOB 是一個可以保存一可變數量的資料的二進位大物件。四個 BLOB 類型(TINYBLOBBLOBMEDIUMBLOBLONGBLOB)之間的不同僅僅在於他們能保存值的最大長度不一致。查看章節 6.2.6 列類型儲存需求

四個 TEXT 類型(TINYTEXTTEXTMEDIUMTEXTLONGTEXT)對應與四個 BLOB 類型,並且有相同的最大長度和儲存需求。在 BLOBTEXT 類型之間的唯一差別就是,對 BLOB 值是以字母大小寫敏感的方式進行排序和比較的,而對 TEXT 值以忽略字母大小寫方式進行排序和比較。換句話說,TEXT 是一個忽略字母大小寫的 BLOB

如果將一個超過列類型最大長度的值賦給一個 BLOBTEXT 列,該值將被截斷以適應它。

在大多數方面,可以將一個 TEXT 列看作是一個你所希望大 VARCHAR 列。同樣的,BLOB 列可以看作是一個 VARCHAR BINARY 列。差別就在於:

MyODBCLONGVARBINARY 定義 BLOB 值,以 LONGVARCHAR 定義 TEXT 值。

因為 BLOBTEXT 值可以非常地長,在使用它們時可能會遇到某些限制:

注意,每個 BLOBTEXT 值在內部由一個獨立分配的物件表示。這與其它所有的列類型不一樣,當表被打開時,它們被按每列分配一次儲存。

6.2.3.3 ENUM 類型

ENUM 是一個字串物件,其值通常選自一個允許值清單中,該清單在表建立時的列規格說明中被明確地列舉。

在下列某些情況下,值也可以是空串("") 或 NULL

每個枚舉值均有一個索引值:

例如,指定為 ENUM("one", "two", "three") 的一個列,可以有下面所顯示的任一值。每個值的索引值也如下所示:

索引值
NULL NULL
"" 0
"one" 1
"two" 2
"three" 3

換個枚舉最大可以有 65535 個成員值。

從 MySQL 3.23.51 開始,當表被建立時,ENUM 值尾部的空格將會自動刪除。

當為一個 ENUM 列賦值時,字母的大小寫是無關緊要的。然而,以後從列中檢索出來的值的大小寫卻是相符於建立表時所指定的允許值。

如果在一個數位語境中檢索一個ENUM,列值的索引值將被傳回。例如,你可以像這樣使用數位值檢索一個 ENUM 列:

mysql> SELECT enum_col+0 FROM tbl_name;

如果將一個數位儲存到一個 ENUM 中,數位被當作為一個索引值,並且儲存的值是該索引值所對應的枚舉成員。(但是,這在 LOAD DATA 將不能工作,因為它視所有的輸入均為字串。) 在一個 ENUM 字串中儲存數位是不明智的,因為它可能會打亂思維。

ENUM 值依照列規格說明中的清單順序進行排序。(換句話說,ENUM 值依照它們的索引號排序。)舉例來說,對於 ENUM("a", "b") "a" 排在 "b" 後,但是對於 ENUM("b", "a")"b" 卻排在 "a" 之前。空字串排在非空字串前,NULL 值排在其它所有的枚舉值前。為了防止意想不到的結果,建議依照字母的順序定義 ENUM 清單。也可以通過使用 GROUP BY CONCAT(col) 來確定該以字母順序排序而不是以索引值。

如果希望得到一個 ENUM 列的所有可能值,可以使用 SHOW COLUMNS FROM table_name LIKE enum_column_name 並分析第二列的 ENUM 定義。

6.2.3.4 SET 類型

SET 是一個字串物件,它可以有 0 或更多個值,每個值均必須選自一個允許值清單中,該清單在表建立時被指定。包含多個集合成員的 SET 列值,由逗號(“,”)將各成員分隔。由此推論,SET 成員值自身不應該包含逗號。

例如,一個指定為 SET("one", "two") NOT NULL 的列可以有下列任一值:

""
"one"
"two"
"one,two"

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

從 3.23.51 開始,當表被建立時,SET 值尾部的空格將被自動地刪除。

MySQL 以數位值儲存 SET 值,以被儲存值的低階比特位(bit)對應於第一個集合成員。如果在一個數位語境中檢索一個 SET 值,檢索的值把比特位設定為對應組成列值的集合成員。例如,你可以使用下面所示的範例從一個 SET 列中檢索出一個數位:

mysql> SELECT set_col+0 FROM tbl_name;

如果將一個數位儲存到一個 SET 列中,被設定的數位的二進位表示法的比特位決定列值中的集合成員。假設一個列被定義為 SET("a","b","c","d")。那麼它的成員有下面所示的比特值:

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

如果將值 9(二進位的 1001) 賦給這個列,那麼 SET 值的第一個和第四個成員 "a""d" 被選擇,結果值為 "a,d"

對於包含超過一個 SET 成員的值,當你插入值時,無所謂以什麼順序列出成員。也無所謂給出的值被列舉了多少次。當以後檢索該值時,在值中的每個成員將出現一次,根據他們在表建立時所指定的順序列出成員。例如,如果一個列被定義為 SET("a","b","c","d"),那麼,"a,d""d,a""d,a,a,d,d" 在被檢索時均將被視為 "a,d"

如果將一個不支援的值賦於一個 SET 列,該值將被忽略。

SET 以數位順序排序。NULL 值排在非 NULL SET 值之前。

通常,可以使用 LIKE 運算子或 FIND_IN_SET() 函數執行在一個 SET 列上的 SELECT

mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;

但是,下列範例也可以工作:

mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
mysql> SELECT * FROM tbl_name WHERE set_col & 1;

第一個語句尋找一個精確的相符。第二個語句尋找包含第一個集合成員的值。

如果希望得到一個 SET 列的所有可能值,可以使用 SHOW COLUMNS FROM table_name LIKE set_column_name 並分析第二列的 SET 定義。

6.2.4 為列選擇正確的類型

為了更有效地使用儲存空間,在任何情況下均嘗試使用最精確的類型。例如,如果一個整數列被用於在 199999 之間的值,MEDIUMINT UNSIGNED 是最好的類型。

精確地表示貨幣值是一個常見的問題。在 MySQL 中,可以使用 DECIMAL 類型。它是作為一個字串儲存的,因而不會發生精度損失的情況。如果精度不是太重要的,那 DOUBLE 類型也是一個不錯的選擇。

對於高精度,總是能轉換一個儲存在 BIGINT 中的定點類型。這將允許你以整型進行任何的計算,並在必要的時候將結果轉換回浮點值。

6.2.5 使用來自其它的資料庫引擎的列類型

為了更容易地使用為其它供應商的 SQL 實現而編寫的代碼,MySQL 以下表所示的形式映射列類型。這些映射使得從其它資料庫引擎移動表到 MySQL 更容易:

其它提供商的類型 MySQL 類型
BINARY(NUM) CHAR(NUM) BINARY
CHAR VARYING(NUM) VARCHAR(NUM)
FLOAT4 FLOAT
FLOAT8 DOUBLE
INT1 TINYINT
INT2 SMALLINT
INT3 MEDIUMINT
INT4 INT
INT8 BIGINT
LONG VARBINARY MEDIUMBLOB
LONG VARCHAR MEDIUMTEXT
MIDDLEINT MEDIUMINT
VARBINARY(NUM) VARCHAR(NUM) BINARY

列類型映射在表建立時發生。如果你使用其它供應商使用的類型建立一個表,然後發出一個 DESCRIBE tbl_name 語句,MySQL 將使用相等價的 MySQL 類型報告表結構。

6.2.6 列類型儲存需求

每個由 MySQL 支援的列類型的儲存需求按類型在下面列出。

6.2.6.1 數位類型儲存需求

列類型 儲存需求
TINYINT 1 字節
SMALLINT 2 字節
MEDIUMINT 3 字節
INT 4 字節
INTEGER 4 字節
BIGINT 8 字節
FLOAT(X) 4 if X <= 24 or 8 ,if 25 <= X <= 53
FLOAT 4 字節
DOUBLE 8 字節
DOUBLE PRECISION 8 字節
REAL 8 字節
DECIMAL(M,D) M+2 字節 if D > 0,M+1 字節 if D = 0 (D+2, if M < D)
NUMERIC(M,D) M+2 字節 if D > 0, M+1 字節 if D = 0 (D+2, if M < D)

6.2.6.2 日期和時間類型儲存需求

列類型 儲存需求
DATE 3 字節
DATETIME 8 字節
TIMESTAMP 4 字節
TIME 3 字節
YEAR 1 字節

6.2.6.3 字串類型儲存需求

列類型 儲存需求
CHAR(M) M 字節, 1 <= M <= 255
VARCHAR(M) L+1 字節, where L <= M and 1 <= M <= 255
TINYBLOB, TINYTEXT L+1 字節, where L < 2^8
BLOB, TEXT L+2 字節, where L < 2^16
MEDIUMBLOB, MEDIUMTEXT L+3 字節, where L < 2^24
LONGBLOB, LONGTEXT L+4 字節, where L < 2^32
ENUM('value1','value2',...) 1 or 2 字節, 取決於枚舉值的數量(最大值為 65535)
SET('value1','value2',...) 1, 2, 3, 4 or 8 字節, 取決於集合成員數量(最大 64 個成員)

VARCHARBLOBTEXT 類型是變長的類型,其儲存需求取決於列值的實際長度(上表中以 L 表示),而不是取決於類型的最大可能尺寸。例如,一個 VARCHAR(10) 列可以保存最大長度為 10 個字元的字串。實際儲存需求為字串長度 (L),再加上 1 個字節用於記錄該字串的長度。對於字串 'abcd'L 為 4,它的儲存需求為 5 字節。

BLOBTEXT 類型需要 1、2、3 或 4 字節記錄列值的長度,這取決於該類型的最大可能長度。查看章節 6.2.3.2 BLOBTEXT 類型

如果一個表包含任何變長類型的列類型,記錄格式也將是變長的。注意,當一個表被建立時,在某種情況下,MySQL 會將一個列從一個變長類型轉換成一個定長類型,或相反的。查看章節 6.5.3.1 隱式的列定義變化

一個 ENUM 物件的大小取決於不同枚舉值的數量。一個字節被用於枚舉時,最大可支援 255 個可能值。2 個字節被用於枚舉時,最大可支援到 65535 個值。查看章節 6.2.3.3 ENUM 類型

一個 SET 物件的大小取決於不同集合成員的數量。如果集合的大小是 N,則物件占用 (N+7)/8 個字節,四舍五入為 1、2、3、4 或 8 個字節。一個 SET 可以有最多 64 個成員。查看章節 6.2.3.4 SET 類型

MyISAM 表的記錄行最大尺寸為 65534 字節。每個 BLOBTEXT 列只占用相對於這個尺寸中的 5-9 個字節。