的表存储引擎在查询顺序（ERROR 1031）上没有此选项(Table storage engine for doesn't have this option on order by qu

的表存储引擎在查询顺序（ERROR 1031）上没有此选项(Table storage engine for doesn't have this option on order by query (ERROR 1031))

<TABLE>的表存储引擎没有此选项。

这是MySQL order by查询order by返回的错误。 列类型为varchar(2000) 。

查询：

select * from `dbo.table_1` order by textT;

返回错误：

错误1031（HY000）：'dbo.table_1'的表存储引擎没有此选项。

为什么会这样呢？ 我该怎么解决？

Table storage engine for <TABLE> doesn't have this option.

This is the error returned by MySQL on an order by query. The column type is varchar(2000).

Query:

select * from `dbo.table_1` order by textT;

Error returned:

ERROR 1031 (HY000): Table storage engine for 'dbo.table_1' doesn't have this option.

Why does this happen? And how can I fix it?

最满意答案

当您导入使用MyISAM创建的表定义但稍后切换到InnoDB时，会出现此问题; 所产生的ROW_FORMAT选项似乎无效。

如果您要导入导出的数据库并遇到此问题，您可以简单地搜索并替换ROW_FORMAT=FIXED 。

我使用以下这样做真的很快：

sed -ie 's/ROW_FORMAT=FIXED//g' backup.sql

问题解决了！ 感谢jbrahy指出，这是问题的ROW_FORMAT。

编辑：根据@七的建议，更新为更多平台工作

This problem appears to occur when you're importing a table definition that had been created with MyISAM but later was switched to InnoDB; the resulting ROW_FORMAT options appear to be invalid.

If you're trying to import an exported database and encounter this problem, you can simply search and replace ROW_FORMAT=FIXED with nothing.

I used the following to do so really quickly:

sed -ie 's/ROW_FORMAT=FIXED//g' backup.sql

Problem solved! Thanks to jbrahy for pointing out that it was the ROW_FORMAT that was the problem.

EDIT: Updated to work for more platforms as per @seven's suggestion