索引长度太长了 #3

Activity

1. rabbitLove520 commented on Nov 17, 2019

   

   rabbitLove520

   on Nov 17, 2019

   Last edited by rabbitLove520

   Author

   More actions

   看样子是MySQL版本的问题，我的是MySQL 5.6

   767 bytes is the stated prefix limitation for InnoDB tables in MySQL version 5.6 (and prior versions). It's 1,000 bytes long for MyISAM tables. In MySQL version 5.7 and upwards this limit has been increased to 3072 bytes.

   You also have to be aware that if you set an index on a big char or varchar field which is utf8mb4 encoded, you have to divide the max index prefix length of 767 bytes (or 3072 bytes) by 4 resulting in 191. This is because the maximum length of a utf8mb4 character is four bytes. For a utf8 character it would be three bytes resulting in max index prefix length of 254.

   5.6里，utf8mb4编码索引长度最多191。话说这些主键也不用varchar(256)吧。

   参考
2. tycoding commented on Nov 17, 2019

   

   tycoding

   on Nov 17, 2019

   Owner

   More actions

   看样子是MySQL版本的问题，我的是MySQL 5.6

   767 bytes is the stated prefix limitation for InnoDB tables in MySQL version 5.6 (and prior versions). It's 1,000 bytes long for MyISAM tables. In MySQL version 5.7 and upwards this limit has been increased to 3072 bytes.
   You also have to be aware that if you set an index on a big char or varchar field which is utf8mb4 encoded, you have to divide the max index prefix length of 767 bytes (or 3072 bytes) by 4 resulting in 191. This is because the maximum length of a utf8mb4 character is four bytes. For a utf8 character it would be three bytes resulting in max index prefix length of 254.

   5.6里，utf8mb4编码索引长度最多191。话说这些主键也不用varchar(256)吧。

   参考

   具体的话是参考的官方的表结构：https://github.com/spring-projects/spring-security-oauth/blob/master/spring-security-oauth2/src/test/resources/schema.sql ，当然是和MySQL版本有关，我用的是MySQL5.7，官方的表结构某些数据类型也是需要修改的