MySQL/Language/Data Types

維基教科書,自由的教學讀本
跳至導覽 跳至搜尋

不要使用utf8[編輯]

因為 MySQL 的utf8只支持 BMP(基本多文種平面)範圍內的 Unicode 字符。也就是,MySQL 的utf8使用三字節表達。使用utf8mb4的字符集,也就是用四字節表達的 UTF-8 編碼。

VARCHAR/CHAR[編輯]

VARCHAR是CHARACTER VARYING的縮寫. 最大列長上限是65,535字符。實際長度是所存儲字符串長度,再加上1或2字節(如果設定最大長度<255則為1字節)用於存儲字符串長度。

CHAR(n)類似於varchar(n),但佔據固定存儲長度。而varchar需要空間來存儲實際的文本。

CHAR 類型用於定長字符串,並且必須在圓括號內用一個大小修飾符來定義。這個大小修飾符的範圍從 0-255。比指定長度大的值將被截短,而比指定長度小的值將會用空格作填補。

CHAR 類型與VARCHAR類型,可以使用 BINARY 修飾符。當用於比較運算時,這個修飾符使 CHAR 以二進制方式參於運算,而不是以傳統的區分大小寫的方式。  

TEXT 與 BLOB[編輯]

BLOB 或 TEXT 列的最大長度是65,535字符。真實長度是存儲數據加上2字節(或1字節當長度<255)。比指定類型支持的最大範圍大的值將被自動截短。BLOB / TEXT數據並不存儲在表的數據文件中。這使得各種操作(INSERT / UPDATE / DELETE / SELECT)涉及BLOB / TEXT數據更慢,但其他操作更快。

TEXT 和 BLOB 類型在分類和比較上存在區別。BLOB 類型區分大小寫,而 TEXT 不區分大小寫。

其他類型:

  • TINYBLOB/TINYTEXT: 0-255字節
  • MEDIUMBLOB/MEDIUMTEXT: 0 - 16 777 215字節
  • LOGNGBLOB/LONGTEXT:0-4 294 967 295字節

BOOL類型[編輯]

只是TINYINT(1)的別名

整型[編輯]

MySQL 中支持的 5 個主要整數類型: TINYINT,SMALLINT,MEDIUMINT,INT 和 BIGINT。存儲長度分別是1、2、3、4、8字節。

可選的顯示寬度指示器是對 SQL 標準進行擴展。例如,字段的類型為 INT(6),則包含數字少於 6個數字的值從數據庫中檢索出來時能夠自動地用空格填充。需要注意的是,使用一個寬度指示器不會影響字段的大小和它可以存儲的值的範圍。

字段存儲一個超出許可範圍的數字,MySQL 會根據允許範圍最接近它的一端截短後再進行存儲。

MySQL 會在不合規定的值插入表前自動修改為 0。

UNSIGNED 修飾符規定字段只保存正值。

ZEROFILL 修飾符規定 0(不是空格)可以用來補輸出的值。使用這個修飾符可以阻止 MySQL 數據庫存儲負值。

浮點[編輯]

FLOAT 數值類型用於表示單精度浮點數值,DOUBLE 數值類型用於表示雙精度浮點數值。

類型也帶有附加參數:一個顯示寬度指示器和一個小數點指示器。如語句 FLOAT(7,3) 規定顯示的值不會超過 7 位數字,小數點後面帶有 3 位數字。小數點後面的位數超過允許範圍的值,MySQL 會自動將它四捨五入為最接近它的值,再插入它。

decimal[編輯]

形式為decimal(n,m)

例如:decimal(4,2)意味着最大99.99 (而不是9999.99如你期望那樣)。

Dates[編輯]

在三種日期類型中,DATETIME, DATE, TIMESTAMP。

  • DATE類型僅有日期數據沒有時間數據;DATE值採取 'YYYY-MM-DD' 格式。3字節長。範圍1000-01-01/9999-12-31。
  • DATETIME包含日期與時間數據。8字節長。格式為 YYYY-MM-DD HH:MM:SS。範圍 1000-01-01 00:00:00/9999-12-31 23:59:59
  • TIMESTAMP也包含日期與時間數據,但僅覆蓋1970-2037。4字節長。即1970-01-01 00:00:00 UTC至2038-01-19 03:14:07 UTC 它實際上存儲為UTC,顯示的結果受session當前的timezone影響。詳見MySQL/Date Time

TIME類型僅表示時間。格式'HH:MM:SS',或者時間間隔。例如: -02:00:00表示"過去2個小時"). 表示範圍為: '-838:59:59' => '838:59:59'. 3字節長。MySQL不會自動把時間補上今天的日期。

YEAR類型表示年份。1字節長。範圍1901/2155

DATETIME或TIMESTAMP的值可以包含秒的小數部分,最高精度為微妙(6位小數)。格式為『YYYY-MM-DD HH:MM:SS[.fraction]』。對DATETIME值域為『1000-01-01 00:00:00.000000』至『9999-12-31 23:59:59.999999』;對於 TIMESTAMP值域為『1970-01-01 00:00:01.000000』至『2038-01-19 03:14:07.999999』. 詳見MySQL Help Section 11.3.6, 「Fractional Seconds in Time Values」.


set 與 enum[編輯]

MySQL 還支持兩種複合數據類型 ENUM 和 SET,它們擴展了 SQL 規範。雖然這些類型在技術上是字符串類型,但是可以被視為不同的數據類型。

ENUM 類型因為只允許在集合中取得一個值,有點類似於單選項。表示相互排拆的數據,如人類的性別。ENUM 類型可使用 null 值。字段中插入非預定義的值都會使 MySQL 插入一個空字符串。如果插入值的大小寫與集合中的值的大小寫不匹配,MySQL 會自動使用插入值的大小寫轉換成與集合中大小寫一致的值。因為ENUM 類型在系統內部可以存儲為數字,並且從 1 開始用數字做索引。一個 ENUM 類型最多可以包含 65536 個元素,其中一個元素被 MySQL 保留,用來存儲錯誤信息,這個錯誤值用索引 0 或者一個空字符串表示。

SET 類型從預定義的集合中取得任意數量的多個值。並且與 ENUM 類型相同的是任何試圖在 SET 類型字段中插入非預定義的值都會使 MySQL 插入一個空字符串。如果插入一個即有合法的元素又有非法的元素的記錄,MySQL 將會保留合法的元素,除去非法的元素。一個 SET 類型最多可以包含 64 項元素。在 SET 元素中值被存儲為一個分離的「位」序列,這些「位」表示與它相對應的元素。所以 SET 類型中不可能包含兩個相同的元素。希望從 SET 類型字段中找出非法的記錄只需查找包含空字符串或二進制值為 0 的行。


例如:

SET("madam", "mister")    -- 合法的:空集, "madam", "mister", "madam, mister", "mister, madam"

ENUM("madam", "mister")   -- 合法的:空集, "madam" or  "mister"

CREATE TABLE `20121101_t` (    
  `id` int(11) NOT NULL AUTO_INCREMENT,    
  `name` varchar(20) NOT NULL,    
  `cl` set('x','w','r') NOT NULL,    
  `c2` enum('f','d') NOT NULL,    
  PRIMARY KEY (`id`)    
) ENGINE=InnoDB     
   
insert into 20121101_t    
values(null,'a.txt','r,w','d');      
insert into 20121101_t    
values(null,'b.txt','r,w','f');

類型轉換函數[編輯]

CAST(expr AS type)
convert(XX,TYPE)

參考文獻[編輯]