《MySQL技术》学习笔记——数据类型

数据值类别

MySQL支持多种常规类别的数据值。
其中包括数值、字符串值、日期/时间这样的时态值、空间值，以及NULL值。

数值

MySQL能识别的数字包括整数，定点数，浮点数，位域值。

精确值数和近似值数

支持精确值数的精确运算，近似值数的近似运算。

带小数部分的精确值由３个部分组成：一个数字序列，一个小数点，另一个数字序列。
小数点前后的数字序列可有一个为空，但不能同时为空。

近似值是采用科学计数法表示的浮点数，带有一个底数和指数。
具体方法为：在整数或浮点数的后面紧跟着字母e或E，接着是一个+或-，然后是一个表示指数的整数。

用精确值计算得到的结果也是精确的，只要没超出那些数值的精度范围，就不会丢失精度。

近似值的计算是近似的，且会有舍入误差。

• 只要表达式有近似值，则被当做近似表达式计算。
• 如果表达式只包含整数精度值，则它会以BIGINT[64位精度]来进行计算。
• 如表达式只包含精确值，但其中有的值带有小数部分，则它会以具有65位精度的DECIMAL算法进行计算。
• 表达式中，如存在字符串必须转换成一个数才能进行计算的情况，则该字符串会被转换成一个双精度浮点值。

位域值

位域值可写成b’val’或0bval，其中，val有一个或多尔二进制数字（０或１）构成。
例如， b’1001’和0b1001即为十进制９ 。

在结果集中，BIT值会被显示为一个二进制串。

让它加上0或用CAST()函数，可将其转换为一个整数：
例：

SELECT b'1001' + 0, CAST(b'1001' AS UNSIGNED);

字符串值

字符串两端的引号既可以是单引号，也可以是双引号。

尽量使用单引号，具体原因有两个：

• SQL语言标准规定使用单引号，因此使用单引号字符串的语句，能够更好地移植到其他数据库引擎。
• 启用SQL模式 ANSI_QUOTES下，MySQL会将双引号处理成将标识符引起来的符号，而不会把它只当成将字符串引起来的符号。

字符串转义序列

转义序列	含义
\0	NUL（零值字节）
’	’ 单引号
"	" 双引号
\b	退格符
\n	换行符
\r	回车符
\t	制表符
\	反斜线
\Z	Ctrl+Z

字符串中嵌入引号，除了转义字符方法。

• 如果嵌入的引号与字符串的引号相同，通过重复两次，标识一个符号
• 如果嵌入的引号和字符串的引号不同，直接嵌入
• 对嵌入的引号使用反斜线进行转义；此办法不受字符串引号的限制

X’xxxxxx’ 每个x是一个16 进制下的值。
X’xxxxxx’在字符串上下文中，每两个16进制数字会被解释为一个8位数字字节值。故X’xxxxxx’结果是一个字符串。
X’xxxxxx’在数字上下文中，会将其解释为一个16进制表示的数值。
X为小写也可；该种写法下要求xxxxxx中x个数需为偶数个。

0xyyyyy，前缀必须为0x不可大写，与前述类似，默认／字符串上下文下被解释为字符串，数值上下文下，被解释为一个16进制表示的数值。

字符串类型与字符集支持

字符串一般分为两类，即二进制串和非二进制串。

• 二进制串是一组字节序列。
  二进制串没有特殊的比较或排序属性。比较基于各字节的数值逐个字节实现的。
• 非二进制串是一个字符序列。
  每个非二进制串都与字符集相关，字符集决定了：哪些字符可用；MySQL如何解释字符串内容。
  每个字符集都有一种或多种排序规则。
  字符串所用的排序规则决定了字符在字符集里的先后顺序。
  非二进制串的尾部空格不参与比较。
  对laltin1这样的单字节字符集，每个字符占用一个字节空间。
  对多字节字符集，有的字符需占多个字节空间。

给定字符集可有多个排序规则可用，每个排序规则只会对应一个字符集。

二进制串和非二进制串有着不同的排序特性。

• 二进制串是逐字节进行比较的，结果只取决于每个字节的数值大小。
• 非二进制串是按字符进行比较的，每个字符相对值依赖于字符集的排序规则。

函数CHARSET() COLLATION()用于确定xxx对象的字符集和排序规则。

有两种记法约定可用于将某个字符串常量强制解释为某种指定的字符集。
第一种记法如下所示：

_charset str

例

_latin2 'abc'
_utf8 'def'
_utf8 X'646566'

第二种记法如下所示：

N'str'

等价于

_utf8'def'

可用CONVERT()将任意字符串转换为另一种指字符集形式的字符串：

CONVERT(str USING charset)

引导符和 CONVERT() 函数是不一样的。
引导符只会改变对字符串的解释，并不会改变字符串的值。

CONVERT依据原字符集下每个字符转换到新字符集下对应字符，产生的结果作为返回值返回。

字符串长度：
１．用CHAR_LENGTH(xxx)，返回构成xxx的字符个数。
２．用LENGTH(xxx)，返回xxx的字节单位的长度。

