欄位型態
|
建立資料表時,我們必須定義每個欄位所採用的資料型態,以下將介紹 MySQL 所支援的各種欄位型態。 |
|
數字型態 |
型態 | 空間
需求 | 範圍 | TINYINT[(M)] | 1 byte | Signed: -128 to 127 (-27 to 27-1)
Unsigned: 0 to 255 (0 to 28-1) | SMALLINT[(M)] | 2 bytes | Signed: -32768 to 32767 (-215 to 215-1)
Unsigned: 0 to 65535 (0 to 216-1) | MEDIUNINT[(M)] | 3 bytes | Signed: -8388608 to 8388607 (-223 to 223-1)
Unsigned: 0 to 16777215 (0 to 224-1) | INT[(M)]
INTEGER[(M)] | 4 bytes | Signed: -2147483648 to 2147483647 (-231 to 231-1)
Unsigned: 0 to 4294967295 (0 to 232-1) | BIGINT[(M)] | 8 bytes | Signed: -9223372036854775808 to 9223372036854775807 (-263 to 263-1)
Unsigned: 0 to 18446744073709551615 (0 to 264-1) | FLOAT(precision) | 4 or 8 | 若 precision <= 24 的話,視為 FLOAT(單精數)
若 25 <= precision <= 53 的話,則視為 DOUBLE(倍精數) | FLOAT[(M,D)] | 4 bytes | ±1.175494351E-38 ±3.402823466E+38 | DOUBLE[(M,D)]
REAL[(M,D)] | 8 bytes | ±1.7976931348623157E+308 ±-2.2250738585072014E-308 | DECIMAL[(M[,D])]
DEC[(M[,D])]
NUMERIC[(M[,D])] | M+2 | 依 M 與 D 值而定 |
|
上表中的 M 代表「最大顯示寬度」,其值不得大於 255。無論您將欄位的型態設定為「INT(4)」或「INT(5)」,都不影響它儲存數值的能力;但在顯示時,就可以發現其差異了。我們將兩個欄位分別設為「INT(4)」與「INT(5) ZEROFILL」(ZEROFILL 會在前頭的空位補 0),然後兩者均存入數字 4,在顯示其資料時,前者是「4」,後者則是「00004」。 |
上表中的 D 代表「小數位數」,其值不得大於 30,也不能大於 M-2。 |
如果您將上述欄位型態設定為 UNSIGNED 的話,對整數型態(TINYINT, SMALLINT, MEDIUNINT, INT, and BIGINT)而言,可以儲存較大的正整數;對其餘的浮點數型態而言,則可以避免被存入負的數值。 |
若您存入的數值超過該欄位的範圍時,MySQL 只會取其所能處理的最大值。例如,您在 TINYINT 型態的欄位中存入「300」這個值,結果將只剩下「127」。 |
日期與時間型態 |
型態 | 空間需求 | 範圍 | DATE | 3 bytes | '1000-01-01' to '9999-12-31' | TIME | 3 bytes | '-838:59:59' to '838:59:59' | DATETIME | 8 bytes | '1000-01-01 00:00:00' to '9999-12-31 23:59:59' | TIMESTAMP[(M)] | 4 bytes | 自 1970 年起,至 2037 年的某時 | YEAR[(2 | 4)] | 1 bytes | 4-digit format: 1901 to 2155
2-digit format: 1970 to 2069 |
|
在使用 TIMESTAMP 型態時,您可以指定「最大顯示寬度」,就是 M。不同的 M 值與儲存所需空間無關,而是與顯示的格式有關。請見下表: |
型態 | 顯示格式 | TIMESTAMP(14) | YYYYMMDDHHMMSS | TIMESTAMP(12) | YYMMDDHHMMSS | TIMESTAMP(10) | YYMMDDHHMM | TIMESTAMP(8) | YYYYMMDD | TIMESTAMP(6) | YYMMDD | TIMESTAMP(4) | YYMM | TIMESTAMP(2) | YY |
|
字串型態 |
型態 | 空間需求 | 最大長度 | CHAR(M) | M bytes | M bytes | VARCHAR(M) | L+1 bytes | M bytes | TINYBLOB, TINYTEXT | L+1 bytes | 28-1 bytes | BLOB, TEXT | L+2 bytes | 216-1 bytes | MEDIUMBLOB, MEDIUMTEXT | L+3 bytes | 224-1 bytes | LONGBLOB, LONGTEXT | L+4 bytes | 232-1 bytes | ENUM('value1','value2',...) | 1 or 2 bytes | 65535 個成員 | SET('value1','value2',...) | 1, 2, 3, 4, or 8 bytes | 64 個成員 |
|
上表中的 L 代表「實際儲存的空間大小」,上述多種型態的空間需求都與實際存入的空間大小有關,意即,它們的空間需求是變動的。 |
CHAR 與 VARCHAR |
使用 CHAR 及 VARCHAR 型態時,必須宣告「最大儲存長度」,就是上表中的 M。這兩種型態是相似的,所能儲存的最大長度都是 255 bytes。其相異之處在於 CHAR 是個固定長度的型態,而 VARCHAR 是個長度可變的型態。我們從下表來看兩者在資料儲存上的表現: |
字串內容 | CHAR(4) | 空間需求 | VARCHAR(4) | 空間需求 | '' | ' ' | 4 bytes | '' | 1 byte | 'ab' | 'ab ' | 4 bytes | 'ab' | 3 bytes | 'abcd' | 'abcd' | 4 bytes | 'abcd' | 5 bytes | 'abcdefgh' | 'abcd' | 4 bytes | 'abcd' | 5 bytes |
|
由於使用 CHAR 型態儲存資料時,MySQL 會以空白字元填補至最大儲存長度,所以無論存入的內容為何,所需的儲存空間都是固定的。當資料內容少於最大儲存長度時,VARCHAR 將可以有效地節省空間,這是它最大的優點;檢索時擁有較佳的效率,則是 CHAR 的長處。 |
VARCHAR 和各類 TEXT 與 BLOB 都是長度可變的型態。當一資料表中同時選用 CHAR 和這類長度可變的型態時,CHAR 會被自動改為 VARCHAR,除非它的最大儲存長度少於 4。 |
另一方面,若資料表中 VARCHAR 欄位的最大儲存長度少於 4 時,其型態也會被自動改為 CHAR。因為少於 4 bytes 的欄位在節省空間方面,實在沒多大的效果,不如改為 CHAR 以加快檢索的速度。 |
TEXT 與 BLOB |
BLOB 的全名是「binary large object」,與 TEXT 一樣,都是用來儲存長度較長的字串或是二元資料。兩者大同小異,唯一的差別在於,TEXT 是有區分大小寫的,而 BLOB 不分。 |
ENUM 與 SET |
ENUM 與 SET 是特別的字串型態,有人稱之為「列舉(enumeration)」型態。這兩種欄位的值只能從固定的項目中挑選,不能隨心所欲的存入資料。舉個例子來看,我們想調查使用者的性別與年齡分布狀況,所以設定了以下兩種欄位: |
sex ENUM("M", "F") |
age ENUM("0-20", "21-30", "31-40", "41-50", "51-100") |
若使用者填答時輸入「男性,32 歲」,我們則要用程式處理它,分別以 sex 的第 1 個選項與 age 的第 3 個選項存入。像 age 這個欄位,它已經將原始的輸入值以分組的形式來儲存,日後我們將無法得知使用者當時所輸入的精確數值。 |
ENUM 型態最多可以建立 65535 個不同的成員,而 SET 型態少得多,只能建立 64 個不同的成員。除了「容量」有別之外,兩者還有一項相異之處:SET 型態可以從中挑選一組以上的值,而 ENUM 只能選其一。換句話說,ENUM 型態是單選,而 SET 型態則是複選。 |
subject SET("Chinese", "English", "Math", "Music", "Art", "Sport") |
這是一個 SET 型態的例子,使用者在從上述 subject 欄位挑選所修習的科目時,就可以同時挑選多個項目。 |