Enums to String in MYSQL using Spring

I'm using Spring (Roo specifically) to develop an application and for one entity I have a drop down list that is based on a set of enums (i.e. enum(Blue,Pink,Red)). When stored in MYSQL database these enums are stored as numbers (obviously). I want these to be stored as the actual string values instead of numbers in the database (so in MYSQL I want "pinK" to be under "color" instead of simply "2".Is this possible? Thanks

edit:

Let me try asking it a different way. Say when I'm retrieving the data from MYSQL. Instead of the symbolic values I want the actual words is there any way to do this?

Solution

You can store the values as text, then use an insert trigger to validate that the stored values are one of the allowed values (since MySQL doesn't support check constraints).

You can also index the minimum number of characters to provide uniqueness, to make your indexing overhead smaller. In your example, you'd only need to index the first character, since 'B', 'P', and 'R' are unique. If you add 'Brown' to the list, you'll need to index the first two characters ('Bl','Pi','Re','Br').

EDIT

My original suggestion was to store the values as their actual text representations, rather than as a number.

Another way to do what you're asking, though, is to add a 'colors' table:

id     INT
name   VARCHAR

Then have your 'enum' column be a foreign key to the colors table. Then when you do your select:

SELECT ...,colors.name AS color
FROM table t
JOIN colors c ON t.color = c.id