List of articles
know what really understands , Don’t know for don’t know , It is knowledge. .——《 The analects of Confucius 》
Hello everyone ！ I only talk about technology, not haircut Tony teacher .
Let’s talk today MySQL Implicit data type conversion and possible problems in .
When two different types of data are operated on , In order to make them compatible ,MySQL Implicit data type conversions may be performed . for example ,MySQL Automatically converts strings to numbers when needed , vice versa .
mysql> SELECT 1+'1'; -> 2 mysql> SELECT CONCAT(2,' test'); -> '2 test'
We can also use CAST() Function to explicitly convert a number to a string .CONCAT() Implicit type conversion in a function is because it can only receive arguments of string type .
mysql> SELECT 38.8, CAST(38.8 AS CHAR); -> 38.8, '38.8' mysql> SELECT 38.8, CONCAT(38.8); -> 38.8, '38.8'
Here are the type conversion rules in comparison operations ：
If any of the parameters is NULL, The result of the comparison is NULL,<=> Except for the equality comparison operator .NULL <=> NULL The result is true, No type conversion is required .
If both parameters are strings , Perform string comparison .
If both parameters are integers , Perform integer comparison .
If it’s not compared to the numbers , Hexadecimal values will be treated as binary strings .
If a parameter is TIMESTAMP perhaps DATETIME Field , The other parameter is a constant , This constant will be converted to a timestamp type before comparison . This rule is to better support ODBC standard .IN() The parameters in the operator do not perform this conversion . For the sake of insurance , Remember to use the full date time when performing the comparison 、 Date or time string . for example , In the use of BTWEEN Operator to determine date or time data , utilize CAST() Function to convert the type display of 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 and DATETIME When comparing data ,DATETIME Will be converted to integer type , Instead of converting the result of a subquery to a time type . If you want to perform a date time comparison , have access to CAST() Function explicitly converts the result of a subquery to DATETIME type .
If a parameter is of exact numeric type （decimal）, The method of comparison depends on the type of the other parameter . If the other parameter is an exact number or an integer type , Use precise numbers to compare ; If the other parameter is a floating-point type , Use floating point comparison .
In other cases , Use floating point comparison . for example , String and exact number comparison using floating-point comparison method .
On the conversion rules between time types , You can refer to Official documents .
The following example demonstrates the comparison operation of converting a string to a number ：
mysql> SELECT 1 > '6x'; -> 0 mysql> SELECT 7 > '6x'; -> 1 mysql> SELECT 0 > 'x6'; -> 0 mysql> SELECT 0 = 'x6'; -> 1
If you compare a string type field with a number ,MySQL The index on this field cannot be used to quickly find data . for example ,str_col It’s an index field , The index cannot be used for the following statement ：
SELECT * FROM tbl_name WHERE str_col=1;
The problem is that many different strings can be converted to numbers 1, for example ’1’、’ 1’ perhaps ‘1a’.
Floating point numbers and INTEGER The comparison between super large values of type is approximate , Because integers need to be converted to double precision floating-point numbers before comparison , Double precision floating point numbers can’t exactly represent all 64 An integer . for example , Integers 253 + 1 Cannot be represented by floating point numbers , It can only be approximated to 253 perhaps 253 + 2.
for instance , Only two values in the first comparison operation are equal , But both comparison operations return true（1）：
mysql> SELECT '9223372036854775807' = 9223372036854775807; -> 1 mysql> SELECT '9223372036854775807' = 9223372036854775806; -> 1
String to floating point number and integer to floating point number may be converted in different ways . Integers may use CPU Convert to floating point , Strings may be converted bit by bit using floating point multiplication . in addition , The result of conversion may be influenced by various factors , For example, the architecture of a computer 、 Compiler version or optimization level, etc . One way to avoid this problem is to use CAST() function , So the data is not implicitly converted to floating-point numbers .
mysql> SELECT CAST('9223372036854775807' AS UNSIGNED) = 9223372036854775806; -> 0
More about floating point comparison , You can refer to Official documents .
MySQL The server provides a transformation Library dtoa, You can support strings or DECIMAL Data and approximate numbers （FLOAT/DOUBLE） The basic conversion function between ：
- Cross platform consistency transformation results , for example , Can eliminate Unix and Windows Differences between .
- It can accurately represent data that cannot provide enough precision before , For example, close to IEEE Restricted data .
- Convert numbers to string format with the highest possible precision . dtoa The accuracy of is always equal to or higher than the standard C Code base functions .
The character set and collation of the result of the implicit conversion of a number or time type to a string depend on character_set_connection and collation_connection System variables .（ These variables usually use SET NAMES Set it up . Information about connected character sets , You can refer to Official documents .）
This means that the result of this transformation is a non binary string （CHAR、VARCHAR perhaps LONGTEXT）, Unless the connection character set is set to binary. here , The result of the conversion is a binary string （BINARY、VARBINARY perhaps LONGBLOB）.
For integer type expressions , The expression evaluation and expression assignment described above are different . For example, the following statement ：
CREATE TABLE t SELECT integer_expr;
In this case , surface t The field type of depends on the length of the integer expression , May be INT perhaps BIGINT. If the maximum length of the expression exceeds INT, Use BIGINT type . This means that we can create… With a long enough expression BIGINT Type field ：
CREATE TABLE t SELECT 000000000000000000000 AS col; DESC t; Field|Type |Null|Key|Default|Extra| -----+------+----+---+-------+-----+ col |bigint|NO | |0 | |
JSON The comparison of data can be divided into two cases . The first level of comparison is based on JSON type , If the two types are different , The result of the comparison depends on the type with higher priority ; If two data are JSON The same type , Use specific type rules for the second level of comparison . about JSON He Fei JSON Data comparison , Let’s start with the non JSON Data to JSON type , And then compare them . Details are available Official documents .
If you find the article useful , Welcome comments 、 give the thumbs-up 、 recommend