Сообщить об ошибке.

MySQL: Преобразования типов при вычислении/сравнении

В операциях БД MySQL, связанных с математическими вычислениями или сравнениях, если используются разные типы БД, то происходит неявное преобразование типов. Например, MySQL автоматически преобразует строки в числа по мере необходимости и наоборот.

mysql> SELECT 1+'1';
        -> 2
mysql> SELECT CONCAT(2,' test');
        -> '2 test'

Также возможно преобразовать число в строку явным образом с помощью функции CAST(). Преобразование происходит неявно с помощью функции CONCAT(), поскольку она ожидает строковые аргументы.

mysql> SELECT 38.8, CAST(38.8 AS CHAR);
        -> 38.8, '38.8'
mysql> SELECT 38.8, CONCAT(38.8);
        -> 38.8, '38.8'

Для операций сравнения действуют следующие правила преобразований типов:

  • Если один или оба аргумента равны NULL, то результат сравнения равен NULL, за исключением оператора <=> - безопасного сравнения равенства NULL, защищенного от нуля. Для NULL <=> NULL результат равен true. Никакого преобразования не требуется.
  • Если оба аргумента в операции сравнения являются строками, то они сравниваются как строки.
  • Если оба аргумента являются целыми числами, то они сравниваются как целые числа.
  • Шестнадцатеричные значения обрабатываются как двоичные строки, если они не сравниваются с числом.
  • Если один из аргументов является десятичным значением, сравнение зависит от другого аргумента. Аргументы сравниваются как десятичные значения, если другой аргумент является десятичным или целым числом, или как значения с плавающей запятой, если другой аргумент является значением с плавающей запятой.
  • Если один из аргументов является столбцом TIMESTAMP или DATETIME, а другой аргумент является константой, то перед выполнением сравнения константа преобразуется в метку времени. Это сделано для большей совместимости с ODBC. Преобразование не выполняется для аргументов функции IN(). Чтобы быть уверенном в правильности сравнений, всегда используйте полные строки DATETIME, DATE или TIME. Например, для достижения наилучших результатов при использовании оператора BETWEEN со значениями даты или времени используйте CAST() для явного преобразования значений в желаемый тип данных.

    Однострочный подзапрос из таблицы или таблиц не считается константой. Например, если подзапрос возвращает целое число для сравнения со значением DATETIME, сравнение выполняется как два целых числа. Целое число не преобразуется в значение даты/времени. Чтобы сравнить операнды как значения DATETIME, используйте CAST() для явного преобразования значения подзапроса в DATETIME.

  • Во всех остальных случаях аргументы сравниваются как числа с плавающей запятой (двойной точности). Например, сравнение строковых и числовых операндов происходит как сравнение чисел с плавающей запятой.

Ниже смотрите "Особенности неявного преобразование между типами даты и времени"

Следующие примеры иллюстрируют преобразование строк в числа для операций сравнения:

mysql> SELECT 1 > '6x';
        -> 0
mysql> SELECT 7 > '6x';
        -> 1
mysql> SELECT 0 > 'x6';
        -> 0
mysql> SELECT 0 = 'x6';
        -> 1

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

SELECT * FROM tbl_name WHERE str_col=1;

Причина этого в том, что существует множество различных строк, которые могут быть преобразованы в значение 1, например '1', ' 1', или '1a'.

Сравнения между числами с плавающей запятой и большими значениями типа INTEGER являются приблизительными, поскольку перед сравнением целое число преобразуется в число с плавающей запятой двойной точности, что не позволяет точно представить все 64-разрядные целые числа. Например, перед сравнением с плавающей запятой, целочисленное значение 253 + 1 не может быть представлено как число с плавающей запятой и округляется до 253 или 253 + 2, в зависимости от платформы.

Чтобы проиллюстрировать, только первое из следующих выражений сравнивает равные значения, но оба возвращают true (1):

mysql> SELECT '9223372036854775807' = 9223372036854775807;
        -> 1
mysql> SELECT '9223372036854775807' = 9223372036854775806;
        -> 1

Когда происходят преобразования из строки в число с плавающей запятой и из целого числа в число с плавающей запятой, то они не обязательно происходят одинаково. ЦП может преобразовать целое число в число с плавающей запятой, тогда как строка преобразуется цифра за цифрой в операции, включающей умножение чисел с плавающей запятой. Кроме того, на результаты могут влиять такие факторы, как архитектура компьютера, версия компилятора или уровень оптимизации. Один из способов избежать таких проблем - использовать CAST(), чтобы значение не преобразовывалось неявно в число с плавающей запятой:

mysql> SELECT CAST('9223372036854775807' AS UNSIGNED) = 9223372036854775806;
        -> 0

Сервер MySQL включает библиотеку преобразования dtoa, которая обеспечивает основу для улучшенного преобразования между строковыми значениями или DECIMAL и приблизительными значениями (FLOAT/DOUBLE):

  • Согласованные результаты преобразования на разных платформах, что устраняет, например, различия в преобразовании Unix и Windows.
  • Точное представление значений в случаях, когда результаты ранее не обеспечивали достаточной точности, например, для значений, близких к ограничениям IEEE.
  • Преобразование чисел в строковый формат с максимально возможной точностью. Точность dtoa всегда такая же или лучше, чем у стандартных функций библиотеки C.

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

