Development Notes: MySQL temporary variables and Python call Methods

As we all know, if the where condition of an SQL statement contains a function expression, indexes may not be used even if the column referenced by the condition already has an index. This is because, an index can only describe the "original state" of the data (that is, the original data in the table ), instead, it cannot describe the status of the data after the function is applied (the data distribution and aggregation may have changed at this time, and the index is no longer applicable ).
Sometimes, even if we "know" that the index is still useful after function operations, MySQL will not be "smart" to this point. As a result, the query efficiency will drop sharply.
In some cases, we can use the temporary MySQL variables to save the results of the previously calculated function operation, and then use these results for further query. You can see through the explain that in some cases, this measure can indeed use indexes to improve query efficiency.

MySQL temporary variables can be used in two ways:
1. Use the set statement
Set @ test1: = 0;
Set @ Test2: = @ test1: = 5;
Select @ test1, @ Test2;
+ -------- +
| @ Test1 | @ Test2 |
+ -------- +
| 5 | 5 |
+ -------- +

2. Select statements
Select @ current_time: = now ();
Select @ current_time;
+ ------------------------ +
| @ Current_time |
+ ------------------------ +
| 11:02:40 |
+ ------------------------ +

Through temporary variables, we only need SQL statements to perform more complex queries (instead of dumping data back and forth before the database and programming language ).

Note that MySQL doesn't evaluate expressions containing user variables until they are sent to the client, that is, expressions containing user variables, the calculation is not performed before it is sent to the client. Therefore, the following SQL statement may cause unexpected results:
Select (@ AA: = ID) as a, (@ AA + 3) as B from tbl_name having B = 5;
In fact, B in the having clause references the variable @ AA. Because the execution of SQL statements is non-procedural, we cannot guarantee that the @ AA calculation will be performed before the having clause (in fact, in MySQL, the @ AA referenced by B is actually the value of @ AA in the last query, rather than the value for this query ).
To solve this problem, more complicated settings are required.

Cute (SQL) will report:
MySQL error 2013, 'lost connection to MySQL server during query' (this problem is weird)
The solution is to call cursor.exe cute () multiple times. Each execution of the parameters is a single SQL statement, and finally the result set is obtained. This makes everything normal.

Trackback: http://tb.donews.net/TrackBack.aspx? Postid = 1111607