二进制串没有字符集的概念，会被解释为字节。
用了二进制排序规则的非二进制串，比较基于的是每个字符实际存储的多个字节计算出来的值。

对二进制串执行UPPER/LOWER等没有效果。

mysql> SET @s1 = BINARY 'abcd';
mysql> SET @s2 = _latin1 'abcd' COLLATE latin1_bin;

UPPER(CONVERT(@s1 USING latin1))

字符集相关变量

有些字符集变量表示的是服务器或当前数据库的属性。

• character_set_system表示的是用于存储标识符的字符集
• character_set_server和collation_server表示服务器的默认字符集和排序规则
• character_set_database和collation_database表示默认数据库的字符集和排序规则
• character_set_client表示客户端向服务器发送SQL语句时用的字符集
• character_set_results表示服务器向客户端返回结果时使用的字符集．结果包括数据值，和诸如列名之类的元数据
• character_set_connection是服务器使用的变量。
  当服务器接收到来自客户端的语句字符串时，会将该字符串从character_set_client转换为character_set_connection，并使用后者的字符集来处理该语句。
  collation_connection适用于语句字符串里的两个文字串值之间的比较。
• character_set_filesystem表示的是文件系统字符集。
  用于解释在SQL语句里代表文件名的字符串。
  文件被打开前，其文件名字符串会从character_set_client转换为character_set_filesystem。

对mysql/mysqladmin，将环境变量LANG或LC_ALL设为指定某一语言区域，客户端会检测这些变量，相应调整。

对于那些成对出现的变量（一个字符集变量和一个排序规则变量），它们之间的影响关系如下。

• 设置字符集变量时，关联的排序规则变量自动设为此字符集默认排序规则
• 设置排序规则变量时，关联的新字符集变量自动设为与排序规则对应的字符集

时态（日期/时间）值

时态值包括日期值或时间值。

日期：‘2020-02-02’
时间：‘12:30:33’
日期和时间：‘2020-02-02 12:30:11’

对于组合后的日期时间值，运行在日期和时间之间加一个字符“T”，但不能用空格，如 ‘2022-01-18T12:00:00’

时间值或日期时间组合值的语法还支持在时间后面紧跟一个小数形式的秒，其中包含一个小数点和多达6位数字（微秒）的精度。

空间值

MySQL支持空间值，不过仅限于 InnoDB、MyISAM、NDB 和 ARCHIVE 这几种引擎。

布尔值

在表达式里：
０为假；
任何非０、非NULL为真。

布尔常量：
TRUE被视为１；
FALSE被视为０。

NULL值

NULL 是一种“没有类型的”值。

它通常用来表示 “无值”、“未知值”、“缺失值”、“超界”、“不适用”和 “不在其中”等。

在书写关键字 NULL 时，不需要加引号，也不用区分大小写。
MySQL还会把单独的 \N （区分大小写）当作 NULL：

mysql> SELECT \N, ISNULL(\N);

MySQL数据类型

每一个数据库里的表都由一个或者多个列构成。

每种数据类型都有以下几个特点：

• 它可以表示何种类型的值。
• 这种类型的值要占用多少存储空间。
• 值的长度是固定的（即该类型的每个值都将占用数量的存储空间），还是可变的（不同的值会占用不同数量的存储空间）。
• MySQL会如何比较这种类型的值，如何对它进行排序。
• 是否可以对这种类型进行索引，如何索引。

数据类型概述

MySQL的数字类型包括整数、定点数、浮点数和位值。

数字数据类型

类型名称	含义
TINYINT
SMALLINT
MEDIUMINT
INT
BIGINT
DECIMAL
FLOAT
DOUBLE
BIT

字符串数据类型

类型名称	含义
CHAR	固定长度非二进制字符串
VARCHAR	可变长度的非二进制串
BINARY	固定长度的二进制串
VARBINARY	可变长度的二进制串
TINYBLOB	非常小型的BLOB［二进制大对象］
BLOB
MEDIUMBLOB
LONGBLOB
TINYTEXT	非常小型的非二进制串
TEXT	小型非二进制串
MEDIUMTEXT	中等大小的非二进制串
LONGTEXT	大型非二进制串
ENUM	枚举集合．每个列的取值将是这个枚举集合中的某一个元素
SET	集合．每个列的取值可为零个或多个集合元素．

时态数据类型

类型名称	含义
DATE	日期值，格式为’YYYY-MM-DD’
TIME	时间值，格式为’hh:mm:ss’
DATETIME	日期加时间值，格式为’YYYY-MM-DD hh:mm:ss’
TIMESTAMP	时间戳值，格式为’YYYY-MM-DD hh:mm:ss’
YEAR	年份值，格式为YYYY或YY

表定义里的特殊列类型

• 所允许的类型特有属性取决于具体的数据类型。
  例如，只有数字类型才有UNSIGNED和ZEROFILL属性；
  只有非二进制串类型才有CHARACTER SET和COLLATE属性.。
• 通用属性可用于任意数据类型，处少数类型。
  例如，NULL/NOT NULL、DEFAULT。

