В операциях БД 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
):
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 | +---------------------+----------------+--------------------+