开发者

MySQL迁移中explicit_defaults_for_timestamp参数影响

目录
  • 前言
  • 测试对比
    • explicit_defaults_for_timestamp = 0
    • explicit_defaults_for_timestamp = 1
  • 总结

    前言

    最近在做数据迁移的时候,使用的是云平台自带的同步工具,在预检查阶段,当时报错 explicit_defaults_for_timestamp 参数在目标端为 off 建议修改 on,有什么风险呢?在此记录下。

    测试对比

    mysql 默认情况下 explicit_defauhttp://www.devze.comlts_for_timestamp = 0 我们对比一下看看。

    explicit_dewww.devze.comfaults_for_timestamp = 0

    在 explicit_defaults_for_timestamp 参数等于 0 的状态下,如果第一个 timestamp 类型的字段如果没有设置 null 将会自动加上 not null 和默认值。如果设置了默认值,则会自动加上 not null,其他类型的字段是不会的。

    create table test03 (
        a_time timestamp ,
        b_time timestamp null ,
        c_time timestamp default '2024-01-01 00:00:00',
        name varchar(2) default 'a');
    

    show create table test03;

    CREATE TABLE `test03` (
      `a_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `b_time` timestamp NULL DEFAULT NULL,
      `c_time` timestamp NOT NULL DEFAULT '2024-01-01 00:00:00',
      `name` varchar(2) DEFAULT 'a'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    

    然后,我们尝试插入一个记录:

    insert into test03 values (null, null, null, null);
    
    a_timeb_timec_timename
    2024-09-05 14:37:12NULL2024-09-05 14:37:12NULL

    可以看到,在 explicit_defaults_for_timestamp 等于 0 的时候,不仅会影响表结构,还会影响写入。虽然 timestamp 是 not null 我们写入 null 后变为了 CURRENT_TIMESTAMP。

    如果此时将参数设置为 1,执行相同的 SQL 语句则会报错:

    # 设置参数为 1
    set global explicit_defaults_for_timestamp = 1;
    
    # 插入相同的数据
    insert into test03 values (null, null, null, null);
    

    [23000][1048] Column ‘a_time’ cannot be null

    explicit_defaults_for_timestamp = 1

    此时在 explicit_defaults_for_timestamp 等于 1 的条件下,执行一个刚才的建表语句:

    create table test04 (
        a_time timandroidestamp ,
        b_time timestamp null ,
        c_time timestamp default '2024-01-01 00:00:00',
        d_time timestamp not null );
    

    show create table test04;

    CREATE TABLE `test04` (
    php  `a_time` timestamp NULL DEFAULT NULL,
      `b_time` timestamp NULL DEFAULT NULL,
      `c_time` timestamp NULL DEFAULT '2024-01-01 00:00:00',
      `d_time` timestamp NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    

    尝试插入数据:

    insert into test04 values (null, null, null, null);
    
    a_timeb_timec_timename
    NULLNULLNULLNULL

    从上面可以看到,参数开启的情况下,MySQL 默认会为 timestamp 为 NULL 的字段添加 default null 属性。而且 MySQL 没有为第一个 timestamp 设置默认值,当我写入 null 后,则按照 null 来存储。

    另外,如果 timestamp 设置为 not null 那么写入 null 时会报错,如果 sql_mode 中不包含 SQL_MODE 的话,则存储为 ‘0000-00-00 00:00:00’ 并抛出一个异常。

    总结

    生产环境 timestamp 字段一般都会设置为如下样式,而且需要 timestamp 字段为 null 的业务场景很少。

    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    

    如果生产环境,要修改这个参数,从 0 调整到 1 需要关注的就是 insert 语句,有没有直接图省事插入 null 表示当前时间,如果有这种不规范的语法,那么调整该参数后,可能会报错。

    云平台一般不会给用户 super 账号,所以在迁移过程中,可能无法设置 session 级别的参数,此时建议跳过该校验,不修改全局级别的参数。

    到此这篇关于MySQL 迁移中 explicit_defaults_for_timestamp参数影响的文章就介绍到这了,更多相关MySQL explicit_defaults_for_timestamp参数内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编http://www.devze.com程客栈(www.devze.com)!

    0

    上一篇:

    下一篇:

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    最新数据库

    数据库排行榜