MySQL数据类型二

转自：https://www.cnblogs.com/Caveolae/p/7058890.html

2.5 SET类型

 SET是一个字符串对象集合，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。例如，指定为SET('one', 'two') NOT NULL的列可以有下面的任何值：

•     ''
•     'one'
•     'two'
•     'one,two'

    SET最多可以设置64个值。创建表时，SET成员值的尾部空格将自动被删除。检索时，保存在SET列的值使用列定义中所使用的大小写来显示。

MySQL用数字保存SET值，所保存值的低阶位对应第1个SET成员。如果在数值上下文中检索一个SET值，检索的值的位设置对应组成列值的SET成员。

    例如，可以这样从一个SET列检索数值值：

1. mysql> SELECT set_col+0 FROM tbl_name;

    如果将一个数字保存到SET列中，数字的二进制的1的位置确定了列值中的SET成员。对于指定为SET('a','b','c','d')的列，成员有下面的十进制和二进制值：

    

    如果你为该列分配一个值9，其二进制形式为1001，因此第1个和第4个SET值成员'a'和'd'被选择，结果值为 'a,d'。

    对于包含多个SET元素的值，当插入值时元素所列的顺序并不重要。在值中一个给定的元素列了多少次也不重要。当以后检索该值时，值中的每个元素出现一次，根据表创建时指定的顺序列出元素。例如，假定某个列指定为SET('a','b','c','d')：

1. CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
2. INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
3. SELECT *,col+0 FROM myset;
4. SELECT *,col+0 FROM myset where col='a,b';

    结果：

1. a,d 9
2. a,d 9
3. a,d 9
4. a,d 9
5. a,d 9

    SET值按数字顺序排序。NULL值排在非NULL SET值的前面。

 通常情况，可以使用FIND_IN_SET()函数或LIKE操作符搜索SET值：

    mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;

    mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';

    第1个语句找出SET_col包含value set成员的行。第2个类似，但有所不同：它在其它地方找出set_col包含value的行，甚至是在另一个SET成员的子字符串中。

    下面的语句也是合法的：

    mysql> SELECT * FROM tbl_name WHERE set_col & 1;

    mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';

    第1个语句寻找包含第1个set成员的值。第2个语句寻找一个确切匹配的值。应注意第2类的比较。将set值与'val1,val2'比较返回的结果与同'val2,val1'比较返回的结果不同。指定值时的顺序应与在列定义中所列的顺序相同。

    如果想要为SET列确定所有可能的值，使用SHOW COLUMNS FROM tbl_name LIKE set_col并解析输出中第2列的SET定义。

    有什么实际应用呢？

    比如我们设定用户的权限控制，一个用户可能会有多种权限，我们使用所有权限创建一个SET类型的字段，我们不需要用一系列int来定义各种权限了，直接使用一个SET字段即可：

1. /*
2. 用户权限permission表
3. */
4. create table user_permission(
5. id int UNSIGNED not null auto_increment,
6. user_id int not null ,
7. permission set('阅读','评论','发帖') not null,
8. primary key(id),
9. unique (user_id)
10. );
11. desc user_permission;
12. insert into user_permission values (0,1,'阅读'),(0,2,'阅读'),(0,3,'阅读,评论');
13. insert into user_permission values (0,4,'阅读,评论,发帖');
14. select *,permission+0 from user_permission;
15. select permission from user_permission where user_id=1;
16. select * from user_permission where permission & 10;
17. SELECT * FROM user_permission WHERE FIND_IN_SET('评论',permission)>0;

 3 时间日期类型

他们的“0”值如下：

    

3.1 DATE, DATETIME, 和TIMESTAMP类型

 当需要同时包含日期和时间信息的值时则使用DATETIME类型。MySQL以'YYYY-MM-DD HH:MM:SS'格式检索和显示DATETIME值。支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。

 当只需要日期值而不需要时间部分时应使用DATE类型。MySQL用'YYYY-MM-DD'格式检索和显示DATE值。支持的范围是'1000-01-01'到 '9999-12-31'。

 TIMESTAMP类型同样包含日期和时间，范围从'1970-01-01 00:00:01' UTC 到'2038-01-19 03:14:07' UTC。

 可以使用任何常见格式指定DATETIME、DATE和TIMESTAMP值：

