Difference in FLOAT(M,D) behavior in versions 4.0 and 5.1?

Question

MySQL

I am transferring the database from the server to
4.0.24-standard-log (it is on Red Hat 9.0)
to the server
5.1.50-log FreeBSD port: mysql-server-5.1.50
And I ran into such a problem that some fields are unloaded as FLOAT[8 ,6], and all values ​​are truncated to 100.000000 when pasted. Although in the old version there were also large values. Conducted an experiment:
In version 4.0

mysql> create table test (amount float(8,6) NOT NULL default '0.000000');
Query OK, 0 rows affected (0.02 sec)

mysql>  insert into test values(1000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+-------------+
| amount      |
+-------------+
| 1000.000000 |
+-------------+
1 row in set (0.00 sec)

in version 5.1

mysql> create table test (amount float(8,6) NOT NULL default '0.000000');
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values(1000);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
+------------+
| amount     |
+------------+
| 100.000000 |
+------------+
1 row in set (0.00 sec)

Here you can see that a warning is generated when inserting.
The documentation for both versions is word for word,
dev.mysql.com/doc/refman/4.1/en/floating-point-typ...
dev.mysql.com/doc/refman/5.1/en/floating-point-typ ...
and it seems like if D=6, then there are 2 digits left for the integer part and everything works correctly in version 5.1, but what's the bug then in version 4.0 it works differently?
Apparently, we will have to divide the dump into structure and data, edit the structure dump, removing these unfortunate (8,6) from FLOAT everywhere, and load it in parts ...
Are there any other suggestions?
Maybe you know other pitfalls that I haven't come across yet?
And another question on the same topic. In this base, the programmers used fields of the TIMESTAMP type, and then in the code they used selections of the type
where SUBSTRING(timefrom,1,6)=201012
but the trouble is that in version 4.0 these dates are displayed in the format "20121203", and in version 5.1 in the format "2012-12-03", so the above code does not work. So far, the only solution I see is to change timefrom to timefrom+0 in the code, then it works correctly, but I will hesitate to change throughout the code, especially since it is difficult to organize a search for all such places. Does anyone know how to make it easier? Maybe you can somehow change the behavior of TIMESTAMP for a particular database?

Answer 1

use FLOAT(9,6)

Answer 2

I recently ran into a similar problem. This article helped . If FLOAT in your database is used to store "business" values ​​(money, etc.) then use the DECIMAL type. In all other cases, use DOUBLE.
FLOAT should be avoided at all costs. it is more prone to calculation errors. “Funny” is the situation when, after adding an integer value to the database, when selecting the same record, you get some units in the ninth digit.

Answer 3

in the case of decimal this is unlikely

o_0?
If we are talking about how to change FLOAT (8,6) to just FLOAT, then the command line is like this

mysqldump | sed (меняем  FLOAT(8,6) на FLOAT ) | mysql

if it's Windows, then it's better to install cygwin. Sorry if my advice is like "thank you, cap", it's just that it's not very clear from your question what exactly is the problem with replacing FLOAT(8,6) with FLOAT

Answer 4

Not for the sake of argument, but purely for sharing information :)

CREATE TABLE digits (
   float_col FLOAT DEFAULT NULL,
   decimal_col DECIMAL(20,18) DEFAULT NULL,
   double_col DOUBLE DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=cp1251

INSERT INTO digits VALUES( ( @pi := 3.141592653589793238 ) / 3, @pi / 3, @pi / 3 );
UPDATE digits SET float_col = float_col * 3 - @pi, decimal_col = decimal_col * 3 - @pi, double_col = double_col * 3 - @pi;

After INSERT in my database (5.5.24) it turns out

float_col decimal_col double_col          
1.0472 1.047197551196597746 1.0471975511965979

After the UPDATE… damn sorcerer!.. legs in my mouth!.. I didn’t expect…

float_col decimal_col double_col             
0.0000000874228 0.000000000000000000 4.440892098500626e-16

Maybe DECIMAL? :)
Apparently, the secret is that For DECIMAL columns, MySQL performs operations with a precision of 65 decimal digits, which should solve most common inaccuracy problems.

Answer 5

$t_date = "INSERT INTO topicer VALUES ('','".$a."','".$a."','".$a."','".$class."','" .$a."','".$response."')";
this line seems suspicious, naim. throw off the mistakes