|
1
2
3
4
5
6
7
8
9
10
|
#创建表t
mysql>
CREATE
TABLE
t (a
INT
UNSIGNED, b
INT
UNSIGNED);
#插入一条数据
mysql>
INSERT
INTO
t
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
Table
:
CREATE
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数据库中，对日期和时间输入格式的要求是非常宽松的，以下输入都可以视为日期类型：
-
2011-01-01 00:01:10
-
2011/01/01 00+01+10
-
20110101000110
-
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类型除了在显示时间范围上有所不同外，还有以下不同：
-
在建表时，列为TIMESTAMP的日期类型可以设置一个默认值，而DATETIME不行。
-
在更新表时，可以设置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
t
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
t
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
t
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)
|
