MySQL Change Datatype of Cell Based on another value
Posted on 2011-03-07
I have a mysql database (obviously). Within that database I have a single table - 'settings' with columns 'SettingName' and 'SettingValue'. The individual setting values may be a number, text, date or whatever.
SettingName - Setting Value
Name - 'Product A'
StartDate - '2011-03-20 00:00:00'
ID - 567
Cost - 12.34
There will only ever be a few settings (there are only a couple of dozen settings so the table will never go over that probably). Is there a way to enforce a datatype on SettingValue based on the value of SettingName? So the datatype is timestamp when SettingName = StartDate and datatype is int when SettingName = ID etc
I can change the datatype from one to another when I extract them using php but only if the value is incorrect (StartDate - 'Cheese') this may fall down.