mysql 8.0.x のsql_mode

Posted at 2024-10-11

訳あってMySQLのsql_mode を変更したい場合

デフォルトの確認

SELECT @@GLOBAL.sql_mode;

+-----------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

変更方法

my.cnf

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

※ONLY_FULL_GROUP_BYを外したい場合

変更結果確認

SELECT @@GLOBAL.sql_mode;

+----------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                  |
+----------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------+

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up