Shulou(Shulou.com)06/01 Report--

I believe most people have written the sub-query, but I encountered a relatively bad problem yesterday. Due to the better backup, I recovered the misoperated data in a very short time, but this problem is worth sharing.

First, set up the following test table:

CREATE TABLE course (

Student_id INT (11)

Course VARCHAR (20)

);

INSERT INTO course VALUES ('1customers,' Test 1')

INSERT INTO course VALUES ('2percent,' Test 2')

INSERT INTO course VALUES ('3percent,' test 3')

CREATE TABLE student (

Id INT (11)

Name VARCHAR (20)

)

INSERT INTO student VALUES ('1customers,' jiate')

INSERT INTO student VALUES ('2percent,' haoshen')

INSERT INTO student VALUES ('3percent,' leishen')

INSERT INTO student VALUES ('44th,' tetui')

Now let's perform the following query operation:

SELECT FROM student WHERE id IN (SELECT id FROM course)

The result is obvious, there is one more. Yesterday, what we developed and wrote was a update operation, that is, update student set name=' Special leg 'where id in (select id from course) originally updated 1000 items of data, but the developer directly mistakenly updated 400000 items.

After careful inspection, I found that the two sql just now found that there is no id column in the course, so why did the two sql not throw the id error, but directly match the whole table?

Let's desc extended to see what the optimizer has done.

DESC EXTENDED SELECT FROM student WHERE id IN (SELECT id FROM course)

SHOW WARNINGS

The first row 1276 is to the effect that the id column in the second query is parsed to the

The second line is the parsed sql of the parser. Let's post it and have a look.

SELECT

Yhtest.student.id AS id

Yhtest.student.name AS name

FROM

Yhtest.student semi

JOIN (yhtest.course)

WHERE (

Yhtest.student.id = yhtest.student.id

)

That is to say, the id column in our subquery is parsed to the id column of the main table, so the where condition is a constant condition, so the result of our query is the full table result of table a, and this is the reason why such a query is not thrown wrong.

The fields in the subquery will first be found in the subquery. 1) if there is no such field in the subquery, it will go to the outer main table to look for it. If it can be found, it will not be thrown wrong! 2) if the field is not in both the subquery and the main table, it will be thrown wrong.

This is actually a less-than-ideal result, and it would be too bad if we inadvertently mistook the update operation.

We have two reminders about this:

1) before doing database operations, it is best to back up.

2) when writing sql, subqueries are written in the form of table association and table association as far as possible. If this error occurs, the error will be thrown directly. Although the form of sub-query is easy to understand and clear at a glance, the performance is generally not good, and again, if you fall into the above false update pit.

3) the optimization operations for subqueries are also different in different versions of the database, and the performance differences are obvious between different versions.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.