ISSUE:Mysql将Enum转换为Int

时间:2021-10-03 16:00:41

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:

我的数据库中有一个非常简单的评级系统,其中每个评级都存储为枚举('1',' - 1')。要计算我尝试使用此语句的总数:

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.

这适用于正1但由于某种原因-1被解析为2。

Can anyone help or offer incite?

任何人都可以帮助或提供煽动?

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

或者我应该放弃并将列更改为SIGNED INT(1)?

5 个解决方案

#1


3  

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.

是的,我建议更改列的类型。当您阅读有关枚举类型的文档(强烈建议不使用数字作为枚举值!)时,问题就变得清晰了 - 返回枚举项的索引,而不是枚举值本身。

#2


14  

this is what you want

这就是你想要的

select enum+0 as enum

#3


11  

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

只能在MySQL中为枚举转换为int:

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

#4


0  

use

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

#5


0  

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.

只是有一点时间与这一个。我了解到我不应该使用整数是值的ENUM。但是,我们有多年的数据,我无法改变数据库。

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

这个坏男孩工作(把它变成一个角色,然后变成一个签名的int)。

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

#1


3  

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.

是的,我建议更改列的类型。当您阅读有关枚举类型的文档(强烈建议不使用数字作为枚举值!)时,问题就变得清晰了 - 返回枚举项的索引,而不是枚举值本身。

#2


14  

this is what you want

这就是你想要的

select enum+0 as enum

#3


11  

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

只能在MySQL中为枚举转换为int:

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

#4


0  

use

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

#5


0  

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.

只是有一点时间与这一个。我了解到我不应该使用整数是值的ENUM。但是,我们有多年的数据,我无法改变数据库。

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

这个坏男孩工作(把它变成一个角色,然后变成一个签名的int)。

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