MySQL 类型属性及时间，日期类型

1.1类型属性

数据类型具有两个属性：UNSIGNED和ZEROFILL，是否使用这两个属性对选择数据类型有莫大的关系。

1.1.1 UNSIGNED

UNSIGNED 属性：就是将数字类型无符号化。

例如，INT的类型的取值范围是：-2,147,483,648 ~ 2,147,483,647; INT UNSIGINED的取值范围就是：0 ~ 4,294,967,295。

看起来这是一个不错的属性选项，特别是对于主键是自增长的类型，因为一般来说，用户都希望主键是非负数。

然而在实际使用中，UNSIGNED可能会带来一些负面的影响，示例如下：

<code>mysql> </code><code>CREATE</code> <code>TABLE</code> <code>t (a </code><code>INT</code> <code>UNSIGNED, b </code><code>INT</code> <code>UNSIGNED);</code>

<code>#插入一条数据</code>

<code>mysql> </code><code>INSERT</code> <code>INTO</code> <code>t </code><code>SELECT</code> <code>1,2;</code>

<code>mysql> </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>t\G;</code>

目前看来都没有问题，接着运行如下语句：

SELECT a - b FROM t

这时结果会是什么呢？答案是什么？ -1 ？答案是不确定的。可以是 -1，可以是很大的正值，还可能报错。

<code>#如下是linux系统运行的结果</code>

<code>mysql> </code><code>SELECT</code> <code>a - b </code><code>FROM</code> <code>t;</code>

<code>ERROR 1690 (22003): </code><code>BIGINT</code> <code>UNSIGNED value </code><code>is</code> <code>out</code> <code>of</code> <code>range </code><code>in</code> <code>'(`test`.`t`.`a` - `test`.`t`.`b`)'</code>

这个错误看起来非常奇怪，提示BIGINT UNSIGNED 超出了范围，但是我们定义表时采用的类型是INT UNSIGNED啊！

那么怎么的到-1的结果呢？只要对SQL_MODE参数进行设置即可，例如：

<code>mysql> </code><code>SET</code> <code>sql_mode = </code><code>'NO_UNSIGNED_SUBTRACTION'</code><code>;</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.00 sec)</code>

其实就是SQL_MODE的设置影响其结果值。

UNSIGNED属性总结：

笔者个人的看法是尽量不要使用UNSIGNED，因为可能带来一些意想不到的结果。另外对于INT类型可能存放不了的数据，INT UNSIGINED同样可能存放不了，与其如此，还不如在数据库设计阶段将INT提升为BIGINT。

1.1.2 ZEROFILL

ZEROFILL属性非常有意思，更像是一个显示的属性。很多初学者往往对MySQL数据库中数字类型后面的长度很迷茫。下面通过SHOW CREATE TABLE命令来看一下t表的建表语句。

<code>mysql> SHOW </code><code>CREATE</code> <code>TABLE</code> <code>t\G;</code>

<code>Table</code><code>: t</code>

<code>Create</code> <code>Table</code><code>: </code><code>CREATE</code> <code>TABLE</code> <code>`t` (</code>

<code>`a` </code><code>int</code><code>(10) unsigned </code><code>DEFAULT</code> <code>NULL</code><code>,</code>

<code>`b` </code><code>int</code><code>(10) unsigned </code><code>DEFAULT</code> <code>NULL</code>

<code>) ENGINE=InnoDB </code><code>DEFAULT</code> <code>CHARSET=latin1</code>

可以看到int(10)，这代表什么意思呢？整形不就4个字节吗？这10又代表什么呢？其实如果没有ZEROFILL这个属性，括号内的数字是毫无意思的。a和b列就是前面插入的数据，例如：

但是对列添加ZEROFILL属性后，显示的结果就有所不同了，例如对表t的a字段添加ZEROFILL属性：

<code>mysql> </code><code>ALTER</code> <code>TABLE</code> <code>t CHANGE </code><code>COLUMN</code> <code>a a </code><code>int</code><code>(4) UNSIGNED ZEROFILL;</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.01 sec)</code>

<code>Records: 0 Duplicates: 0 Warnings: 0</code>

<code>#将默认的</code><code>int</code><code>(10)修改为</code><code>int</code><code>(4)，这时在进行查找操作：</code>

这次看到a的值有原来的1变为了0001，这就是ZEROFILL属性的作用，如果宽度小于设定的宽度（这里宽度为4），则自动填充0。要注意的是：这只是最后显示的结果。在MySQL中实际存储的还是1。

