ISSUE: Mysql converting Enum to Int

Question

I have a very simple rating system in my database where each rating is stored as an enum('1','-1'). To calculate the total I tried using this statement:

SELECT SUM(CONVERT(rating, SIGNED)) as value from table WHERE _id = 1

This works fine for the positive 1 but for some reason the -1 are parsed out to 2's.

Can anyone help or offer incite?

Or should I give up and just change the column to a SIGNED INT(1)?

Answer 1

Yes, I'd suggest to change the type of the column. The issue becomes clear when you read the doc about enum type (which strongly recommends not to use numbers as enumeration values!) - the index of the enum item is returned, not the enum value itself.

Answer 2

this is what you want

select enum+0 as enum