一般将数据类型特有属性（如 UNSIGNED 或 ZEROFILL）放在通用属性（如 NULL 或 NOT NULL）的前面。

指定列的默认值

默认值需为常量。

在往表里插入新的行时，如果没有为列指定具体值，这会影响MySQL对列的处理。

• 如果没有启用SQL的严格模式，那么该列将被设置成其数据类型的隐含默认值。
• 在启用SQL的严格模式之后，如果表是事务性的，就会出现错误。

列的隐含默认值取决于它的数据类型。

• 对于数字列（不包括那些具有 AUTO_INCREMENT 属性的列），其默认值为0。对于 AUTO_INCREMENT列，其默认值是下一个列序号。
• 对于大多数时态类型列，其默认值为该类型的“零”值。对于 TIMESTAMP 列，其自动初始化规则比较特殊。
• 对于字符串类型（不包括 ENUM 类型）列，其默认值为空串。对于 ENUM 列，其默认值为枚举集里的第一个元素。对于 SET 列，如果不允许包含 NULL 值，那么默认值将是一个空集，不过它等价于空串。

数字数据类型

MySQL的数字数据类型分为３大类。

• 精确值类型
  包括整数类型和DECIMAL。
  此类型值被精确保存。
• 浮点类型
  细分为单精度，双精度。
  和DECIMAL一样，可用来存放带小数部分的数字，但存储的是可能发生四舍五入的近似值。
• BIT类型
  用于存储位域值。

精确值数字数据类型

NUMERIC和FIXED都是DECIMAL的同义词。

对各类整形，M表示显示位数；
对DECIMAL，FLOAT，DOUBLE修饰中M表示有效值位数，D表示小数位位数。

对DECIMAL类型，若限定为UNSIGNED，仅仅是把负数部分砍掉。
对FLOAT和DOUBLE类型，若限定为UNSIGNED，也仅仅是把负数部分砍掉。

BIT 数据类型

默认下，从BIT列检索出来的值不能打印显示．要显示一个位域值的可打印形式，需将其+0或用CAST．

mysql> CREATE TABLE t(b BIT(3))
mysql> INSERT INTO t (b) VALUES(0), (b'11'), (b'101'), (b'111')
mysql> SELECT b+0, CAST(b AS UNSIGNED) FROM t
mysql> SELECT BIN(b), BIN(b & b'101'), BIN(b | b'101') FROM t;

数字数据类型的属性

ZEROFILL属性会在列里的显示值前面填充若干０，使其宽度最终达到显示宽度.
如果给某个列指定了ZEROFILL属性，则它将自动转换成一个UNSIGNED列。
指定了AUTO_INCREMENT下，若列的值未指定或指定为NULL，MySQL自动生成一个序列值。
每个表最多只能有一个AUTO_INCREMENT列，该类型的列应具备NOT NULL约束；且必须被索引，AUTO_INCREMENT修饰下默认为NOT NULL，

在MYSQL严格模式下，插入到列的值超出列的值类型范围，产生错误，
在非严格模式下，将值截断后插入，生成警告，

字符串数据类型

其中M表示长度，对二进制串，表示字节单位长度；对非二进制串，表字符个数。
每种非二进制串类型及ENUM和SET，可指定字符集和排序规则。
BINARY/CHAR，采用0x00/空格进行多余空间填充。

CHAR (M)实际占据空间　＝　M * 字符集下单个字符最大可占空间。
变长字符串存储时，既存储值内容有存储实际长度。

对VARCHAR (M)：
１．一个长VARCHAR列需要２个字节来存放字符串的长度，最终长度不能超过行的总长；
２．用多字节字符可减少字符个数。

对BLOB和TEXT：
１．分别表示二进制／非二进制存储大对象的两个系列．两者均以字节为单位衡量最大长度。
２．在存储引擎Innodb和MyISAM都支持对BLOB和TEXT列进行索引。
但需指定一个前缀长度。

对ENUM和SET：

size ENUM('small', 'medium', 'large')

一个ENUM类型的值只能取定义ENUM时给定常量集合中的某个。

size SET('small', 'medium', 'large')

一个SET类型的值可以是定义时值集合的任何一个子集。

SET列的定义，可写成逗号分隔的单个字符串列表。
SET列的值必须是一个字符串．如此值由集合里多个成员构成，则需用逗号把各个成员分隔开。

为ENUM或SET列定义合法取值列表时，需考虑：

• 此列表确定了列的所有允许值。
• 如果ENUM或SET列有一个不区分大小写的排序规则，则插入合法值时也不区分大小写。
  但当在检索ENUM或SET列里的数据时，它们将按列定义的合法取值列表里的字母大小写形式来显示。
  如ENUM或SET列有一个区分大小写的排序规则或二进制排序规则，则在插入数据时，需区分大小写，
• ENUM定义里的值的顺序就是排序所用的顺序．SET定义里的值的排序也确定了排序顺序，只是关系更复杂些。
• 当MySQL显示某个由多个集合成员构成的SET值时，这些成员的列出顺序由它们在SET列定义里的顺序确定。