2.1 日期和时间类型

MySQL数据库中有五种与日期和时间有关的数据类型，其数据类型所占能用的空间对比图如下：

类型

所占空间

DATETIME

8字节

TIMESTAMP

4字节

TIME

3字节

DATE

YEAR

1字节

2.1.1 DATETIME和DATE

DATETIME占用8字节，是占用空间最多的一种日期类型。既显示了日期又同时显示了时间。

显示格式为：‘YYYY-MM-DD HH:MM:SS'

表达日期范围：'1000-01-01 00:00:00' to '9999-12-31 23:59:59'

DATE占用3字节，仅显示日期。

显示格式为：'YYYY-MM-DD'

表达的日期范围：'1000-01-01' to '9999-12-31'

在MySQL数据库中，对日期和时间输入格式的要求是非常宽松的，以下输入都可以视为日期类型：

2011-01-01 00:01:10

2011/01/01 00+01+10

20110101000110

其中，最后一种类型中的“11”有些模棱两可，MySQL数据库将其视为2011还是1911呢？下面测试下：

<code>mysql> </code><code>select</code> <code>CAST</code><code>(</code><code>'11/01/01 00@01@10'</code> <code>AS</code> <code>DATETIME) </code><code>AS</code> <code>datetime\G;</code>

<code>datetime: 2011-01-01 00:01:10</code>

可以看到数据库将其视为离现在最近的一个年份，这可能并不是一个非常好的习惯。如果没有特别的条件和要求，还是在输入时按照标准的'YYYY-MM-DD HH:MM:SS'格式来进行。

说明：MySQL的CAST()函数可用来获取一个类型的值。格式：CAST(value AS type)

在MySQL5.5版本之前（包括5.5），数据库的日期类型不能精确到微秒级别，任何的微妙数值都会被数据库截断，例如：

<code>mysql> </code><code>create</code> <code>table</code> <code>time_test (a DATETIME);</code>

<code>mysql> </code><code>INSERT</code> <code>INTO</code> <code>time_test </code><code>SELECT</code> <code>'2013-07-09 15:47:39.123456'</code><code>;</code>

<code>Query OK, 1 row affected (0.01 sec)</code>

<code>Records: 1 Duplicates: 0 Warnings: 0</code>

<code>mysql> </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>time_test\G;</code>

不过MySQL数据库提供了函数MICROSECOND()来提取日期中的微妙值，例如：

<code>mysql> </code><code>SELECT</code> <code>MICROSECOND(</code><code>'2013-07-09 15:47:39.123456'</code><code>)\G;</code>

<code>MICROSECOND(</code><code>'2013-07-09 15:47:39.123456'</code><code>): 123456</code>

然而从MySQL5.6.4版本开始，MySQL增加了对秒的小数部分的支持，具体语法为：type_name(fsp)

其中type_name的类型可以是TIME，DATETIME和TIMESTAMP。fsp表示支持秒的小数部分的精度，最大为6，表示微妙；默认为0，表示没有小数部分。对于时间函数，如CURTIME(),SYSDATE(),和UTC_TIMESTAMP()也增加了对fsp的支持，例如：

<code>#说明下面的例子MySQL的版本为：5.6.12</code>

<code>mysql> </code><code>CREATE</code> <code>TABLE</code> <code>time_test (a DATETIME(4));</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.04 sec)</code>

<code>Query OK, 1 row affected (0.00 sec)</code>

<code>mysql> </code><code>SELECT</code> <code>CURTIME(4) </code><code>AS</code> <code>TIME</code><code>\G;</code>

2.1.2 TIMESTAMP

TIMESTAMP和DATETIME显示的结果是一样的。

显示格式：'YYYY-MM-DD HH:MM:SS'

表达日期范围：'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC

TIMESTAMP类型和DATETIME类型除了在显示时间范围上有所不同外，还有以下不同：

在建表时，列为TIMESTAMP的日期类型可以设置一个默认值，而DATETIME不行。

在更新表时，可以设置TIMESTAMP类型的列自动更新时间为当前时间。

特别说明：这两种情况是在MySQL5.6.5之前有区别的，之后TIMESTAMP和DATETIME都可以自动初始化及Update。

首先我们来测下5.6.5版本之前后的一个默认设置时间的例子：

<code>#此数据库的版本为：</code>

<code>mysql> </code><code>select</code> <code>version();</code>

<code>| version() |</code>

<code>mysql> </code><code>CREATE</code> <code>TABLE</code> <code>test_time</code>

