MySQL 中 SUM 结果的数据类型 [英] 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';

price 是一个整数，看起来 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.

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

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';

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