MySQL <TABLE> 的表存储引擎在按查询排序时没有此选项（错误 1031）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29486583/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Table storage engine for <TABLE> doesn't have this option on order by query (ERROR 1031)
提问by Umair Iqbal
Table storage engine for <TABLE> doesn't have this option.
<TABLE> 的表存储引擎没有这个选项。
This is the error returned by MySQL on an order byquery. The column type is varchar(2000).
这是 MySQL 在order by查询中返回的错误。列类型为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.
错误 1031 (HY000)：“dbo.table_1”的表存储引擎没有此选项。
Why does this happen? And how can I fix it?
为什么会发生这种情况？我该如何解决？
回答by jhaagsma
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_FORMAToptions appear to be invalid.
当您导入使用 MyISAM 创建但后来切换到 InnoDB 的表定义时，似乎会出现此问题；结果ROW_FORMAT选项似乎无效。
If you're trying to import an exported database and encounter this problem, you can simply search and replace ROW_FORMAT=FIXEDwith nothing.
如果您尝试导入导出的数据库并遇到此问题，您可以简单地搜索并替换ROW_FORMAT=FIXED为空。
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.
问题解决了！感谢 jbrahy 指出问题在于 ROW_FORMAT。
EDIT: Updated to work for more platforms as per @seven's suggestion
编辑：根据@seven 的建议更新为适用于更多平台
回答by jbrahy
I get the same error when I import a table definition that's InnoDB with ROW_FORMAT=DYNAMIC in it. The table was created with a MyISAM engine but I later switched it to InnoDB. When I removed the ROW_FORMAT=DYNAMIC from the create table statement and recreated the table it worked fine. My solution to your problem would be this.
当我导入一个包含 ROW_FORMAT=DYNAMIC 的 InnoDB 表定义时，我遇到了同样的错误。该表是使用 MyISAM 引擎创建的，但后来我将其切换到 InnoDB。当我从 create table 语句中删除 ROW_FORMAT=DYNAMIC 并重新创建表时，它工作正常。我对你的问题的解决方案是这样的。
show create table `dbo.table_1`;
then take the output from that command and remove the ROW_FORMAT=DYNAMIC then rename the table to dbo.table_1_old
然后从该命令中获取输出并删除 ROW_FORMAT=DYNAMIC 然后将表重命名为 dbo.table_1_old
rename table `dbo.table_1` to `dbo.table_1_old`;
Then execute the create table statement from the first step i.e.
然后执行第一步的create table语句即
-- don't use this create as there are missing columns use yours
create table `dbo.table_1` (textT VARCHAR(255));
Then repopulate your table with the old data.
然后用旧数据重新填充您的表。
insert into `dbo.table_1` select * from `dbo.table_1_old`;
Then you should be able to execute your original SQL
然后你应该能够执行你原来的 SQL
select * from `dbo.table_1` order by textT;
回答by Michele Manzato
You can also try this:
你也可以试试这个：
ALTER TABLE `dbo.table_1` ROW_FORMAT = DEFAULT ;
回答by ANF-67
This problem appears to occur when you're importing a table definition to MySQL 5.7 that had been created with MySQL 5.6 and earlier. The same error can produceb by option KEY_BUFFER_SIZE=8192 and similar sizes defined in bytes for INNODB ENGINE. I had this error when I'm importing base from sql-dump. Decision: sed -ie 's/KEY_BLOCK_SIZE=16384//g' my-file-sql_dump.sql
当您将使用 MySQL 5.6 及更早版本创建的表定义导入 MySQL 5.7 时，似乎会出现此问题。选项 KEY_BUFFER_SIZE=8192 和 INNODB ENGINE 以字节为单位定义的类似大小可以产生相同的错误。当我从 sql-dump 导入 base 时出现此错误。决策：sed -ie 's/KEY_BLOCK_SIZE=16384//g' my-file-sql_dump.sql
回答by prgmrDev
I was facing this problem and my backup file was encrypted .zsqlfile. So I modified my.cnf by adding innodb_strict_mode = off. It worked fine
我遇到了这个问题，我的备份文件是加密.zsql文件。所以我通过添加innodb_strict_mode = off. 它工作得很好
