MySQL 如何在 MariaDB 10 中启用大索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43379717/
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
How to enable large index in MariaDB 10?
提问by w.k
In Debian Jessie I installed MariaDB server 10.0.30 and I try to increase max key length. AFAIU it depends of the config parameter innodb_large_prefixbeing enabled. According to the docs, it also requires barracudafile format and innodb_file_per_table. After setting them in config and restarting server I see in client, that those parameters are set correctly:
在 Debian Jessie 中，我安装了 MariaDB 服务器 10.0.30，并尝试增加最大密钥长度。AFAIU 这取决于innodb_large_prefix启用的配置参数。根据文档，它还需要barracuda文件格式和innodb_file_per_table. 在配置中设置它们并重新启动服务器后，我在客户端看到这些参数设置正确：
> SHOW GLOBAL VARIABLES LIKE 'innodb_large%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | ON |
+---------------------+-------+
1 row in set (0.00 sec)
> SHOW GLOBAL VARIABLES LIKE 'innodb_file%';
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | OFF |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
+--------------------------+-----------+
4 rows in set (0.00 sec)
> SHOW GLOBAL VARIABLES LIKE 'innodb_page%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)
I am not sure, why innodb_file_format_maxis set Antelope, but while innodb_file_format_checkis OFF, it should not matter. Actually, even if I had it also set Barracuda, it did not made difference.
我不确定，为什么innodb_file_format_max是 set Antelope，但是虽然innodb_file_format_check是 OFF ，但没关系。实际上，即使我也设置了它Barracuda，也没有什么区别。
If i try now create table with large index like:
如果我现在尝试创建具有大索引的表，例如：
CREATE TABLE `some_table` (
`some_tableID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`column` varchar(750) COLLATE utf8mb4_estonian_ci NOT NULL DEFAULT '',
PRIMARY KEY (`some_tableID`),
KEY `column` (`column`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_estonian_ci;
I get error:
我得到错误：
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
On Ubuntu 16.04 with mysql server 5.7.17 are all related settings same (by default) and there is no problem with large index (for utf8mb4 it is 750*4 = 3000).
在带有 mysql 服务器 5.7.17 的 Ubuntu 16.04 上，所有相关设置都相同（默认情况下）并且大索引没有问题（对于 utf8mb4，它是 750*4 = 3000）。
What is wrong with my MariaDB setup?
我的 MariaDB 设置有什么问题？
回答by Rick James
It requires more than just those two settings...
它需要的不仅仅是这两个设置......
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC; -- or COMPRESSED
Perhaps all you need is to add ROW_FORMAT=...to your CREATE TABLE.
也许您所需要的只是添加ROW_FORMAT=...到您的CREATE TABLE.
These instructions are needed for 5.6.3 up to 5.7.7. Beginning with 5.7.7, the system defaults correctly to handle larger fields.
5.6.3 到 5.7.7 需要这些说明。从 5.7.7 开始，系统默认正确处理更大的字段。
Alternatively, you could use a "prefix" index:
或者，您可以使用“前缀”索引：
INDEX(column(191))
(But prefix indexing is flawed in many ways.)
（但是前缀索引在很多方面都有缺陷。）
"If the server later creates a higher table format, innodb_file_format_max is set to that value" implies that that setting is not an issue.
“如果服务器稍后创建更高的表格式，则 innodb_file_format_max 设置为该值”意味着该设置不是问题。
回答by elenst
innodb_large_prefixonly applies to COMPRESSEDand DYNAMICrow formats.
innodb_large_prefix仅适用于COMPRESSED和DYNAMIC行格式。
MariaDB 10.0 and 10.1 have InnoDB 5.6, which by default creates tables with ROW_FORMAT=Compact(even if innodb_file_formatis set to Barracuda). So, to use large prefixes, you need to specify the row format explicitly. Same is true for MySQL 5.6.
MariaDB 10.0 和 10.1 具有 InnoDB 5.6，默认情况下创建表ROW_FORMAT=Compact（即使innodb_file_format设置为Barracuda）。因此，要使用大前缀，您需要明确指定行格式。MySQL 5.6 也是如此。
InnoDB 5.7 by default creates the table with ROW_FORMAT=DYNAMIC, which is why the same CREATErelying on innodb_large_prefixworks in MySQL 5.7 and MariaDB 10.2 without any additional clauses.
InnoDB 5.7 默认使用 来创建表ROW_FORMAT=DYNAMIC，这就是为什么同样的CREATE依赖innodb_large_prefix在 MySQL 5.7 和 MariaDB 10.2 中工作而没有任何额外的子句。
回答by Kojo
After the steps provided by @Rick :
在@Rick 提供的步骤之后：
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
SET GLOBAL innodb_large_prefix=1;
-- logout & login (to get the global values);
I changed last step to
我将最后一步更改为
SET GLOBAL innodb_default_row_format=DYNAMIC;
So far so good.
到现在为止还挺好。
回答by Techifylogic
I think solution is answered here for the session but if you restart the MySQL, I don't think these settings will work.
我认为这里为会话提供了解决方案，但如果您重新启动 MySQL，我认为这些设置将不起作用。
For the permanent solution, you need to enter following code in your My.Ini file-
对于永久解决方案，您需要在 My.Ini 文件中输入以下代码-
## Innodb settings to bypass error of max size 737
innodb-file-format=barracuda
innodb-file-per-table=ON
innodb-large-prefix=ON
## Above 3 didnot work so i added below
innodb_default_row_format = 'DYNAMIC'
Hope it helps reducing rework and scratches on your head :)
希望它有助于减少返工和头上的划痕:)
