現(xiàn)象描述

在給varchar字段建立索引時(shí)，報(bào)錯(cuò)如下：

[root@localhost:(test) 13:53:27]> CREATE INDEX b_name_IDX USING BTREE ON test.b(name);ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

查看表結(jié)構(gòu)：

CREATE TABLE `b` (
  `name` varchar(250) DEFAULT NULL,
  `standardized_name` varchar(250) DEFAULT NULL,
  `is_reagent` int(11) NOT NULL DEFAULT '0',
  `is_solvent` int(11) NOT NULL DEFAULT '0',
  `is_catalyst` int(11) NOT NULL DEFAULT '0',
  `is_ligand` int(11) NOT NULL DEFAULT '0',
  `to_delete` int(11) DEFAULT '0',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

原因分析

索引字段的長度大于767，或者說，使用到的字段的長度和大于767則報(bào)錯(cuò)。

MySQL 5.6 中的innodb_large_prefix默認(rèn)是關(guān)閉的。

在MySQL中，innodb_large_prefix 參數(shù)是一個(gè) InnoDB 存儲(chǔ)引擎的配置選項(xiàng)。

這個(gè)參數(shù)控制是否允許使用超過767字節(jié)（或255個(gè)字符）的索引前綴。

默認(rèn)情況下，在MySQL 5.6及以前版本中，InnoDB存儲(chǔ)引擎對(duì)索引列的最大長度限制為767字節(jié)。

對(duì)于變長數(shù)據(jù)類型如VARCHAR，這個(gè)限制包括了字符集的每個(gè)字符可能占用的字節(jié)數(shù)，而不是僅僅指字符數(shù)。

例如，如果你使用的是UTF-8字符集，每個(gè)字符可能占用1到4個(gè)字節(jié)，所以一個(gè)VARCHAR(255)字段的實(shí)際最大長度可能會(huì)遠(yuǎn)小于255個(gè)字符。

當(dāng) innodb_large_prefix 設(shè)置為 ON 時(shí)，InnoDB 支持更大的索引前綴長度，最大可以達(dá)到3072字節(jié)。

這意味著你可以創(chuàng)建更長的索引，特別是對(duì)于包含大量變長數(shù)據(jù)類型的列。

這對(duì)于處理大數(shù)據(jù)表和需要更復(fù)雜查詢的情況非常有用。

要啟用 innodb_large_prefix，你可以在 MySQL 配置文件（如 my.cnf 或 my.ini）中添加以下行，并重啟 MySQL 服務(wù)以應(yīng)用更改：

[mysqld]
innodb_large_prefix = ON

或者，你可以在運(yùn)行時(shí)通過設(shè)置全局變量來開啟它：

SET GLOBAL innodb_large_prefix = ON;

請(qǐng)注意，為了使 innodb_large_prefix 生效，還需要同時(shí)滿足以下條件：

• 數(shù)據(jù)庫文件格式必須是 Barracuda。
• 表格式必須是 DYNAMIC 或 COMPRESSED。
• 對(duì)于 ROW_FORMAT=COMPACT 的表，仍然有 767 字節(jié)的索引前綴限制。

有關(guān)這些條件的詳細(xì)信息，請(qǐng)參閱 MySQL 文檔。

問題處理

set global innodb_large_prefix=on;
show variables like 'innodb_large_prefix';
alter table b Row_format=dynamic;
set global innodb_file_format=BARRACUDA;

再次加索引：

[root@localhost:(test) 13:54:18]> CREATE INDEX b_name_IDX USING BTREE ON test.b(name);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論