MySQL 5.7 の sql_mode のデフォルト値によって生じる落とし穴と解決策

通常のプロジェクト開発中に、MySQL バージョンが 5.6 から 5.7 にアップグレードされた場合。 DBA がデータベース バージョンのアップグレードの影響を考慮する場合、一般的に注意すべき点がいくつかあります。

SQLモード
オプティマイザースイッチ

この記事の主な内容は、MySQL をバージョン 5.7 にアップグレードした後にデフォルトの sql_mode 値によって発生する落とし穴と、それに対する解決策です。

ケース1: ONLY_FULL_GROUP_BY

問題の説明

MySQL バージョンを 5.6 から 5.7 にアップグレードした後、いくつかの SQL 実行エラーが報告されます。エラー情報は次のとおりです。

エラー 1055 (42000): XXXXXX リストの式 #3 は GROUP BY 句に含まれておらず、GROUP BY 句の列に機能的に依存しない非集計列 'XXXXX.XXXXXX' が含まれています。これは sql_mode=only_full_group_by と互換性がありません。

この問題の原因は、バージョン 5.6 からバージョン 5.7 にアップグレードした後に sql_mode のデフォルト値が変更されたことです。バージョン 5.7 の sql_mode のデフォルト値では、ONLY_FULL_GROUP_BY が意図されています。このオプションの意味は、クエリに GROUP BY を使用する SQL の場合、GROUP BY に表示されないフィールドは SELECT 部分に表示できないことを意味します。つまり、SELECT によってクエリされるフィールドは GROUP BY に表示されるか、集計関数を使用する必要があります。

解決

解決策1（非推奨）：バージョン5.7のsql_mode値を変更し、ONLY_FULL_GROUP_BYを削除します。

ONLY_FULL_GROUP_BY は、SQL 仕様を強化するために使用されます。その目的は、SQL クエリの結果をより標準化し、正確にすることです。

ONLY_FULL_GROUP_BY 指定制限がない場合、次の SQL を実行できます: SELECT a,b,c FROM t GROUP BY a 。 SQL はフィールド a の値でグループ化します。同じフィールド値 a が複数の b または c 値に対応する場合、クエリ結果の b および c 値は不確実になります。

解決策2: SQLを書き直す

ケース 2: NO_ZERO_DATE & NO_ZERO_IN_DATE & time_zone

問題の説明

トラブルシューティングフェーズ 1

MySQL バージョンを 5.6 から 5.7 にアップグレードした後、テーブル作成プロセスが失敗しました。

mysql> テーブル `t_manager` を作成します (
  .....
  -> `CREATE_DATETIME` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時間',
  -> `MODIFIER` varchar(32) デフォルト NULL コメント 'Updater',
  -> `MODIFY_DATETIME` タイムスタンプ NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '変更時刻',
  -> `IS_DELETED` ビット(1) デフォルト b'0' コメント '削除ステータス 1: 削除済み 0: 削除されていない',
  -> `IS_ENABLE` ビット(1) デフォルト b'1' コメント '有効化ステータス 1: 有効化 0: 無効化',
  -> 主キー (`CACHE_ID`)
  -> ) ENGINE=InnoDB デフォルト文字セット=utf8;
エラー 1067 (42000): 'MODIFY_DATETIME' のデフォルト値が無効です

エラー メッセージには、MODIFY_DATETIME フィールドのデフォルト値が無効であると表示されています。バージョン 5.6 から 5.7 にアップグレードしたばかりであることを考慮して、5.7 のデフォルトの sql_mode 値を確認しました。影響を与える可能性のあるオプションが 2 つあることがわかりました。

日付なし
日付にゼロがない

トラブルシューティングフェーズ2

したがって、解決策は、NO_ZERO_DATE と NO_ZERO_IN_DATE の要件に従ってデフォルト値を設定し、MODIFY_DATETIME フィールドのデフォルト値を '1001-01-01 01:01:01' に設定することです。その結果、テーブルを正常に作成できないことがわかります。

mysql>テーブル `t_manager` を作成します (
  .....
  -> `CREATE_DATETIME` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時間',
  -> `MODIFIER` varchar(32) デフォルト NULL コメント 'Updater',
  -> `MODIFY_DATETIME` タイムスタンプ NOT NULL DEFAULT '1001-01-01 01:01:01' ON UPDATE CURRENT_TIMESTAMP COMMENT '変更時刻',
  -> `IS_DELETED` ビット(1) デフォルト b'0' コメント '削除ステータス 1: 削除済み 0: 削除されていない',
  -> `IS_ENABLE` ビット(1) デフォルト b'1' コメント '有効化ステータス 1: 有効化 0: 無効化',
  -> 主キー (`CACHE_ID`)
  -> ) ENGINE=InnoDB デフォルト文字セット=utf8;
