MySQL默认顺序取决于WHERE [英] MySQL default order depends on WHERE

问题描述

可能重复:
MySQL中的默认排序顺序(ALTER TABLE… ORDER BY&hellip ;;)

Possible Duplicate:
Default sort-ordering in MySQL (ALTER TABLE … ORDER BY …;)

我有一个这样的表:

CREATE TABLE IF NOT EXISTS `table_test` (
  `id` mediumint(8) unsigned NOT NULL,
  `country` enum('AF','AX','AL') DEFAULT NULL,
  `number` tinyint(3) unsigned DEFAULT NULL,
  `sort_order` double unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `country` (`country`),
  KEY `id` (`id`,`country`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1;

我有一个表，该表更改了默认顺序，例如:

I have a table for which I changed the default ordering like:

ALTER TABLE test_table ORDER BY sort_order ASC;

此表永远不会更新，并且在其生命周期内不会删除或添加任何记录.这一切似乎都有效，因此，如果我使用以下查询:

This table is never updated and no records are removed or added during its life. This all seems to work so if I use the folowwing query:

SELECT * FROM test_table LIMIT 10

它以正确的顺序返回10条记录.

It returns the 10 records in the right order.

即使我使用:

SELECT * FROM test_table WHERE num=3

它以正确的顺序返回结果.

it returns the results in the right order.

但是，如果我这样做

SELECT * FROM test_table WHERE country='AX'

它将以相反的顺序返回结果.

It will return the results in reversed order.

有人可以告诉我这是怎么发生的吗?

Can someone tell me how this can happen?

推荐答案

在表上指定ORDER BY只是帮助引擎以相同顺序加速查询.不会强制mysql总是以相同的顺序返回结果.

Specifying the ORDER BY on a table is just a help for the engine to speed up queries with the same order. It will not force mysql to always return the result with the same ordering.

在此描述: http://dev.mysql. com/doc/refman/5.1/en/alter-table.html

"ORDER BY使您可以按特定顺序创建具有行的新表.请注意，表在插入和删除后不会保持该顺序.主要在您知道要查询的大部分信息时，此选项才有用在大多数情况下，按一定顺序排列行.通过对表进行重大更改后使用此选项，您可能可以获得更高的性能.在某些情况下，如果表按顺序排列，则对MySQL的排序可能会更容易.您想稍后订购的列."

"ORDER BY enables you to create the new table with the rows in a specific order. Note that the table does not remain in this order after inserts and deletes. This option is useful primarily when you know that you are mostly to query the rows in a certain order most of the time. By using this option after major changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later. "

因此，您也必须在查询中使用ORDER BY表达式.

So you must use the ORDER BY expression in your queries too.

这篇关于MySQL默认顺序取决于WHERE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！