•     'YYYY-MM-DD HH:MM:SS'或'YY-MM-DD HH:MM:SS'格式的字符串。允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符。例如，'98-12-31 11:30:45'、'98.12.31 11+30+45'、'98/12/31 11*30*45'和'98@12@31 11^30^45'是等价的。
•     'YYYY-MM-DD'或'YY-MM-DD'格式的字符串。这里也允许使用“不严格的”语法。例如，'98-12-31'、'98.12.31'、'98/12/31'和'98@12@31'是等价的。
•     YYYYMMDDHHMMSS'或'YYMMDDHHMMSS'格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，'19970523091528'和'970523091528'被解释为'1997-05-23 09:15:28'，但'971122129015'是不合法的(它有一个没有意义的分钟部分)，将变为'0000-00-00 00:00:00'。
•     'YYYYMMDD'或'YYMMDD'格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，'19970523'和'970523'被解释为 '1997-05-23'，但'971332'是不合法的(它有一个没有意义的月和日部分)，将变为'0000-00-00'。
•     YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字，假定数字对于日期类型是有意义的。例如，19830905132800和830905132800被解释为 '1983-09-05 13:28:00'。
•     YYYYMMDD或YYMMDD格式的数字，假定数字对于日期类型是有意义的。例如，19830905和830905被解释为'1983-09-05'。
•     函数返回的结果，其值适合DATETIME、DATE或者TIMESTAMP上下文，例如NOW()或CURRENT_DATE。

    对于包括日期部分间割符的字符串值，如果日和月的值小于10，不需要指定两位数。'1979-6-9'与'1979-06-09'是相同的。同样，对于包括时间部分间割符的字符串值，如果时、分和秒的值小于10，不需要指定两位数。'1979-10-30 1:2:3'与'1979-10-30 01:02:03'相同。

 数字值应为6、8、12或者14位长。如果一个数值是8或14位长，则假定为YYYYMMDD或YYYYMMDDHHMMSS格式，前4位数表示年。如果数字 是6或12位长，则假定为YYMMDD或YYMMDDHHMMSS格式，前2位数表示年。其它数字被解释为仿佛用零填充到了最近的长度。

    指定为非限定符字符串的值使用给定的长度进行解释。如果字符串为8或14字符长，前4位数表示年。否则，前2位数表示年。从左向右解释字符串内出现的各部分，以发现年、月、日、小时、分和秒值。这说明不应使用少于6字符的字符串。例如，如果你指定'9903'，认为它表示1999年3月，MySQL将在你的表内插入一个“零”日期值。这是因为年和月值是99和03，但日部分完全丢失，因此该值不是一个合法的日期。但是，可以明显指定一个零值来代表缺少的月或日部分。例如，可以使用'990300'来插入值'1999-03-00'。

    可以将一个日期类型的值分配给一个不同的日期类型。但是，值可能会更改或丢失一些信息：

•     如果你为一个DATETIME或TIMESTAMP对象分配一个DATE值，结果值的时间部分被设置为'00:00:00'，因为DATE值未包含时间信息。
•     如果你为一个DATE对象分配一个DATETIME或TIMESTAMP值，结果值的时间部分被删除，因为DATE值未包含时间信息。
•     记住尽管可以使用相同的格式指定DATETIME、DATE和TIMESTAMP值，不同类型的值的范围却不同。例如，TIMESTAMP值不能早于1970或晚于2037。这说明一个日期，例如'1968-01-01'，虽然对于DATETIME或DATE值是有效的，但对于TIMESTAMP值却无效，如果分配给这样一个对象将被转换为0。

    当指定日期值时请注意某些缺陷：

•     指定为字符串的值允许的非严格格式可能会欺骗。例如，值'10:11:12'由于‘:’间割符看上去可能象时间值，但如果用于日期上下文值则被解释为年'2010-11-12'。值'10:45:15'被转换为'0000-00-00'因为'45'不是合法月。
•     在非严格模式，MySQL服务器只对日期的合法性进行基本检查：年、月和日的范围分别是1000到9999、00到12和00到31。任何包含超出这些范围的部分的日期被转换成'0000-00-00'。请注意仍然允许你保存非法日期，例如'2002-04-31'。要想确保不使用严格模式时日期有效，应检查应用程序。 在严格模式，非法日期不被接受，并且不转换。
•     包含两位年值的日期会令人模糊，因为世纪不知道。MySQL使用以下规则解释两位年值： o 00-69范围的年值转换为2000-2069。 o 70-99范围的年值转换为1970-1999。

    各种相关操作：

3.1.1 TIME类型

 MySQL以'HH:MM:SS'格式检索和显示TIME值(或对于大的小时值采用'HHH:MM:SS'格式)。

    TIME值的范围可以从'-838:59:59'到'838:59:59'。小时部分会因此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。

    你可以用各种格式指定TIME值：

