不使用 CAST 的 VARCHAR 字段的 mysql SUM [英] mysql SUM of VARCHAR fields without using CAST
问题描述
当SUM用于查询MySql数据库中VARCHAR类型的字段时,SUM是否自动将其转换为数字?我尝试使用
When SUM is used in query on field of type VARCHAR in MySql database, does SUM automatically convert it into number ? I tried this by using
SELECT SUM(parametervalue) FROM table
它显示 MySql 返回总和,尽管我希望它抛出一个错误,因为参数值"字段是 VARCHAR 类型
and it reveals that MySql returns the sum although I expected to throw it an error as "parametervalue" field is of VARCHAR type
推荐答案
MySQL 对数字上下文中的字符串进行静默转换.因为它需要 sum()
的数字,所以 MySQL 只是使用字符串中的前导数字"进行转换.请注意,这包括小数点、减号,甚至表示科学记数法的 e
.所以,'1e6'
被解释为一个数字.
MySQL does silent conversion for a string in a numeric context. Because it expects a number for the sum()
, MySQL simply does the conversion using the leading "numbers" from a string. Note that this include decimal points, minus sign, and even e
representing scientific notation. So, '1e6'
is interpreted as a number.
在代码中,我个人会通过添加 0
来明确转换:
In code, I personally would make the conversion explicit by adding 0
:
SELECT SUM(parametervalue + 0) FROM table
具有讽刺意味的是,如果字符串不是数字格式,cast()
可能会返回错误,但在这种情况下不会返回错误.
Ironically, the cast()
might return an error if the string is not in a numeric format, but this doesn't return an error in that case.
这篇关于不使用 CAST 的 VARCHAR 字段的 mysql SUM的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!