Implicit MySQL conversion and implicit mysql Conversion

Implicit MySQL conversion and implicit mysql Conversion

 

Implicit conversion rules

The implicit conversion rules in the official documentation are described as follows:

If one or both arguments are NULL, the result of the comparison is NULL, should t 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 TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is already med. this is done to be more ODBC-friendly. note that 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 DATETIME value, the comparison is done as two integers. the integer is not converted to a temporal value. to compare the operands as DATETIME values, use CAST () to explicitly convert the subquery value to 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 (real) numbers.

The translation is as follows:

 

Problem description

• In the where Condition Statement, the field attribute and the assigned condition cannot be directly compared when the data type is different. Consistent conversion is required.
• The default conversion rules are as follows:
  • All different types are converted to floating-point type (all of them are described as integer type in the following text, meaning one)
  • If the field is a character and the condition is an integer, all the fields in the table are converted to an integer (that is, the problem in the figure above, which is explained in detail below)

Conversion Summary Case Analysis

• Table structure. The name field has an index.

-- Note that the name field is an indexed create table 't3 '('id' int (11) not null, 'c1' int (11) not null, 'name' varchar (100) not null default 'fajlfjalfka ', KEY 'name' ('name'), KEY 'id' ('id ')) ENGINE = InnoDB default charset = latin11 row in set (0.00 sec)

-- Simulate a slow query on the whole table caused by implicit conversion on the line -- implicit conversion xxxx occurs. test> select * from t3 where name = 0; + ---- + ------------- + | id | c1 | name | + ---- + ------------- + | 1 | 2 | fajlfjalfka | 2 | 0 | fajlfjalfka | 1 | 2 | fajlfjalfka | 2 | 0 | fajlfjalfka | + ---- + ------------- + 4 rows in set, 4 warnings (0.00 sec) -- the preceding SQL Execution Plan is a full table scan. After scanning, the characters are converted to integer type, all of which are 0. when conditions are matched, all xxxx is returned. test> desc select * from t3 where name = 0; + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------- + | 1 | SIMPLE | t3 | ALL | name | NULL | 4 | Using where | + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------- + 1 row in set (0.00 sec) -- after a single quotation mark is added, the name index is used, and xxxx is scanned for non-full tables. test> desc select * from t3 where name = '0 '; + ---- + ------------- + ------- + ------ + ------------- + ------ + --------- + ------- + ------ + keys + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------- + ------ + ----------------------- + | 1 | SIMPLE | t3 | ref | name | 102 | const | 1 | Using index condition | + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------- + ------ + --------------------- + 1 row in set (0.00 sec) -- index, no xxxx is returned. test> select * from t3 where name = '1'; Empty set (0.00 sec)

Explanation

• If the condition is set to 0 or 1, the entire table is scanned. You need to convert all the name fields from all the characters to integer type, and then compare them with 0 or 1. Because all the characters starting with a letter are converted to 0, the returned result is all rows.
• Someone asked why I didn't put0Automatically changed'0'? See below.

Conversion example

-- Starts with 0 XXXX. test> select cast ('a1' as unsigned int) as test; + ------ + | test | + ------ + | 0 | + ------ + 1 row in set, 1 warning (0.00 sec) xxxx. test> show warnings; + --------- + ------ + levels + | Level | Code | Message | + --------- + ------ + ----------------------------------------- + | Warning | 1292 | Truncated incorrect INTEGER value: 'a1' | + --------- + ------ + --------------- -------------------------- + 1 row in set (0.00 sec) -- starting with no character, it is intercepted until the first position is not the character xxxx. test> select cast ('1a1' as unsigned int) as test; + ------ + | test | + ------ + | 1 | + ------ + 1 row in set, 1 warning (0.00 sec) xxxx. test> select cast ('123a1 'as unsigned int) as test; + ------ + | test | + ------ + | 123 | + ------ + 1 row in set, 1 warning (0.00 sec) -- directly truncate by character and add 20 (not 19) xxxx. test> select cast ('23: 12: 13 'as datetim E) as test; + ------------------- + | test | + ------------------- + | 2023-12-13 00:00:00 | + --------------------- + 1 row in set (0.00 sec) -- Why cannot it be converted to timestamp, the official document does not provide timestamp in the conversion type. If so, the above datetime is hard to interpret as what is not 1923. Is the current system time detected? Xxxx. test> select cast ('23: 12: 13' as timestamp) as test; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'timestamp) as test' at line 1 -- this time cannot be converted to datetimexxxx. test> select cast ('10: 12: 32' as datetime) as test; + ------ + | test | + ------ + | NULL | + ------ + 1 row in set, 1 warning (0.00 sec) xxxx. test> show warnings; + --------- + ------ + ------------------------------------ + | Level | Code | Message | + --------- + ------ + Warning + | Warning | 1292 | Incorrect datetime value: '10: 12: 32' | + --------- + ------ + -------------------------------------- + 1 row in set (0.00 sec) -- in version 5.5, the time conversion character will be added to msxxxx. (none)> select version (); + ------------ + | version () | + ------------ + | 5.5.31-log | + ------------ + 1 row in set (0.00 sec) xxxx. (none)> select CURTIME (), CURTIME () + 0, NOW (), NOW () + 0; + ----------- + --------------- + --------------------- + ----------------------- + | CURTIME () | CURTIME () + 0 | NOW () + 0 | + ----------- + ------------- + hour + | 15:40:01 | 154001.000000 | 15:40:01 | 20160506154001.000000 | + ------------- + --------------- + --------------------- + hour + 1 row in set (0.00 sec) -- 5.6 won't xxxx. test> select version (); + ------------ + | version () | + ------------ + | 5.6.24-log | + ------------ + 1 row in set (0.00 sec) xxxx. test> select CURTIME (), CURTIME () + 0, NOW (), NOW () + 0; + ----------- + ------------- + ------------------- + -------------- + | CURTIME () + 0 | NOW () + 0 | + ----------- + ------------- + hour + -------------- + | 15:40:55 | 154055 | 15:40:55 | 20160506154055 | + ------------- + hour + ---------------- + 1 row in set (0.00 sec)

Why not where name = 0In 0Convert '0'?

• If a number is converted to a character, for example, 0 to '0', the query result can only be that the field is equal to '0'. In fact, the data in the table, such as 'a0 ', '00', this is actually all the user wants 0. After all, the User specifies the number 0, so MySQL still takes the user's requirements as the standard. Otherwise, none of '00' will be returned to the user.

Summary

• With the above content, the question at the beginning can be explained.
• Is it possible to bypass authentication?

Supplement

-- The issues left over above have nothing to do with the system time. It is suspected that the specified datetime is used internally according to timestamp. Mysql> select now (); + --------------------- + | now () | + ------------------- + | 14:16:50 | + --------------------- + 1 row in set (0.00 sec) mysql> select cast ('23: 12: 13 'as datetime) as test; + --------------------- + | test | + --------------------- + | 2023-12-13 00:00:00 | + ----------------------- + 1 row in set (0.00 sec)

By JackpGao
Link: http://www.jianshu.com/p/6f34e9708a80
Source: Simplified book
Copyright owned by the author