mysql 数据类型只存储月份和年份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9134497/
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 datatype to store month and year only
提问by Daniel Wilhoit
I'm writing a php application that will store STUDENT data in a MySQL relational database. I'm trying to find the best way/datatype to store a month and year together without the day. I don't know whether I should just store it as a DATE and use php someway to just store the day as the 1st or use a different datatype that I'm not currently familiar with. Ideally, I do not want to store a day, because the day will not always be the same and would require changing php source code if the day changed in the future.
我正在编写一个 php 应用程序，它将在 MySQL 关系数据库中存储 STUDENT 数据。我试图找到最好的方式/数据类型来存储没有一天的月份和年份。我不知道我是否应该将它存储为 DATE 并以某种方式使用 php 将这一天存储为第 1 天，还是使用我目前不熟悉的其他数据类型。理想情况下，我不想存储一天，因为一天不会总是一样的，如果将来一天发生变化，则需要更改 php 源代码。
Just for more background info, I'm storing a STUDENT's INTENT_TO_GRAD. The client seems to only want this information as a reference or a visual for a report as opposed to using it for data manipulation. In other words, the only functional requirement for this data is to be displayed in a report.
只是为了获得更多背景信息，我正在存储一个 STUDENT 的 INTENT_TO_GRAD。客户似乎只想将此信息作为报告的参考或视觉效果，而不是将其用于数据操作。换句话说，此数据的唯一功能要求是显示在报告中。
回答by Timo Huovinen
It says in the MySQL manual that you can store partial dates
它在 MySQL 手册中说您可以存储部分日期
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
Ranges for the month and day specifiers begin with zero due to the fact that MySQL permits the storing of incomplete dates such as '2014-00-00'.
由于 MySQL 允许存储不完整的日期，例如“2014-00-00”，因此月和日说明符的范围从零开始。
This means that to store the year and month only, you can use a DATEcolumn and put 00instead of the day. e.g 2013-12-00.
这意味着要仅存储年和月，您可以使用DATE列并放置00而不是日。例如2013-12-00。
回答by Brian Roach
Why bother? Just store it as a complete date (perhaps always using the first as the day) and use the database functions MONTH()and YEAR()if you only need part of it. This makes using that field much easier as you can still do range queries, etc.
何苦？只需将它存储为一个完整的日期（也许总是使用第一个作为日期）并使用数据库函数MONTH()，YEAR()如果您只需要它的一部分。这使得使用该字段变得更加容易，因为您仍然可以进行范围查询等。
回答by Brandon Moore
Consider how you are going to use the data. If there are any reports you have to create, which way would allow you to retrieve and work with the data more easily?
考虑您将如何使用这些数据。如果您必须创建任何报告，哪种方式可以让您更轻松地检索和处理数据？
And you don't have to use a date type field. You could just have a Year field and a Month field that are both integers. Then when you actually need to do any kind of expression with it requiring a date it's easy enough to put them together and cast to a date.
而且您不必使用日期类型字段。您可以只有一个 Year 字段和一个 Month 字段，它们都是整数。然后当你真的需要用它做任何需要日期的表达式时，很容易将它们放在一起并转换为日期。
And storing as a date with the day number as 1 and just ignoring it is perfectly okay and fairly normal too. In the end this isn't a decision that's going to matter a whole lot (relatively speaking) so I would just choose the one you like best and get it done.
并将日期存储为日期为 1 的日期并忽略它是完全可以的，也很正常。最后，这不是一个非常重要的决定（相对而言），所以我会选择你最喜欢的一个并完成它。
回答by Kyle Macey
I would store the two as two separate columns as integers. It would make validation cake and allow quick and easy sorting and grouping possibilities.
我会将两者存储为两个单独的列作为整数。它将制作验证蛋糕，并允许快速轻松地进行排序和分组。
回答by Furqan Freed
SELECT * FROM Users
WHERE
MONTH(DateTime) = '07'
AND
YEAR(DateTime) = '2015'
AND
DAY(DateTime) = '26'
you can filter like above but still if you want to store year/month and day as separate, its useless until you apply indexing and its very big data.
您可以像上面那样进行过滤，但是如果您想将年/月和日分开存储，那么在您应用索引及其非常大的数据之前它是无用的。
回答by shapiy
Another solution is to build generated columns from your DATETIME/DATEsource.
另一种解决方案是从您的DATETIME/DATE源构建生成的列。
ALTER TABLE stats
ADD COLUMN year SMALLINT GENERATED ALWAYS AS (YEAR(stat_date)) NOT NULL,
ADD COLUMN month smallint GENERATED ALWAYS AS (MONTH(stat_date)) NOT NULL;
Background
背景
I had a similar problem. After reading this thread, I decided to store incomplete dates (with zeros). It worked on older versions of MySQL, but newer versions produced "Incorrect date" error. As mentioned before, you can turn the error into warning using the NO_ZERO_IN_DATEsetting. But the setting itself is deprecated. Hence, in the future, it would only be possible to support zeros in dates by disabling the strict mode, thus, silencing other types of errors.
我有一个类似的问题。阅读此线程后，我决定存储不完整的日期（带零）。它适用于较旧版本的 MySQL，但较新版本会产生“日期不正确”错误。如前所述，您可以使用NO_ZERO_IN_DATE设置将错误转换为警告。但该设置本身已被弃用。因此，将来，只能通过禁用严格模式来支持日期中的零，从而消除其他类型的错误。
Because NO_ZERO_IN_DATE is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.
因为 NO_ZERO_IN_DATE 已被弃用，它将在未来的 MySQL 版本中作为单独的模式名称被删除，其效果包含在严格 SQL 模式的效果中。
My requirement was to build a monthly view of a table. I had to add an index on month. So, computing moth on-the-fly with YEAR()and MONTH()was not an option.
我的要求是建立一个表的月度视图。我不得不在月份添加一个索引。因此，使用YEAR()和即时计算蛾MONTH()不是一种选择。
Generated columns served their purpose. The yearand monthcolumns weren't part of the primary index, and I could even save space thanks to the VIRTUAL(not STORED) generated columns.
生成的列服务于他们的目的。在year与month列不是主索引的一部分，我甚至可以由于空间节省的VIRTUAL（不STORED）生成列。
Links
链接
回答by Ritz
You can use VARCHARdatatype to store month and year only.
您只能使用VARCHAR数据类型来存储月份和年份。
For those who use datepicker:-
对于那些使用datepicker:-
1)Set VARCHAR datatype in mysql to store month and year.
1）在mysql中设置VARCHAR数据类型来存储月份和年份。
2)If you are using jquery validation and have jquery plugin date picker then you have to do below code.
2）如果您使用 jquery 验证并有 jquery 插件日期选择器，那么您必须执行以下代码。
For ex:-
例如：-
<script>
$(document).ready(function (){
$('#monthyear').datepicker({
autoclose:true,
format:'mm-yy'
});
//Your form validation code here//
});
</script>
