Mysql Update Enum failed?

Sayian

Somthing weird is happening with mysql ...

I had a col in a table called total_photos that kept track of how many photos a user had in their account

Running mysql analyse on this table , told me to change the datatype to enum, and set values from 0 - 15

Well when i do update col set col = col + 1

It was adding 2 onto the total_photo count

if the datatype is tinyint(2) then it updates it correctly, and only add's 1

Why is this?

devinemke

you do not want to use an ENUM type for this. use an interger type, otherwise you will be limited to the ENUM list of values.

Sxooter

And, unless things have changed, you need to be careful. Enums are basically an array type. I.e. if you create it with choices like '6', '9' ,'1', '55' and update it without quoting, it will use the number to reference it. I.e. an update with an unquoted 1, in this instance, would assign '6' as the value. Unless things have changed lately.

I recommend to not use them, (or arrays in postgresql) because they are implementation specific, and they can be implemented properly using a relational model.

Sayian

Thanks for the information, enums are ok to use, if i have a choice of the values on the column only as my values inserted right?

Sxooter

Sure, they work fine. My preference is to use a dependent foregn keyed table with the values in it, that way it's easy to have an app look up the possible values and fill a drop menu with them. With enum, you pretty much have to parse the output of desc table to do the same thing, and I hate hard coding values into an application, which is what usually happens with enum types.

So, if the values aren't likely to ever change, enum is fine. If you'll want to change them or you DON'T want them hard coded into your app, then go with the fk'd table approach.

Note that the FK'd table approach works generically with any database, while enum is mysql specific.