Lecture
Problems with incorrect data arise not only in the case of a SELECT similar to the one previously discussed, but also in the case of queries that modify the data. The so-called DML queries.
Consider an example.
mysql> create table t1(f1 int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2(f2 int);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> delete from t1, t2 using t1, t2;
Query OK, 0 rows affected (0.00 sec) This query would delete all rows in tables t1 and t2: “using t1, t2;” is used and there is no WHERE predicate. However, it is not.
Pay attention to the line "0 rows affected". It indicates that 0 lines have been deleted, that is, nothing has been deleted! Why?
Check that we have in the tables:
mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec) What happened?
In the case of a SELECT, we could use the previous technique and see the output of EXPLAIN EXTENDED. But MySQL does not support EXPLAIN DELETE / UPDATE / INSERT. This feature is planned, but not yet implemented. What to do in this case?
The best way to check delete / insert / update requests is to convert them to the corresponding select requests:
mysql> select * from t1, t2;
Empty set (0.00 sec) We received the same request as discussed in the previous chapter. Accordingly, the conclusion is the same: in this case, the expression t1, t2 denotes t1 INNER JOIN t2. And since there is no data in table t2, there is no field for which they could be combined. Accordingly, nothing has been removed.
In a similar way, you can work with problematic update requests.
Reception number 6: convert DML queries to the appropriate SELECT to find out which lines will be changed.
Comments
To leave a comment
Databases - Error detection methods in SQL application
Terms: Databases - Error detection methods in SQL application