mysql在varchar字段中使用数字val的最大行为

We have a database table that has keys and values where the value can be anything in a varchar field. The values are for attributes about an item and one attribute is price.

我们有一个数据库表,其中包含键和值,其值可以是varchar字段中的任何值。值是关于项目的属性,一个属性是价格。

To make a select field for prices, I was using the Max() function to find the greatest value in the value column which seemed to work.

要为价格创建一个选择字段,我使用Max()函数在值列中找到似乎有效的最大值。

Then when we got prices over £100, they started to not get returned as the max value. I under stand that this is because it is a string value and not numeric.

然后当我们得到超过100英镑的价格时,他们开始没有作为最大值返回。我认为这是因为它是一个字符串值而不是数字。

The confusion comes when running a command like select max(value) from attributes where value > 100 because now the statement recognises that 101 is > 100 but 99 is not so returns 101 as the max value, however without the where value > 100 clause, 99 is treated as > 101. Why does the > 100 clause work as a numeric comparison but max does not?

当从值> 100的属性中运行诸如select max(value)之类的命令时会产生混淆,因为现在语句识别出101> 100但99不是因此返回101作为最大值,但是没有where value> 100子句, 99被视为> 101.为什么> 100子句作为数字比较,但max不起作用?

Is there a reason that this happens?

有这种情况发生的原因吗?

1 个解决方案

#1

Do not mixed varchar with numeric,
idea solution is only stored numeric for used of max aggregate function,
alternatively

不要将varchar与数字混合,想法解决方案只存储数字用于max aggregate函数,或者

max( cast(value as unsigned) )

When you are doing a MAX, is a cast to string.
When you are doing comparison, is a cast to numeric.

当你做MAX时,是一个转换为字符串。当你进行比较时,是一个转换为数字。

Reason?
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_max
(it returns the maximum string value)

原因? http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_max(它返回最大字符串值)

#1