使用 CHAR(6) 表示年+月字段是一个坏主意吗？
我正在使用 MySQL，表中有一个字段需要存储年+月值。该字段不需要日、分、秒信息。我正在考虑将字段创建为 CHAR(6) 因为使用 >、= 和 < 似乎没问题; 比较字符串。
SELECT '201108' < '201109'
>1
我想使用这种格式，因为我可以将相同的字符串插入到 Lucene 索引引擎中。 这是个好主意还是我应该坚持使用DATE？
I am using MySQL and I have a field in a table that needs to store a year+month value. The field doesn't need the day, minute and second info. I am thinking to create the field as CHAR(6) because it seems to be fine using the >, = and < to compare the string.
SELECT '201108' < '201109'
>1
I want to use this format because I can insert the same string to Lucene index engine.
Is it a good idea or I should stick with DATE?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这会很好地工作，直到您必须实现自己的代码来计算两个值之间的差异，或者计算出未来六个月内您需要什么值。
使用日期类型，这就是(a)的用途。如果分辨率过高，则强制执行将日期始终设置为 1 的约束。或者使用插入/更新触发器强制执行该约束。
然后，您可以使用 DBMS 供应商已经编写的所有精美的日期操作代码，这些代码可能会更加高效，因为它将处理本机二进制类型而不是字符串。
在这种特殊情况下，您也可以节省空间，因为 MySQL
date类型实际上比char(6)短。数据库决策为您提供空间和时间优势的情况并不常见（这通常是一种权衡），因此您应该尽可能抓住它们。(a) 这适用于所有这些类型，例如
date、time和datetime。That will work fine, right up to the point where you have to implement your own code for working out the difference between two values, or figuring out what value you need for a time six months into the future.
Use the date type, that's what it's for (a). If is has too much resolution, enforce the constraint that the day will always be set to 1. Or force that with an insert/update trigger.
Then you can use all the fancy date manipulation code that your DBMS vendor has already written, code that's probably going to be much more efficient since it will be dealing with a native binary type rather than a character string.
And you'll save space in this particular case as well since a MySQL
datetype is actually shorter than achar(6). It's not often that a database decision gives you both space and time advantages (it's usually a trade-off), so you should seize them whenever you can.(a) This applies to all of those types, such as
date,timeanddatetime.您想要使用日期，但不在“日”字段中存储任何内容。数据库的搜索效率比您的代码更高，因为数据库经过优化可以处理此类查找。您需要在“日”字段中存储一个虚拟值才能实现此目的。
You'd want to use a date, but not store anything in the Day field. The database is more efficient at searching than your code will ever be because the database is optimized to handle lookups such as this one. You'd want to store a dummy value in the Day field to make this work.
好吧，因为MySQL只需要3个字节来存储日期 （警告：该链接适用于 MySQL 5.0 版本。请检查您的版本的文档以确保），从存储的角度以及比较时的性能角度来看，使用它会更好
日期。Well, since MySQL only takes 3 bytes to store a date (Warning: The link is for MySQL version 5.0. Check the docs for your version to make sure), it would be better from a storage standpoint--as well as a performance standpoint when it comes to comparisons--to use
date.您还可以使用日期字段，然后在选择值时可以使用 DATE_FORMAT 函数和该日期来选择年份和月份。
将字段设置为日期类型，然后
像您输入的日期为“2011-08-30”
现在您希望结果为 201108，写入
select DATE_FORMAT('2011-08-30','%Y%m');
它将给出结果 201108
有关 DATE_FORMAT 的更多详细信息，请访问
http://davidwalsh.name/format -date-mysql-date_format
You can also use the Date Field for that and then while selecting the values you can use DATE_FORMAT function with that Date for selecting the year and month.
having field as Date type then
like the Date you entered is '2011-08-30'
Now you want the result as 201108 the write
select DATE_FORMAT('2011-08-30','%Y%m');
it will give result as 201108
for more detailed information for DATE_FORMAT please visit
http://davidwalsh.name/format-date-mysql-date_format