我最近接手了一个10年前创建的老项目。它使用MySQL 5.1。
除此之外，我需要将默认字符集从latin1更改为utf8。
举个例子，我有这样的表格:
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`last_name` varchar(45) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`username` varchar(127) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`email` varchar(127) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`pass` varchar(20) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
`active` char(1) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'Y',
`created` datetime NOT NULL,
`last_login` datetime DEFAULT NULL,
`author` varchar(1) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT 'N',
`locked_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`ripple_token` varchar(36) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
`ripple_token_expires` datetime DEFAULT '2014-10-31 08:03:55',
`authentication_token` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_users_on_reset_password_token` (`reset_password_token`),
UNIQUE KEY `index_users_on_confirmation_token` (`confirmation_token`),
UNIQUE KEY `index_users_on_unlock_token` (`unlock_token`),
KEY `users_active` (`active`),
KEY `users_username` (`username`),
KEY `index_users_on_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=1677 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
我设置了自己的Mac来处理这个。没有考虑太多，我运行“brew install mysql”安装mysql 5.7。所以我有一些版本冲突。
我下载了这个数据库的副本并导入了它。
如果我试着像这样运行查询:
ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
我得到这个错误:
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1
我想我可以用:
ALTER TABLE users MODIFY created datetime NULL DEFAULT '1970-01-01 00:00:00';
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
但我明白:
ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ;
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 1
我必须更新每个值吗?
使用ALTER TABLE语句更改列的默认值，例如:
ALTER TABLE users MODIFY created datetime NULL DEFAULT '1970-01-02'
．.． 不会改变已经存储的任何值。“默认”值应用于插入的行，并且没有为列提供值。
至于为什么会遇到这个错误，可能是因为会话的sql_mode设置包含了NO_ZERO_DATE。
参考:http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html # sqlmode_no_zero_date
当您执行“导入”操作时，执行INSERT操作的SQL语句将在允许零日期的会话中运行。
查看sql_mode设置:
SHOW VARIABLES LIKE 'sql_mode' ;
-or-
SELECT @@sql_mode ;
至于如何“修复”当前的问题，以便在运行ALTER TABLE语句时不会抛出错误。
几个选项:
1)通过删除NO_ZERO_DATE和NO_ZERO_IN_DATE，将sql_mode更改为允许零日期。更改可以应用在my.cnf文件中，因此在MySQL服务器重新启动后，sql_mode变量将被初始化为my.cnf中的设置。
对于临时更改，我们可以使用单个会话修改设置，而不需要全局更改。
-- save current setting of sql_mode
SET @old_sql_mode := @@sql_mode ;
-- derive a new value by removing NO_ZERO_DATE and NO_ZERO_IN_DATE
SET @new_sql_mode := @old_sql_mode ;
SET @new_sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@new_sql_mode,','),',NO_ZERO_DATE,' ,','));
SET @new_sql_mode := TRIM(BOTH ',' FROM REPLACE(CONCAT(',',@new_sql_mode,','),',NO_ZERO_IN_DATE,',','));
SET @@sql_mode := @new_sql_mode ;
-- perform the operation that errors due to "zero dates"
-- when we are done with required operations, we can revert back
-- to the original sql_mode setting, from the value we saved
SET @@sql_mode := @old_sql_mode ;
2)将创建的列更改为允许空值，并更新现有行以将零日期更改为空值
3)更新现有行，将零日期更改为有效日期
我们不需要运行单独的语句来更新每一行。我们可以一口气更新所有的行(假设它是一个合理大小的表。对于一个较大的表，为了避免产生大量的回滚/撤销，我们可以在合理大小的块中执行操作。)
在这个问题中，表定义中显示的AUTO_INCREMENT值向我们保证行数不会过多。
如果我们已经修改了创建的列以允许NULL值，我们可以这样做:
UPDATE `users` SET `created` = NULL WHERE `created` = '0000-00-00 00:00:00'
或者，我们可以将它们设置为一个有效的日期，例如1970年1月2日
UPDATE `users` SET `created` = '1970-01-02' WHERE `created` = '0000-00-00 00:00:00'
(注意，datetime值为1970年1月1日午夜('1970-01-01 00:00:00')是一个“零日期”。它将被计算为'0000-00-00 00:00:00'
在MySQL从5.6升级到5.7后，我也有这个错误
我发现，对我来说，最好的解决方案是把这里的一些解决方案结合起来，用最少的输入来实现它。
我使用MyPHPAdmin是为了通过接口发送查询的简单性，因为这样我就可以轻松地检查结构和所有这些。您可以直接使用ssh或其他接口。无论如何，方法应该相似或相同。
...
1.
首先检查实际错误时，试图修复db:
joomla。jos_menu注意:旧格式的TIME/TIMESTAMP/DATETIME列已升级为新格式。
警告:错误的日期时间值:'0000-00-00 00:00:00'列checked_out_time在第1行
错误:'checked_out_time'的默认值无效
状态:操作失败
这告诉我列checked_out_time在表jos_menu需要有所有坏的日期固定以及“默认”改变。
...
2.
我根据错误消息中的信息运行SQL查询:
UPDATE jos_menu SET checked_out_time = '1970-01-01 08:00:00' WHERE checked_out_time = 0
如果你得到一个错误，你可以使用下面的查询，似乎总是工作:
UPDATE jos_menu SET checked_out_time = '1970-01-01 08:00:00' WHERE CAST(checked_out_time AS CHAR(20)) = '0000-00-00 00:00:00'
...
3.
然后，一旦完成，我运行第二个SQL查询:
ALTER TABLE `jos_menu` CHANGE `checked_out_time` `checked_out_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP;
或者在这种情况下，它是一个必须为NULL的日期
ALTER TABLE `jos_menu` CHANGE `checked_out_time` `checked_out_time` DATETIME NULL DEFAULT NULL;
...
如果我现在运行修复数据库，我得到:
joomla。jos_menu 好吧
...
工作得很好:)
下面是我的解决方案PhpMyAdmin / Fedora 29 / MySQL 8.0(以)为例:
设置sql_mode = '东西';不工作，命令调用成功，但什么都没有改变。
set GLOBAL sql_mode='SOMETHING';更改全局配置永久更改。
set SESSION sql_mode='SOMETHING';session变量只影响当前客户端。
https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
所以我这样做:
显示类似SQL_MODE的变量;
结果:
ONLY_FULL_GROUP_BY、STRICT_TRANS_TABLES NO_ZERO_IN_DATE、NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION
删除结果:NO_ZERO_IN_DATE,NO_ZERO_DATE
设置新的配置:Set GLOBAL SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
您可以以同样的方式删除或添加其他模式。
这有助于更改使用和测试框架的全局或sql_mode必须在每个文件或一组查询中指定。
改编自这里的一个问题:how-can-i-disable-mysql-strict-mode
示例:安装最新的Joomla 4.0 alpha内容。
编辑:
在PhpMyadmin中，如果您拥有服务器的控制权，您可以直接在Plus > Variables > sql_mode中更改sql_mode(以及所有其他参数)
我在https://support.plesk.com/hc/en-us/articles/115000666509-How-to-change-the-SQL-mode-in-MySQL上找到了解决方案。我有这个:
mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)
注意上面结果中的NO_ZERO_IN_DATE,NO_ZERO_DATE。我通过这样做去掉了它:
mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
然后我有了这个:
mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)
在此之后，我可以成功地使用ALTER TABLE并更改我的表。
出现这个问题是因为MYSQL 5.7引入了默认模式，比如
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;
=> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
在默认模式中，有一个模式NO_ZERO_DATE阻止您在datetime列中放置值0000-00-00 00:00:00。
临时修复将从默认模式中删除模式NO_ZERO_DATE/NO_ZERO_IN_DATE，并保持其他模式不变。
SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
但是一旦你重新启动你的mysql服务器，你所有的临时设置将消失，默认模式将再次启用。要在重新启动时修复此问题，请更改计算机上my.cnf文件中的默认模式。
参考文档:- https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#:~:text=To%20set%20the%20SQL%20mode,ini%20(Windows)。
我个人的观点:-正确的开发实践应该是在列中放入正确的datetime值，而不是0000-00-00 00:00:00。这样你就不用担心SQL模式了。
通读这些答案，我尝试了很多，但没有任何变化。还是会得到错误。
我研究了一下，找到了一个真正有效的方法;至少对我来说。但如果它对我有效，它很可能对每个人都有效。因为我永远找不到答案!哈哈
我使用的是Ubuntu 18.04，但我使用过其他Linux操作系统，如Fedora，直进Linux, XAMPP, yadda。他们的档案似乎都是一样的。我也使用MySQL版本5.7.37-0。
在Ubuntu上，我进入/etc/mysql/。在那里，你有几个conf文件，但你想要的是my.cnf。
在my.cnf中，您将有一个标记为[mysqld]的部分。就在这下面(我把它放在其他任何东西之前)，你会输入以下内容:
sql_mode="ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
重新启动MySQL服务器，您应该不再收到这个错误。
这是长久之计。有一个临时解决方案，输入SQL命令——
设置sql_mode = " ";
——在你的INSERT或UPDATE QUERY之前，它包含date或datetime值，如' 0000-00-00 00:00:00 '，但这是一个临时解决方案，仍然保留在当前会话中，你必须在下次登录时再次输入它，并遇到同样的问题。
所以，永久的解决方案似乎是可行的。
