i have to store year and month in my table with two other columns next month theme and theme details what data type should i use
我必须在我的表中存储年份和月份以及下个月的其他两列主题和主题详细信息我应该使用哪种数据类型
should i use smalldatetime and store them by making a date like 09/01/2011 ?
我应该使用smalldatetime并通过制作像09/01/2011这样的日期来存储它们吗?
should i use two columns of varchar or int to store separate year and month like 2011 09 ?
我应该使用两列varchar或int来存储2011年09月的单独年份和月份吗?
should i use one column varchar in which store them with concatenating them like 201109 ?
我应该使用一个列varchar来存储它们像201109一样连接它们吗?
EDITED
one thing i also want to add that i have to use these columns in searching also so i have to use them in where clause so in answer this thing is also important
有一件事我还想补充一点,我必须在搜索中使用这些列,所以我必须在where子句中使用它们所以在回答这个事情也很重要
3 个解决方案
#1
7
Normally, I would advise to use the date date types no matter what your restrictions, since it allows you to do date manipulations and comparisons. You could then use triggers to restrict dates to the first of the month. You'd need triggers to ensure that you couldn't get two rows for a single month/year combo.
通常情况下,无论您的限制是什么,我都会建议使用日期日期类型,因为它允许您进行日期操作和比较。然后,您可以使用触发器将日期限制为该月的第一天。您需要触发器以确保单个月/年组合不能获得两行。
Actually, as
Damian_The_Unbelieverrightly points out in a comment, you don't need triggers if your intent is to only allow users to attempt insertion of dates where the day of the month is 1. In that case, constraints will probably be enough. It's only the case where you want to allow users to attempt to insert any date but actually force it to become the first day of that month, that triggers would be required.实际上,正如Damian_The_Unbeliever在评论中正确指出的那样,如果您的意图是仅允许用户尝试插入月份日期为1的日期,则不需要触发器。在这种情况下,约束可能就足够了。只有在您希望允许用户尝试插入任何日期但实际强制它成为该月的第一天的情况下,才需要触发。
However, in this case, I'd be quite happy with a two-column year/month setup, based on your use case.
但是,在这种情况下,根据您的使用情况,我会对两列年/月设置感到满意。
By using two integer-type columns, you don't need to worry about triggers, and you don't seem to have a need for massive processing of the table contents, based on your column specifications.
通过使用两个整数类型列,您无需担心触发器,并且您似乎不需要根据列规范对表内容进行大量处理。
I wouldn't store them as a single integer-type column if you ever foresee the need to process data for a given year (independent of the month). Having the year separate will allow a distinct index which will probably be faster than getting a range of YYYYMM values.
如果您预见到需要处理给定年份的数据(与月份无关),我不会将它们存储为单个整数类型列。将年份分开将允许一个不同的索引,这可能比获得一系列YYYYMM值更快。
Seriously, choose the one that you think will be easiest to code up (and meets the functional requirements). Then if, and only if, you discover a performance problem, look into a schema re-org. Databases are not set-and-forget things, you should be constantly monitoring them for problems and, if necessary, changing things.
说真的,选择你认为最容易编码的那个(并满足功能要求)。然后,当且仅当您发现性能问题时,请查看架构重组。数据库不是一劳永逸的事情,你应该不断监视它们的问题,并在必要时改变一些事情。
#2
2
If you ever need to make lookups on these fields I would use a dateformat. Two int will use more space, varchar should be replaced with char, cause you size is fix. But as I mentioned, I would stick to a dateformat.
如果你需要在这些字段上进行查找,我会使用dateformat。两个int将使用更多空间,varchar应该用char替换,因为你的大小是固定的。但正如我所提到的,我会坚持使用日期格式。
#3
0
I'd suggest to have two seprate columns for month and year:
我建议每月和每年有两个单独的列:
Month TINYINT
Year INT
#1
7
Normally, I would advise to use the date date types no matter what your restrictions, since it allows you to do date manipulations and comparisons. You could then use triggers to restrict dates to the first of the month. You'd need triggers to ensure that you couldn't get two rows for a single month/year combo.
通常情况下,无论您的限制是什么,我都会建议使用日期日期类型,因为它允许您进行日期操作和比较。然后,您可以使用触发器将日期限制为该月的第一天。您需要触发器以确保单个月/年组合不能获得两行。
Actually, as
Damian_The_Unbelieverrightly points out in a comment, you don't need triggers if your intent is to only allow users to attempt insertion of dates where the day of the month is 1. In that case, constraints will probably be enough. It's only the case where you want to allow users to attempt to insert any date but actually force it to become the first day of that month, that triggers would be required.实际上,正如Damian_The_Unbeliever在评论中正确指出的那样,如果您的意图是仅允许用户尝试插入月份日期为1的日期,则不需要触发器。在这种情况下,约束可能就足够了。只有在您希望允许用户尝试插入任何日期但实际强制它成为该月的第一天的情况下,才需要触发。
However, in this case, I'd be quite happy with a two-column year/month setup, based on your use case.
但是,在这种情况下,根据您的使用情况,我会对两列年/月设置感到满意。
By using two integer-type columns, you don't need to worry about triggers, and you don't seem to have a need for massive processing of the table contents, based on your column specifications.
通过使用两个整数类型列,您无需担心触发器,并且您似乎不需要根据列规范对表内容进行大量处理。
I wouldn't store them as a single integer-type column if you ever foresee the need to process data for a given year (independent of the month). Having the year separate will allow a distinct index which will probably be faster than getting a range of YYYYMM values.
如果您预见到需要处理给定年份的数据(与月份无关),我不会将它们存储为单个整数类型列。将年份分开将允许一个不同的索引,这可能比获得一系列YYYYMM值更快。
Seriously, choose the one that you think will be easiest to code up (and meets the functional requirements). Then if, and only if, you discover a performance problem, look into a schema re-org. Databases are not set-and-forget things, you should be constantly monitoring them for problems and, if necessary, changing things.
说真的,选择你认为最容易编码的那个(并满足功能要求)。然后,当且仅当您发现性能问题时,请查看架构重组。数据库不是一劳永逸的事情,你应该不断监视它们的问题,并在必要时改变一些事情。
#2
2
If you ever need to make lookups on these fields I would use a dateformat. Two int will use more space, varchar should be replaced with char, cause you size is fix. But as I mentioned, I would stick to a dateformat.
如果你需要在这些字段上进行查找,我会使用dateformat。两个int将使用更多空间,varchar应该用char替换,因为你的大小是固定的。但正如我所提到的,我会坚持使用日期格式。
#3
0
I'd suggest to have two seprate columns for month and year:
我建议每月和每年有两个单独的列:
Month TINYINT
Year INT