The date type in the Mysql Create Table statement

The data types in the MySQL Create statement include the time type, with a few categories:

| DATE | time[( fsp )] | timestamp[( fsp )] | datetime[( fsp )] | Year

Of these types, what is particularly noteworthy is what is the difference between date,datetime,timestamp?

DATE

Mysql> Select Get_format (date, ' ISO '); +------------------------+| Get_format (date, ' ISO ') |+------------------------+| %y-%m-%d |+------------------------+1 row in Set (0.00 sec)

Datetime

Mysql> Select Get_format (datetime, ' ISO '); +----------------------------+| Get_format (datetime, ' ISO ') |+----------------------------+| %y-%m-%d%h:%i:%s |+----------------------------+1 row in Set (0.00 sec)

TIMESTAMP

Mysql> Select Get_format (timestamp, ' ISO '); +-----------------------------+| Get_format (timestamp, ' ISO ') |+-----------------------------+| %y-%m-%d%h:%i:%s |+-----------------------------+1 row in Set (0.00 sec)

Time

Mysql> Select Get_format (Time, ' ISO '); +------------------------+| Get_format (Time, ' ISO ') |+------------------------+| %h:%i:%s |+------------------------+1 row in Set (0.00 sec)

Year

Mysql> Select Year (Curdate ()); +-----------------+|            Year (Curdate ()) |+-----------------+| |+-----------------+1 Row in Set (0.00 sec)

The definition of the format can be found in the function Date_format (Date,format), as defined in the previous format:

%a	Abbreviated weekday name (. Sun . Sat )
%b	Abbreviated month name (... Jan Dec )
%c	Month, numeric ( 0 .. 12 )
%D	Day of the month with 中文版 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 the year (... 001 366 )
%k	Hour ( 0 .. 23 )
%l	Hour ( 1 .. 12 )
%M	Month name (. January . December )
%m	Month, numeric ( 00 .. 12 )
%p	AMOrPM
%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 are the first day of the week, numeric, and four digits; Used with%V
%x	Year for the week, where Monday was the first day of the week, numeric, and four digits; Used with%v
%Y	Year, numeric, four digits
%y	year, numeric (digits)
%%	A literal " % " character
%x	x, for any " x " not listed above

Why you need to know this, first when you create a field of a time type, if you need to specify a time type and a default value, then the type and default merit relationship must be clear and the following are mentioned in the official website:

The DEFAULT clause specifies a default value for a column. With one exception, the default value must is a constant; It cannot is a function or an expression. This means, for example, so 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 so you can specify as the CURRENT_TIMESTAMP default for a TIMESTAMP or (as of MySQL 5.6.5) DATETIME column. See sections 11.3.5, "Automatic initialization and Updating for TIMESTAMP and DATETIME".

Simply put, the default value must be a constant, no time function is allowed, only current_timestamp is used as the default value for timestamp and datetime types. For example, the default value is specified，那么当使用DATE类型时会明显的出现如下错误警告：

[ERR] 1067-invalid default value for ' Act_date '
The correct creation default time can only be as follows:

CREATE TABLE act_tab (act_time TIMESTAMP DEFAULT current_timestamp on UPDATE current_timestamp, act_date DATETIME Defau LT current_timestamp on UPDATE current_timestamp);

Reference:

Http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_get-format

Http://dev.mysql.com/doc/refman/5.6/en/create-table.html

Http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

This article is from the "linuxoracle" blog, make sure to keep this source http://onlinekof2001.blog.51cto.com/3106724/1639582

The date type in the Mysql Create Table statement