SUM和CONCAT在MySql中的怪异行为 [英] Weird behaviour of SUM and CONCAT in MySql

问题描述

如果我想对MySQL中的特定数字列求和，我会这样做

If I want to make a sum of a specific numeric column in MySQL, I do

SELECT SUM(MyColumn) FROM MyTable WHERE 1;

例如返回数字100.

但是我想在总和值之前添加一些文本，所以我这样做

But I'd like to prepend some text to the sum value, so I do

SELECT CONCAT('Sum is: ',SUM(MyColumn)) FROM MyTable WHERE 1;

但没有得到Sum is: 100，而是得到了类似546573743a20343030的东西.

but instead of getting Sum is: 100 I get something like 546573743a20343030.

这是错误还是功能?我在做什么错了?

Is this a bug or a feature? What am I doing wrong?

更新

SELECT CONCAT('Sum is: ',CAST(SUM(MyColumn) AS varchar(20))) FROM MyTable WHERE 1;

投射到varchar不起作用:出现SQL语法错误.

Casting to varchar doesn't work: getting SQL syntax error.

推荐答案

我的问题下方的注释中建议了 FreshPrinceOfSO ，MySQL服务器无法处理对varchar的强制转换.

As FreshPrinceOfSO suggested in the comments below my question, MySQL server doesn't handle casts to varchar.

所以即使查询

SELECT CONCAT('Sum is: ',CAST(SUM(MyColumn) AS varchar(20))) FROM MyTable WHERE 1;

导致语法错误，而是强制转换为char即可:

results in syntax error, casting to char instead works just fine:

SELECT CONCAT('Sum is: ',CAST(SUM(MyColumn) AS char(20))) FROM MyTable WHERE 1;

这篇关于SUM和CONCAT在MySql中的怪异行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！