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

1.SQL changes

GET_LOCK() function behavior

Before MySQL 5.7.5 GET_LOCK() will release the previously acquired lock when executing the second amount. After this version, it supports acquiring multiple locks at the same time, 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)

Return null means the lock has been released

So applications that rely on the behavior of GET_LOCK() releasing any previous locks must modify for the new behavior.

derived_merge is automatically enabled

5.7 The optimizer uses a consistent mechanism for handling derived tables and views in the from statement to better avoid unnecessary materialization and to be able to produce more efficient execution plans through conditional delegation.

However, for statements that modify tables (such as Delete or UPDATE), a merge policy that uses a previously materialized derived table may result in an ER_UPDATE_TABLE_USED error:

Cause of error: This error is triggered if the derived tables are merged into the external query block when the external query table and the internally changed table belong to the same table (materialization does not cause this error because, in fact,

It converts derived tables into separate tables)

For example:

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 in FROM clause

Solution: Turn off the derived_merge option of optimizer_switch, which is turned on by default

Close derived_merge

SET optimizer_switch = 'derived_merge=off';

III. Keywords and reserved words

If you want to quote reserved words, you must use back quotes to enclose or follow the comma of the qualified name, otherwise you will report syntax errors, such as

mysql> CREATE TABLE interval (begin INT, end INT);

ERROR 1064 (42000): You have an 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)

Keywords and reserved words that are present in MySQL 5.7 but not in MySQL 5.6; reserved words marked with R, 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

IV. table union query

If there is an order by or limit keyword in a single query statement joined by union, the single statement needs to be parenthesized. For example:

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)

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.