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)