ISSUE：Mysql将Enum转换为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:
我的数据库中有一个非常简单的评级系统,其中每个评级都存储为枚举('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.
是的,我建议更改列的类型。当您阅读有关枚举类型的文档(强烈建议不使用数字作为枚举值!)时,问题就变得清晰了 - 返回枚举项的索引,而不是枚举值本身。
更多相关文章
- Java MySQL数据类型对照
- MySQL数据类型中DECIMAL的作用和用法
- 可以在SELECT语句中嵌入描述语法吗?
- 如何从MySQL DBs的不同表中提取create语句?
- mysql查询一条工单时间需要10秒。优化sql语句得以解决。
- MySQL数据库储存bit类型的值报错
- MySql SELECT 语句执行顺序
- MySQL 数据（字段）类型
- MySQL 绿色版基本设置语句