快捷導(dǎo)航

MySQL遷移中explicit_defaults_for_timestamp參數(shù)影響

更新時(shí)間：2024年09月09日 10:51:35 作者：Bing@DBA

在MySQL數(shù)據(jù)庫(kù)遷移過(guò)程中,explicit_defaults_for_timestamp參數(shù)設(shè)置為off可能導(dǎo)致數(shù)據(jù)寫(xiě)入行為變化,本文就來(lái)介紹一下explicit_defaults_for_timestamp參數(shù)影響,感興趣的可以了解一下

前言

最近在做數(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 類(lèi)型的字段如果沒(méi)有設(shè)置 null 將會(huì)自動(dòng)加上 not null 和默認(rèn)值。如果設(shè)置了默認(rèn)值，則會(huì)自動(dòng)加上 not null，其他類(lèi)型的字段是不會(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);

a_time	b_time	c_time	name
2024-09-05 14:37:12	NULL	2024-09-05 14:37:12	NULL

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

如果此時(shí)將參數(shù)設(shè)置為 1，執(zhí)行相同的 SQL 語(yǔ)句則會(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è)剛才的建表語(yǔ)句：

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);

a_time	b_time	c_time	name
NULL	NULL	NULL	NULL

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

另外，如果 timestamp 設(shè)置為 not null 那么寫(xiě)入 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 語(yǔ)句，有沒(méi)有直接圖省事插入 null 表示當(dāng)前時(shí)間，如果有這種不規(guī)范的語(yǔ)法，那么調(diào)整該參數(shù)后，可能會(huì)報(bào)錯(cuò)。

云平臺(tái)一般不會(huì)給用戶 super 賬號(hào)，所以在遷移過(guò)程中，可能無(wú)法設(shè)置 session 級(jí)別的參數(shù)，此時(shí)建議跳過(guò)該校驗(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)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: