SUM的数据类型导致MySQL [英] Datatype of SUM result in MySQL

问题描述

使用SUM时，我将MySQL查询的结果转换为Java类时遇到了一些问题。

I'm having a bit of a problem with converting the result of a MySQL query to a Java class when using SUM.

在MySQL中执行简单的SUM时

When performing a simple SUM in MySQL

SELECT SUM(price) FROM cakes WHERE ingredient = 'chocolate';

价格是一个整数，它出现 SUM 有时会返回一个字符串，有时会返回一个整数，具体取决于JDBC驱动程序的版本。

with price being an integer, it appears that the SUM sometimes returns a string and sometimes an integer, depending on the version of the JDBC driver.

显然服务器确实告诉JDBC驱动程序 SUM 的结果是一个字符串，JDBC驱动程序有时方便地将其转换为整数。 （参见 Marc Matthews的解释）。

Apparently the server does tell the JDBC driver that the result of SUM is a string, and the JDBC driver sometimes 'conveniently' converts this to an integer. (see Marc Matthews' explanation).

Java代码使用了一些 BeanInfo 和自省用查询结果自动填充（列表）bean。但是，如果部署应用程序的服务器之间的数据类型不同，这显然无效。

The Java code uses some BeanInfo and Introspection to automagically fill in a (list of) bean(s) with the result of a query. But this obviously can't work if the datatypes differ between servers where the application is deployed.

我不在乎我得到一个字符串或整数，但我我希望始终拥有相同的数据类型，或者至少事先知道我将获得哪种数据类型。

I don't care wether I get a string or an integer, but I'd like to always have the same datatype, or at least know in advance which datatype I'll be getting.

有没有办法知道Java代码中的MySQL SUM 将返回哪种数据类型？或者有没有人知道一些更好的方法来解决这个问题？

Is there some way to know which datatype will be returned by a MySQL SUM from within the Java code? Or does anyone know some better way to deal with this?

推荐答案

这只是猜测，但可能会强制转换为整数MySQL始终告诉它是一个整数。

This is just a guess, but maybe casting to integer will force MySQL to always tell it is an integer.

SELECT CAST(SUM(price) AS SIGNED) FROM cakes WHERE ingredient = 'marshmallows';

这篇关于SUM的数据类型导致MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！