前言

最近在做數(shù)據(jù)遷移的時(shí)候，使用的是云平臺(tái)自帶的同步工具，在預(yù)檢查階段，當(dāng)時(shí)報(bào)錯(cuò) explicit_defaults_for_timestamp 參數(shù)在目標(biāo)端為 off 建議修改 on，有什么風(fēng)險(xiǎn)呢？在此記錄下。

測(cè)試對(duì)比

MySQL 默認(rèn)情況下 explicit_defaults_for_timestamp = 0 我們對(duì)比一下看看。

explicit_defaults_for_timestamp = 0

在 explicit_defaults_for_timestamp 參數(shù)等于 0 的狀態(tài)下，如果第一個(gè) timestamp 類型的字段如果沒有設(shè)置 null 將會(huì)自動(dòng)加上 not null 和默認(rèn)值。如果設(shè)置了默認(rèn)值，則會(huì)自動(dòng)加上 not null，其他類型的字段是不會(huì)的。

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

然后，我們嘗試插入一個(gè)記錄：

insert into test03 values (null, null, null, null);

可以看到，在 explicit_defaults_for_timestamp 等于 0 的時(shí)候，不僅會(huì)影響表結(jié)構(gòu)，還會(huì)影響寫入。雖然 timestamp 是 not null 我們寫入 null 后變?yōu)榱?CURRENT_TIMESTAMP。

如果此時(shí)將參數(shù)設(shè)置為 1，執(zhí)行相同的 SQL 語句則會(huì)報(bào)錯(cuò)：

# 設(shè)置參數(shù)為 1
set global explicit_defaults_for_timestamp = 1;

# 插入相同的數(shù)據(jù)
insert into test03 values (null, null, null, null);

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

explicit_defaults_for_timestamp = 1

此時(shí)在 explicit_defaults_for_timestamp 等于 1 的條件下，執(zhí)行一個(gè)剛才的建表語句：

create table test04 (
    a_time timestamp ,
    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` (
  `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

嘗試插入數(shù)據(jù)：

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

從上面可以看到，參數(shù)開啟的情況下，MySQL 默認(rèn)會(huì)為 timestamp 為 NULL 的字段添加 default null 屬性。而且 MySQL 沒有為第一個(gè) timestamp 設(shè)置默認(rèn)值，當(dāng)我寫入 null 后，則按照 null 來存儲(chǔ)。

另外，如果 timestamp 設(shè)置為 not null 那么寫入 null 時(shí)會(huì)報(bào)錯(cuò)，如果 sql_mode 中不包含 SQL_MODE 的話，則存儲(chǔ)為 ‘0000-00-00 00:00:00’ 并拋出一個(gè)異常。

總結(jié)

生產(chǎn)環(huán)境 timestamp 字段一般都會(huì)設(shè)置為如下樣式，而且需要 timestamp 字段為 null 的業(yè)務(wù)場(chǎng)景很少。

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新時(shí)間',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間',

如果生產(chǎn)環(huán)境，要修改這個(gè)參數(shù)，從 0 調(diào)整到 1 需要關(guān)注的就是 insert 語句，有沒有直接圖省事插入 null 表示當(dāng)前時(shí)間，如果有這種不規(guī)范的語法，那么調(diào)整該參數(shù)后，可能會(huì)報(bào)錯(cuò)。

云平臺(tái)一般不會(huì)給用戶 super 賬號(hào)，所以在遷移過程中，可能無法設(shè)置 session 級(jí)別的參數(shù)，此時(shí)建議跳過該校驗(yàn)，不修改全局級(jí)別的參數(shù)。

到此這篇關(guān)于MySQL 遷移中 explicit_defaults_for_timestamp參數(shù)影響的文章就介紹到這了,更多相關(guān)MySQL explicit_defaults_for_timestamp參數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論