Re: useCursorFetch=true impacts getFloat() values.
Hi Zhang,
This is so because of the way MySQL encodes numeric values in the MySQL protocol.
When you set `useCursorFetch=true`, `useServerPrepStmts` is consequently also set to `true` -- check the documentation for `useCursorFetch` in
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html.
Setting `useServerPrepStmts=true` internally causes the MySQL protocol to change from "Text protocol" to "Binary Protocol". Consult
https://dev.mysql.com/doc/internals/en/text-protocol.html and
https://dev.mysql.com/doc/internals/en/prepared-statements.html if you want to learn more about this.
So, in the "Text Protocol", all numeric values are returned to the client in decimal format, i.e., those values are returned as the data type definition dictates. In your example, FLOAT adjusts the fractional part according to number precision, a big number like 358294.648941 then becomes 358295 after rounding (the ".0" is added by Java). But in the "Binary Protocol" the same number is returned in binary format, using 4 Bytes, more precisely in IEEE 754 single precision format. As such, the client gets more information about the stored number and so being able to decode it up to 358294.66 as Java float.
You have some options to try to minimize such behavior with these big numbers. Here are some:
- Use DOUBLE instead of FLOAT.
- Use DECIMAL instead of FLOAT.
- Use FLOAT with higher binary precision, e.g., FLOAT(32)
- Use FLOAT with decimal precision, e.g., FLOAT(25,4)
Please consult the following pages to help you better understand this topic:
-
https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html
-
https://dev.mysql.com/doc/refman/8.0/en/problems-with-float.html
-
https://dev.mysql.com/doc/refman/8.0/en/precision-math.html
IHTH