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

客官°小女子只卖身不卖艺 2022-06-14 06:53 112阅读 0赞

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

[SELECT]: http://dev.mysql.com/doc/refman/5.5/en/select.html
[DELETE]: http://dev.mysql.com/doc/refman/5.5/en/delete.html