mysql 导入SQL文件报错, Specified key was too long; max key length is 767 bytes

Wesley13
• 阅读 797

----MySQL导入数据的时候,总是报错,本地测试都没办法测试---- 导出SQL文件正常, 在导入SQL文件的时候, 报错-----大部分数据表导入成功, 个别的失败

         在网上看到很多建议: 

  1. 建议别直接运行sql文件,你可以尝试打开sql文件自己选取sql执行
  2. Navicat运行SQL文件时, 去掉第二个√(每个运行中运行多重查询这个选项去掉√)
  3. 确认下字段类型跟内容是否一致,主键是否重复插入?可以单独执行出错的语句看下报错提示
  4. SQL文件分批次执行, 排查哪里出问题了.

         我看了一下我的两个数据库的版本, 线上的MySQL是5.7.24, 本地的MySQL是5.5.53, 不过我感觉版本应该没什么问题, 因为5.5已经支持utfmb4编码了. 我这里导入失败的原因也不是版本的问题(个人感觉). 记录一下解决的过程:

  1. 按照上面第二个建议, 去掉Navicat第二个√, 大部分可以导入成功, 然后检查有那些导入失败的. 我是用浏览器运行项目,根据项目报错 然后看那些数据表不存在, 来确定有哪些数据表没导入成功. 

    Base table or view not found: 1146 Table 'txsj_fdqs.hjmallind_goods' doesn't exist The SQL being executed was: SELECT (case when g.cat_id=0 
         then c2.name else c.name end) name FROM `hjmallind_goods` `g` LEFT JOIN `hjmallind_cat` `c` ON c.id=g.cat_id LEFT JOIN 
         `hjmallind_goods_cat` `gc` ON gc.goods_id=g.id LEFT JOIN `hjmallind_cat` `c2` ON gc.cat_id=c2.id WHERE ((`g`.`store_id`=5) AND 
         (`g`.`is_delete`=0) AND (`g`.`mch_id`=0)) AND (`g`.`type`=0) AND (((`gc`.`is_delete`=0) AND (`gc`.`store_id`=5)) OR (isnull(gc.id))) GROUP BY 
         `name` ORDER BY `g`.`cat_id`
    
  2. 说明数据库刚刚没有导入成功这张表, 那么现在单独导入这张表,  报错:   Specified key was too long; max key length is 767 bytes  ,针对这个问题, 又上网查了半天, 解决办法是, 打开这个表的SQL文件, 修改了联合主键, 把varchar(255)改成了varchar(60). 导入成功.

  3. 新的问题: 是什么导致的上面的问题呢?

    1. 猜测是编码问题, 因为utf8mb4编码是4字节的, 4×255>757 , 但是我去查看导入成功的表, 发现对应的字段也是utf8mb4编码. 那应该就不是编码导致的问题了.(因为这两个版本的都支持utf8mb4,怎么说也不应该是编码问题吧.)
    2. 猜测可能是本地PHPstudy默认设置了禁用innodb_large_prefix , 然后导致索引键前缀限制为767字节 (需要去研究一下) .如果禁用innodb_large_prefix,不管是什么表,索引键前缀限制为767字节。----参考
  4. 新建mysql库或者表的时候还有一个排序规则: (之前一直没去想)
    utf8_unicode_ci比较准确,utf8_general_ci速度比较快。通常情况下 utf8_general_ci的准确性就够我们用的了, 如果是utf8mb4那么对应的就是 utf8mb4_general_ci utf8mb4_unicode_ci

  5. utf8和utf8mb4的区别: mb4就是most bytes 4的意思,专门用来兼容四字节的unicode. utf8mb4是utf8的超集,除了将编码改为utf8mb4外不需要做其他转换, 当然,为了节省空间,一般情况下使用utf8也就够了.  原来mysql支持的 utf8 编码最大字符长度为 3 字节,如果遇到 4 字节的宽字符就会插入异常了.

  6.        系统变量innodb_large_prefix开启了,则对于使用DYNAMIC或COMPRESSED行格式的InnoDB表,索引键前缀限制为3072字节。如果禁用innodb_large_prefix,不管是什么表,索引键前缀限制为767字节。

           上述的bug很明显是索引超出了限制的长度767(我司生产上innodb_large_prefix禁用了):

           我发现报错的那张表建立了一个varchar类型的索引,varchar(255),觉得没什么问题,其实不然,上述的767是字节,而varchar类型是字符,同时我发现我使用的字符集为(utf8mb4),这个指每个字符最大的字节数为4,所以很明显 4*255 > 767

    所以就报上述错了(Specified key was too long; max key length is 767 bytes)。

           解决方法:

           改变varchar的字符数,我改成了64就可以了。varchar(64)

           或者启用innodb_large_prefix,那么限制值会增加到3072            ----------------------------https://blog.csdn.net/chenjianhuideyueding/article/details/88426021

  7. 终于找到了原因, 去本地MySQL查看是否开启了innodb_large_prefix, 这个参数是限制索引列长度的

    show variables like 'innodb_large_prefix';         //查看是否开启innodb_large_prefix 的命令
    

    发现本地的MySQL默认是没有开启的. 线上的是开启了的(不过不知道是之前有人开启的, 还是5.7版本默认开启的. 应该是默认开启的! 反正不用管这些~无所谓) 每个人的MySQL配置不同, 要看具体什么情况了.....

  8. 关于如何开启innodb_large_prefix,解决方案也在这里:  可参考https://www.cnblogs.com/kerrycode/p/9680881.html

  9. mysql uses 1 or 2 extra bytes to record the values length: 1 byte if the column's max length is 255 bytes or less, 2 if it's longer than 255 bytes. the utf8_general_ci encoding needs 3 bytes per character so varchar(20) uses 61 bytes, varchar(500) uses 1502 bytes in total 1563 bytes。引自--https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
2年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Stella981 Stella981
2年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Stella981 Stella981
2年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
2年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
2个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这