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

  1. 云栖社区>
  2. 博客>
  3. 正文

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

余二五 2017-11-08 23:15:00 浏览828
展开阅读全文

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可能会带来一些负面的影响,示例如下:
1
2
3
4
5
6
7
8
9
10
#创建表t
mysql> CREATE TABLE t (a INT UNSIGNED, b INT UNSIGNED);
#插入一条数据
mysql> INSERT INTO SELECT 1,2;
#运行查询
mysql> SELECT FROM t\G;
*************************** 1. row ***************************
a: 1
b: 2
1 row in set (0.00 sec)

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

SELECT a - b FROM t

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

1
2
3
#如下是linux系统运行的结果
mysql> SELECT a - b FROM t;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - `test`.`t`.`b`)'

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

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

1
2
3
4
5
6
7
8
9
mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT a - b FROM t;
+-------+
| a - b |
+-------+
|    -1 |
+-------+
1 row in set (0.00 sec)

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

UNSIGNED属性总结:

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

1.1.2 ZEROFILL

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

1
2
3
4
5
6
7
8
mysql> SHOW CREATE TABLE t\G;
*************************** 1. row ***************************
Table: t
Create TableCREATE TABLE `t` (
`a` int(10) unsigned DEFAULT NULL,
`b` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

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

1
2
3
4
5
mysql> SELECT FROM t\G;
*************************** 1. row ***************************
a: 1
b: 2
1 row in set (0.00 sec)

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

1
2
3
4
5
6
7
8
9
mysql> ALTER TABLE t CHANGE COLUMN a a int(4) UNSIGNED ZEROFILL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
#将默认的int(10)修改为int(4),这时在进行查找操作:
mysql> SELECT FROM t\G;
*************************** 1. row ***************************
a: 0001
b: 2
1 row in set (0.00 sec)

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

2.1 日期和时间类型

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

类型 所占空间
DATETIME 8字节
TIMESTAMP 4字节
TIME 3字节
DATE 3字节
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数据库中,对日期和时间输入格式的要求是非常宽松的,以下输入都可以视为日期类型:

  1. 2011-01-01 00:01:10

  2. 2011/01/01 00+01+10

  3. 20110101000110

  4. 11/01/0100@01@10

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

1
2
3
4
mysql> select CAST('11/01/01 00@01@10' AS DATETIME) AS datetime\G;
*************************** 1. row ***************************
datetime: 2011-01-01 00:01:10
1 row in set (0.00 sec)

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

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

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

1
2
3
4
5
6
7
8
9
mysql> create table time_test (a DATETIME);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO time_test SELECT '2013-07-09 15:47:39.123456';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> SELECT FROM time_test\G;
*************************** 1. row ***************************
a: 2013-07-09 15:47:39
1 row in set (0.00 sec)

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

1
2
3
4
mysql> SELECT MICROSECOND('2013-07-09 15:47:39.123456')\G;
*************************** 1. row ***************************
MICROSECOND('2013-07-09 15:47:39.123456'): 123456
1 row in set (0.00 sec)

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
#说明下面的例子MySQL的版本为:5.6.12
mysql> CREATE TABLE time_test (a DATETIME(4));
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO time_test SELECT '2013-07-09 15:47:39.123456';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> SELECT FROM time_test\G;
*************************** 1. row ***************************
a: 2013-07-09 15:47:39.1235
1 row in set (0.00 sec)
mysql> SELECT CURTIME(4) AS TIME\G;
*************************** 1. row ***************************
TIME: 15:31:20.4552

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类型除了在显示时间范围上有所不同外,还有以下不同:

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

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

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

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

1
2
3
4
5
6
7
8
9
10
11
#此数据库的版本为:
mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.31-log |
+------------+
mysql> CREATE TABLE test_time
-> ( a INT,
->   b DATETIME DEFAULT CURRENT_TIMESTAMP);
ERROR 1067 (42000): Invalid default value for 'b'

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.12    |
+-----------+
mysql> CREATE TABLE test_time  ( a INT,   b DATETIME DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO test_time (a) VALUES (1);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT FROM test_time\G;
*************************** 1. row ***************************
a: 1
b: 2013-07-09 16:45:38
1 row in set (0.00 sec)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> CREATE TABLE t
-> ( a INT,
->   b TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO SELECT 1,CURRENT_TIMESTAMP;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> SELECT FROM t\G;
*************************** 1. row ***************************
a: 1
b: 2013-07-09 16:48:18
1 row in set (0.00 sec)
#等待一段时间
mysql> UPDATE SET a = 2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT FROM t\G;
*************************** 1. row ***************************
a: 2
b: 2013-07-09 16:49:34
1 row in set (0.00 sec)

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

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT FROM t\G;
*************************** 1. row ***************************
a: 2
b: 2013-07-09 16:49:34
1 row in set (0.00 sec)
mysql> UPDATE SET a = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> SELECT FROM t\G;
*************************** 1. row ***************************
a: 2
b: 2013-07-09 16:49:34
1 row in set (0.00 sec)

可以看到执行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年。

看如下示例:

1
2
3
4
5
6
7
8
9
10
11
12
mysql> CREATE TABLE year_test ( a YEAR(2));
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> INSERT INTO year_test SELECT '1990';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> SELECT FROM year_test;
+------+
| a    |
+------+
|   90 |
+------+
1 row in set (0.00 sec)

TIME类型占用3字节。

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
mysql> CREATE TABLE time_time ( a TIME );
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO time_time SELECT '14:30:56.3543534';
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1
mysql> SELECT FROM time_time;
+----------+
| a        |
+----------+
| 14:30:56 |
+----------+
1 row in set (0.00 sec)








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

网友评论

登录后评论
0/500
评论
余二五
+ 关注