mysql - 为什么这对于mysql复制不安全

121214 11:54:30 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: update db_ds_pax p
        set p.mbp_id = 8861912, updated_ts = now()
        where p.flight_id = 2506912
        and p.logically_deleted = 0
        and (
            exists (
                    select * from db_bkg_passenger bkgp
                    where bkgp.bkg_pax_id = p.bkg_pax_id
                    and bkgp.ticket_num = '2202326623256'
                    and bkgp.logically_deleted = 0 )
            or exists (
                    select * from db_dcs_pax dcsp
                    where dcsp.dcs_pax_id = p.dcs_pax_id
                    and dcsp.ticket_num = '2202326623256'
                    and dcsp.logically_deleted = 0 ))

这句话中有什么不安全的地方？我没有插入任何东西，只是在更新。实际上，我什至没有选择任何将用于更新的内容。

我试图使用左联接重写，但是它仍然抱怨。我还认为也许updated_ts = now()是罪魁祸首，并将其设置为固定时间，但仍是相同的警告。

服务器是Oracle 5.5.27

最佳答案

从另一个表中进行选择后，写入具有自动增量列的表的语句是不安全的，因为检索行的顺序决定了要写入的行（如果有）。该顺序无法预测，在主服务器和从服务器上可能会有所不同


除了复制警告消息中的解释外，我什么也没做。

因此，似乎UPDATE被视为“写入表”

关于mysql - 为什么这对于mysql复制不安全，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13877763/