Mysql：建表时将DATETIME的格式设置为'DD-MM-YYYY HH:MM:SS'
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8338031/
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
Mysql: Setup the format of DATETIME to 'DD-MM-YYYY HH:MM:SS' when creating a table
提问by Iam Zesh
After googling around, I cannot find a way to create a new table with a DATETIMEcolumn with the default format set to 'DD-MM-YYYY HH:MM:SS'
谷歌搜索后，我找不到一种方法来创建一个新表，其中DATETIME列的默认格式设置为“ DD-MM-YYYY HH:MM:SS”
I saw a tutorial in which it was done in phpmyadminso I suspect that I could use mysql via command line and achieve the same thing when creating my new table with
我看到了一个教程，其中完成了phpmyadmin所以我怀疑我可以通过命令行使用 mysql 并在创建我的新表时实现同样的事情
CREATE TABLE ()
Thank you in advance
先感谢您
回答by Mirko Akov
"MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format." This is from mysql site. You can store only this type, but you can use one of the many time format functions to change it, when you need to display it.
“MySQL 以 'YYYY-MM-DD HH:MM:SS' 格式检索并显示 DATETIME 值。” 这是来自 mysql 站点。您只能存储这种类型，但是当您需要显示它时，您可以使用许多时间格式函数之一来更改它。
For example, one of those functions is the DATE_FORMAT, which can be used to like so:
例如，这些函数之一是DATE_FORMAT，它可以用来喜欢这样：
SELECT DATE_FORMAT(column_name, '%m/%d/%Y %H:%i') FROM tablename
回答by Mithun Sasidharan
回答by aksu
As others have explained that it is not possible, but here's alternative solution, it requires a little tuning, but it works like datetime column.
正如其他人所解释的那样，这是不可能的，但这是替代解决方案，它需要进行一些调整，但它的工作原理类似于日期时间列。
I started to think, how I could make formatting possible. I got an idea. What about making trigger for it? I mean, adding column with type char, and then updating that column using a MySQL trigger. And that worked! I made some research related to triggers, and finally come up with these queries:
我开始思考，如何才能使格式化成为可能。我有个主意。为它制作触发器怎么样？我的意思是，添加带有 type 的列char，然后使用 MySQL 触发器更新该列。那奏效了！我做了一些与触发器相关的研究，最后提出了这些查询：
CREATE TRIGGER timestampper BEFORE INSERT ON table
FOR EACH
ROW SET NEW.timestamp = DATE_FORMAT(NOW(), '%d-%m-%Y %H:%i:%s');
CREATE TRIGGER timestampper BEFORE UPDATE ON table
FOR EACH
ROW SET NEW.timestamp = DATE_FORMAT(NOW(), '%d-%m-%Y %H:%i:%s');
You can't use TIMESTAMPor DATETIMEas a column type, because these have their own format, and they update automatically.
您不能使用TIMESTAMP或DATETIME作为列类型，因为它们有自己的格式，并且会自动更新。
So, here's your alternative timestamp or datetime alternative! Hope this helped, at least I'm glad that I got this working.
所以，这是您的替代时间戳或日期时间替代！希望这有帮助，至少我很高兴我得到了这个工作。
回答by Datta Salunkhe
i have used following line of code & it works fine Thanks.... @Mithun Sasidharan **
我使用了以下代码行并且它工作正常谢谢....@Mithun Sasidharan **
SELECT DATE_FORMAT(column_name, '%d/%m/%Y') FROM tablename
SELECT DATE_FORMAT(column_name, '%d/%m/%Y') FROM tablename
**
**
回答by colithium
I'm pretty certain that you can't change the datetime format in mysql. The phpmyadmin setting is probably applying a custom format as it reads the datetime (using DATE_FORMAT or something from php). It shouldn't matter what format the database uses, format in the application to display it as you wish.
我很确定您无法更改 mysql 中的日期时间格式。phpmyadmin 设置可能在读取日期时间时应用自定义格式（使用 DATE_FORMAT 或来自 php 的内容）。数据库使用什么格式无关紧要，在应用程序中设置格式以根据需要显示它。
Date formatting is a pretty common task. I typically like to abstract it out into internationalization code or, if you don't need to deal with i18n, into a common date utility library. It helps keep things consistent and makes it easier to change later (or add i18n support).
日期格式化是一项非常常见的任务。我通常喜欢将其抽象为国际化代码，或者如果您不需要处理 i18n，则将其抽象为通用日期实用程序库。它有助于保持一致并使以后更容易更改（或添加 i18n 支持）。
回答by Rahul
No you can't; datetime will be stored in default format only while creating table and then you can change the display format in you selectquery the way you want using the Mysql Date Time Functions
不，你不能；日期时间将仅在创建表时以默认格式存储，然后您可以select使用Mysql 日期时间函数以您想要的方式更改显示格式
回答by Devart
This cannot be done for the table; besides, you even cannot change this default value at all.
这不能为桌子做；此外，您甚至根本无法更改此默认值。
The answer is a server variable datetime_format, it is unused.
答案是服务器变量datetime_format，它未使用。
回答by ynot
Dim x as date
x = dr("appdate")
appdate = x.tostring("dd/MM/yyyy")
dr is the variable of datareader
dr是datareader的变量
回答by GigolNet Guigolachvili
try this:
尝试这个：
DATE NOT NULL FORMAT 'YYYY-MM-DD'
