Is it a bad idea using CHAR(6) to represent year+month field?

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?

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 date type is actually shorter than a char(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, time and datetime.

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.

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.

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