欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Mysql5.7.9 trigger语法新限制,造成错误:Error Code: 1221. Incorrect usage of UNION and LIMI

程序员文章站 2022-07-08 15:50:35
...
今天新做一个Mysql从服务器,版本5.7.9,dump数据库的时候遇到错误
Error Code: 1221. Incorrect usage of UNION and LIMIT

网上找一圈,没发现相同问题。

在5.6.19版本上测试,无法重现此问题。

看看dump数据库的结果,貌似终结在一个表的数据dump完成,dump触发器时出错。

翻看回触发器代码有这么一段:
select XXXXX
limit 1
union all
select XXXX
limit 2

改成
(select XXXXX
limit 1 )
union all
(select XXXX
limit 1)

解决此问题。
翻回mysql5.7的手册,嗯,果然,一切早已写在上头:
http://dev.mysql.com/doc/refman/5.7/en/union.html

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
However, use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.

To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;
A statement without parentheses is equivalent to one parenthesized as just shown.

写这个触发器的时候就觉得貌似这段代码是有歧义的。。。。。。。
现在报应来了吧。。。