When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts strings to numbers as necessary, and vice versa.
mysql> SELECT 1+'1';
-> 2
mysql> SELECT CONCAT(2,' test');
-> '2 test'It is also possible to convert a number to a string explicitly using the 'CAST()' function. Conversion occurs implicitly with the 'CONCAT()' function because it expects string arguments.
mysql> SELECT 38.8, CAST(38.8 AS CHAR);
-> 38.8, '38.8'
mysql> SELECT 38.8, CONCAT(38.8);
-> 38.8, '38.8'See later in this section for information about the character set of implicit number-to-string conversions, and for modified rules that apply to 'CREATE TABLE ... SELECT' statements.
The following rules describe how conversion occurs for comparison operations:
If one or both arguments are 'NULL', the result of the comparison is 'NULL', except for the 'NULL'-safe '<=>' equality comparison operator. For 'NULL <=> NULL', the result is true. No conversion is needed.
If both arguments in a comparison operation are strings, they are compared as strings.
If both arguments are integers, they are compared as integers.
Hexadecimal values are treated as binary strings if not compared to a number.
If one of the arguments is a note 'TIMESTAMP': datetime. or note 'DATETIME': datetime. column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. This is not done for the arguments to 'IN()'. To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using 'BETWEEN' with date or time values, use 'CAST()' to explicitly convert the values to the desired data type.
A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a note 'DATETIME': datetime. value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as note 'DATETIME': datetime. values, use 'CAST()' to explicitly convert the subquery value to *note 'DATETIME': datetime.
If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
In all other cases, the arguments are compared as floating-point (double-precision) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.
For information about conversion of values from one temporal type to another, see *note date-and-time-type-conversion::.
Comparison of JSON values takes place at two levels. The first level of comparison is based on the JSON types of the compared values. If the types differ, the comparison result is determined solely by which type has higher precedence. If the two values have the same JSON type, a second level of comparison occurs using type-specific rules. For comparison of JSON and non-JSON values, the non-JSON value is converted to JSON and the values compared as JSON values. For details, see *note json-comparison::.
The following examples illustrate conversion of strings to numbers for comparison operations:
mysql> SELECT 1 > '6x';
-> 0
mysql> SELECT 7 > '6x';
-> 1
mysql> SELECT 0 > 'x6';
-> 0
mysql> SELECT 0 = 'x6';
-> 1For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If STR_COL is an indexed string column, the index cannot be used when performing the lookup in the following statement:
SELECT * FROM TBL_NAME WHERE STR_COL=1;The reason for this is that there are many different strings that may convert to the value '1', such as ''1'', '' 1'', or ''1a''.
Another issue can arise when comparing a string column with integer '0'. Consider table 't1' created and populated as shown here:
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: 0Observe the result when selecting from this table and comparing 'c3', which is a *note 'VARCHAR': char. column, with integer '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)This occurs even when using strict SQL mode. To prevent this from happening, quote the value, as shown here:
mysql> SELECT * FROM t1 WHERE c3 = '0';
Empty set (0.00 sec)This does not occur when note 'SELECT': select. is part of a data definition statement such as note 'CREATE TABLE ... SELECT': create-table-select.; in strict mode, the statement fails due to the invalid comparison:
mysql> CREATE TABLE t2 SELECT * FROM t1 WHERE c3 = 0;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'grape'When the '0' is quoted, the statement succeeds, but the table created contains no rows because there were none matching ''0'', as shown here:
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)This is a known issue, which is due to the fact that strict mode is not applied when processing 'SELECT'. See also *note sql-mode-strict::.
Comparisons between floating-point numbers and large integer values are approximate because the integer is converted to double-precision floating point before comparison, which is not capable of representing all 64-bit integers exactly. For example, the integer value 2^53 + 1 is not representable as a float, and is rounded to 2^53 or 2^53 + 2 before a float comparison, depending on the platform.
To illustrate, only the first of the following comparisons compares equal values, but both comparisons return true (1):
mysql> SELECT '9223372036854775807' = 9223372036854775807;
-> 1
mysql> SELECT '9223372036854775807' = 9223372036854775806;
-> 1When conversions from string to floating-point and from integer to floating-point occur, they do not necessarily occur the same way. The integer may be converted to floating-point by the CPU, whereas the string is converted digit by digit in an operation that involves floating-point multiplications. Also, results can be affected by factors such as computer architecture or the compiler version or optimization level. One way to avoid such problems is to use 'CAST()' so that a value is not converted implicitly to a float-point number:
mysql> SELECT CAST('9223372036854775807' AS UNSIGNED) = 9223372036854775806;
-> 0For more information about floating-point comparisons, see *note problems-with-float::.
The server includes 'dtoa', a conversion library that provides the basis for improved conversion between string or note 'DECIMAL': fixed-point-types. values and approximate-value (note 'FLOAT': floating-point-types./*note 'DOUBLE': floating-point-types.) numbers:
Consistent conversion results across platforms, which eliminates, for example, Unix versus Windows conversion differences.
Accurate representation of values in cases where results previously did not provide sufficient precision, such as for values close to IEEE limits.
Conversion of numbers to string format with the best possible precision. The precision of 'dtoa' is always the same or better than that of the standard C library functions.
Because the conversions produced by this library differ in some cases from non-'dtoa' results, the potential exists for incompatibilities in applications that rely on previous results. For example, applications that depend on a specific exact result from previous conversions might need adjustment to accommodate additional precision.
The 'dtoa' library provides conversions with the following properties. D represents a value with a *note 'DECIMAL': fixed-point-types. or string representation, and F represents a floating-point number in native binary (IEEE) format.
F -> D conversion is done with the best possible precision, returning D as the shortest string that yields F when read back in and rounded to the nearest value in native binary format as specified by IEEE.
D -> F conversion is done such that F is the nearest native binary number to the input decimal string D.
These properties imply that F -> D -> F conversions are lossless unless F is '-inf', '+inf', or 'NaN'. The latter values are not supported because the SQL standard defines them as invalid values for note 'FLOAT': floating-point-types. or note 'DOUBLE': floating-point-types.
For D -> F -> D conversions, a sufficient condition for losslessness is that D uses 15 or fewer digits of precision, is not a denormal value, '-inf', '+inf', or 'NaN'. In some cases, the conversion is lossless even if D has more than 15 digits of precision, but this is not always the case.
Implicit conversion of a numeric or temporal value to string produces a value that has a character set and collation determined by the 'character_set_connection' and 'collation_connection' system variables. (These variables commonly are set with note 'SET NAMES': set-names. For information about connection character sets, see note charset-connection::.)
This means that such a conversion results in a character (nonbinary) string (a note 'CHAR': char, note 'VARCHAR': char, or note 'LONGTEXT': blob. value), except in the case that the connection character set is set to 'binary'. In that case, the conversion result is a binary string (a note 'BINARY': binary-varbinary, note 'VARBINARY': binary-varbinary, or note 'LONGBLOB': blob. value).
For integer expressions, the preceding remarks about expression evaluation apply somewhat differently for expression assignment; for example, in a statement such as this:
CREATE TABLE t SELECT INTEGER_EXPR;In this case, the table in the column resulting from the expression has type note 'INT': integer-types. or note 'BIGINT': integer-types. depending on the length of the integer expression. If the maximum length of the expression does not fit in an note 'INT': integer-types, note 'BIGINT': integer-types. is used instead. The length is taken from the 'max_length' value of the note 'SELECT': select. result set metadata (see C API Basic Data Structures (https://dev.mysql.com/doc/c-api/5.7/en/c-api-data-structures.html)). This means that you can force a note 'BIGINT': integer-types. rather than *note 'INT': integer-types. by use of a sufficiently long expression:
CREATE TABLE t SELECT 000000000000000000000;File: manual.info.tmp, Node: non-typed-operators, Next: flow-control-functions, Prev: type-conversion, Up: functions