MYSQL踩坑经历

设置自动记录创建和更新时间的坑

在MySQL5.7版本下执行下面建表语句不会报错。MySQL5.5版本使用下面的语句会报错：
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,

上面的语句在MySQL5.7执行没问题，但在5.5执行有两个bug：
1、MySQL5.5版本只有timestamp类型能使用CURRENT_TIMESTAMP，datetime不能设置CURRENT_TIMESTAMP。
2、MySQL5.5版本一张表只能有一个字段可以设置CURRENT_TIMESTAMP。不支持多个字段设置CURRENT_TIMESTAMP了。

解决方案

1、用触发器。
2、用代码去更新时间吧。(所以在建表的时候还是得先判断测试环境和正式环境的数据库版本是否满足要求，不然后面就蛋疼了~~)

设置字符串索引的长度限制

在MySQL5.7下执行以下建表语句不会报错。但是在MySQL5.5报错了。
建表语句如下：
CREATE TABLE etcd_key_desc(
id int(10) unsigned NOT NULL AUTO_INCREMENT,
key_name varchar(200) NOT NULL COMMENT ‘key的名称’,
PRIMARY KEY (id),
UNIQUE KEY idx_key_name (key_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=‘key详情表’;

报错内容：
Specified key was too long; max key length is 767 bytes

分析：
MySQL5.6以前的版本，字符索引长度限制为767个字节，到5.7版本才变为3072个字节。
这里的key_name作为索引的长度超过了767个字节，因为字符集类型是是utf8mb4,一个字符占4个字节，所以索引长度为200*4=800字节。所以在5.6版本才会报错，而5.7版本不会。

MySQL文档：
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.

三种解决方案

1、如果确定key_name不会存储特殊字符，就可以修改字符集为utf8(CHARSET=utf8)，因为一个utf8类型的字符占用的3个字节。所以utf8下的字符我们一般可以设置255长度：255*3=765。
2、把key_name的长度设为180(反正乘以4小于767字节即可)
3、设置前缀索引

总结

建表的时候，要确认测试环境和正式环境的MySQL版本，避免版本不兼容带来的代码返工！！！