创建ENUM和SET类型列时，需以字符串形式列出枚举和集合成员，因此它们都被归类为字符串类型。
但，它们的成员在内部的存储形式是数字，且也可把它们当数字来对等。
意味着，ENUM和SET值可用在字符串环境里，也可用在数字环境里。
如果在字符串环境里使用ENUM和SET列，但希望它们表现的像数字（或相反），则可能会引起混乱。

MySQL将从１开始依次对ENUM列定义里的成员进行顺序编号。（编号０被保留为出错代码，其字符串形式即为一个空串）

ENUM列占用的存储空间大小由枚举值的个数确定。

MySQL需为出错代码预留一个位置，并将它作为每一个枚举的隐含成员，所以在ENUM定义里最多能指定的是65535个成员。

当把某个非法值放入ENUM列时，MySQL会赋值为那个出错成员（严格模式下，产生错误）

mysql> CREATE TABLE table_e(e ENUM('jane', 'fred', 'will', 'marcia'));
mysql> INSERT INTO table_e 
    -> VALUES('jane'), ('fred'), (NULL);
mysql> SELECT e, e+0 FROM table_e;

可以按名字或编号来对 ENUM 成员进行比较：

SELECT e FROM table_e WHERE e = 'will';

SELECT e FROM table_e WHERE e = 3;

也可把空串定义为有效的枚举成员，不过此时需注意。
这个字符串被赋值为一个非零值，与该定义里列出的其他成员没什么两样。
但是，空串也被用于那个数值为０的错误成员．故，它会对应到两个内部数字元素值。

非严格模式下，插入非法值，实际按插入出错成员。
严格模式下，出错，并且不会存储任何值。

SET成员没有按顺序编号。
每个SET成员对应着SET值里的一个二进制位。
第一个成员对应于第０位，第二个对应于第１位，以此类推
SET成员的数字值都是２个幂．空串对应的SET数字值为０。
SET的值都被存储为二进制位值．每个字节对应８个SET成员。
故一个SET列占用的存储空间取决于它的成员个数．最多只能有64个成员。

既然一个SET可由一组二进制位表示，则一个SET值就可由多个集合成员组成。
这样的值可展开为某种二进制位的组合，故它也可以由SET定义里与这些二进制位相对应的那些字符串组合而成。

mysql> CREATE TABLE table_s (s SET('table', 'chair', 'lamp', 'stool'));
mysql> INSERT INTO table_s
    -> VALUES('table'), ('lamp'), (' '), (NULL);
mysql> SELECT s, s+0, BIN(s+0) FROM table_s;

在对SET列进行赋值时，其中各个子字符串的顺序不用与定义这个列时所用的顺序完全一样。
不过，在以后的检索操作中，这个成员是按它们在声明里的先后顺序显示的。

在给SET列赋值时，如这个值包含的某些子字符串不属于集合成员，则这些子字符串会被剔除调，剩余的子字符串会被赋给这个列。
以后检索这个值时，也看不到那些非法的子字符串。

如果把’chair, couch, table’赋值给table_s的列s列，那么会发生两件事。

• ‘couch’ 会被剔除掉，因为它不是集合成员。
• 检索时显示为’table’, ‘chair’。

严格模式下，使用非法SET成员将导致错误，且这个值也不会被存储。

MySQL会重排SET列值里的成员，如果要搜索那些使用字符串的值，则需按正确的顺序列出各个成员。
这意味着如要搜索那些使用字符串的值，则需按正确的顺序列出各个成员。
如，先插入’chair, table’，然后搜索’chair, table’，则无结果；
需使用’table, chair’来查找才行。

ENUM和SET列的排序和索引操作都是按列值的内部值［数字值］执行的．

SELECT e FROM table_e ORDER BY e;

如果想让某个ENUM列按常规的字母表顺序排序，则可先用CAST()函数把这个列的值转换为一个非ENUM的字符串，然后再对结果排序。

SELECT CAST(e AS CHAR) AS e_str FROM table_e ORDER BY e_str;

字符串数据类型属性

字符串数据类型特有的属性有CHARACTER SET和COLLATE，
分别用于指定字符集和排序规则。

可把它们指定为表自身的默认选项，也可指定为各个列的单独选项，从而改写表的默认选项。

CHARACTER SET和COLLATION属性适用的数据类型有CHAR, VARCHAR, TEXT, ENUM和SET。
它们并不适用于二进制串数据类型（BINARY, VARBINARY, BLOB），因为这些类型包含的是字节串，而非字符串。

在指定CHARACTER SET和COLLATION属性时，不管是在列，表和数据库三级别中的哪一级，都要遵从以下规则：

• 使用的字符集必须是服务器支持的。
• 如果定义里同时使用了CHARACTER SET和COLLATION属性，则它们所代表的字符集和排序规则要兼容。
• 如果定义里只有CHARACTER SET，而没有COLLATION，则使用默认排序规则。
• 如果定义里只有COLLATION，而没有CHARACTER SET，则具体的字符集就要由排序规则名的第一部分来确定。

