Разница в поведении FLOAT(M,D) в версиях 4.0 и 5.1?

Question

[ddakapoh]

Переношу базу с сервера на

4.0.24-standard-log (стоит на Red Hat 9.0)

на сервер

5.1.50-log FreeBSD port: mysql-server-5.1.50


И столкнулся с такой пепякой, что некоторые поля выгружаются как FLOAT[8,6], и при вставке все значений обрезаются до 100.000000. Хотя в старой версии там были и большие значения. Провел эксперимент:


В версии 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)

в версии 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)

тут видно, что генерится варнинг при вставке.


В документации по обоим версиям слово в слово,
dev.mysql.com/doc/refman/4.1/en/floating-point-typ...
dev.mysql.com/doc/refman/5.1/en/floating-point-typ...

и вроде как если D=6, то на целую часть остается 2 разряда и в версии 5.1 все правильно получается, но какого бага тогда в версии 4.0 оно работает иначе?


Видимо придется делить дамп на структуру и данные, дамп структуры править, убирая везде у FLOAT эти несчастные (8,6), и загружать по частям…


Есть ли другие предложения?

Может знаете другие подводные камни, на которые я еще не натолкнулся?


И еще вопрос по той же теме. В этой базе горе программисты использовали поля типа TIMESTAMP, и потом в коде юзали выборки типа
where SUBSTRING(timefrom,1,6)=201012

но беда в том, что в версии 4.0 эти даты отображаются в формате «20121203», а в версии 5.1 в формате «2012-12-03», поэтому вышеприведенный код нифига не работает. Пока решение вижу только менять в коде timefrom на timefrom+0, тогда работает корректно, но менять я заколебусь по всему коду, тем более что сложно организовать поиск всех таких мест. Может кто знает как попроще можно сделать? Может можно как-то изменить поведение TIMESTAMP для отдельной базы данных?

Answer 1

[Андрей Буров]

используйте FLOAT(9,6)

Comments

[Андрей Буров]

формат timestamp изменить можно, но только для всего сервера (в my.cnf datetime_format = "%Y%m%d %H:%i:%s")
Но возникнет проблема, текущие базы (включая базу «mysql») имею данные в формате "%Y-%m-%d %H:%i:%s" и mysql не запустится… придется долго повозиться чтоб запустить.

PS: поменять «SUBSTRING(timefrom» на «SUBSTRING(timefrom+0» можно с помощью sed и регулярных выражений.

[ddakapoh]

Если бы везде одинаково было прописано, то да, а то ведь где-то еще «timefrom like '201212%'» можно встретить, а вообще по базе много где используется timestamp, поэтому все равно придется как-то руками искать.

За наводку на настройку формата спасибо, хотя в данном случае не поможет, ибо база переносится на существующий кластер, где пачка других баз.

Answer 2

[KEKSOV]

Недавно столкнулся с похожей проблемой. Помогла вот эта статья. Если FLOAT в вашей базе используется для хранения «бизнес» значений (деньги и т.п.) то используйте тип DECIMAL. Во всех остальных случаях используйте DOUBLE.

FLOAT нужно всячески избегать, т.к. он в большей степени подвержен ошибкам вычисления. «Забавно» выглядит ситуация, когда после добавления в базу целого значения при выборке этой же записи получаешь какие-то единицы в девятом знаке.

Comments

[ddakapoh]

Да какой тип использовать надо я и сам знаю, проблема как проще исправить, ну и так, осветить этот камень. Заметил ведь случайно, обходил другие камни, стал проверять, в старой версии счет на 3000 рублей, а в новой на рубль, тут-то я и присел. А float был использован вполне целенаправленно, там подневно снимается часть абонентской платы, и если тариф не менялся, то в сумме должна выйти искомая абонентская плата, в случае с decimal это маловероятно.

Answer 3

[KEKSOV]

в случае с decimal это маловероятно

o_0?

Если речь о том, чем поменять FLOAT(8,6) на просто FLOAT, то командная строка вот такая

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

если это винда, то лучше поставить cygwin. Простите, если мой совет из разряда «спасибо, кэп», просто из вашего вопроса не очень понятно, в чем именно состоит проблема с заменой FLOAT(8,6) на FLOAT

Comments

[ddakapoh]

Спасибо, седом я и сам нарублю что угодно как угодно. Вопрос я вроде озвучил внятно и даже специально выделил, еще раз

Есть ли другие предложения?
Может знаете другие подводные камни, на которые я еще не натолкнулся?

[KEKSOV]

Я уже тоже об этом этом написал — используйте DECIMAL. На сайте mysql об этом подробно написано.

[ddakapoh]

Так, использовать я буду просто float, чтобы не резались значения. Для Вас могу на пальцах показать. Вот абонентка 100 рублей, раз в сутки снимается часть. Для месяца с 31 днем эта часть будет составлять 3,225806451612903. Если например определить DECIMAL[10,2], то будет сохранено 3.23, тогда в сумме получится не 100 рублей, как планировалось, а 100.13. Если увеличим точность до [10,3] например и сохраним 3.226, то в сумме получим 100.006, что в округлении также даст лишнюю копейку. Ну и тд. А float без указания точности — это число с плавающей запятой, поэтому для данного случая оно больше подходит.
Вопрос про другие предложения предполагал… а хрен знает что он предполагал, видимо я надеялся, что есть какие-то возможности, о которых я даже не подозреваю :) Ну и осветить проблему хотелось, вдруг кому пригодится когда-нибудь, я про эту багу нагуглить ничего не смог, везде как раз обсуждаются вопросы различия float и decimal, а это и школьнику ясно должно быть.

Answer 4

[KEKSOV]

Не спора ради, а сугубо для дележа информацией :)

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;

После INSERT у меня в базе (5.5.24) получается

float_col  decimal_col           double_col          
1.0472     1.047197551196597746  1.0471975511965979

После UPDATE… чертов колдун!.. в рот мне ноги!.. сам не ожидал…

float_col        decimal_col           double_col             
0.0000000874228  0.000000000000000000  4.440892098500626e-16

Может все-таки DECIMAL? :)

Видимо, секрет в том, что For DECIMAL columns, MySQL performs operations with a precision of 65 decimal digits, which should solve most common inaccuracy problems.

Comments

[KEKSOV]

Заморочился я с Вашей задачкой по-полной программе…

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

INSERT INTO digits VALUES( ( @price := 100.0 ), @price, @price );
UPDATE digits SET float_col = float_col - ( @oneDayPrice := 100 / 31 ), decimal_col = decimal_col - @oneDayPrice, double_col = double_col - @oneDayPrice;

31 итерация UPDATE дает вот такой результат:

float_col     decimal_col                  double_col                  
-0.000018573  0.0000000190000000000000000  0.000000019000062856378008

По большому счету, ROUND наше все :)

PS. Спасибо Вашему вопросу — нашел у себя в одном из скриптов косяк с точностью вычислений…

[ddakapoh]

float — 4 байта
decimal(30,25)… что-то затрудняюсь даже сколько, но по идее больше, ведь 4 байта дадут максимум 10 разрядов.

а точности float должно хватить для деления на 28,29,30 и 31. Во всяком случае в старой версии я не имел проблем. (а вот с делением на 1024*1024 имел, и там round — действительно оказался нашим всем)

А эксперимент у Вас интересный на самом деле получился.