12.3 Type Conversion in Expression Evaluation — MySQL 5.7

12.3 Преобразование типов в выражениях

Когда оператор используется с операндами разных типов, происходит преобразование типов для обеспечения совместимости операндов. Некоторые преобразования происходят неявно. Например, 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'

Более подробную информацию о кодировке символов при неявных преобразованиях числа в строку, а также о модифицированных правилах, применяемых к CREATE TABLE ... SELECT операциям, смотрите далее в этом разделе.

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

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

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

Дополнительную информацию о преобразовании значений из одного временного типа в другой см. в разделе 11.2.9 «Преобразование между типами дат и времени».

Сравнение значений JSON происходит на двух уровнях. Первый уровень сравнения основан на типах значений JSON. Если типы различаются, результат сравнения определяется исключительно тем, какой тип имеет более высокий приоритет. Если два значения имеют одинаковый тип JSON, происходит сравнение на втором уровне с использованием правил, специфичных для типов. При сравнении значений JSON и не-JSON значений значение не-JSON преобразуется в JSON, и значения сравниваются как значения JSON. Более подробную информацию см. в разделе «Сравнение и порядок значений JSON».

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

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'.

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

mysql> CREATE TABLE t1 (
    ->   c1 INT NOT NULL AUTO_INCREMENT,
    ->   c2 INT DEFAULT NULL,
    ->   c3 VARCHAR(25) DEFAULT NULL,
    ->   PRIMARY KEY (c1)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES ROW(1, 52, 'grape'), ROW(2, 139, 'apple'),
    ->                       ROW(3, 37, 'peach'), ROW(4, 221, 'watermelon'),
    ->                       ROW(5, 83, 'pear');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

Обратите внимание на результат при выборке из этой таблицы и сравнении c3, который является столбцом VARCHAR, с целым числом 0:

mysql> SELECT * FROM t1 WHERE c3 = 0;
+----+------+------------+
| c1 | c2   | c3         |
+----+------+------------+
|  1 |   52 | grape      |
|  2 |  139 | apple      |
|  3 |   37 | peach      |
|  4 |  221 | watermelon |
|  5 |   83 | pear       |
+----+------+------------+
5 rows in set, 5 warnings (0.00 sec)

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

mysql> SELECT * FROM t1 WHERE c3 = '0';
Empty set (0.00 sec)

Это не происходит, когда SELECT является частью оператора определения данных, такого как CREATE TABLE ... SELECT; в строгом режиме оператор завершается ошибкой из-за некорректного сравнения:

mysql> CREATE TABLE t2 SELECT * FROM t1 WHERE c3 = 0;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'grape'

Когда 0 заключено в кавычки, оператор успешно выполняется, но создаваемая таблица не содержит строк, так как не было ни одной строки, соответствующей '0', как показано ниже:

mysql> CREATE TABLE t2 SELECT * FROM t1 WHERE c3 = '0';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t2;
Empty set (0.00 sec)

Это известная проблема, связанная с тем, что строгий режим не применяется при обработке SELECT. Смотрите также раздел «Строгий режим SQL».

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

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

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

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

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

Дополнительную информацию о сравнениях чисел с плавающей точкой см. в разделе B.3.4.8 «Проблемы с числами с плавающей точкой».

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

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

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

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

Преобразование F в D выполняется с максимальной точностью, возвращая D как самую короткую строку, которая при повторном чтении и округлении до ближайшего значения в формате исходного двоичного представления, как указано стандартом IEEE, даёт F.
Преобразование 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. (Эти переменные обычно устанавливаются с помощью SET NAMES. Сведения о наборах символов соединений см. в разделе 10.4 «Наборы символов и сортировки соединений».)

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

Для целочисленных выражений вышеупомянутые замечания об оценке выражений применяются несколько иначе для присваивания выражений; например, в таком операторе:

CREATE TABLE t SELECT integer_expr;

В этом случае таблица в столбце, полученная в результате выражения, имеет тип INT или BIGINT в зависимости от длины целочисленного выражения. Если максимальная длина выражения не помещается в INT, используется BIGINT вместо него. Длина берется из значения max_length метаданных набора результатов SELECT (см. ). Это означает, что вы можете принудительно использовать BIGINT вместо INT с помощью достаточно длинного выражения:

CREATE TABLE t SELECT 000000000000000000000;