CODEWITHSUNDEEP
mysqlenumsint
NSjonas
NSjonas

Reputation: 12032

ISSUE: Mysql converting Enum to Int

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)?

Upvotes: 11

Views: 22468

Answers (6)

RusArtM
RusArtM

Reputation: 1300

I wouldn't use enum here too, but it is still possible in this case to get what is needed

Creating table:

CREATE TABLE test (
    _id INT PRIMARY KEY,
    rating ENUM('1', '-1')
);

Filling table:

INSERT INTO test VALUES(1, "1"), (2, "1"), (3, "-1"), (4, "-1"), (5, "-1");

Performing math operations on enums converts them to indexes, so it is possible just to scale the result value:

SELECT 
    SUM(3 - rating * 2)
FROM
    test;

Result: -1 which is true for the test case.

Upvotes: 0

Jiri Novak
Jiri Novak

Reputation: 301

This conversion to int in MySQL for enum is only possible:

CAST(CAST(`rating` AS CHAR) AS SIGNED) as value from table WHERE _id = 1

Upvotes: 17

Chris
Chris

Reputation: 2344

this is what you want

select enum+0 as enum

Upvotes: 25

Daryl B
Daryl B

Reputation: 525

Ok guys,

Just had a bit of a mere of a time with this one. I learned that i shouldn't use ENUMs where integers are the values. However We had years worth of data and i couldn't alter the database.

This bad boy worked (turning it into a character, then into a signed int).

CAST(CAST(`rating` AS CHAR) AS SIGNED) as value from table WHERE _id = 1

Upvotes: 2

Sachin Khanna
Sachin Khanna

Reputation: 9

use

SELECT SUM( IF( columnname >0, CAST( columnname AS CHAR ) , NULL ) ) AS vals
FROM `tableName`

Upvotes: 0

ain
ain

Reputation: 22749

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.

Upvotes: 6

Related Questions