【WordPress】优化MySQL新增索引报错和解决方案

最近在优化WordPress的数据库，在执行以下 SQL 语句时：

ALTER TABLE wp_users
ADD INDEX idx_display_name (display_name(191));

遇到了 MySQL 的错误：

ERROR 1067 (42000): Invalid default value for 'user_registered'

这个错误看似与添加索引无关，但实际上与 MySQL 的表结构和严格模式（Strict Mode）设置有关。让我们一步步分析问题原因，并提供解决方案。

🌟 问题原因分析

1. 错误背景

MySQL 的错误信息提到 user_registered，但你的语句只涉及 display_name 的索引。这表明问题不是直接由 display_name 引起的，而是由表的其他列（user_registered）触发。
wp_users 表的默认结构（WordPress 标准）包含 user_registered 列，定义如下：

  user_registered DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'

在 MySQL 5.7+（或 MariaDB 10.2+）中，默认启用了严格模式（STRICT_TRANS_TABLES），不允许使用无效的默认值（如 '0000-00-00 00:00:00'），因为它不是合法的 DATETIME 值。

2. 为什么 ALTER TABLE 触发错误？

当你执行 ALTER TABLE 添加索引时，MySQL 会检查表的完整性，包括所有列的定义和默认值。
如果表中有不符合严格模式的默认值（例如 user_registered 的 '0000-00-00 00:00:00'），即使你没有修改该列，MySQL 也会报错，阻止操作。

3. 你的环境

从错误来看，使用的 MySQL 版本可能 >= 5.7，且启用了严格模式。
WordPress 的默认表结构未完全适配严格模式，导致这类问题。

🛠 解决方案

以下是几种可行的解决方法，从临时调整到永久修复，逐步列出：

🔧 方案 1：临时禁用严格模式

步骤：

检查当前 SQL 模式：
sql SELECT @@sql_mode;
如果结果包含 STRICT_TRANS_TABLES 或 NO_ZERO_DATE，则严格模式生效。
临时禁用严格模式：
sql SET SESSION sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
再次执行索引创建：
sql ALTER TABLE wp_users ADD INDEX idx_display_name (display_name(191));

优点：无需修改表结构，立即生效。
缺点：仅对当前会话有效，重启连接后需重新设置。

🔧 方案 2：全局调整 SQL 模式

步骤：

编辑 MySQL 配置文件（通常是 /etc/mysql/my.cnf 或 /etc/my.cnf）：
ini

[mysqld]

sql_mode = “NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION” 重启 MySQL 服务：
bash sudo service mysql restart 再次执行：
sql ALTER TABLE wp_users ADD INDEX idx_display_name (display_name(191));

优点：永久解决，适用于所有表。
缺点：需要服务器管理权限，且可能影响其他应用。

🔧 方案 3：修复表结构（推荐）

思路：修改 user_registered 的默认值，使其符合严格模式。
步骤：

检查 wp_users 表结构：
sql SHOW CREATE TABLE wp_users;
确认 user_registered 定义为：
user_registered DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
更新默认值： sql ALTER TABLE wp_users MODIFY COLUMN user_registered DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
- 将默认值改为当前时间戳，或用 '1970-01-01 00:00:00'（合法值）。
添加索引：
sql ALTER TABLE wp_users ADD INDEX idx_display_name (display_name(191));

优点：彻底解决问题，符合现代 MySQL 规范，且不影响 WordPress 功能（user_registered 默认值很少被依赖）。
注意：先备份数据库，避免意外数据丢失。

🔧 方案 4：绕过问题（仅添加索引）

如果你不想修改 user_registered，可以用 ALGORITHM=INPLACE 强制添加索引（MySQL 5.6+ 支持）：

  ALTER TABLE wp_users
  ADD INDEX idx_display_name (display_name(191)), ALGORITHM=INPLACE, LOCK=NONE;

条件：表引擎必须是 InnoDB，且没有其他结构性问题。
缺点：不解决根本问题，可能在未来操作中再次报错。

🎯 推荐执行步骤

考虑到长期稳定性和兼容性，方案 3（修复表结构） 是最佳选择。以下是具体操作：

备份数据库：

   mysqldump -u username -p wordpress_db > backup.sql

修改表结构：

   ALTER TABLE wp_users
   MODIFY COLUMN user_registered DATETIME NOT NULL DEFAULT '1970-01-01 00:00:00';

添加索引：

   ALTER TABLE wp_users
   ADD INDEX idx_display_name (display_name(191));

验证结果：

   SHOW INDEX FROM wp_users;

确认 idx_display_name 已成功创建。

🌌 为什么选择这个方案？

user_registered 默认值问题：WordPress 的旧默认值 '0000-00-00 00:00:00' 是历史遗留问题，现代数据库已淘汰这种用法。
性能提升：idx_display_name 能显著加速 ORDER BY display_name ASC，尤其在用户量大时。
未来兼容性：修复后，你的表结构更符合 MySQL/MariaDB 的严格模式，避免类似错误反复出现。

⚡ 优化后的效果

假设你之前优化了查询：

SELECT wp_users.ID, wp_users.user_login, wp_users.display_name
FROM wp_users
INNER JOIN wp_usermeta
    ON wp_users.ID = wp_usermeta.user_id
WHERE wp_usermeta.meta_key = 'wp_capabilities'
AND wp_usermeta.meta_value REGEXP '"(edit_posts|administrator|editor|author|contributor|lp_teacher)"'
ORDER BY wp_users.display_name ASC;

加上 idx_display_name 后：

排序效率：从全表排序变为索引排序，时间复杂度从 O(n log n) 降至 O(n)。
总体性能：结合之前的 wp_usermeta 索引（如 idx_user_meta），查询速度可提升数倍。

🎉 总结

这个错误虽然表面上与索引无关，但揭示了 WordPress 表结构与现代 MySQL 规范的冲突。通过修复 user_registered 的默认值并添加 idx_display_name，不仅解决了当前问题，还为未来的性能优化打下了基础。