If I want to make a sum of a specific numeric column in MySQL, I do
如果我想在MySQL中对特定数字列求和,我会这样做
SELECT SUM(MyColumn) FROM MyTable WHERE 1;
This returns for example number 100.
这返回例如数字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;
but instead of getting Sum is: 100 I get something like 546573743a20343030.
但不是得到总和是:100我得到像546573743a20343030。
Is this a bug or a feature? What am I doing wrong?
这是一个错误还是一个功能?我究竟做错了什么?
UPDATE
SELECT CONCAT('Sum is: ',CAST(SUM(MyColumn) AS varchar(20))) FROM MyTable WHERE 1;
Casting to varchar doesn't work: getting SQL syntax error.
转换为varchar不起作用:获取SQL语法错误。
1 个解决方案
#1
8
As FreshPrinceOfSO suggested in the comments below my question, MySQL server doesn't handle casts to varchar.
正如FreshPrinceOfSO在我的问题下面的评论中提出的,MySQL服务器不处理对varchar的强制转换。
So even though the query
所以即使查询
SELECT CONCAT('Sum is: ',CAST(SUM(MyColumn) AS varchar(20))) FROM MyTable WHERE 1;
results in syntax error, casting to char instead works just fine:
导致语法错误,转换为char而不是很好:
SELECT CONCAT('Sum is: ',CAST(SUM(MyColumn) AS char(20))) FROM MyTable WHERE 1;
#1
8
As FreshPrinceOfSO suggested in the comments below my question, MySQL server doesn't handle casts to varchar.
正如FreshPrinceOfSO在我的问题下面的评论中提出的,MySQL服务器不处理对varchar的强制转换。
So even though the query
所以即使查询
SELECT CONCAT('Sum is: ',CAST(SUM(MyColumn) AS varchar(20))) FROM MyTable WHERE 1;
results in syntax error, casting to char instead works just fine:
导致语法错误,转换为char而不是很好:
SELECT CONCAT('Sum is: ',CAST(SUM(MyColumn) AS char(20))) FROM MyTable WHERE 1;