Mysql Create Table 语句中Date类型

e13e

Mysql创建语句中的数据类型包括时间类型,有一下几类：
| DATE  | TIME[(fsp)]  | TIMESTAMP[(fsp)]  | DATETIME[(fsp)]  | YEAR
这几个类型中，特别值得注意的是DATE,DATETIME,TIMESTAMP有什么区别？
DATE

1 2 3 4 5 6 7

mysql> select get_format(date,'ISO');    +------------------------+ | get_format(date,'ISO') | +------------------------+ | %Y-%m-%d               | +------------------------+ 1 row in set (0.00 sec)

DATETIME

1 2 3 4 5 6 7

mysql> select get_format(datetime,'ISO'); +----------------------------+ | get_format(datetime,'ISO') | +----------------------------+ | %Y-%m-%d %H:%i:%s          | +----------------------------+ 1 row in set (0.00 sec)

TIMESTAMP

1 2 3 4 5 6 7

mysql> select get_format(timestamp,'ISO');       +-----------------------------+ | get_format(timestamp,'ISO') | +-----------------------------+ | %Y-%m-%d %H:%i:%s           | +-----------------------------+ 1 row in set (0.00 sec)

TIME

1 2 3 4 5 6 7

mysql> select get_format(time,'ISO');    +------------------------+ | get_format(time,'ISO') | +------------------------+ | %H:%i:%s               | +------------------------+ 1 row in set (0.00 sec)

YEAR

1 2 3 4 5 6 7

mysql> select year(curdate()); +-----------------+ | year(curdate()) | +-----------------+ |            2015 | +-----------------+ 1 row in set (0.00 sec)

上述中用到的是format的定义可以再函数DATE_FORMAT(date,format)中找到对格式的定义：

%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u	Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V	Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v	Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal “%” character
%x	x, for any “x” not listed above

为什么需要了解这点，首先在创建时间类型的字段时，如果需要指定时间类型和默认值，那么类型和默认值得关系必须明确，在官网中提到以下内容：
The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP or (as of MySQL 5.6.5) DATETIME column. See Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.
简单的说就是默认值必须是常量，不允许使用时间函数，只能使用CURRENT_TIMESTAMP作为TIMESTAMP和DATETIME类型的默认值。比如指定了默认值，那么当使用DATE类型时会明显的出现如下错误警告：
[Err] 1067 - Invalid default value for 'ACT_DATE'
正确的创建默认时间只能如下：

1 2 3 4

CREATE TABLE ACT_TAB (   ACT_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   ACT_DATE DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

参照：
http://dev.mysql.com/doc/refman/ ... function_get-format
http://dev.mysql.com/doc/refman/5.6/en/create-table.html

http://dev.mysql.com/doc/refman/ ... initialization.html