CREATE TABLE mytbl
(
	c1 CHAR(10),
	c2 CHAR(40) CHARACTER SET latin2,
	c3 CHAR(10) COLLATE latin1_german1_ci,
	c4 BINARY(40)
) CHARACTER SET utf8;

最终的表将使用utf8作为其默认字符集。
默认的表排序规则为utf8_genaral_ci。
c1列使用表的默认字符集和排序规则；c2, c3有自己的字符集和排序规则，c4是二进制字符串类型，字符集属性对其不适用。

SHOW CREATE TABLE mytbl;

SHOW FULL COLUMNS FROM mytbl;

在MySQL处理字符列的定义时，会依次根据以下原则为它确定使用何种字符集。

• 如果列的定义里指定了字符集，则使用．仅列出COLLATE属性下，字符集属性也会被确定。
• 如果表的定义里有字符集选项，则使用这个字符集。
• 把数据库的字符集作为表的默认字符集，该字符集还将称为列的默认字符集。

数据库的字符集没显示指定下，采用服务器的。

字符集名binary比较特殊。
当将某个非二进制串列指定为binary字符集时，相当于把该列定义为相应的二进制串类型。
以下每一对定义均等价：

c1 CHAR(10) CHARACTER SET binary
c1 BINARY(10)

c2 VARCHAR(10) CHARACTER SET binary
c2 VARBINARY(10)

c3 TEXT CHARACTER SET binary
c3 BLOB

为某个二进制列指定CHARACTER SET binary，那么它将被忽略，因为该类型已经是二进制类型了。

如果把binary字符集赋值为表选项，则它将应用到所有在其定义里没有指定任何字符集信息的字符串列。

简写形式：

• ASCII代表CHARACTER SET latin1。
• UNICODE代表CHARACTER SET ucs2。
• 对非二进制串列，ENUM列，SET列。
  如果指定BINARY属性，则等同于在指定该列字符集的二进制排序规则。

如果为某个二进制串列指定了BINARY，它将被忽略。

任何字符串类型都能使用通用属性NULL或NOT NULL。
如果没指定，则默认为NULL。
把一个字符串列声明为NOT NULL，并不意味着它不能存储空字符串（即 ’ '）。

在MySQL里，空值与没有值是有区别的。

可使用DEFAULT子句，为除BLOB和TEXT类型外的其他字符串数据类型指定默认值。

选择字符串数据类型

• 值要表示为字符数据，还是二进制数据
• 比较需区分大小写吗？
• 占用存储空间
• 列的取值是无限还是有限
• 尾部填充

数据类型	存储	检索	结果
CHAR	填充空格	去掉	检索值无尾部填充
BINARY	填充0x00	不处理	检索值保留尾部填充
VARCHAR,VARBINARY	不处理	不处理	尾部填充无变化
TEXT, BLOB	不处理	不处理	尾部填充无变化

启用SQL模式PAD_CHAR_TO_FULL_LENGTH，可以让检索出来的CHAR列值保留尾部空格。

时态（日期/时间）数据类型

MySQL提供了多种存储时态（与时间相关的）值的类型：
DATE，TIME，DATETIME，TIMESTAMP和YEAR。

• 对数据类型TIME，DATETIME和TIMESTAMP，新版本增加了对小数秒的支持。
• 新版本自动把当前时间戳作为初始值并进行更新。
  以前版本，这些属性只能用于表里的大部分单个TIMESTAMP列
  现在可用于任何TIMESTAMP列，且也可用于DATETIME列。
• 新版本丢弃了对YEAR(2)的支持。

时态数据类型的取值范围

类型	取值范围
DATE	1000-01-01 ~ 9999-12-31
TIME	-838:59:59[.000000] ~ 838:59:59[.000000]
DATETIME	1000-01-01 00:00:00[.000000] ~ 9999-12-31 23:59:59[.999999]
TIMESTAMP	1970-01-01 00:00:00[.000000] ~ 2038-01-19 03:14:07[.999999]
YEAR	1901~2155

当为某种日期／时间类型插入非法值时，该类型会把它存储为一个零值。
零值也是那些声明时带有NOT NULL属性的日期／时间类型列的默认值。

类型	零值
DATE	0000-00-00
TIME	00:00:00[.000000]
DATETIME	0000-00-00 00:00:00[.000000]
TIMESTAMP	0000-00-00 00:00:00[.000000]
YEAR	0000

为满足检索显示要求，可使用DATE_FORMAT()和TIME_FORMAT()来显示各种格式的日期和时间。

STR_TO_DATE()用于把非ISO格式的字符串转换为ISO格式的日期值。

mysql> INSERT INTO mytbl (date_col)
    -> VALUES (STR_TO_DATE('12-3-99', '%m-%d-%Y'));

DATE、TIME和DATATIME数据类型

DATE和TIME分别用于保存日期值和时间值；
DATETIME用于保存日期和时间的组合值。

如果把DATE值赋值给DATETIME列，则MySQL会自动把时间部分补足为 ‘00:00:00’ 。
如果把DATETIME值赋值给DATE或TIME列，则MySQL会把不相干的部分去掉。

