MySQL: enum or char(1)?

October 12, 2004 / Filed under: SQL

In a recent table creation, in MySQL, I ran into a situation that could go one of two ways.

The field I want to create will only contain a single letter. That letter is simply any letter of the alphabet. There are 26 letters total, and only one is chosen to be stored in that field.

In MySQL, which column type should be used?

An enum column type, with an option for each letter of the alphabet?

enum('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p',
'q','r','s','t','u','v','w','x','y','z');

Or... a char column type, with a character limit of 1?

char(1);

What do you think?

Comments/Mentions

# dale at 10/12/2004 7:43 pm cst

You should read this (if you haven't already), mainly the comments.

http://dev.mysql.com/doc/mysql/en/ENUM.html

enum column type allows for a more strict set of input values, which could be good or bad.

Overall I don't think your choice should affect anything too much.

# Matthom at 10/12/2004 8:27 pm cst

Thanks for the link and feedback. You're right, though. It's barely worth consideration. It's not going to affect anything drastically.

Therefore, I am going with char(1).

# Joshua Street at 10/13/2004 4:12 am cst

Because I'm lazy, and can't be bothered looking it up, is enum case sensitive, or could you have got away with using the column type you specified in the post for both upper- and lower-case characters?

# Matthom at 10/13/2004 4:54 am cst

Josh, enum is NOT case sensitive. But you bring up a good point. I will have to look into that.

# kaolin fire at 7/17/2005 2:29 pm cst

Actually, enum can be case sensitive.

When retrieved, values stored into an ENUM column are displayed using the lettercase that was used in the column definition. Before MySQL 4.1.1, lettercase is irrelevant when you assign values to an ENUM column. As of 4.1.1, ENUM columns can be assigned a character set and collation. For binary or case-sensitive collations, lettercase does matter when you assign values to to the column.

# kaolin fire at 7/17/2005 2:30 pm cst

Nice blockquote style!

# Brice at 1/17/2008 9:12 am cst

Haha this is pretty late feedback.

CHAR can be any character. Which include numbers, etc...