Библиотека dtoa предоставляет преобразования со следующими свойствами. Далее D - представляет значение с DECIMAL или строковым представлением, а F - представляет число с плавающей запятой в собственном двоичном (IEEE) формате.

  • Преобразование F-D выполняется с максимально возможной точностью, возвращая D как самую короткую строку, которая дает F при обратном считывании и округлении до ближайшего значения в собственном двоичном формате, как указано IEEE.
  • Преобразование D-F выполняется таким образом, что F является ближайшим исходным двоичным числом к ​​входной десятичной строке D.

Эти свойства подразумевают, что преобразования F-D-F выполняются без потерь, если только F не является -inf, +inf или NaN. Последние значения не поддерживаются, поскольку стандарт SQL определяет их как недопустимые значения для типов FLOAT или DOUBLE.

Для преобразований D-F-D достаточным условием без потерь является то, что D использует 15 или меньше цифр точности, не является денормальным значением, -inf, +inf или NaN. В некоторых случаях преобразование происходит без потерь, даже если D имеет точность более 15 цифр, но это не всегда так.

Неявное преобразование числового значения или значений времени в строку создает значение, имеющее набор символов и параметры сортировки, определяемые системными переменными character_set_connection и collation_connection. Эти переменные обычно задаются с помощью SQL-запроса SET NAMES.

Это означает, что результатом такого преобразования является символьная (недвоичная) строка (значение CHAR, VARCHAR или LONGTEXT), за исключением случая, когда набор символов соединения установлен в двоичный. В этом случае результатом преобразования является двоичная строка (значение BINARY, VARBINARY или LONGBLOB).

Особенности неявного преобразование между типами даты и времени.

В некоторой степени можно преобразовать значение из одного временного типа в другой. Однако возможно некоторое изменение значения или потеря информации. Во всех случаях преобразование между временными типами зависит от диапазона допустимых значений результирующего типа. Например, хотя значения DATE, DATETIME и TIMESTAMP могут быть указаны с использованием одного и того же набора форматов, но не все типы имеют одинаковый диапазон значений. Значения TIMESTAMP не могут быть раньше 1970 UTC или позже '2038-01-19 03:14:07' UTC. Это означает, что такая дата, как '1968-01-01', допустима как значение DATE или DATETIME, но недействительна как значение TIMESTAMP и преобразуется в 0.

Преобразование значений DATE:

  • Преобразование в значение DATETIME или TIMESTAMP добавляет часть времени '00:00:00', поскольку значение DATE не содержит сведений о времени.
  • Преобразование в значение TIME бесполезно; результат будет '00:00:00'.

Преобразование значений DATETIME и TIMESTAMP:

  • Преобразование в значение DATE учитывает доли секунды и округляет временную часть. Например, '1999-12-31 23:59:59.499' становится '1999-12-31', тогда как '1999-12-31 23:59:59.500' становится '2000-01-01'.
  • Преобразование в значение TIME отбрасывает часть даты, поскольку тип TIME не содержит информации о дате.

При преобразовании значений TIME в другие временные типы, для части даты используется значение CURRENT_DATE(). TIME интерпретируется как прошедшее время (а не время суток) и добавляется к дате. Это означает, если значение времени выходит за пределы диапазона от '00:00:00' до '23:59:59', то что часть результата с датой отличается от текущей даты.

Например предположим, что текущая дата - '2012-01-01', а значения TIME - '12:00:00', '24:00:00' и '-12:00:00'. Преобразовании в значения DATETIME или TIMESTAMP приведут к результату '2012-01-01 12:00:00', '2012-01-02 00:00:00' и '2011-12-31 12:00:00' соответственно.

Преобразование TIME в DATE аналогично, но отбрасывает часть времени из результата: '2012-01-01', '2012-01-02' и '2011-12-31' соответственно.

для переопределения неявного преобразования можно использовать явное преобразование. Например, при сравнении значений DATE и DATETIME значение DATE приводится к типу DATETIME путем добавления временной части '00:00:00'. Чтобы выполнить сравнение, игнорируя временную часть значения DATETIME, необходимо использовать функцию CAST() следующим образом:

date_col = CAST(datetime_col AS DATE)

Преобразование значений TIME и DATETIME в числовую форму (например, путем добавления +0) зависит от того, содержит ли значение долю секунды. TIME(N) или DATETIME(N) преобразуется в целое число, когда N равно 0 (или опущено), и в значение DECIMAL с N десятичными цифрами, когда N больше 0:

mysql> SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0;
+-----------+-------------+--------------+
| CURTIME() | CURTIME()+0 | CURTIME(3)+0 |
+-----------+-------------+--------------+
| 09:28:00  |       92800 |    92800.887 |
+-----------+-------------+--------------+
mysql> SELECT NOW(), NOW()+0, NOW(3)+0;
+---------------------+----------------+--------------------+
| NOW()               | NOW()+0        | NOW(3)+0           |
+---------------------+----------------+--------------------+
| 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 |
+---------------------+----------------+--------------------+