Алексей В. Паутов
MySQL: руководство профессионала
Введение
Это не совсем книга. Просто по ходу работы и изучения пакета у меня накопилось немало заметок, которые я в конце концов собрал воедино и опубликовал с оглавлением и под единым названием.
Данные заметки относятся к версиям 4 и 5 пакета MySQL. По ходу текста особо отмечены места, относящиеся к специфической версии пакета. Необходимо также отметить, что эти заметки логически продолжают книгу MySQL: Руководство администратора и ориентированы на ту же аудиторию.
Данный материал подготовлен Паутовым Алексеем в рамках некоммерческого проекта RussianLDP:MySQL. При любом использовании ссылка на автора и проект обязательна!
Глава 1. Точная математика
MySQL 5.1 обеспечивает поддержку для точной математики: числовая обработка значения, которая приводит к чрезвычайно точным результатам, и высокой степени контроль над недопустимыми значениями. Точность основана на этих двух свойствах:
SQL-режимы, которые управляют тем, как строг сервер относительно принятия или отклонения недопустимых данных.
MySQL-библиотека для арифметики с фиксированной запятой.
Эти свойства имеют несколько импликаций для числовых операций:
Точные вычисления : для получения точного значения вычисления не представляют ошибки с плавающей запятой. Вместо этого, используется высокая точность. Например, число типа .0001 обрабатывается как точное значение, а не как приближенное, и суммирование этого 10000 раз производит результат точно 1, а не значение, близкое к 1.
Четкое поведение округления : для чисел высокой точности результат ROUND() зависит только от параметра, а не от относящихся к окружению факторах типа того, как основная библиотека C работает.
Независимость от платформы: операции на точных числовых значениях дают тот же самый результат на различных платформах, типа Windows и Unix.
Контроль над обработкой недопустимых значений: переполнение и деление на нуль обнаруживаются и могут обрабатываться как ошибки. Например, Вы можете обрабатывать значение, которое является слишком большим для столбца, как ошибку, а не как наличие значения, усеченного так, чтобы попасть внутрь диапазона типа данных столбца. Точно так же Вы можете обрабатывать деление на нуль как ошибку, а не как операцию, которая производит результат NULL. Выбор подхода определен установкой переменной системы sql_mode.
Важный результат этих свойств: MySQL 5.1 обеспечивает высокую степень согласия со стандартом SQL.
Следующее обсуждение покрывает несколько аспектов того, как работает высокая точность (включая возможные несовместимости со старыми прикладными программами). В конце есть некоторые примеры.
1.1. Типы числовых значений
Контекст математической точности для операций с точным значением включает типы данных с точным значением (DECIMAL и целочисленные типы) и числовые литералы с точным значением. Типы данных с приблизительным значением и числовые литералы все еще обработаны как числа с плавающей запятой.
Числовые литералы с точным значением имеют целочисленную, дробную части или обе. Они могут быть со знаком или без него. Примеры: 1, .2, 3.4, -5, -6.78, +9.10.
Числовые литералы с приблизительным значением представляются в экспоненциальном формате с мантиссой и экспонентой. Любая из частей или обе могут иметь знак. Примеры: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3.
Два числа, которые выглядят одинаково, могут не быть оба точными или приблизительными. Например, 2.34 представляет собой число с точным значением (с фиксированной запятой), в то время как 2.34E0 задает число с приблизительным значением (с плавающей запятой).
Тип данных DECIMAL является типом с фиксированной запятой, и вычисления точны. В MySQL тип DECIMAL имеет несколько синонимов: NUMERIC, DEC, FIXED. Целочисленные типы также типы с точным значением.
Типы данных FLOAT и DOUBLE являются типами с плавающей запятой, и вычисления приблизительны. В MySQL типы, которые являются синонимичными с FLOAT или DOUBLE, это DOUBLE PRECISION и REAL.
1.2. Изменения типа данных DECIMAL
Этот раздел обсуждает характеристики типа данных DECIMAL (и синонимов) в MySQL 5.1, со специфическим отношением к следующим темам
Максимальное число цифр
Формат хранения
Требования к памяти
Ненормативное MySQL расширение к верхнему диапазону столбцов DECIMAL
Возможные несовместимости с прикладными программами, которые написаны для старых версий MySQL, отмечены в этом разделе.
Синтаксис объявления для столбца DECIMAL: DECIMAL(M,D). Диапазоны значений для параметров в MySQL 5.1 следующие:
M: максимальное число цифр (точность). Это имеет диапазон от 1 до 65. Старые версии MySQL позволяли диапазон от 1 до 254.
D: число цифр направо от десятичной точки (масштаб). Это имеет диапазон от 0 до 30 и должно быть не больше, чем M.
Максимальное значение 65 для M означает, что вычисления на значениях DECIMAL точны до 65 цифр. Это ограничение точности в 65 цифр также применяется к числовым литералам с точным значением, так что оно задает максимальный диапазон таких литералов. В старых версиях MySQL десятичные значения могли иметь до 254 цифр. Однако, вычисления были выполнены, используя числа с плавающей запятой и таким образом были приблизительны, не точны.
Значения для столбцов DECIMAL в MySQL 5.1 сохранены, используя двоичный формат, который упаковывает девять десятичных цифр в четыре байта. Требования к памяти для целочисленных и дробных частей каждого значения определены отдельно. Каждые девять цифр требуют четырех байт, и любые цифры сверх этого требуют некоторой доли четырех байтов. Например, DECIMAL(18,9) имеет девять цифр с обеих сторон десятичной точки, так что целочисленная и дробная части требуют четырех байтов каждая. Столбец DECIMAL(20,10) имеет по десять цифр с обеих сторон десятичной точки. Каждая часть требует четырех байтов для девяти из цифр и одного байта для остающейся цифры.
Память, требуемая для остающихся цифр, показана в следующей таблице:
Остающиеся цифры | Число байтов
0 | 0
1 | 1
2| 1
3 | 2
4 | 2
5 | 3
6 | 3
7 | 4
8 | 4
9 | 4
В отличие от старых версий MySQL (до 5.0.3), столбцы DECIMAL в MySQL 5.1 не сохраняют символ + или цифры 0 в начале значения. Если Вы вставляете +0003.1 в столбец DECIMAL(5,1), это сохранено как 3.1. Прикладные программы, которые полагаются на старое поведение, должны измениться, чтобы обработать это изменение.
Столбцы DECIMAL в MySQL 5.1 не позволяют значения больше, чем диапазон, подразумеваемый по определению столбца. Например, столбец DECIMAL(3,0) поддерживает диапазон от -999 до 999. А столбец DECIMAL(M,D) позволяет M цифр налево от десятичной точки. Это не совместимо с прикладными программами, полагающимися на старые версии MySQL, которые разрешали сохранять дополнительную цифру вместо знака +.
SQL-стандарт требует, чтобы точность NUMERIC(M,D) была точно M цифр. Для DECIMAL(M,D) стандарт требует точности по крайней мере M цифр, но позволяет больше. В MySQL DECIMAL(M,D) и NUMERIC(M,D) то же самое, и оба типа имеют точность ровно M цифр.
1.3. Обработка выражений
Числа с точным значением используются как даны всякий раз, когда возможно. Например, числа в сравнениях используются точно как даны, без изменения в значении. В строгом SQL-режиме для INSERT в столбец с точным типом данных (DECIMAL или целое число), значение вставлено с точным значением, если оно внутри диапазона столбца. Когда значение получено, оно должно быть таким же как то, что было вставлено. Без строгого режима допустимо усечение для INSERT.
Обработка числового выражения зависит от значений, которое выражение содержит:
Если любые приблизительные значения присутствуют, выражение оценено приблизительно, используя арифметику с плавающей запятой.
Если никакие приблизительные значения не присутствуют, выражение содержит только точные значения. Если любое точное значение содержит дробную часть (значение после десятичной точки), выражение оценено, используя точную арифметику DECIMAL и имеет точность 65 цифр.
Иначе, выражение содержит только целочисленные значения. Выражение точно и оценено, используя целочисленную арифметику и имеет точность BIGINT (64 бита).
Если числовое выражение содержит любые строки, они преобразованы в значения с плавающей запятой двойной точности, и выражения приблизительны.
На вставки в числовые столбцы воздействует SQL-режим, который управляется переменной системы sql_mode. Следующее обсуждение упоминает строгий режим (выбранный значениями режима STRICT_ALL_TABLES или STRICT_TRANS_TABLES) и ERROR_FOR_DIVISION_BY_ZERO. Чтобы включить все ограничения, Вы можете просто использовать режим TRADITIONAL, который включает строгие значения режима и ERROR_FOR_DIVISION_BY_ZERO: