🍺 know is know, noknow is noknow, is also know. Today we are going to talk about implicit data type conversions in MySQL and the problems they may cause.

When two different types of data are being evaluated, MySQL may perform implicit data type conversions to make them compatible. For example, MySQL automatically converts strings to numbers when needed, and vice versa.

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

We can also explicitly convert numbers to strings using the CAST() function. The implicit conversion in the CONCAT() function is because it can only accept arguments of type string.

Mysql > SELECT count (*) from mysql> SELECT count (*); Mysql > SELECT mysql.concat (mysql.concat (mysql.concat)); - > 38.8 ', 38.8 'Copy the code

Here are the conversion rules for comparison operations:

If either argument is NULL, the result of the comparison is NULL, except for the <=> equality comparison operator. NULL <=> NULL evaluates to true and does not require type conversion.

If both arguments are strings, a string comparison is performed.

If both arguments are integers, an integer comparison is performed.

Hexadecimal values are treated as binary strings unless compared to numbers.

If one parameter is a TIMESTAMP or DATETIME field and the other parameter is a constant, the constant will be converted to the TIMESTAMP type before the comparison. This rule is intended to better support the ODBC specification. Arguments IN the IN() operator do not perform this conversion. To be on the safe side, remember to use the full date-time, date, or time string when performing comparisons. For example, when using the BTWEEN operator to determine date or time data, use the CAST() function to convert the type display of the data to the corresponding type.

Subqueries that return single-row results are not treated as constants. For example, when a subquery that returns an integer is compared to DATETIME data, the DATETIME will be converted to integer, but the result of the subquery will not be converted to time. If you want to perform a date-time comparison, you can use the CAST() function to explicitly CAST the result of a subquery to DATETIME.

If one parameter is of exact numeric type (Decimal), the method of comparison depends on the type of the other parameter. If the other argument is of exact number or integer type, use exact number comparison; If the other argument is of floating-point type, use floating-point comparisons.

In other cases, floating-point comparisons are used. For example, strings and exact numbers are compared using the floating-point comparison method.

You can refer to the official documentation for conversion rules between time types.

The following example demonstrates a comparison operation that converts a string to a number:

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

If a string field is compared to a number, MySQL cannot use the index on that field to quickly find the data. For example, str_col is an index field. The index cannot be used in the following statements: SELECT * FROM tbl_name WHERE str_col=1; The problem is that many different strings can be converted to the number 1, such as’ 1 ‘, ‘1’, or ‘1a’.

The comparison between a floating-point number and a very large value of type INTEGER is an approximate comparison because integers need to be converted to a double-precision floating-point number before they can be compared, and double-precision floating-point numbers cannot accurately represent all 64-bit integers. For example, the integer 253 + 1 cannot be represented as a floating point number and can only be approximated to 253 or 253 + 2.

For example, only the two values in the first comparison are equal, but both comparisons return true (1) :

mysql> SELECT '9223372036854775807' = 9223372036854775807; -> 1 mysql> SELECT '9223372036854775807' = 9223372036854775806; - > 1Copy the code

A string to float may be converted differently than an integer to float. Integers may be converted to floating-point numbers using CPU, while strings may be converted bit by bit using floating-point multiplication. In addition, conversion results can be influenced by various factors, such as the architecture of the machine, compiler version, or optimization level. One way to avoid this problem is to use the CAST() function so that data is not implicitly converted to floating point numbers.

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

For more information on floating point comparisons, refer to the official documentation. The MySQL server provides a conversion library, DTOA, that supports basic conversion between string or DECIMAL data and approximate numbers (FLOAT/DOUBLE) :

Consistent cross-platform conversions result, for example, in eliminating the differences between Unix and Windows.

Can accurately represent data that previously could not provide sufficient precision, such as data that is close to IEEE limits.

Convert numbers to string format with the highest possible precision. Dtoa accuracy is always equal to or higher than standard C code base functions.

The character set and collation rules for the result of an implicit conversion from a numeric or time type to a string depend on the CHARACTER_set_CONNECTION and COLLation_CONNECTION system variables. These variables are usually SET using SET NAMES. For information about the character set of the connection, refer to the official documentation.

This means that the result of this conversion is a non-binary string (CHAR, VARCHAR, or LONGTEXT), unless the connection character set is set to binary. The result of the conversion is a BINARY string (BINARY, VARBINARY, or LONGBLOB).

For expressions of integer type, the expression evaluation and expression assignment described above are different. For example, CREATE TABLE t SELECT integer_expr; In this case, the field type of table T depends on the length of the integer expression, which can be either INT or BIGINT. If the maximum length of the expression exceeds INT, use BIGINT. This means that we can create a BIGINT field with a sufficiently long expression:

CREATE TABLE t SELECT 000000000000000000000 AS col;

DESC t;
Field|Type  |Null|Key|Default|Extra|
-----+------+----+---+-------+-----+
col  |bigint|NO  |   |0      |     |
Copy the code

JSON data is compared in two cases. The first level of comparison is based on the JSON type of the data being compared, and if the two types are different, the result of the comparison depends on the type with higher priority. If the two data have the same JSON type, use specific type rules for a second-level comparison. For comparing JSON and non-JSON data, the non-JSON data is first converted to JSON type and then compared. Please refer to the official documentation for details.