•     'D HH:MM:SS.fraction'格式的字符串。还可以使用下面任何一种“非严格”语法：'HH:MM:SS.fraction'、'HH:MM:SS'、'HH:MM'、'D HH:MM:SS'、'D HH:MM'、'D HH'或'SS'。这里D表示日，可以取0到34之间的值。请注意MySQL还不保存分数。
•     'HHMMSS'格式的没有间割符的字符串，假定是有意义的时间。例如，'101112'被理解为'10:11:12'，但'109712'是不合法的(它有一个没有意义的分钟部分)，将变为'00:00:00'。
•     HHMMSS格式的数值，假定是有意义的时间。例如，101112被理解为'10:11:12'。下面格式也可以理解：SS、MMSS、HHMMSS、HHMMSS.fraction。请注意MySQL还不保存分数。
•     函数返回的结果，其值适合TIME上下文，例如CURRENT_TIME。

    对于指定为包括时间部分间割符的字符串的TIME值，如果时、分或者秒值小于10，则不需要指定两位数。'8:3:2'与'08:03:02'相同。

    为TIME列分配简写值时应注意。没有冒号，MySQL解释值时假定最右边的两位表示秒。(MySQL解释TIME值为过去的时间而不是当天的时间）。例如，你可能认为'1112'和1112表示'11:12:00'(11点过12分)，但MySQL将它们解释为'00:11:12'(11分，12 秒)。同样，'12'和12 被解释为 '00:00:12'。相反，TIME值中使用冒号则肯定被看作当天的时间。也就是说，'11:12'表示'11:12:00'，而不是'00:11:12'。

    超出TIME范围但合法的值被裁为范围最接近的端点。例如，'-850:00:00'和'850:00:00'被转换为'-838:59:59'和'838:59:59'。

    无效TIME值被转换为'00:00:00'。请注意由于'00:00:00'本身是一个合法TIME值，只从表内保存的一个'00:00:00'值还不能说出原来的值是 '00:00:00'还是不合法的值。

3.1.2 YEAR类型

YEAR类型是一个单字节类型用于表示年。

    MySQL以YYYY格式检索和显示YEAR值。范围是1901到2155。

    可以指定各种格式的YEAR值：

•     四位字符串，范围为'1901'到'2155'。
•     四位数字，范围为1901到2155。
•     两位字符串，范围为'00'到'99'。'00'到'69'和'70'到'99'范围的值被转换为2000到2069和1970到1999范围的YEAR值。
•     两位整数，范围为1到99。1到69和70到99范围的值被转换为2001到2069和1970到1999范围的YEAR值。请注意两位整数范围与两位字符串范围稍有不同，因为你不能直接将零指定为数字并将它解释为2000。你必须将它指定为一个字符串'0'或'00'或它被解释为0000。
•     函数返回的结果，其值适合YEAR上下文，例如NOW()。

    非法YEAR值被转换为0000。

4 几何类型

几何类型层次结构如下：

    

    到用的时候再说吧。

    官方文档：http://dev.mysql.com/doc/refman/5.7/en/opengis-geometry-model.html

5 各种类型占用的存储

5.1 数值类型

    

    定点数的比较特殊，而且与具体版本也有关系，此处单独解释：

    使用二进制格式将9个十进制(基于10)数压缩为4个字节来表示DECIMAL列值。每个值的整数和分数部分的存储分别确定。每个9位数的倍数需要4个字节，并且“剩余的”位需要4个字节的一部分。下表给出了超出位数的存储需求：

    

5.2 时间日期

    

    从版本5.6.4开始，存储需求就有所改变，根据精度而定。不确定部分需要的存储如下：

    

    比如，TIME(0), TIME(2), TIME(4), 和TIME(6) 分别使用3, 4, 5, 6 bytes。

5.3 字符串
    

6 类型的选择

 为了优化存储，在任何情况下均应使用最精确的类型。

    例如，如果列的值的范围为从1到99999，若使用整数，则MEDIUMINT UNSIGNED是好的类型。在所有可以表示该列值的类型中，该类型使用的存储最少。

    用精度为65位十进制数(基于10)对DECIMAL 列进行所有基本计算(+、-、*、/)。

    使用双精度操作对DECIMAL值进行计算。如果准确度不是太重要或如果速度为最高优先级，DOUBLE类型即足够了。为了达到高精度，可以转换到保存在BIGINT中的定点类型。这样可以用64位整数进行所有计算，根据需要将结果转换回浮点值。

7 使用其他数据库的SQL语句

为了使用为其它数据库编写的SQL执行代码，MySQL按照下表所示对列类型进行映射。通过这些映射，可以很容易地从其它数据库引擎将表定义导入到MySQL中：