----MySQL导入数据的时候,总是报错,本地测试都没办法测试---- 导出SQL文件正常, 在导入SQL文件的时候, 报错-----大部分数据表导入成功, 个别的失败
在网上看到很多建议:
- 建议别直接运行sql文件，你可以尝试打开sql文件自己选取sql执行
- Navicat运行SQL文件时, 去掉第二个√(每个运行中运行多重查询这个选项去掉√)
- 确认下字段类型跟内容是否一致，主键是否重复插入？可以单独执行出错的语句看下报错提示
- SQL文件分批次执行, 排查哪里出问题了.
我看了一下我的两个数据库的版本, 线上的MySQL是5.7.24, 本地的MySQL是5.5.53, 不过我感觉版本应该没什么问题, 因为5.5已经支持utfmb4编码了. 我这里导入失败的原因也不是版本的问题(个人感觉). 记录一下解决的过程:
按照上面第二个建议, 去掉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`说明数据库刚刚没有导入成功这张表, 那么现在单独导入这张表, 报错: Specified key was too long; max key length is 767 bytes ,针对这个问题, 又上网查了半天, 解决办法是, 打开这个表的SQL文件, 修改了联合主键, 把varchar(255)改成了varchar(60). 导入成功.
新的问题: 是什么导致的上面的问题呢?
- 猜测是编码问题, 因为utf8mb4编码是4字节的, 4×255>757 , 但是我去查看导入成功的表, 发现对应的字段也是utf8mb4编码. 那应该就不是编码导致的问题了.(因为这两个版本的都支持utf8mb4,怎么说也不应该是编码问题吧.)
- 猜测可能是本地PHPstudy默认设置了禁用innodb_large_prefix , 然后导致索引键前缀限制为767字节 (需要去研究一下) .如果禁用innodb_large_prefix，不管是什么表，索引键前缀限制为767字节。----参考
新建mysql库或者表的时候还有一个排序规则: (之前一直没去想)
utf8_unicode_ci比较准确，utf8_general_ci速度比较快。通常情况下 utf8_general_ci的准确性就够我们用的了, 如果是utf8mb4那么对应的就是 utf8mb4_general_ci utf8mb4_unicode_ciutf8和utf8mb4的区别: mb4就是most bytes 4的意思，专门用来兼容四字节的unicode. utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换, 当然，为了节省空间，一般情况下使用utf8也就够了. 原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了.
系统变量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
终于找到了原因, 去本地MySQL查看是否开启了innodb_large_prefix, 这个参数是限制索引列长度的
show variables like 'innodb_large_prefix'; //查看是否开启innodb_large_prefix 的命令发现本地的MySQL默认是没有开启的. 线上的是开启了的(不过不知道是之前有人开启的, 还是5.7版本默认开启的. 应该是默认开启的! 反正不用管这些~无所谓) 每个人的MySQL配置不同, 要看具体什么情况了.....
关于如何开启innodb_large_prefix，解决方案也在这里: 可参考https://www.cnblogs.com/kerrycode/p/9680881.html
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
