問題闡述

• 建表語句

  
      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