エラー 1067 (42000): 'MODIFY_DATETIME' のデフォルト値が無効です

すべてのsql_mode値をチェックしたところ、すべて仕様を満たしていることがわかりましたが、それでもテーブルを正常に作成できませんでした。タイムスタンプの紹介を見つけるには、公式マニュアルを参照する必要がありました。

TIMESTAMP データ型は、日付と時刻の両方の部分を含む値に使用されます。TIMESTAMP の範囲は、UTC の「1970-01-01 00:00:01」から UTC の「2038-01-19 03:14:07」までです。

トラブルシューティングフェーズ3

タイムスタンプ フィールドの値の範囲の公式定義は、「1970-01-01 00:00:01」から「2038-01-19 03:14:07」であることがわかります。設定したデフォルト値はタイムスタンプの範囲内ではないことがわかります。したがって、デフォルト値を再度変更します。

mysql>テーブル `t_manager` を作成します (
  .....
  -> `CREATE_DATETIME` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時間',
  -> `MODIFIER` varchar(32) デフォルト NULL コメント 'Updater',
  -> `MODIFY_DATETIME` タイムスタンプ NOT NULL DEFAULT '1970-01-01 00:00:01' ON UPDATE CURRENT_TIMESTAMP COMMENT '変更時刻',
  -> `IS_DELETED` ビット(1) デフォルト b'0' コメント '削除ステータス 1: 削除済み 0: 削除されていない',
  -> `IS_ENABLE` ビット(1) デフォルト b'1' コメント '有効化ステータス 1: 有効化 0: 無効化',
  -> 主キー (`CACHE_ID`)
  -> ) ENGINE=InnoDB デフォルト文字セット=utf8;
エラー 1067 (42000): 'MODIFY_DATETIME' のデフォルト値が無効です

残念です！まだテーブルを正常に作成できません。私は途方に暮れていたため、同僚に助けを求めました。彼は自分のマシンで試してみると、同じステートメントが彼の MySQL (バージョン 5.7.23) でも正常に機能したと言いました。

まったく理解できないんです。

怒りに駆られて、私は両側のパラメータ値を比較し、確かに違いの原因を見つけました。

タイムスタンプ フィールドによって定義された範囲を振り返ってみましょう。

TIMESTAMP データ型は、日付と時刻の両方の部分を含む値に使用されます。TIMESTAMP の範囲は、UTC の「1970-01-01 00:00:01」から UTC の「2038-01-19 03:14:07」までです。

この時間範囲は、UTC タイムゾーンの時間範囲を指します。テスト環境が CST East 8 タイムゾーンに設定されている場合、対応する時間範囲も 8 時間増やす必要があります。したがって、タイムスタンプ フィールドのデフォルト値は '1970-01-01 08:00:01' に変更され、最終的にテーブルが正常に作成されます。

mysql>テーブル `mn_cache_refresh_manager` を作成します (
  ......
  -> `CREATE_DATETIME` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時間',
  -> `MODIFIER` varchar(32) デフォルト NULL コメント 'Updater',
  -> `MODIFY_DATETIME` タイムスタンプ NOT NULL DEFAULT '1970-01-01 08:00:01' ON UPDATE CURRENT_TIMESTAMP COMMENT '変更時刻',
  -> `IS_DELETED` ビット(1) デフォルト b'0' コメント '削除ステータス 1: 削除済み 0: 削除されていない',
  -> `IS_ENABLE` ビット(1) デフォルト b'1' コメント '有効化ステータス 1: 有効化 0: 無効化',
  -> 主キー (`CACHE_ID`)
  -> ) ENGINE=InnoDB デフォルト文字セット=utf8;
クエリは正常、影響を受けた行は 0 行 (0.02 秒)

要約する

上記は、編集者が紹介した MySQL 5.7 の sql_mode のデフォルト値によってもたらされる落とし穴と解決策です。皆様のお役に立てれば幸いです。ご質問がある場合は、メッセージを残してください。編集者がすぐに返信します。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。