NO_ZERO_DATEモード、サーバーが '0000-00-00' を有効な日付として許可するかどうか影響チェック

5970 ワード
MySQL Database datetime date mysql5.6 MySQL テキストリンク

サーバー SQL モード

MySQLサーバーsql_mode使い方によって正しく設定すれば、 データベースにゴミデータ入れない様に設定できます。例：DATETIME又はDATEカラムに「0000-00-00 00:00:00」か「0000-00-00」を登録又は更新できない様にできます。

存在しない日付を登録と更新できるか、できないか確認しましょう！

準備

CREATE TABLE `test_order_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `insert_date` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

sql_modeを確認

  • show VARIABLES LIKE 'sql_mode';↓
Variable_name Value
sql_mode
  • show VARIABLES LIKE 'innodb_strict_mode';↓
Variable_name Value
innodb_strict_mode OFF

innodbのモードも色々設定できます。例：not strict mode ,ALLOW_INVALID_DATES , -- invalid date :0000-00-00
今回sql_modeの、STRICT_ALL_TABLESとNO_ZERO_DATEのみを確認します。

test_order_tableを作成できたら以下を実行する、存在しない日付DBに登録できました。次に書いてあるupdate文も実行する、念の為update実行する前「0000-00-00 00:00:00」の日付now()に更新する。

insert into test_order_table(id, insert_date, updated_at)
values(1, '0000-00-00 00:00:00', now());

update test_order_table set insert_date = now() where id = 1;

-- update文でも0000の日付更新できた、now()->0000を確認
update test_order_table
set insert_date = '0000-00-00 00:00:00'
where id = 1;

sql_mode少し厳しい設定

-- strict mode
show VARIABLES LIKE 'sql_mode';
SET sql_mode = 'STRICT_ALL_TABLES';
Mysqlの仕様に書いてるとおり、動くか先ほどと同じ順番でクエリ実行して、確認しましょう。
「すべてのストレージエンジンについて厳密モードを有効にします。無効なデータ値は拒否されます。」

-insert文実行できた
insert into test_order_table(id, insert_date, updated_at)
values(2, '0000-00-00 00:00:00', now());
-- insert_date →now()に更新
update test_order_table set insert_date = now() where id = 2;

-- update文も実行できた
update test_order_table
set insert_date = '0000-00-00 00:00:00'
where id = 2;

sql_modeもう少し厳しい設定

仕様通りに設定して、確認する

  • show VARIABLES LIKE 'sql_mode';↓
Variable_name Value
sql_mode STRICT_ALL_TABLES
  • SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE';
  • show VARIABLES LIKE 'sql_mode';
Variable_name Value
sql_mode STRICT_ALL_TABLES,NO_ZERO_DATE

先ほどと同じ順番でクエリを実行しましょう、0000日付でクエリ実行できないはずです。

-- 実行できないです
insert into test_order_table(id, insert_date, updated_at)
values(3, '0000-00-00 00:00:00', now());
エラーが表示される：
Incorrect datetime value: '0000-00-00 00:00:00' for column 'insert_date' at row 1

-- 正し日付でデータをinsertする
insert into test_order_table(id, insert_date, updated_at)
values(3, '2021-06-07 12:00:00', now());

-- 0000の日付でupdateする
update test_order_table set insert_date = now() where id = 3;

update test_order_table
set insert_date = '0000-00-00 00:00:00'
where id = 3;
エラーが表示される：
Incorrect datetime value: '0000-00-00 00:00:00' for column 'insert_date' at row 1

・興味あればこちらも試してください！
Select @@version;
結果：
@@version
5.6.24-log

-- USER、DBにtest_order_tableテーブル作成してます
select engine 
from   information_schema.tables 
where  table_schema = 'USER'
   and table_name = 'test_order_table' ;
結果：
engine
InnoDB

SHOW TABLE STATUS FROM `USER`;
Name    Engine  Version Row_format  Rows    Avg_row_length  Data_length Max_data_length Index_length    Data_free   Auto_increment  Create_time Update_time Check_time  Collation   Checksum    Create_options  Comment
test_order_table    InnoDB  10  Compact 5   3276    16384   0   0   0   6   2021-07-06 13:07:25 NULL    NULL    utf8_general_ci NULL
  • GLOBAL 変数を設定するには SUPER 権限が必要で、この設定はその時点以降に接続するすべてのクライアントの動作に影響します。SESSION 変数を設定すると、現在のクライアントにのみ影響します。すべてのクライアントは、自分のセッションの sql_mode 値をいつでも変更できます。
SELECT @@GLOBAL.sql_mode;
結果：
@@GLOBAL.sql_mode
空

SELECT @@SESSION.sql_mode;
結果：
@@SESSION.sql_mode
STRICT_ALL_TABLES,NO_ZERO_DATE

参考資料：
・サーバー SQL モード
・MySQLがゆるふわなのはどう考えてもSQLモードが悪い！