从TIME到DATETIME的转换，依赖于具体的MySQL版本：
对DATATIME类型，时间部分表示的是一天里的时间，且必须在00:00:00~23:59:59的范围内。
TIME值表示的则是一段逝去的时间。

当往表里插入’短’的TIME值时。
它们可能不会被解释成你所期望的样子。
如’30’和’12:30’插入到TIME列，最终值为’00:00:30’和’00:12:30’。

TIMESTAMP数据类型

TIMESTAMP是一种时态数据类型，用于存储日期和时间的组合值。
TIMESTAMP数据类型有一些特殊的属性。
TIMESTAMP类型列的取值范围是’1970-01-01 00:00:00[.000000]’ ~ ‘2038-01-19 03:14:07[.999999]’ 。

对于每一个TIMESTAMP值，MySQL会用４个字节来把它存储为自纪元以来总共逝去的秒数。

MySQL会按世界标准时间来存储TIMESTAMP值
当保存这样的值时，服务器会把它从会话时区转换为UTC。
当以后检索该值时，服务器又会把它从UTC转换回会话时区，从而让你看到与你存储结果一样的时间值。

如果另一客户端用了另一时区去连接服务器，并检索该值，则它所看到的值则是调整为其所设置时区的那个值。

mysql> CREATE TABLE t(ts TIMESTAMP);
mysql> SET time_zone = '+00:00';
mysql> INSERT INTO t VALUES('2000-01-01 00:00:00');
mysql> SELECT ts FROM t;

mysql> SET time_zone = '+03:00';
mysql> SELECT ts FROM t;

如在定义TIMESTAMP列时，为允许存放NULL值而带有NULL属性，则当把NULL存储到该列时，该列值会被设置为当前时间戳。

YEAR数据类型

声明YEAR列时，可指定一个显示宽度M，M值只能为4或2。默认为4。
YEAR取值范围是1901-2155。
MySQL会使用其年值判断规则，将输入的２位YEAR转换成４位值。

时态数据类型的属性

时态列的定义可包含通用属性NULL或NOT NULL。
如果都不指定，则默认为NULL，TIMESTAMP类型除外，默认值为NOT NULL。
也可用DEFAULT子句来设定默认值。

除了TIMESTAMP和DATETIME外，你都不能使用像CURRENT_TIMESTAMP这样的函数来将DATETIME列的默认值设为　当前日期和时间。
TIMESTAMP和DATETIME的默认值可为当前日期和时间。

时态类型的小数秒功能

在TIME, DATETIME和TIMESTAMP类型的声明语法中，允许设置一个可选的小数秒精度(fsp)。
对于待时态参数的函数，其接受或返回的时态值中都带有小数秒部分。

时态类型的自动特性

TIMESTAMP和DATETIME列可有自动初始化属性和自动更新特性。

• 自动初始化意味着，对于新行，如果 INSERT省略了此类型的列，则列被设为当前时间戳。
• 自动更新意味着，对已有的行，当把任何其他列更改为不同值时，这两种类型的列都会被更新为当前时间戳。

新版本，任何TIMESTAMP列都可有这两种特性中的一种或全部，和DATETIME列一样。

还有一只适用于TIMESTAMP列的特殊属性：如果将列设为NULL
则它的值会被设为当前时间戳。（前提该列允许带有NULL属性）

col_name TIMESTAMP [DEFAULT default_value] [ON UPDATE CURRENT_TIMESTAMP]

如果同时指定DEFAULT和ON UPDATE属性，它们的顺序无关紧要。
默认值可为CURRENT_TIMESTAMP、像０那样的常量值，或格式为’CCYY-MM-DD hh:mm:ss’的值。
也可用CURRENT_TIMESTAMP的同义词，如NOW()。

如果向想让表里第一个TIMESTAMP列有一个或两个自动特性，则可组合使用DEFAULT属性和ON UPDATE属性来定义它。

• 如果使用DEFAULT CURRENT_TIMESTAMP，则列将拥有自动初始化特性。
  如果指定UPDATE CURRENT_TIMESTAMP，则它还会拥有自动更新特性。
• 如果两种属性都没指定，则MySQL会将列定义为具有DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP。
• 如果使用指定了常量值的DEFAULT constant_value属性，
  则列将不具有自动初始化特性。
• 如果没DEFAULT，但有ON UPDATE CURRENT_TIMESTAMP，则具体的默认值为０。

MySQL 5.6.5起，对TIMESTAMP列，可自由用DEFAULT xxx或ON UPDATE xxx。
TIMESTAMP列默认为NOT NULL．此时插入NULL，实际插入当前时间戳内容。

若设为支持NULL，插入NULL，则实际存储NULL。DATETIME默认是NULL。

处理时态值

DATETIME和TIMESTAMP支持的格式有：

'CCYY-MM-DD hh:mm:ss[.uuuuuu]'
'YY-MM-DD hh:mm:ss[.uuuuuu]'

• 如果把DATETIME或TIMESTAMP赋值给DATE，时间部分丢失。
• 如果把DATE赋值给DATETIME或TIMESTAMP，结果值的时间部分被设为零（‘00:00:00’）。

MySQL如何处理无效数据值

