优化数据库表和解决SQL备份恢复时的问题

前言

如果你使用Wordpress的Database Cleaner插件或者其他清理插件，那么就应该对清理Wordpress博客中的垃圾略有了解，他通过删除文章的修订记录来节省空间；或者，如果你使用自己管理的phpmyadmin查看数据库，就会发现打开的表有一个“优化表”选项，他也可以节省空间。

然而，你可能会发现执行插件的清理操作之后，数据库的大小并没有变；而你执行phpmyadmin的“优化表”选项后，它执行失败了，并且看到了这样的提示：“Error: Invalid default value for 'post_date'”.

没错，我就碰到了这个问题……这里记录一下遇到的问题以及解决的办法。

清理原理

文章的修订

当文章的撰写者编辑Wordpress的文章时，会产生文章的历史版本。换句话说，一篇文章在数据库中存储着很多个历史副本。以我自己的情况来说，每篇文章可能有十数个修订版。显然，让这些修订版一直存在着是比较占用空间的，特别是你不考虑将它们回滚的时候。

Database Cleaner这样的插件可以找到这样的修订版，并将他们删掉，这样就可以节省空间。

然而，就像前言中说的一样，有的时候你删除了很多副本，但是却发现显示的数据库大小纹丝不动，这是怎么回事呢？

数据库中的删除

这就牵扯到一些关键知识了。比如，运行这样一条删除语句：

DELETE FROM my_table WHERE id < 10;

这句SQL很好理解，就是把满足条件id < 10的数据都从my_table这个表里面删掉了。然而，MySQL使用的是懒删除策略，换句话说，它只是标记了这些数据是无效的，但是实际上并没有从文件中真的删掉，自然就没有节省这些空间。这样做也是有好处的，比如说减少数据移动操作、如果后续插入可以原地修改等等好处。

然而，我们现在想要删除的是文章的修订版，自然是希望真的节省空间。那么有什么办法呢？没错，就是开头提到的phpmyadmin的“优化表”，他会把这些标记删除的冗余数据真的删掉。它的原理实际上就是执行一条SQL语句：

OPTIMIZE TABLE `wp_posts`;

这样就可以把Wordpress中存储文章的表wp_posts给优化了。

表损坏问题

定位原因

然而，执行这步优化操作会遇到如下错误：“Error: Invalid default value for 'post_date'”

查看表结构，发现表wp_posts中有四个关于时间的字段：

post_date，文章发布日期
post_date_gmt，文章发布日期（格林威治时间）
post_modified，文章最后修改日期
post_modified_gmt，文章最后修改日期（格林威治时间）

然后，你可能会在phpmyadmin中看到它的默认值是：0000-00-00 00:00:00. 结合一下错误提示问题，这可能就是一个非法的默认值。

这个问题，很有可能是发生在数据备份、迁移、恢复的过程中。由于我先前的文章，如何一键在云端备份自己的小破站，里面是使用导出SQL语句的方法进行备份的。那么，有可能一些表的元数据信息就没有备份恢复成功，比如说这里的默认值？此外，在查阅资料的时候，我发现这个问题可能同样出现在表的自增id上面。

深度剖析

现在，我们可以看一下具体备份得到的表wp_posts究竟是如何构建的。只需要找到我备份脚本导出的sql语句即可。这里，构建表的语句非常长：

DROP TABLE IF EXISTS `wp_posts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `wp_posts` (
  `ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_excerpt` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `to_ping` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `pinged` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_parent` bigint unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `menu_order` int NOT NULL DEFAULT '0',
  `post_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_count` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`(191)),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=779 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

于是可以发现问题所在了，备份的时候就是用的是这个0000-00-00 00:00:00这个数值。

我进一步查了一些资料，发现MySQL 5.7 及更高版本默认启用了严格模式（STRICT_TRANS_TABLES），在这种模式下，数据库会对插入或更新数据时的非法默认值进行更严格的检查。在这种情况下，0000-00-00 00:00:00 是一个非法的日期时间值，因为 MySQL 认为它不符合有效的日期格式。在严格模式下，尝试使用这样的值就会导致错误。

而WordPress 在创建 wp_posts 表时，默认将 post_date、post_date_gmt 等字段设置为 DATETIME 类型，并指定默认值为 0000-00-00 00:00:00。所以就遇到了问题。

修复数据库

那么，现在知道了是默认值的问题，只需要修正默认值就能解决了。这里我发现如果四个字段分别修复（也就是想分别执行四条SQL语句），可能会遇到报错。所以使用一条语句把所有修改都做完，使用如下代码

ALTER TABLE `wp_posts`
  CHANGE `post_date` `post_date`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHANGE `post_date_gmt` `post_date_gmt`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHANGE `post_modified` `post_modified`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHANGE `post_modified_gmt` `post_modified_gmt`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

这样就把这四个时间相关的默认字段都修改了当前时间戳了。

执行优化

现在执行优化操作就没问题了。

可以看到清理了50%的冗余垃圾数据！清理非常有效。

其他

还有一些其他的解决方法，比如说，取消MySQL使用的严格模式。

这里列一些其他参考资料：

python虚拟环境和bypy

使用软链接解决STEAM更新下载到机械硬盘的问题

Comments NOTHING

取消回复