MySQL5.1升级到MySQL5.5 产品运行出现异常

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 nested SELECT 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-table DELETE 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-table DELETE 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-table DELETE statements. Alias declarations are permitted only in the table_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 a2
    WHERE 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 a2
    WHERE 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