MySQL5.1升级到MySQL5.5 产品运行出现异常
来源:互联网 发布:mac怎么连iphone 编辑:程序博客网 时间:2024/05/17 09:07
MySQL5.1升级到MySQL5.5 产品运行出现异常
http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html
后来发现MySQL修改了SQL语法,而我们的产品正好使用了该语法。就是delete中使用了表的别名
http://stackoverflow.com/questions/6634987/delete-with-alias-reference-impossible-without-selecting-a-db-since-mysql-5-5-3
SQL Changes
Incompatible change: Previously, the parser accepted an INTO clause in nestedSELECT statements, which is invalid because such statements must return their results to the outer context. As of MySQL 5.5.3, this syntax is no longer permitted and statements that use it must be changed.
Incompatible change: In MySQL 5.5.3, several changes were made to alias resolution in multiple-tableDELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases.
In MySQL 5.1.23, alias declarations outside the table_references part of the statement were disallowed for the USING variant of multiple-tableDELETE syntax, to reduce the possibility of ambiguous aliases that could lead to ambiguous statements that have unexpected results such as deleting rows from the wrong table.
As of MySQL 5.5.3, alias declarations outside table_references are disallowed for all multiple-tableDELETE statements. Alias declarations are permitted only in thetable_references part.
Incorrect:
DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2;DELETE t1 AS a2 FROM t1 AS a1 INNER JOIN t2 AS a2;
Correct:
DELETE FROM t1 USING t1 AS a1 INNER JOIN t2 AS a2;DELETE t1 FROM t1 AS a1 INNER JOIN t2 AS a2;
Previously, for alias references in the list of tables from which to delete rows in a multiple-table delete, the default database is used unless one is specified explicitly. For example, if the default database is db1, the following statement does not work because the unqualified alias reference a2 is interpreted as having a database of db1:
DELETE a1, a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2WHERE a1.id=a2.id;
To correctly match an alias that refers to a table outside the default database, you must explicitly qualify the reference with the name of the proper database:
DELETE a1, db2.a2 FROM db1.t1 AS a1 INNER JOIN db2.t2 AS a2WHERE a1.id=a2.id;
As of MySQL 5.5.3, alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases.
Statements containing alias constructs that are no longer permitted must be rewritten
解决办法
MySQL 5.5 says:
As of MySQL 5.5.3, alias resolution does not require qualification and alias references should not be qualified with the database name. Qualified names are interpreted as referring to tables, not aliases.
So you're right, you can't uses aliases in a multi-table delete spanning multiple databases,and have the same code work against 4.1/5.0/5.1 versus 5.5. The workaround is to skip aliases and give the full names of tables, qualified by database.
This should work, except for cases when you have a multi-table DELETE involving both a self-join and a cross-database join- MySQL5.1升级到MySQL5.5 产品运行出现异常
- MySQL5.1升级到MySQL5.5
- mysql5.5升级到5.6
- Mysql5.5升级到5.6
- Mysql5.5升级到5.6步骤详解
- ubuntu上mysql5.5升级到5.7
- 线上数据库mysql5.5升级到5.7
- Mysql5.5升级到5.6步骤详解
- mysql4.0到mysql5.1数据升级
- mysql5.6升级到5.7
- 升级 mysql5.1 -> 5.5
- mysql5.1数据库升级
- mysql5.1升级5.6步骤以及出现问题解决方法
- innobackupex 升级mysql5.5到mysql5.7对系统性能不支持问题
- ubuntu 12.04 升级 mysql5.5 到 mysql 5.6
- MySQL升级:从MySQL4.1到MySQL5.0
- MYSQL5.1升级到5.5及utf8mb4使用方法
- mysql4升级到mysql5乱码问题解决方案
- Buff Your Brain I
- 10.3例题:最长上升子序列
- iphone开发面试总结
- VMWare卸载
- Hibernate学习之事务
- MySQL5.1升级到MySQL5.5 产品运行出现异常
- Linux多进程_消息通信_设计思想交流
- BuildForge service 端口的配置
- 【STL string中erase用法】
- Android画图之Matrix(二)
- Encode String&&湘潭大学月赛
- QListWidget和QStackedWidget
- 未来规划
- QListWidget和 QListWidgetItem