MySQL创建表失败：Index column size too large. The maximum column size is 767 bytes

问题阐述

• 建表语句

  
      DROP TABLE IF EXISTS oauth_client_details;
      CREATE TABLE oauth_client_details (
          client_id VARCHAR(256),
          resource_ids VARCHAR(256),
          client_secret VARCHAR(256),
          scope VARCHAR(256),
          authorized_grant_types VARCHAR(256),
          web_server_redirect_uri VARCHAR(256),
          authorities VARCHAR(256),
          access_token_validity INTEGER,
          refresh_token_validity INTEGER,
          additional_information VARCHAR(4096),
          autoapprove VARCHAR(256),
          PRIMARY KEY (`client_id`)
      )ENGINE=INNODB DEFAULT CHARSET=utf8;
  
  
  

• 报错信息

  
  
      Index column size too large. The maximum column size is 767 bytes
  
  
  

• 原因分析

  1. 当你创建或设置主键的时候，mysql会自动添加一个与主键对应的唯一索引

  2. 字符编码格式是utf-8，一个字符占了3个字节(utf8mb4编码，一个字符是占了4个字节)

解决方案

• 修改字段的长度(把索引的字段取消部分)

  修改主键长度为client_id VARCHAR(255)

• 限定字段的前n个字符为索引

  
      -- 表示后面store_code，sku_division_code，abc三个字段取前20字符作为唯一索引
      UNIQUE KEY `uniq_store_code` (`record_date`,`store_code`(20),`sku_division_code`(20),`abc`(20))
  
  
  
  

• 启用系统变量innodb_large_prefix

  
      -- 1. 开启数据库支持索引
      show variables like '%innodb_large_prefix%';
      set global innodb_large_prefix=on;
  
      -- 2. 查看当前的innodb_file_format引擎格式类型是不是BARRACUDA
      show variables like '%innodb_file_format%';
      -- 显示结果如下
      innodb_file_format    Antelope
      innodb_file_format_check    ON
      innodb_file_format_max    Antelope
  
      set global innodb_file_format=Barracuda;
  
      -- 3. 查看当前表是否自动格式化索引
      show table status from MyDB where name='TEST';
      -- 修改表自动格式化索引
      ALTER TABLE TEST ROW_FORMAT=DYNAMIC;
      -- 或者在建表的时候加上自动格式化索引
      ROW_FORMAT=DYNAMIC
  
  

其他

• 启用系统变量innodb_large_prefix后不生效

  
      -- 未知原因：重启mysql服务后innodb_large_prefix，innodb_file_format恢复默认
      SELECT VERSION(); --当前mysql版本为5.5.49
      
  
      -- 重新修改完1，2参数后，创建表的时候加上自动格式化索引
      DROP TABLE IF EXISTS oauth_client_details;
      CREATE TABLE oauth_client_details (
          client_id VARCHAR(256),
          resource_ids VARCHAR(256),
          client_secret VARCHAR(256),
          scope VARCHAR(256),
          authorized_grant_types VARCHAR(256),
          web_server_redirect_uri VARCHAR(256),
          authorities VARCHAR(256),
          access_token_validity INTEGER,
          refresh_token_validity INTEGER,
          additional_information VARCHAR(4096),
          autoapprove VARCHAR(256),
          PRIMARY KEY (`client_id`)
      )ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  
  
      -- 插入测试数据
      INSERT INTO oauth_client_details
          (client_id, client_secret, scope, authorized_grant_types,
          web_server_redirect_uri, authorities, access_token_validity,
          refresh_token_validity, additional_information, autoapprove)
      VALUES
          ('user-client', '$2a$10$o2l5kA7z.Caekp72h5kU7uqdTDrlamLq.57M1F6ulJln9tRtOJufq', 'all',
          'authorization_code,refresh_token,password', NULL, NULL, 3600, 36000, NULL, TRUE);
  
      INSERT INTO oauth_client_details
          (client_id, client_secret, scope, authorized_grant_types,
          web_server_redirect_uri, authorities, access_token_validity,
          refresh_token_validity, additional_information, autoapprove)
      VALUES
          ('order-client', '$2a$10$GoIOhjqFKVyrabUNcie8d.ADX.qZSxpYbO6YK4L2gsNzlCIxEUDlW', 'all',
          'authorization_code,refresh_token,password', NULL, NULL, 3600, 36000, NULL, TRUE);
  
  
  

参考链接

https://blog.csdn.net/noDr_butTry/article/details/90813327