跳至內容

MySQL/Language/Data Types

維基教科書，自由的教學讀本

不要使用utf8

[編輯]

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

NCHAR與NVARCHAR都是utf8編碼。以下3種數據類型等價： 

CHAR(10) CHARACTER SET utf8
NATIONAL CHARACTER(10)
NCHAR(10)

以下6種數據類型等價： 

VARCHAR(10) CHARACTER SET utf8
NATIONAL VARCHAR(10)
NVARCHAR(10)
NCHAR VARCHAR(10)
NATIONAL CHARACTER VARYING(10)
NATIONAL CHAR VARYING(10)

字符串

[編輯]

VARCHAR/CHAR

[編輯]

VARCHAR(n)類型用於變長字符串。最大列長上限是65,535字符。實際長度是所存儲字符串長度，再加上1或2位元組（如果設定最大長度<255則為1位元組）用於存儲字符串長度。所以可以有尾部空格字符。CHARACTER VARYING是同義詞。

CHAR(n)類型用於定長字符串，並且必須在圓括號內用一個0-255的整數值定義長度。比指定長度大的值將被截短，而比指定長度小的值將會在右側（尾部）用空格作填補。返回值時所有尾部空格被刪除。

字符型字符串，以字符為計數單位。可以指定字符集與COLLATE。 

CREATE TABLE t
(
    c1 VARCHAR(20) CHARACTER SET utf8,
    c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);

#查询所有的字符集
SHOW CHARACTER SET;
#查询所有的排序集
SHOW COLLATION;
#查询'utf8mb4'编码时可用的排序集
SHOW COLLATION WHERE CHARSET = 'utf8mb4';

#举例:名称根据中文排序(不考虑多音字)
#升序
SELECT * FROM TABLE_NAME ORDER BY CONVERT(NAME USING GBK) COLLATE GBK_CHINESE_CI ASC
#降序
SELECT * FROM TABLE_NAME ORDER BY CONVERT(NAME USING GBK) COLLATE GBK_CHINESE_CI DESC

#查看当前字符集与排序规则：
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

CHARSET與CHARACTER SET為同義詞。

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

字符串字面量

[編輯]

字符串字面量用單引號或雙引號括起來。兩個相鄰的字符串字面量自動相接為1個字符串字面量。

字符串字面量可帶字符集前綴與collate後綴： [_charset_name]'string' [COLLATE collation_name]。如 SELECT _utf8'string' COLLATE utf8_danish_ci; 使用 N'literal' (or n'literal') 創建一個使用national character set的字面量。

轉義序列區分大小寫。不符合下表的轉義序列為該字符本身。

轉義序列清單
轉義序列 表示字符
\0 X'00'字符
\' 單引號
\" 雙引號
\b backspace符
\n 換行符newline (linefeed)
\r 回車符
\t 水平制表符
\Z ASCII 26 (Control+Z)因為在Windows中它代表END-OF-FILE
\\ backslash符
\% % 因為在SQL中用作通配符
\_ _ 因為在SQL中用作通配符

單引號括起來的字符串，內部中連續兩個單引號解釋外一個作為字面量的單引號。雙引號同理。

BINARY與VARBINARY

[編輯]

二進制字符串，以字節為單位。

BINARY右填充0x00位元組。讀出時自動去除尾部的零字節。

TEXT 與 BLOB

[編輯]

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

TEXT 和 BLOB 類型在分類和比較上存在區別。BLOB 類型區分大小寫，因為它是二進制比較；而 TEXT 可不區分大小寫。

TEXT上的索引，會在尾部填充空格。這可能導致不同長度的值發生duplicate-key error。

下述3個類型都不必指定最大長度：

  • TINYBLOB/TINYTEXT：255個字節長。
  • MEDIUMBLOB/MEDIUMTEXT： 0 - 16 777 215位元組
  • LOGNGBLOB/LONGTEXT：0-4 294 967 295位元組

set 與 enum

[編輯]

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

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

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

BIT類型

[編輯]

可以是1-64個比特。默認為1. 

