I. SQL changes
Get_lock () function behavior
MySQL 5.7.5 before Get_lock () releases the previously acquired lock at the second time, enabling multiple locks to be obtained at the same time after this version, such as:
Mysql> select version ();
+------------+
| Version () |
+------------+
| 5.6.33-log |
+------------+
1 row in Set (0.00 sec)
mysql> SELECT get_lock (' Lock1 ', 10);
+----------------------+
| Get_lock (' Lock1 ', 10) |
+----------------------+
| 1 |
+----------------------+
1 row in Set (0.00 sec)
mysql> SELECT get_lock (' Lock2 ', 10);
+----------------------+
| Get_lock (' Lock2 ', 10) |
+----------------------+
| 1 |
+----------------------+
1 row in Set (0.00 sec)
mysql> SELECT release_lock (' Lock2 ');
+-----------------------+
| Release_lock (' Lock2 ') |
+-----------------------+
| 1 |
+-----------------------+
1 row in Set (0.00 sec)
mysql> SELECT release_lock (' Lock1 ');
+-----------------------+
| Release_lock (' Lock1 ') |
+-----------------------+
| NULL |
+-----------------------+
1 row in Set (0.00 sec)
Returns null indicating that the lock has been released
Therefore, applications that rely on the behavior of Get_lock () that release any previous locks must be modified for the new behavior.
Two. Derived_merge is automatically turned on
The 5.7 optimizer uses a consistent mechanism to process derived tables and views from the from statement in order to better avoid unnecessary materialization and to produce a more efficient execution plan through a conditional devolution.
However, for statements that modify a table, such as delete or UPDATE, a merge policy that uses a previously materialized derived table may cause a er_update_table_used error:
Cause of error: When the external query table and the internally changed tables belong to the same table, this error is triggered if the derived table is merged into an external query block (materialization does not cause this error because, in fact,
It converts the derived table to a separate table)
Such as:
mysql> Delete from T1 where ID in (select t1.id from t1 inner JOIN T2 using (ID) where t2.a1=100);
ERROR 1093 (HY000): You can ' t specify target table ' T1 ' for update on FROM clause
Workaround: Turn off the optimizer_switch derived_merge option, which is turned on by default
Close Derived_merge
SET optimizer_switch = ' Derived_merge=off ';
Three. Keywords and reserved words
If you want to reference a reserved word, you must enclose or follow the comma in the qualified name with an inverse quotation mark, or else report a syntax error, such as
mysql> CREATE TABLE interval (begin INT, end int);
Error 1064 (42000): You have a error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' interval (begin INT, end INT) ' at line 1
mysql> CREATE TABLE ' interval ' (begin int, end int);
Query OK, 0 rows affected (1.14 sec)
Mysql> CREATE TABLE test.interval (begin INT, end int);
Query OK, 0 rows affected (1.84 sec)
MySQL 5.7 has keywords and reserved words that are not in MySQL 5.6, and those with r marked as reserved words, see the following table:
Account always CHANNEL
COMPRESSION Encryption File_block_size
FILTER follows GENERATED (R)
Group_replication INSTANCE JSON
Master_tls_version never Optimizer_costs (R)
parse_gcol_expr precedes replicate_do_db
Replicate_do_table replicate_ignore_db replicate_ignore_table
replicate_rewrite_db replicate_wild_do_table replicate_wild_ignore_table
ROTATE stacked STORED (R)
VALIDATION VIRTUAL (R) without
XID
Four. Table Union query
If there is an order by or limit keyword in a single query statement that uses a union connection, you need to enclose this single statement with parentheses. Such as:
Mysql> SELECT * FROM T1 LIMIT 1 Union SELECT * FROM T2 limit 2;
ERROR 1221 (HY000): Incorrect usage of UNION and LIMIT
Mysql> (SELECT * from T1 limit 1) union (SELECT * from T2 limit 2);
+------+-------+-------+
| ID | name1 | name2 |
+------+-------+-------+
| 1 | A1 | A2 |
| 1 | 2 | 2 |
| 1 | 1 | 1 |
+------+-------+-------+
3 Rows in Set (0.00 sec)
MySQL5.6 to version 5.7 upgrades using In-place upgrade methods need specific attention to the place
