Зберігання не цілих чисел в mysql - публікації
В MySQL існує безліч типів даних для зберігання чисел як цілих, так і з плаваючою крапкою.
Розглянемо існуючі формати:
Для цілих використовуються: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Ці типи використовуються для зберігання цілих чисел і займають від 1 до 8 байт пам'яті відповідно.
Для чисел з плаваючою точкою використовуються: FLOAT, DOUBLE. Головний мінус чисел з плаваючою точкою (або дійсних чисел) - принцип їх представлення і збереження. Такі числа зберігаються як ступеня двійки. Так як не всі десяткові числа мають точне уявлення у вигляді двійковій дробу, результати зберігання виходять закругленими. Навіть при елементарних математичних операціях можливі розбіжності. Більш докладно про це можна прочитати на Хабре
При зберіганні важливих даних (наприклад, банківський рахунок) важливий кожен символ навіть в останньому розряді після точки, тому використовувати числа з плаваючою точкою не можна.
Уникнути проблем зберігання і пошуку даних можна за допомогою типів: DECIMAL, NUMERIC.
До версії 5.03 ці типи зберігалися як рядки, при цьому використовувалося по одному символу рядка для кожного розряду числа (при необхідності, витрачалися додатково по одному символу на знак числа і на десятковий роздільник), також була можлива втрата точності через некоректну реалізації арифметики таких чисел.
Починаючи з 5.03, в MySQL з'явилася нова бібліотека для арифметики чисел з фіксованою точкою і змінився підхід до зберігання таких чисел. Тепер ціла частина і частина після точки зберігаються як 2 окремих, цілих числа. Оцінити витрата місця можна на підставі такої таблиці:
Розрахунок ведеться дуже просто: наприклад, нам треба зберегти 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
Наочний приклад, чому не варто довіряти важливі дані числах з плаваючою точкою. Але не варто думати, що DOUBLE - панацея. FLOAT і DOUBLE - однакові за принципом зберігання. Обидва типи однаково не точні, просто в DOUBLE неточності з'являються при великій кількості знаків.
Проблеми можуть спливти навіть в більш нешкідливих випадках. Припустимо, ми зберігаємо дані про баланс клієнта з точністю до копійок:
Якщо я вас ще не переконав відмовитися від FLOAT. приготуйтеся до проблем при пошуку.
Проблема з пошуком:
Якщо ж у вас назріла необхідність в пошуку по полю FLOAT - використовуйте кордону для пошуку.
Питання залишається відкритим. Як же зберігати дані?
Припустимо, перед нами стоїть завдання зберегти число з двома знаками після коми. Це можна реалізувати наступним чином:
1. FLOAT - забиваємо на точність, згадуємо про проблеми з пошуком і все одно використовуємо. Діапазон збережених чисел дуже великий: [-3,402823466E + 38. -1,175494351E-38], 0, і [1,175494351E-38. 3,402823466E + 38]. Займає 4 байти.
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 для доступу до даних, і всі операції для перекладу з цілого в дробове і назад будуть прозорі);
4. Якщо ви використовуєте версію MySQL до 5.0.3. настійно рекомендується оновитися до що-небудь свіжіше;