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)