默认下，MySQL会按照以下规则处理越界值和其他非正常值.

• 对数值列或TIME列，超出合法取值范围的那些值将被截断到取值范围最近的那个端点.
• 对除TIME以外的其他时态类型列，非法值被转换为该类型相一致的’零’值.
• 对不包括ENUM和SET的字符串列，过长的字符串将被截断到该列的最大长度.
• 给ENUM或SET类型列进行赋值时，需根据列定义里给出的合法取值列表进行.
  如果不是把枚举成员值赋给ENUM列，那么列的值会变成’出错’成员。
  如果把包含非集合成员的子字符串的值赋给SET列，则这些字符串会被清理，剩余的成员才被赋值给列。

如果在执行SQL语句时发生了上述情形，MySQL会给出警告信息。
可以使用 SHOW WARNINIGS可查看。

设置严格模式：

SET sql_mode = 'STRICT_ALL_TABLES';
SET sql_mode = 'STRICT_TRANS_TABLES';

• 对支持事务的表，执行SQL语句发生上述情形，产生错误，事务回滚。
• 对不支持事务的表，如果在插入或修改首行时，产生，引发错误，语句中止。

如果在中间某行执行出错，则在STRICT_ALL_TABLES模式下，抛出错误，导致部分更新。
则在STRICT_TRANS_TABLES模式下，按非严格下策略，执行更新。

处理序列

MySQL提供唯一编号的机制是使用AUTO_INCREMENT列属性——它会自动生成序列编号。

通用的 AUTO_INCREMENT 属性

AUTO_INCREMENT 列必须按照以下条件进行定义。

• 每个表只能有一个列具有AUTO_INCREMENT属性，且它应为整数数据类型。
• 列必须建立索引。常见的是使用PRIMARY KEY或UNIQUE索引
  但也允许用不唯一的索引。
• 列需拥有NOT NULL约束条件。

在创建后，AUTO_INCREMENT列将具有以下行为。

• 把NULL值插入AUTO_INCREMENT列将引发MySQL自动生成下一个序列编号，并把它插入列 。
• 要获得最近生成的序号值，可调LAST_INSERT_ID()函数。

LAST_INSERT_ID()只会依赖于与服务器的当前会话连接所生成的AUTO_INCREMENT值。

• 在插入一行时，如不为AUTO_INCREMENT列指定值，则等同于向该列插入一个NULL。
  如果 ai_col是一个AUTO_INCREMENT列，则下面这两条语句是等效的：

INSERT INTO t (ai_col, name) VALUES (NULL, 'abc');
INSERT INTO t(name) VALUES ('abc');

• 默认下，把０插入AUTO_INCREMENT列，等效于插入NULL
  如启用了SQL的NO_AUTO_VALUE_ON_ZERO，则插入０会导致存储０。
• 如果要插入一行，并为某个拥有唯一索引的AUTO_INCREMENT列指定一个既不为NULL，也不为０的值。
  如果已存在一行使用该值的记录，将发生键重复错误。
  否则，正常插入。
• 对某些存储引擎，从序列顶端删除的值可被重用。
• 如果用UPDATE命令把AUTO_INCREMENT列的值设为某个正被其他行使用的值，并且这个列拥有唯一索引，则出现一个键重复错误。
• 如果根据AUTO_INCREMENT列的值，用REPLACE来更新行，则此行的AUTO_INCREMENT值将保持不变。
  如果根据其他有PRIMARY KEY或UNIQUE索引的列的值，用REPLACE命令来更新行，则当你把AUTO_INCREMENT列设为NULL或把它设为０，且没启用NO_AUTO_VALUE_ON_ZERO时，该列值将被更新为一个新的序列号。

存储引擎特有的 AUTO_INCREMENT 属性

MyISAM表的AUTO_INCREMENT列

• MyISAM表的序列一般是单调的。
  例外情况：
  如果用TRUNCATE TABLE清空了表，则计数器被重置为从１开始；
  如果在表里使用了复合索引来生成多个序列，顶端删除值将被重用。
• MyISAM序列默认从１开始。

Create table xxx
(...)
AUTO_INCREMENT=n;//显式指定

• 可用ALTER TABLE来更改某个已有MyISAM表的当前序列计数器。

ALTER TABLE xxx AUTO_INCREMENT = 1111;

CREATE TABELE bugs
(
	proj_name VARCHAR(20) NOT NULL
	bug_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	PRIMARY KEY(proj_name, bug_id)
)
ENGINE = MYISAM;

先按proj_name分组，每个分组内bug_id唯一。

InnoDB表的AUTO_INCREMENT列

• 在CREATE TABLE语句里，可使用AUTO_INCREMENT=n表选项来设置初始序列值，且在表创建之后，还可用ALTER TABLE来更改。
• 从序列顶端删除的值一般不重用。
  TRUNCATE TABLE清空表下，后续从１开始。
• 复合索引下不会出现多组独立序列。

MEMORY表的AUTO_INCREMENT列
与InnoDB一样

在无 AUTO_INCREMENT 情况下生成序列

