如何更改数据库的 MySQL 日期格式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4052862/
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
How to change MySQL date format for database?
提问by Matt McCormick
We are using a MySQL database with FileMaker. It appears that when FileMaker is reading the MySQL tables, it will only accept dates in the format of m/d/y.
我们在 FileMaker 中使用 MySQL 数据库。当 FileMaker 读取 MySQL 表时，它似乎只接受 m/d/y 格式的日期。
Is there any way I can get our MySQL database to change its default format to be m/d/y instead of YYYY-MM-DD?
有什么办法可以让我们的 MySQL 数据库将其默认格式更改为 m/d/y 而不是 YYYY-MM-DD？
I know I can use the DATE_FORMAT() function on individual SELECT queries but I want to see if I can just change the default formatting.
我知道我可以在单个 SELECT 查询中使用 DATE_FORMAT() 函数，但我想看看是否可以更改默认格式。
回答by Gabriel Sosa
Edit 1
编辑 1
Reading a little more I found you can change the format for an specific fieldbut there is not recommended.
阅读更多我发现您可以更改特定字段的格式，但不建议这样做。
you cannot change the storage format
you could set ALLOW_INVALID_DATESand save the dates with the format you wish but I really don't recommend that.
您可以设置ALLOW_INVALID_DATES并以您希望的格式保存日期，但我真的不建议这样做。
if your field isn't indexed there is not issue on call DATE_FORMAT()when you are doing the select, the only issue is if you need to make a select for that field in which case the index wont be used because you are calling the function.
如果您的字段未编入索引，则DATE_FORMAT()在进行选择时不会出现随叫随到的问题，唯一的问题是您是否需要为该字段进行选择，在这种情况下索引不会被使用，因为您正在调用该函数。
回答by Spudley
The trick is not to change the format in MySQL (it isn't stored as a string anyway), but to specify the format you want when you query it.
诀窍不是更改 MySQL 中的格式（无论如何它都不会存储为字符串），而是在查询时指定所需的格式。
This can be achieved using something like this:
这可以使用这样的东西来实现：
SELECT date_format(mydatefield,'%m/%d/%Y') as mydatefield FROM mytable
The official MySQL manual for this is here: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
官方 MySQL 手册在这里：http: //dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
By the way, off-topic but I feel I should mention it: I'd always recommend against using mm/dd/yyyy as a date format -- you'll confuse anyone from outside the US; virtually every other country in the world would normally use dd/mm/yyyy. But both those formats are ambiguous - what date do you mean when you say "12/05/2010"? probably a different one from me, but it's impossible to actually know which way round you intended.
顺便说一句，题外话，但我觉得我应该提一下：我总是建议不要使用 mm/dd/yyyy 作为日期格式——你会混淆美国以外的任何人；世界上几乎所有其他国家通常都会使用 dd/mm/yyyy。但是这两种格式都是模棱两可的 - 当您说“12/05/2010”时，您指的是什么日期？可能与我不同，但实际上不可能知道您打算走哪条路。
If you're intending to use the a date for display purposes, I'd always show the month as a word or abbreviation, as this removes any ambiguity. For input purposes, use a calendar control to avoid any possible confusion.
如果您打算将日期用于显示目的，我总是将月份显示为单词或缩写，因为这样可以消除任何歧义。出于输入目的，请使用日历控件以避免任何可能的混淆。
回答by martin clayton
I don't think you can, see Changing MySQL's Date Format.
我认为您不能，请参阅更改 MySQL 的日期格式。
There are system variables called date_formatand datetime_formatthat look promising, but if you try to set them, you'll get an error, and the documentationsay they are 'unused'.
有系统变量称为date_format与datetime_format那看好的，但如果你尝试设置它们，你会得到一个错误，并且文件说他们是“未使用”。
回答by tim
I use a WAMP installation and usually simply create a column INT(10)and then store my dates like this:
我使用 WAMP 安装，通常只创建一个列INT(10)，然后像这样存储我的日期：
UPDATE `test` SET `dateandtime` = DATE_FORMAT( NOW(), '%y%m%d%H%i' ) WHERE `id` =1234;
This stores 2013-11-22 12:45:09as a number like 1322111245. Yes, it may be considered "improper" but I don't care. It works, and I can sort easily and format on the client any which way I like.
这存储2013-11-22 12:45:09为一个数字，如1322111245. 是的，它可能被认为是“不适当的”，但我不在乎。它有效，我可以轻松地在客户端上以我喜欢的任何方式进行排序和格式化。
This is obviously not suggested if you expect to run any other date functions, but for me, I usually just want to know the record's last update and sort a result set by date.
如果您希望运行任何其他日期函数，显然不建议这样做，但对我而言，我通常只想知道记录的上次更新并按日期对结果集进行排序。
回答by Romancha KC
SELECT COUNT(field_name) AS count_it FROM table_name WHERE DATE_FORMAT(date_field,'%Y-%m-%d') = DATE_FORMAT(CURDATE(), '%Y-%m-%d')-- to count records for today.
回答by Dr. Abhishek
SELECT DATE_FORMAT(NAME_COLUMN, "%d/%l/%Y %H:%i:%s") AS 'NAME'