<code>-> b DATETIME </code><code>DEFAULT</code> <code>CURRENT_TIMESTAMP</code><code>);</code>

<code>ERROR 1067 (42000): Invalid </code><code>default</code> <code>value </code><code>for</code> <code>'b'</code>

可以看出初始化b字段的值，会报非法的默认值。看在MySQL5.6.5之后的版本：

<code>| version() |</code>

<code>mysql> </code><code>CREATE</code> <code>TABLE</code> <code>test_time ( a </code><code>INT</code><code>, b DATETIME </code><code>DEFAULT</code> <code>CURRENT_TIMESTAMP</code><code>);</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.03 sec)</code>

<code>mysql> </code><code>INSERT</code> <code>INTO</code> <code>test_time (a) </code><code>VALUES</code> <code>(1);</code>

<code>mysql> </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>test_time\G;</code>

接着来看一个执行UPDATE的时更新为当前时间的例子：

<code>mysql> </code><code>CREATE</code> <code>TABLE</code> <code>t</code>

<code>-> b </code><code>TIMESTAMP</code> <code>ON</code> <code>UPDATE</code> <code>CURRENT_TIMESTAMP</code><code>);</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.09 sec)</code>

<code>mysql> </code><code>INSERT</code> <code>INTO</code> <code>t </code><code>SELECT</code> <code>1,</code><code>CURRENT_TIMESTAMP</code><code>;</code>

<code>#等待一段时间</code>

<code>mysql> </code><code>UPDATE</code> <code>t </code><code>SET</code> <code>a = 2;</code>

<code>Query OK, 1 row affected (0.02 sec)</code>

<code>Rows</code> <code>matched: 1 Changed: 1 Warnings: 0</code>

可以发现在执行UPDATE操作后，b列的时间由原来的16:48:18更新为了16:49:34。如果执行了UPDATE操作，而实际上行并没有得到更新，那么是不会更新b列的，例如：

<code>Rows</code> <code>matched: 1 Changed: 0 Warnings: 0</code>

可以看到执行UPDATE并没有改变行的任何数据，显示Changed: 0，故b列并不会进行相应的更新操作。

2.1.3 YEAR和TIME

YEAR类型占用1字节，并且在定义时可以指定显示的宽度为YEAR(4)或YEAR(2)。

对于YEAR(4)显示年份的范围：'1901' to '2155'

对于YEAR(2)显示年份的范围：'0' to '69'代表2000~2069年。

看如下示例：

<code>mysql> </code><code>CREATE</code> <code>TABLE</code> <code>year_test ( a </code><code>YEAR</code><code>(2));</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected, 1 warning (0.01 sec)</code>

<code>mysql> </code><code>INSERT</code> <code>INTO</code> <code>year_test </code><code>SELECT</code> <code>'1990'</code><code>;</code>

<code>mysql> </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>year_test;</code>

TIME类型占用3字节。

显示格式：'HH:MM:SS'

表达的时间范围：'-838:59:59' to '838:59:59'

有人会奇怪为什么TIME类型的时间可以大于23.因为TIME类型不仅可以用来保存一天中的时间，也可以用来保存时间间隔，同时这也解释了为什么TIME类型也可以存在负值。和DATETIME类型一样，TIME类型同样可以显示微妙时间，但是在插入时，数据库同样会进行截断操作。示例如下：

<code>mysql> </code><code>CREATE</code> <code>TABLE</code> <code>time_time ( a </code><code>TIME</code> <code>);</code>

<code>Query OK, 0 </code><code>rows</code> <code>affected (0.02 sec)</code>

<code>mysql> </code><code>INSERT</code> <code>INTO</code> <code>time_time </code><code>SELECT</code> <code>'14:30:56.3543534'</code><code>;</code>

<code>Query OK, 1 row affected, 1 warning (0.01 sec)</code>

<code>Records: 1 Duplicates: 0 Warnings: 1</code>

<code>mysql> </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>time_time;</code>

本文转自 kuchuli 51CTO博客，原文链接：http://blog.51cto.com/lgdvsehome/1243676，如需转载请自行联系原作者

MySQL 类型属性及时间，日期类型

继续阅读

禁止ubuntu系统弹出报错界面

MySQL的4种隔离级别？出现问题

Ubuntu Linux下Apache的配置文件

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

samba服务器的功能

登录plsql 报错 the account is locked --用户被锁

【Linux】UDP广播报文接收速率问题

SequoiaDB巨杉数据库C++驱动概述

Linux设备模型（中）之上层容器

PowerPC平台 Linux移植三