MySQL日期格式

2021-11-20 00:00:00 date mysql

当我在 mysql 中创建一个表并创建一个 date 类型的字段时，它存储的日期类似于 0000-00-00.是否可以将格式更改为d-m-Y"?

When I make a table and create a field of type date in mysql, it stores date like 0000-00-00. Is it possible to change the format to 'd-m-Y'?

推荐答案

转到 MySQL 参考 - 10.5.数据类型存储要求

搜索:日期和时间类型的存储要求

日期在内部存储为一个三字节整数打包为DD + MM×32 + YYYY×16×32

但是，如果您选择一个日期列显示，它必须以某种方式显示，所以它显示为 0000-00-00.它不存储为具有该特定格式的字符(10).

But, if you select a date column for display, it has to be shown in some way, so it comes out as 0000-00-00. It is not stored as a char(10) with that specific format.

如果，为了显示，您需要查看特定格式，您可以使用 Date_Format().但是，请记住，如果您在编程工具中使用查询，这不是您想要做的.您需要原始日期值，出于显示目的，无论您使用何种编程环境，都会有类似的格式化功能.

If, for display, you need to see a specific format, you can convert it to VARCHAR in a specific format using Date_Format(). However, bear in mind that if you are using the query in a programming tool, this is not what you want to do. You want the raw date value, for display purposes, there will be a similar formatting function from whatever programming environment you use.

从 DATE_FORMAT 中的参考可以看出，您需要使用 '%d-%m-%Y'，例如

As you can see from the reference in DATE_FORMAT, you will want to use '%d-%m-%Y', e.g.

SELECT col1, col2, DATE_FORMAT(datecolumn, '%d-%m-%Y') AS datecolumn, more1... FROM sometable ....

相关文章