不使用 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屋！