Enum datatype in mysql

23 / Jul / 2010 by Uday Pratap Singh 0 comments

Recently in a Grails project that I am currently working on, the client wanted to have few functionalities of our web application on iPhone as well. We are using Enums heavily in our project and the iPhone development cannot recognize from looking at the tables that few fields are Enums so those need to be one of value of Enum. So they use to store some different value in it as the datatype of Enums in table is varchar the sql doesn’t mind storing the different values.

So after looking at here and there for Enum datatypes for sql we found a very easy solution to do that. We altered few tables like this

alter table documents change document_priority document_priority enum('Critical','Major','Blocker','Backlog') default 'Major';

For adding an Enum column you just need to do something like

alter table documents add column enum('Critical','Major','Blocker','Backlog') 

after doing this the user can see that which values the field expect so iPhone development team also get to know what should they do. But it does not restrict user to other values in it though if user tries to add some other value in it the sql will set it to blank(” “). It still doesn’t serves our purpose because in application there is no blank Enum. The solution for this is in the sql_mode. So we set the sql_mode

SET sql_mode = STRICT_TRANS_TABLES

So can see the sql_mode of your database like this

SELECT @@global.sql_mode, @@session.sql_mode;

I hope this helped for some people the only thing I miss in this. I dont get the solution for this in Hibernate. May be there is a solution for this either in Hibernate or in Grails. Looking for the better solution that can be done in the application itself.
Hope it helps

FOUND THIS USEFUL? SHARE IT

Leave a comment -