Хранение не целых чисел в MySQL

В MySQL существует множество типов данных для хранения чисел как целых, так и с плавающей точкой.
Рассмотрим существующие форматы:

Для целых используются: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Эти типы используются для хранения целых чисел и занимают от 1 до 8 байт памяти соответственно.

Для чисел с плавающей точкой используются: FLOAT, DOUBLE. Главный минус чисел с плавающей точкой (или вещественных чисел) – принцип их представления и хранения. Такие числа хранятся как степени двойки. Так как не все десятичные числа имеют точное представление в виде двоичной дроби, результаты хранения получаются округленными. Даже при элементарных математических операциях возможны расхождения. Более подробно про это можно прочитать на хабре

При хранении важных данных (к примеру, банковский счет) важен каждый символ даже в последнем разряде после точки, поэтому использовать числа с плавающей точкой нельзя.
Избежать проблем хранения и поиска данных можно при помощи типов: DECIMAL, NUMERIC.

До версии 5.03 эти типы хранились как строки, при этом использовалось по одному символу строки для каждого разряда числа (при необходимости, тратились дополнительно по одному символу на знак числа и на десятичный разделитель), также была возможна потеря точности из-за некорректной реализации арифметики таких чисел.

Начиная с 5.03, в MySQL появилась новая библиотека для арифметики чисел с фиксированной точкой и изменился подход к хранению таких чисел. Теперь целая часть и часть после точки хранятся как 2 отдельных, целых числа. Оценить расход места можно на основании следующей таблицы:

Оставшиеся цифры	Число байт
0	0
1-2	1
3-4	2
5-6	3
7-9	4

 

Расчет ведется очень просто: к примеру, нам надо сохранить DECIMAL(10,2) – целая часть имеет 8 цифр и занимает 4 байта, часть после точки – 2 цифры и занимает 1 байт. Итого на хранение будет потрачено 5 байт.

В ранних версиях MySQL типы DECIMAL и NUMERIC вели себя по-разному. SQL-стандарт требует, чтобы точность NUMERIC(M,D) была точно M цифр. Для DECIMAL(M,D) стандарт требует точности не меньше M цифр, но позволяет хранить больше. Это означает, что если бы вы захотели сохранить число 1,00005 в DECIMAL(6,4) и NUMERIC(6,4), то по стандарту NUMERIC сохранит 1.0000, а DECIMAL может сохранить 1.00005. Такое поведение при большом количестве математических операций может дать небольшую погрешность.

В последних версиях MySQL DECIMAL и NUMERIC оба типа имеют точность ровно M цифр.

Чтобы переварить все вышеизложенное, рассмотрим пару примеров (Server version: 5.0.77 Gentoo Linux).

Создадим таблицу test с 4 полями разных типов. Внесем одинаковые данные и посмотрим, как будет вести себя MySQL

CREATE TABLE test (a FLOAT, b DOUBLE, c DECIMAL (6,4), d NUMERIC(6,4)) ENGINE = MyISAM;
INSERT INTO test(a,b,c,d) VALUES (0.3, 0.3, 0.3, 0.3), (0.00003, 0.00003, 0.00003, 0.00003);
 
SELECT a, b, c, d FROM test;
+-------+-------+--------+--------+
| a     | b     | c      | d      |
+-------+-------+--------+--------+
|   0.3 |   0.3 | 0.3000 | 0.3000 |
| 3e-05 | 3e-05 | 0.0000 | 0.0000 |
+-------+-------+--------+--------+

SELECT a*1000, b*1000, c*1000, d*1000 FROM test;
+-------------------+--------+----------+----------+
| a*1000            | b*1000 | c*1000   | d*1000   |
+-------------------+--------+----------+----------+
|   300.00001192093 |    300 | 300.0000 | 300.0000 |
| 0.029999999242136 |   0.03 |   0.0000 |   0.0000 |
+-------------------+--------+----------+----------+

Наглядный пример, почему не стоит доверять важные данные числам с плавающей точкой. Но не стоит думать, что DOUBLE – панацея. FLOAT и DOUBLE – одинаковы по принципу хранения. Оба типа одинаково не точны, просто в DOUBLE неточности появляются при бОльшем числе знаков.

Проблемы могут всплыть даже в более безобидных случаях. Предположим, мы храним данные о балансе клиента с точностью до копеек:

CREATE TABLE test (money FLOAT) ENGINE = MyISAM;
INSERT INTO test(money) VALUES (123.34),(8691.00),(0.01);
 
SELECT money, 1+money, 1*money FROM test;
+--------+-----------------+--------------------+
| money  | 1+money         | 1*money            |
+--------+-----------------+--------------------+
| 123.34 | 124.33999633789 |    123.33999633789 |
|   8691 |            8692 |               8691 |
|   0.01 | 1.0099999997765 | 0.0099999997764826 |
+--------+-----------------+--------------------+

Если я вас еще не убедил отказаться от FLOAT , приготовьтесь к проблемам при поиске.

Проблема с поиском:

SELECT money FROM test WHERE money = 123.34;
Empty set (0.00 sec)

Если же у вас назрела необходимость в поиске по полю FLOAT – используйте границы для поиска.

SELECT money FROM test WHERE money > 123.33 AND money <123.35;
+--------+
| money  |
+--------+
| 123.34 |
+--------+
1 row in set (0.00 sec)

 

Вопрос остается открытым. Как же хранить данные?
Предположим, перед нами стоит задача сохранить число с двумя знаками после запятой. Это можно реализовать следующими способами:

1. FLOAT – забиваем на точность, вспоминаем про проблемы с поиском и все равно используем. Диапазон хранимых чисел очень большой: [-3,402823466E+38 .. -1,175494351E-38], 0, и [1,175494351E-38 .. 3,402823466E+38]. Занимает 4 байта.

2. DOUBLE – как уже упоминалось выше, ни чем не отличается от FLOAT, рано или поздно – всплывет погрешность. Диапазон больше, чем звезд во Вселенной: [-1,7976931348623157E+308 .. -2,2250738585072014E-308], 0, и [2,2250738585072014E-308 .. 1,7976931348623157E+308]. Занимает 8 байтов.

3. DECIMAL(X,2) – точное число с десятичной точкой. В зависимости от требуемого числа знаков до точки, меняем X. К примеру, нам надо хранить числа до 10 миллионов. Тогда инициализация поля будет иметь вид DECIMAL(9,2), поле – занимать 5 байтов и хранить числа в диапазоне [-9999999.99 .. 9999999.99].

4. INT – храним сразу в «копейках», а всеми преобразованиями занимаются хранимые процедуры, триггеры, PHP или другой язык программирования. При таком подходе мы сможем сохранить числа в диапазоне: [-21474836.48 .. 21474836.47], или при использовании UNSIGNED [0 .. 42949672.95]. Если вдруг нужен больший диапазон, всегда можно использовать 8 байтовый BIGINT.

 

Выводы:

1. Для хранения важных данных нельзя использовать типы FLOAT и DOUBLE;

2. Оптимальный способ для хранения – DECIMAL. В крайнем случае – INT или BIGINT (особенно, если у вас используется ORM или DAO для доступа к данным, и все операции для перевода из целого в дробное и обратно будут прозрачны);

3. DECIMAL и NUMERIC – идентичны;

4. Если вы используете версию MySQL до 5.0.3, настоятельно рекомендуется обновиться до что-нибудь посвежее;