14.3 Type Conversion in Expression Evaluation — MySQL 9.2

14.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.
Если один из аргументов имеет значение типа десятичная дробь, сравнение зависит от другого аргумента. Аргументы сравниваются как десятичные значения, если другой аргумент является десятичным или целым значением, или как числа с плавающей точкой, если другой аргумент — число с плавающей точкой.
Во всех остальных случаях аргументы сравниваются как числа с плавающей точкой (с двойной точностью).

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

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

Библиотека 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. Сведения о наборах символов соединений см. в Разделе 12.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;