foreplay

SQL write wonderful, pay quack quack!

Xiao Yang, the new intern, wrote an SQL statement

SELECT wx_id from `user` WHERE wx_id = 2
Copy the code

When Xiao Yang can’t wait to go home from work, manager Wang next door caught Xiao Yang, and EXPLAIN the education of Xiao Yang command, Xiao Yang shed tears without culture.

In this SQL statement, wx_id has an index, but manager Wang found the result is this

image

If the key field is Null, it is clear that the SQL statement does not run the index.

Yang thought to himself, “Oops, I wrote the wrong SQL statement again, and now I have to face the mixed doubles of operation and peacekeeping manager. No, I have to change this SQL statement immediately. Let me think about what went wrong.”

image

Wx_id field is vARCHAR type, I did not add quotation marks when I queried.

Xiao Yang grabbed the keyboard in the hands of the manager and added quotation marks to the query conditions of WX_ID

image

Sure enough, the SQL statement began to walk the index. Xiao Yang thought he had solved a big Bug.

The manager smiled and asked, “Do you know why you put quotes around the index? If the field is an int, do I need to put quotes around it? And why?”

Dinner is coming

Xiao Yang was asked to stay in place, unable to answer.

After xiao Yang’s research, it is found that if the field is vARCHAR type, the right side of the equal sign must be quoted before the index; If the field is an int, the equal sign will be indexed with or without quotation marks.

What? You don’t believe what Xiao Yang said, there is a picture and a truth. (Bonus field type int)

image

But the conclusion came out, or unable to answer the manager’s killing three questions.

Xiao Yang brought the answer

“The owner of the yard told Xiao Yang

In MySQL queries, implicit conversions occur when the left and right side of the query criteria do not match

SELECT wx_id from 'user' WHERE wx_id = 2, equivalent to SELECT wx_id from 'user' WHERE CAST(wx_id AS signed int) = 2Copy the code

Once a function is performed on an index field, MySQL abandons the use of the index

Therefore, if the field is of type VARCHAR, the equal sign must be quoted before the index is removed; otherwise, MySQL will not use the index due to implicit conversion. Why can int use an index with or without quotes?

That’s because int numbers can only be converted to ‘2’ and are uniquely deterministic. So although implicit conversion is required, it does not affect the use of indexes

Yang asked, “Can you also tell me some knowledge of implicit conversion?”

The main and backhand is an English document

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 TIMESTAMP or 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. 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.Copy the code

I was kind enough to translate it into Chinese for you

If at least one of the two arguments is NULL, the result of the comparison is NULL, except that <=> is used to compare two NULL arguments. In both cases, type conversion 2 is not required. Both arguments are strings and the comparison is performed as strings. A hexadecimal value is treated as a binary string 5 when compared with a non-numeric value. One parameter is TIMESTAMP or DATETIME, and the other parameter is constant. Constants are converted to timestamp 6, one argument is of type DECIMAL, integers are converted to decimal for comparison if the other argument is decimal or integer, and if the other argument is floating point, Decimal is converted to a floating-point number for comparison 7, and in all other cases both parameters are converted to a floating-point number for comparisonCopy the code

Share one more implicitly converted pit

  • Do you occasionally delete data you don’t know about?
mysql> select * from test; +----+-------+-----------+ | id | name | password | +----+-------+-----------+ | 1 | test1 | password1 | | 2 | test2 | password2 | | 3 | aaa | aaaa | | 4 | 55aaa | 55aaaa | | 5 | 1212 | aaa | | 6 | 1212a | aaa | +----+-------+-----------+ 6 rows in set (0.00 SEC) mysql> select * from test where name = 1212; +----+-------+----------+ | id | name | password | +----+-------+----------+ | 5 | 1212 | aaa | | 6 | 1212a | aaa | + - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, five warnings (0.00 SEC) mysql > select * from test where name = '1212'; +----+------+----------+ | id | name | password | +----+------+----------+ | 5 | 1212 | aaa | +----+------+----------+ 1 Row in set (0.00 SEC)Copy the code

The example above is intended to query the record with id 5, and results in the query with ID 6. What do I mean by that? Sometimes some columns in our database table are vARCHAR type, but the value stored is’ 1123 ‘, such as pure numeric string value, some students are not used to use quotation marks when writing SQL. This makes it possible to manipulate more data when performing select, UPDATE, or DELETE operations. So don’t forget to put quotes around it.

In a nutshell

Implicit conversions have the risk of missing an index, and in the case of high concurrency and large amounts of data, missing an index can have more consequences than just being hit by operations and managers. Write SQL and EXPLAIN