Mysql5.5 Specified key was too long； max key length is 767 bytes

最新推荐文章于 2022-03-09 18:05:23 发布

白不懂黑的静

最新推荐文章于 2022-03-09 18:05:23 发布

阅读量704

点赞数 2

分类专栏： MySql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/yyj108317/article/details/111030529

版权

MySql 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

1、现象

在mysql5.5版本中执行如下SQL建表语句

CREATE TABLE `oauth_client_details` (
  `client_id` VARCHAR(256) CHARACTER SET utf8 NOT NULL,
  `resource_ids` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  `client_secret` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  `scope` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  `authorized_grant_types` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  `web_server_redirect_uri` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  `authorities` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  `access_token_validity` INT(11) DEFAULT NULL,
  `refresh_token_validity` INT(11) DEFAULT NULL,
  `additional_information` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  `autoapprove` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`client_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

执行时报如下错：

Specified key was too long; max key length is 767 bytes

2、解决办法

第一步：修改mysql配置文件，我的路径是：C:\Program Files\MySQL\MySQL Server 5.5\my.ini

增加如下配置：

innodb_large_prefix = 1 
innodb_file_format = Barracuda 
innodb_file_per_table = 1

第二步：在sql语句后面增加 ROW_FORMAT = DYNAMIC，如下所示：

CREATE TABLE `oauth_client_details` (
  `client_id` VARCHAR(256) CHARACTER SET utf8 NOT NULL,
  `resource_ids` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  `client_secret` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  `scope` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  `authorized_grant_types` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  `web_server_redirect_uri` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  `authorities` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  `access_token_validity` INT(11) DEFAULT NULL,
  `refresh_token_validity` INT(11) DEFAULT NULL,
  `additional_information` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  `autoapprove` VARCHAR(256) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`client_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 ROW_FORMAT = DYNAMIC;

第三步：重启mysql 服务，执行语句

别问我为什么，照着做就是了