Float Not Correct in MySQL

Float not correct in MySQL

Floats only have a certain level of precision, you may be going beyond how precise a float data type can be. Try using a DOUBLE instead.

float not working in mysql database

From what I know float type isn't precise. It doesn't show you that actual value so 1.1 that you saved may not be the actual value stored. Trying setting your field as decimal and give it a length of say, 10,1 where 10 is the maximum number of digits (the precision) and 1 is the number of digits to the right of the decimal point (the scale). It should work doing query like stuff='1.1' or stuff=1.1.

Wrong value returned from mysql float

If you want to store exact values, you'd use the DECIMAL data types.

By manual of FLOAT:

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values

The thing to mention here is approximation.

You can read more on floats here: http://dev.mysql.com/doc/internals/en/floating-point-types.html

Sql not saving correct float values

First of all, float is unreliable and you should use DECIMAL(x,y) to store your coordinates instead.

Second thing, even when using float, your definition float(x,x) is incorrect. It basically means that there are NO digits before the decimal delimiter and x decimal digits. When trying to put 39.888, you should get an error:

ERROR 1264 (22003) at line x: Out of range value for column 'Latitutde'

On some configurations it's only a warning and it tries to put the number nearest to allowed. That would be 0.99999(9) or -0.99999(9). But, as floats are inaccurate, it reads it as 1.0 and -1.0.

It should be float(x,y) where x is total allowed number of digits, and y is the number of decimal digits.

---

Long answer short:

For example: Use DECIMAL(8,6) so 39.809484 would work - it has 8 digits total, and 6 decimals.

Normally coordinates should be stored as:

lat - DECIMAL(10, 8) as it can be in range of -90 to +90 degrees

lng - DECIMAL(11, 8) as it can be in range of -180 to +180 degrees

Float value stored in MySQL keeps getting messed up

If you need a value to be precise, store it as an exact data type such as DECIMAL(17,7), which would provide the same range as FLOAT(10,7). The only down side is that the DECIMAL will take up more disk space than the equivilant FLOAT, however this is trivial compared to correcting for floating point errors where precision is a concern.

http://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html

For more information on floating point number issues, the following may be worth a read

http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html

MySQL float rounding

You need the DOUBLE data type here. FLOAT only allows about seven significant decimal digits of precision. The numbers represented by the text strings 1399999.05 and 1399999.00 are indistiguishable from each other when converted to IEEE 32-bit FLOAT values.

MySQL ignores the precision specification for FLOAT, DOUBLE, INT (and TINYINT, SMALLINT, BIGINT) data types. Instead it uses the native data type. MySQL honors the precision specification for DECIMAL data.

In your case your precision spec is (12,2).

Please consider reading this: What Every Computer Scientist Should Know About Floating Point Numbers.

And, for a hint about what can go wrong when you lose precision you need, read this.

How to avoid floating-point arithmetic in a MySQL query?

How will the 100 affect the query? Will this somehow cause MySQL to do floating-point arithmetic? If so, how can I avoid it?

100 is an exact-value numeric literal, here we have decimal divided by decimal times decimal(integer) => result is still decimal(but with higher precision).

Numeric Literals

Exact-value numeric literals have an integer part or fractional part, or both. They may be signed. Examples: 1, .2, 3.4, -5, -6.78, +9.10.

Approximate-value numeric literals are represented in scientific notation with a mantissa and exponent. Either or both parts may be signed. Examples: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3.

Two numbers that look similar may be treated differently. For example, 2.34 is an exact-value (fixed-point) number, whereas 2.34E0 is an approximate-value (floating-point) number.

Example:

CREATE TABLE t(A DECIMAL(9,2), B DECIMAL(9,2));

CREATE TABLE r1 AS SELECT SUM(A) / SUM(B) AS result FROM t;

SELECT column_name, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'r1';
+--------------+---------------+
| COLUMN_NAME  |  COLUMN_TYPE  |
+--------------+---------------+
| result       | decimal(37,6) |
+--------------+---------------+

CREATE TABLE r2 AS SELECT SUM(A) / SUM(B) * 100 AS result FROM t;

SELECT column_name, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'r2';
+--------------+---------------+
| COLUMN_NAME  |  COLUMN_TYPE  |
+--------------+---------------+
| result       | decimal(40,6) |
+--------------+---------------+

Now using approximate value numeric literal 100E0:

CREATE TABLE r3 AS SELECT SUM(A) / SUM(B) * 100E0 AS result FROM t;

SELECT column_name, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'r3';
+--------------+-------------+
| COLUMN_NAME  | COLUMN_TYPE |
+--------------+-------------+
| result       | double      |
+--------------+-------------+

db<>fiddle demo

---

It is worth noting that some operations may look exactly the same but return different data types. For example:

CREATE TABLE r4 AS SELECT POWER(A,2) AS result, A*A AS result2 FROM t;

SELECT column_name, column_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'r4';
+--------------+---------------+
| COLUMN_NAME  |  COLUMN_TYPE  |
+--------------+---------------+
| result       | double        |
| result2      | decimal(18,4) |
+--------------+---------------+

---

Related Topics