mysql> CREATE TABLE t (b BIT(8));
mysql> INSERT INTO t SET b = b'11111111';
mysql> INSERT INTO t SET b = b'1010';
mysql> INSERT INTO t SET b = b'0101';

mysql> SELECT b+0, BIN(b), OCT(b), HEX(b) FROM t;
+------+----------+--------+--------+
| b+0  | BIN(b)   | OCT(b) | HEX(b) |
+------+----------+--------+--------+
|  255 | 11111111 | 377    | FF     |
|   10 | 1010     | 12     | A      |
|    5 | 101      | 5      | 5      |
+------+----------+--------+--------+

mysql> SET @v1 = b'000010101' | b'000101010';
mysql> SET @v2 = _binary b'000010101' | _binary b'000101010';
mysql> SELECT HEX(@v1), HEX(@v2);
+----------+----------+
| HEX(@v1) | HEX(@v2) |
+----------+----------+
| 3F       | 003F     |
+----------+----------+

數值

[編輯]

BOOL類型

[編輯]

只是TINYINT(1)的別名.

TRUE和FALSE是1和0的別名。

0是FALSE，所有非0值都為真。

整型

[編輯]

MySQL 中支持的 5 個主要整數類型： TINYINT，SMALLINT，MEDIUMINT，INT 和 BIGINT。存儲長度分別是1、2、3、4、8位元組。關鍵字INT是INTEGER的同義詞。SERIAL等效於BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

MySQL中所有算術運算都是採用signed BIGINT或double。所以，除了位操作，不要試圖對大於63比特的 unsigned BIGINT做計算。

可選的顯示寬度指示器是對 SQL 標準進行擴展。使用一個寬度指示器不會影響字段的大小和它可以存儲的值的範圍。對於寬度M的設定，如果該列的數據位數沒有達到M位，那麼會從左使用空格補齊，如果該列數據已經超出M位，也不會影響數據的正常顯示。數據的寬度補齊默認使用空格，在列聲明時，添加ZEROFILL可以使用0進行補齊

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

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

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

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

浮點

[編輯]

FLOAT 數值類型用於表示單精度浮點數值，DOUBLE 數值類型用於表示雙精度浮點數值。 DOUBLE與REAL、DOUBLE PRECISION同義詞。

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

FLOAT(M,D) 默認為10,2。小數精度可以到24個浮點。

DOUBLE(M,D)默認為16,4，小數精度可以達到53位的DOUBLE。

decimal

[編輯]

DECIMAL數據類型的同義詞：NUMERIC, DEC, FIXED

必須定義為decimal(n,m)形式。顯示長度(n)和小數(m)。每個小數對應於一個字節。例如：decimal(4,2)意味着最大99.99 (而不是9999.99)。

n最大值為65，不含小數點與正負號，默認值為10。m最大值為30，默認值為0。可以為unsigned

日期時間

[編輯]
  • DATE類型：僅有日期數據沒有時間數據；DATE值採取 'YYYY-MM-DD' 格式。3位元組長。範圍1000-01-01/9999-12-31。
  • TIME類型：存儲時間為HH:MM:SS格式。
  • DATETIME類型：包含日期與時間數據。8位元組長。格式為 YYYY-MM-DD HH:MM:SS。範圍 1000-01-01 00:00:00/9999-12-31 23:59:59
  • TIMESTAMP類型：1973年12月30日下午3點30分將被存儲為19731230153000(YYYYMMDDHHMMSS)。僅覆蓋1970-2037。4位元組長。範圍：1970-01-01 00:00:00 UTC至2038-01-19 03:14:07 UTC。它實際上存儲為UTC，顯示的結果受session當前的timezone影響。詳見MySQL/Date Time
  • YEAR類型：以2位或4位數字格式來存儲年份。如果長度指定為2(例如YEAR(2))，年份就可以為1970至2069(70〜69)。如果長度指定為4，年份範圍是1901-2155，默認長度為4。

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」.

類型轉換函數

[編輯]
CAST(expr AS type)

convert(XX,TYPE)

參考文獻

[編輯]
取自 "https://zh.wikibooks.org/w/index.php?title=MySQL/Language/Data_Types&oldid=143758"