插入 MySQL 表时忽略 ORDER BY [英] ORDER BY ignored when INSERTing into MySQL table

问题描述

我刚刚将 MySQL 5.0 服务器升级到 MySQL 5.5，发现以前工作的存储例程已损坏.区别:MySQL 5.5 似乎以任意顺序INSERT 行.所以在下面的代码中 ORDER BY 子句不起作用.AFAIK，它曾经在 MySQL 5.0 中使用过.

I just upgraded a MySQL 5.0 server to MySQL 5.5 and found that stored routines that worked before had broken. Difference: MySQL 5.5 seems to INSERT rows in an arbitrary order. So in the following code the ORDER BY clause has no effect. AFAIK, it used to have that in MySQL 5.0.

INSERT INTO MyTable
SELECT * FROM MyOtherTable ORDER BY Col1, Col2 DESC;

人们说，根据定义，顺序无关 在 INSERT s 中:当使用表中的 SELECT 时，只需使用 ORDER BY.问题是我使用游标来循环表并执行复杂的操作.当然，我可以将 ORDER BY 语句放在游标定义上:

People say that, by definition, order is irrelevant in INSERTs: Just use ORDER BY when using SELECT from the table. Problem is I use a cursor to loop the table and perform complex operations. Surely I can put the ORDER BY statement on the cursor definition instead:

DECLARE cur CURSOR FOR SELECT * FROM MyTable ORDER BY Col1, Col2 DESC;

但这会减慢例程:从 MySQL 5.0 上的 10 秒到 MySQL 5.5 上的 10 多分钟.

But that slows down the routine: from 10 seconds on MySQL 5.0 to over 10 minutes on MySQL 5.5.

对如何解决问题有任何想法吗?

Any ideas on how to solve problem?

推荐答案

在 (Col1, Col2) 上添加索引以加快排序.

Add an index on (Col1, Col2) to speed up ordering.

这篇关于插入 MySQL 表时忽略 ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！