MySQL 创建带有日期列的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28041953/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create table with date column
提问by realheaven
I want to create a student table with column 'student_birthday' and its format should be dd-mm-yy.
我想创建一个包含“student_birthday”列的学生表，其格式应为 dd-mm-yy。
create table `student`.`studentinfo`(
`student_id` int(10) not null auto_increment,
`student_name` varchar(45) not null,
`student_surname` varchar(45) not null,
`student_birthday` date(???),
(some lines of code)
primary key(student_id));
what should be inputted in the (???) to get the right format above?
应该在 (???) 中输入什么才能获得上面的正确格式？
采纳答案by neuhaus
Just use "DATE" without the brackets. The brackets are only needed for certain column types where you want to specify the maximum number of bytes/characters that can be stored.
只需使用不带括号的“日期”。只有某些列类型需要使用方括号来指定可以存储的最大字节数/字符数。
For MySQL, it's documented at https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html
对于 MySQL，它记录在https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html
回答by hygull
The following example will explain your problem. I am using MySQL 5.7.18.
以下示例将解释您的问题。我正在使用MySQL 5.7.18。
Firstly I have described the structure of userstable as I am going to create posts table with FOREIGN KEY.
首先，我已经描述了users表的结构，因为我将使用FOREIGN KEY创建 posts 表。
Later I created poststable and it has a DATEfield named createdwith many other columns.
后来我创建了posts表，它有一个名为created的DATE字段，其中包含许多其他列。
Finally I inserted 1 row in the newly created table.
最后我在新创建的表中插入了 1 行。
mysql> desc users;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id | bigint(20) | NO | PRI | NULL | |
| fname | varchar(50) | NO | | NULL | |
| lname | varchar(50) | NO | | NULL | |
| uname | varchar(20) | NO | | NULL | |
| email | text | NO | | NULL | |
| contact | bigint(12) | NO | | NULL | |
| profile_pic | text | NO | | NULL | |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> CREATE TABLE posts(id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, title text NOT NULL, description text NOT NULL, posted_by bigint, FOREIGN KEY(posted_by) REFERENCES users(id) ON DELETE CASCADE ON UPDATE RESTRICT , created DATE);
Query OK, 0 rows affected (0.01 sec)
mysql> desc posts;
+-------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| title | text | NO | | NULL | |
| description | text | NO | | NULL | |
| posted_by | bigint(20) | YES | MUL | NULL | |
| created | date | YES | | NULL | |
+-------------+------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> INSERT INTO posts(title, description, posted_by, created) values("Getting started with MySQL", "Excellent Database system", 1, "2017-05-26");
Query OK, 1 row affected (0.00 sec)
mysql> select * from posts;
+----+----------------------------+---------------------------+-----------+------------+
| id | title | description | posted_by | created |
+----+----------------------------+---------------------------+-----------+------------+
| 1 | Getting started with MySQL | Excellent Database system | 1 | 2017-05-26 |
+----+----------------------------+---------------------------+-----------+------------+
1 row in set (0.00 sec)
mysql>
回答by shree.pat18
The datatype dateon its own is enough to represent a date value. The format will matter when you are displaying the data, for which you can use the FORMATfunction on your datecolumn.
数据类型date本身足以表示日期值。当您显示数据时，格式很重要，您可以使用列FORMAT上的函数date。
I should add that there is a certain amount of flexibility as to the format when inserting date time literals as documented here.
我要补充一点，有弹性有一定量的格式插入日期时间文字时，作为记录在这里。
