MySQL 复制 INSERT...SELECT 和 ORDER BY
INSERT ... SELECT 的文档页面指出为了使 INSERT ... SELECT 与复制一起使用，必须使用 ORDER BY 某些列，以便可以按可预测的顺序插入行，因此自动增量列将功能正常。
这让我想知道......当您执行 ORDER BY 且您选择的列不唯一时，MySQL 使用什么来决定进一步排序？它必须是确定性的，否则文档会说您需要选择一个 ORDER BY 唯一列，是吗？或者文档有误？
The documentation page for INSERT ... SELECT states that for INSERT ... SELECT to work with replication, an ORDER BY some column must be used so that rows may be inserted in a predictable order, therefore auto-increment columns will function correctly.
This has me wondering... when you do an ORDER BY and the column you pick is non-unique, what does MySQL use to decide further ordering? It must be deterministic, otherwise the documentation would say that you need to choose an ORDER BY unique column, yes? Or is the documentation wrong?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果对所有匹配行没有不同值的列使用 ORDER BY，则顺序是不确定的，是的。
如果插入的顺序很重要，这确实会破坏基于语句的复制。如果您在目标表中使用 auto_increment 列，则插入顺序当然很重要，但通常情况下并不重要。
如果您对语句有限制（更新和删除也是如此），顺序也很重要。
但是，如果您使用基于行的复制，您几乎可以做任何您喜欢的事情，并且不会破坏复制。至少，除非你真的尝试过。
这些规则非常复杂，因此我建议您始终在插入...选择或带有 LIMIT 的 UPDATE 上使用完全确定的 ORDER BY。
基于行的复制也是您的朋友。我在生产中并没有真正使用它，但了解
If you use an ORDER BY with a column which does not have distinct values for all matched rows, the order is nondeterminstic, yes.
This does break statement-based replication, IF the inserted order matters. The inserted order certainly matters if you use an auto_increment column in the destination table, but often doesn't otherwise.
Order also matters if you have a LIMIT on the statement (update and delete too).
However, if you use row-based replicaiton you can do pretty much anything you like and not break replication. At least, not unless you really try.
These rules are so complicated, that I recommend that you just ALWAYS use a completely determinstic ORDER BY on an insert... select, or an UPDATE with a LIMIT.
Row-based replication is also your friend. I've not really used it much in production, but understand that