将格式为 DD-MMM-YYYY 的日期插入 MySQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21327592/
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
Inserting date with format DD-MMM-YYYY into MySQL database
提问by user2995578
I want to insert date into my mysql database either in DD_MM-YYYY or DD-MM-YYYY format. I tried all options but could not succeed. Is there any sample program or script for executing the same.
我想以 DD_MM-YYYY 或 DD-MM-YYYY 格式将日期插入到我的 mysql 数据库中。我尝试了所有选项，但都没有成功。是否有用于执行相同的示例程序或脚本。
回答by korulis
Here is an example on how I imported the following table (see: http://www.sqlcourse2.com/items_ordered.html)
这是我如何导入下表的示例（请参阅：http: //www.sqlcourse2.com/items_ordered.html）
customerid order_date item quantity price
10330 30-Jun-1999 Pogo stick 1 28.00
10101 30-Jun-1999 Raft 1 58.00
...
into my DB:
进入我的数据库：
create table items_ordered (
customerid int(6),
order_date date,
item varchar(30),
quantity int(6),
price float(6,2)
);
LOAD DATA INFILE '/home/korulis/Documents/sqltut2 - items_ordered.csv'
IGNORE INTO TABLE items_ordered
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'-- '\r\n' or '\n'
IGNORE 1 LINES
(customerid, @var1, item, quantity, price)
SET order_date = STR_TO_DATE(@var1,'%d-%b-%Y');
回答by STT LCU
You don't really want to do that.
What you really want is to store the date inside a DATEtype column with the default format YYYY-MM-DDand leave your application to format the date as you wish.
你真的不想那样做。您真正想要的是DATE使用默认格式将日期存储在类型列中，YYYY-MM-DD并让您的应用程序根据需要设置日期格式。
回答by Nagaraj S
The DATETIMEtype is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format.
的DATETIME类型用于同时包含日期和时间部分的值。MySQL 以“ YYYY-MM-DD HH:MM:SS”格式检索和显示 DATETIME 值。
The TIMESTAMPdata type is used for values that contain both date and time parts. TIMESTAMP has a range of '2001-01-01 00:00:01
所述时间戳数据类型用于同时包含日期和时间部分的值。TIMESTAMP 的范围是 '2001-01-01 00:00:01
The DATEtype is used for DATE values in 'YYYY-MM-DD' format
的DATE类型用于DATE值“YYYY-MM-DD”格式
回答by Oswald
To insert the date, use SUBSTRING()to extract the components of the date and CONCAT()to build a date from the components in the format required by MySQL.
插入日期，使用SUBSTRING()提取日期的组成部分，并CONCAT()以MySQL要求的格式从组成部分中构建日期。
When reading the date, use DATE_FORMAT()to transform the date back into the original format.
读取日期时，使用DATE_FORMAT()将日期转换回原始格式。
