SQL类 本节介绍了SQL类相关问题与处理方法。 建表时timestamp字段默认值无效 场景描述 客户执行一个建表SQL语句失败,详细SQL语句及报错如下: CREATE TABLE clustermembership ( ... sessionstart TIMESTAMP DEFAULT '19700101 00:00:01', ... ); 执行失败,失败原因:ERROR 1067: Invalid default value for 'sessionstart' 原因分析 表字段类型是TIMESTAMP类型, 关于timestamp字段:MySQL会把该字段插入的值从当前时区转换成UTC时间(世界标准时间)存储,查询时,又将其从UTC时间转化为当前时区时间返回 1. timestamp类型字段的时间范围:'19700101 00:00:01' UTC '20380119 03:14:07' UTC,详见官方文档: 2. 使用如下命令,查看当前的时区: show variables like "%zone%"; 3. 故障场景中使用的是utc+8时区,如下图,所以timestamp字段默认值需要加8小时才是有效范围,有效支持的范围是从19700101 08:00:01开始; 解决方案 执行命令,修改timestamp字段参数默认值。 sessionstart TIMESTAMP DEFAULT '19700101 08:00:01', 索引长度限制导致修改varchar长度失败 场景描述 执行alter table修改表结构失败,报错如下: Specified key was too long; max key length is 3072 bytes 原因分析 在“innodblargeprefix”设置为off的情况下,InnoDB表的单字段索引的最大字段长度不能超过767字节,联合索引的每个字段的长度不能超过767字节,且所有字段长度合计不能超过3072字节。 当“innodblargeprefix”设置为on时,单字段索引最大长度可为3072字节,联合索引合计最大长度可为3072字节。 索引长度与字符集相关。使用utf8字符集时,一个字符占用三个字节,在“innodblargeprefix”参数设置为on情况下,索引的所有字段的长度合计最大为1072个字符。 查看表结构如下: CREATE TABLE xxxxx ( …… subscriptiontype varchar(64) NOT NULL DEFAULT 'DEVICEEXCEPTION' COMMENT '订阅类型', authkey varchar(255) DEFAULT '' COMMENT '签名,接口请求头会根据这个值增加token', createtime timestamp NOT NULL DEFAULT CURRENTTIMESTAMP COMMENT '创建时间', updatetime timestamp NOT NULL DEFAULT CURRENTTIMESTAMP ON UPDATE CURRENTTIMESTAMP COMMENT '修改时间', PRIMARY KEY (id) USING BTREE, UNIQUE KEY enterpriseid (subscriptiontype,enterpriseid,callbackurl) USING BTREE) ) ENGINEInnoDB AUTOINCREMENT1039 DEFAULT CHARSETutf8 ROWFORMATDYNAMIC 该表使用了utf8字符集,一个字符占用三个字节。联合索引“enterpriseid”包含了“callbackurl”字段,如果执行DDL操作将“callbackurl”修改为varchar(1024),会超出联合索引最大长度限制,所以报错。