How can I enable strict sql_mode in MySQL?

Question

How can I enable strict sql_mode in MySQL?

I want to fetch data from SQL and process the same in strict mode.

My current sql_mode is:

mysql> SELECT @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+

Answer 1

You basically have two ways of doing it, using SQL command or changing configuration file. If you set it using SQL command - it will change back after the server is restarted.

Doing it in SQL:

SET GLOBAL sql_mode='STRICT_TRANS_TABLES';

Doing it in config file:

[mysqld] sql_mode="STRICT_TRANS_TABLES"

File location varies depending on your operating system, more on where to find it here: https://dev.mysql.com/doc/refman/5.7/en/option-files.html

Important to note, that you can have multiple modes specified:

sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

this is especially important when using SQL statement, since it could override your whole mode string.

More stuff about SQL modes here: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Answer 2

Do the following:

SET GLOBAL sql_mode='STRICT_TRANS_TABLES';