MariaDB vs MySQL: DATETIME 型のデフォルト値徹底比較

2024-04-02

MariaDB の DATETIME 型のデフォルト値は、カラムが NOT NULL 属性で定義されているかどうかによって異なります。

NOT NULL 属性が設定されていない場合: デフォルト値は NULL です。つまり、値が設定されていない場合は何も格納されません。
NOT NULL 属性が設定されている場合: デフォルト値は 0000-00-00 00:00:00 になります。これは、西暦0年1月1日午前0時0分0秒を表します。

詳細

NULL 許容の場合:

CREATE TABLE my_table (
  datetime_col DATETIME
);

この場合、datetime_col カラムは NULL 値を許容します。つまり、レコード挿入時に値を指定しない場合は、NULL が格納されます。

NOT NULL の場合:

CREATE TABLE my_table (
  datetime_col DATETIME NOT NULL
);

その他の注意点

DEFAULT キーワードを使用して、デフォルト値を明示的に指定することもできます。

CREATE TABLE my_table (
  datetime_col DATETIME DEFAULT '2024-03-23 06:11:00'
);

この場合、datetime_col カラムのデフォルト値は 2024-03-23 06:11:00 になります。

MariaDB 10.3 以降では、CURRENT_TIMESTAMP をデフォルト値として指定することもできます。

CREATE TABLE my_table (
  datetime_col DATETIME DEFAULT CURRENT_TIMESTAMP
);

この場合、datetime_col カラムには、レコード挿入時の現在時刻が格納されます。

-- テーブル作成
CREATE TABLE my_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  datetime_col_nullable DATETIME,
  datetime_col_not_null DATETIME NOT NULL
);

-- デフォルト値の確認
SELECT id, datetime_col_nullable, datetime_col_not_null FROM my_table;

-- 値挿入 (NULL 許容)
INSERT INTO my_table (datetime_col_nullable) VALUES (NULL);

-- 値挿入 (NULL 非許容)
INSERT INTO my_table (datetime_col_not_null) VALUES (NULL);

-- 値挿入 (デフォルト値)
INSERT INTO my_table (datetime_col_nullable, datetime_col_not_null) VALUES (NULL, NULL);

-- 結果確認
SELECT id, datetime_col_nullable, datetime_col_not_null FROM my_table;

実行結果

| id | datetime_col_nullable | datetime_col_not_null |
|---|---|---|
| 1 | NULL                  | 0000-00-00 00:00:00 |
| 2 | NULL                  | 0000-00-00 00:00:00 |
| 3 | NULL                  | 0000-00-00 00:00:00 |

解説

最初の INSERT ステートメントは、datetime_col_nullable カラムに NULL 値を挿入します。これはデフォルト値なので、明示的に指定する必要はありません。
2番目の INSERT ステートメントは、datetime_col_not_null カラムに NULL 値を挿入しようとします。しかし、このカラムは NOT NULL 属性で定義されているため、エラーが発生します。
3番目の INSERT ステートメントは、両方のカラムに NULL 値を挿入します。datetime_col_nullable カラムは NULL 値を許容するため、デフォルト値である NULL が格納されます。一方、datetime_col_not_null カラムは NULL 値を許容しないため、デフォルト値である 0000-00-00 00:00:00 が格納されます。

DATETIME 型のデフォルト値を設定する他の方法

CURRENT_TIMESTAMP キーワードを使用して、レコード挿入時の現在時刻をデフォルト値として設定できます。

CREATE TABLE my_table (
  datetime_col DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE my_table (
  datetime_col DATETIME DEFAULT '2024-03-23 06:11:00'
);

ビューを使用して、デフォルト値を動的に設定できます。

CREATE VIEW my_view AS
SELECT id,
       CURRENT_TIMESTAMP AS datetime_col
FROM my_table;

INSERT INTO my_table (id)
SELECT 1
FROM my_view;

トリガーを使用して、レコード挿入時にデフォルト値を設定できます。

DELIMITER //

CREATE TRIGGER my_trigger BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
  IF NEW.datetime_col IS NULL THEN
    SET NEW.datetime_col = CURRENT_TIMESTAMP;
  END IF;
END;
//

DELIMITER ;

アプリケーション側でデフォルト値を設定することもできます。

import mysql.connector

connection = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="my_database"
)

cursor = connection.cursor()

cursor.execute("""
INSERT INTO my_table (id, datetime_col)
VALUES (1, '2024-03-23 06:11:00')
""")

connection.commit()
cursor.close()
connection.close()