如果在插入或修改一个列时使用了LAST_INSERT_ID(expr)函数，则下次不带参数调LAST_INSERT_ID()时，会返回表达式expr的值。
例：

CREATE TABLE seq_table(seq INT UNSIGNED NOT NULL);
INSERT INTO seq_table VALUES(0);

UPDATE seq_table SET seq = LAST_INSERT_ID(seq+1);
SELECT LAST_INSERT_ID();

如果需要多个计数器，可以这样做：
首先给这个表增加一列，用作计数器的标识符；然后在这个表里为每个计数器单独增加一行。

创建一有两个列的表：
一列用于保存计数器的唯一标识，另一列则用于保存计数器的当前值。

仍可使用LAST_INSERTED_ID()函数，但需根据计数器的名字来确定它应该应用于哪一行。

表达式计算和类型转换

表达式包含子项和运算符，且经过计算可产生具体值。
子项可包含值，如常数，函数调用，表列引用，标量子查询。

大部分情况下，表达式出现在输出列的列表里，及SELECT语句的WHERE子句里。

SELECT 
	CONCAT(last_name, ', ', first_name), 	
	TIMESTAMPDIFF(YEAR, birth, death)
FROM president
WHERE 
	birth>'1900-1-1' AND DEATH IS NOT NULL;

编写表达式

表达式可为常量，可用函数调用。

调用内建函数时，它的参数间允许出现空格；函数名和其后左括号间，不允许出现空格。

表达式里可对表进行引用。

运算符类型

两个操作数都是整数时，对运算符+, -和*，整个运算过程所用的都是BIGINT整数值。
两个整数操作数中一个是无符号，结果也为无符号数。
字符串转换为数字时，被转换为双精度数。

LIKE与REGEXP

• LIKE模式只能用于匹配整个字符串。REGEXP可匹配字符串的任何部分。
• LIKE运算符是多字节安全的。
  REGEXP只能正确地处理单字节字符集，不会考虑排序规则。

LIKE下可用%和_%可匹配任意长度字符序列。
%不会匹配NULL。
可以匹配单个字符。
如果想匹配%和_本身，用 \%和 \_ 。

对正则表达式，
.可匹配任何单个字符。
[…]可匹配方括号内任何字符。
x1-x2表示x1到x2的范围。
[^…]对含义取反。
＊表示*前一字符/模式字符的０次到无数次出现。
+表示+前一字符/模式字符的０次到无数次出现。
'^xxxKaTeX parse error: Double superscript at position 4: ' ^̲用于限制从参与模式匹配字符串开…限定此处应为字符串的末尾。

LIKE必须是整体与模式匹配。
REGEXP只需要参与模式匹配的字符串中某个子串可以和模式匹配即可。

运算符优先级

INTERVAL
BINARY COLLATE
!
- （负号） ~  （按位求反）
^
* / DIV % MOD
+ -
<< >>
&
|
< <= = <> <=> != >= > IN IS LIKE REGEXP RLIKE
BETWEEN CASE WHEN THEN ELSE
NOT
AND &&
XOR
OR ||
:=

表达式里的NULL值

当把NULL用作算术运算符或位运算符的操作数时，其计算结果皆为NULL。

１+NULL          → NULL
1 | NULL         → NULL

把NULL与逻辑运算符一起用时，除非计算结果可确定，否则结果皆为NULL。

1 AND NULL        → NULL
1 OR NULL         → 1
0 AND NULL        → 0
0 OR NULL         → NULL

把NULL用作比较运算符或模式匹配运算符的操作数时，计算结果皆为NULL。
<=>,IS NULL,IS NOT NULL专用于处理NULL的除外。

类型转换

可根据character_set_connection和collation_connection所指定的字符集和排序规则，将数字转换成字符串或时态值。

1+‘2’
字符２转换为数字２参与运算。

CONCAT(1,2)
数字1,2转换为字符串’1’,'2’参与运算。

如果CONCAT参数有二进制串时，结果为二进制串；否则，结果为非二进制串。
除非上下文明确表明需要一个数字，否则十六进制常量会被当作二进制串来对待。
字符串上下文里，每两个十六进制数字转换为一个字符后参与运算。

使用字符集引导符或CONVERT()，可把十六进制常量强制转换成一个非二进制串。

进行比较运算时，MySQL根据需要按以下规则对操作数进行类型转换。

• 除<=>外，所有涉及NULL值的比较结果都为NULL。
• 如果两个操作数都是字符串，则它们将按字符串进行词法比较。
  对二进制串，会按字节逐个比较各个对应字节的数值。
  对非二进制串，会使用字符串对应的字符集的排序规则。
• 如果两个操作数都是整数，按整数方式比较
• 除IN外，如有一个操作数为TIMESTAMP或DATETIME，而另一个操作数为常量，则两个操作数会按TIMESTAMP值进行比较。
• 如果一个操作数为小数，小数比较。
• 其他，按双精度浮点数比较。

选择数据类型

在为列挑选类型时需要思考的问题。

• 确定列的类型。
• 值是否在区间内
• 性能，效率

学习参考资料：

《MySQL技术内幕》第5版