MySQL の Datetime が NULL に見える問題と対処法

▶

この記事の要点

MySQL 旧仕様: 未設定の DATETIME は 0000-00-00 00:00:00 という擬似ゼロ値（NULL ではない）
sql_mode に NO_ZERO_DATE / NO_ZERO_IN_DATE / STRICT_TRANS_TABLES が入ると 0000-00-00 投入はエラー
PHP DateTime / Laravel Eloquent では 0000-00-00 を読み出すと例外 → NULL 許容 + $casts 推奨
対処: ① カラムを NULL 許容に変更、② デフォルトを CURRENT_TIMESTAMP に、③ 既存 0 値を NULL/有効な日時に UPDATE
互換性確認: SHOW VARIABLES LIKE 'sql_mode'、SELECT @@sql_mode

症状: 0000-00-00 00:00:00 と NULL は別物

MySQL 5.6 以前は DATETIME / DATE / TIMESTAMP の「未設定」を 0000-00-00 00:00:00 という擬似ゼロ値で表現していました。これはNULL ではないため、WHERE col IS NULL ではヒットしません。一方、PHP DateTime 等で読み出すと 「不正な日時」として扱われ NULL のように見える挙動になります。

-- 旧 MySQL の挙動
CREATE TABLE events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    started_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'
);

INSERT INTO events () VALUES ();

SELECT * FROM events;
-- id | started_at
--  1 | 0000-00-00 00:00:00

SELECT * FROM events WHERE started_at IS NULL;
-- → 0 rows (NULL ではない)

SELECT * FROM events WHERE started_at = '0000-00-00 00:00:00';
-- → 1 row（これでヒット）

原因: sql_mode と PHP / Java 側の扱い

-- 現在の sql_mode を確認
SHOW VARIABLES LIKE 'sql_mode';
SELECT @@sql_mode;

-- MySQL 5.7+ のデフォルト例
-- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
-- NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

-- これが入っていると 0000-00-00 投入はエラー
INSERT INTO events (started_at) VALUES ('0000-00-00 00:00:00');
-- ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00'

PHP / Laravel 側の挙動:

// Laravel Eloquent でカラムに 0000-00-00 が入っていると...
$event = Event::find(1);
echo $event->started_at;
// InvalidArgumentException: Trailing data
// or
// Carbon\Exceptions\InvalidFormatException

// 対処: $casts で datetime 指定し、NULL 許容に
class Event extends Model {
    protected $casts = ['started_at' => 'datetime'];
}

// PHP 生
$dt = new DateTime($row['started_at']);  // 0000-00-00 → 例外
$dt = $row['started_at'] === '0000-00-00 00:00:00'
    ? null
    : new DateTime($row['started_at']);

対処1: カラムを NULL 許容に変更

未設定状態を本来の NULL で表現するのが現代的な設計です。

-- カラムを NULL 許容に変更
ALTER TABLE events MODIFY started_at DATETIME NULL DEFAULT NULL;

-- 既存の 0000-00-00 を NULL に更新
UPDATE events SET started_at = NULL WHERE started_at = '0000-00-00 00:00:00';

-- 確認
SELECT * FROM events WHERE started_at IS NULL;

対処2: デフォルトを CURRENT_TIMESTAMP に

-- 作成時刻として常に「現在」を入れる
ALTER TABLE events
    MODIFY started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- 更新時にも自動更新
ALTER TABLE events
    MODIFY updated_at DATETIME NOT NULL
        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

対処3: sql_mode を整合させる

-- 一時的にゼロ日付を許容（移行作業中のみ）
SET SESSION sql_mode = REPLACE(
    REPLACE(@@sql_mode, 'NO_ZERO_DATE', ''),
    'NO_ZERO_IN_DATE', ''
);

-- 永続化（my.cnf）
-- [mysqld]
-- sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
-- → NO_ZERO_DATE を外した状態（非推奨、移行中のみ）

-- 推奨は「sql_mode は厳格にしてアプリ側を NULL 対応へ修正」

対処4: 表示時の整形

-- 0000-00-00 を NULL として返す
SELECT
    id,
    CASE WHEN started_at = '0000-00-00 00:00:00' THEN NULL ELSE started_at END
        AS started_at
FROM events;

-- DATE_FORMAT で表示用整形
SELECT
    id,
    IF(started_at = '0000-00-00 00:00:00', '未設定',
        DATE_FORMAT(started_at, '%Y年%m月%d日 %H:%i')) AS started_label
FROM events;

Laravel 側の安全な書き方

// マイグレーション
Schema::create('events', function (Blueprint $t) {
    $t->id();
    $t->string('title');
    $t->dateTime('started_at')->nullable();        // ✅ NULL 許容
    $t->dateTime('ended_at')->nullable();
    $t->timestamps();                              // created_at / updated_at は自動
});

// モデル
class Event extends Model {
    protected $casts = [
        'started_at' => 'datetime',
        'ended_at'   => 'datetime',
    ];

    public function isStarted(): bool {
        return $this->started_at !== null
            && $this->started_at->isPast();
    }
}

// 旧データの掃除（一度きり）
DB::statement("UPDATE events SET started_at = NULL WHERE started_at = '0000-00-00 00:00:00'");
DB::statement("UPDATE events SET ended_at   = NULL WHERE ended_at   = '0000-00-00 00:00:00'");

診断クエリ

-- スキーマ内の DATETIME / DATE / TIMESTAMP カラム一覧
SELECT table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND data_type IN ('datetime', 'date', 'timestamp')
ORDER BY table_name, ordinal_position;

-- 0000-00-00 を持つ行の検索（テーブルごとに）
SELECT COUNT(*) FROM events WHERE started_at = '0000-00-00 00:00:00';

-- 厳格化前のリハーサル
SELECT * FROM events
WHERE started_at = '0000-00-00 00:00:00' OR started_at IS NULL;

FAQ

Q: 「Invalid default value for 'created_at'」とマイグレーションで失敗
A: sql_mode に NO_ZERO_DATE が含まれた状態で DEFAULT '0000-00-00 00:00:00' を投入しようとしている。デフォルトを CURRENT_TIMESTAMP に変えるか、カラムを NULL DEFAULT NULL に。

Q: 既存システムが 0000-00-00 を前提にしている
A: 段階移行: ① まず NULL を扱える状態にする（NULL 許容 + アプリ修正）→ ② バッチで 0 を NULL に更新 → ③ 最後に sql_mode 厳格化。

Q: タイムゾーンも絡んで余計分からない
A: MySQL の TIMESTAMP は内部 UTC、DATETIME は文字列そのまま。アプリは UTC 統一が無難。SET time_zone = '+09:00' で接続セッションの TZ を変更可能。

編集

子ページ

子ページはありません

同階層のページ

2026-06-11 07:12:00 guest 448

ページ一覧

その他