错误的日期时间值:'0000-00-00 00:00:00'

我最近接手了一个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

我必须更新每个值吗?

当前回答

我的解决方案

SET sql_mode='';
UPDATE tnx_k2_items
SET created_by = 790
, modified = '0000-00-00 00:00:00'
, modified_by = 0

2020-03-27 15:20:28

其他回答

对于Symfony用户:添加一个默认的orm列，如下所示:

/**
 * @var DateTime $updatedAt
 *
 * @ORM\Column(name="updated_at", type="datetime", nullable=false, options={"default" : "CURRENT_TIMESTAMP"})
 */

2021-02-03 10:08:00

出现这个问题是因为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模式了。

2022-02-10 08:00:55

我有一个类似的问题，但在我的情况下，一些行值为NULL。

所以首先我更新表:

update `my_table`set modified = '1000-01-01 00:00:00' WHERE modified is null

问题解决了，至少对我来说。

2019-04-11 14:23:01

我通过在查询之前这样做来修复它

SET SQL_MODE='ALLOW_INVALID_DATES';

2018-09-14 14:34:42

SET sql_mode = 'NO_ZERO_DATE';
UPDATE `news` SET `d_stop`='2038-01-01 00:00:00' WHERE `d_stop`='0000-00-00 00:00:00'

2018-09-11 16:44:00

错误的日期时间值:'0000-00-00 00:00:00'

推荐文章

最新文章

标签