How to change 'sql-mode' parameter for MySQL?

Posted:  May 14th, 2018

 

Question

 

How to change 'sql-mode' parameter for MySQL?

 

Answer

 

1. To change 'sql-mode' parameter permanently.

 

1.1. Modify /etc/my.cnf file ( /etc/mysql/my.cnf for Debian based system) by adding the following line in [mysqld] section:

 

    sql-mode = 'name-of-modes';

 

Where "name-of-modes" are the ones of Server SQL Modes . This value may be empty to disable all sql-modes.

 

1.2. Restart mysql service.

 

2. To change 'sql-mode' parameter temporary.

 

2.1. Login MySQL database - How to login MySQL database .

 

2.2. Execute the following query to show which sql modes currently enabled:

 

    mysql> SELECT @@sql_mode;

 

2.3. Execute the following query to set desirable sql-modes:

 

    mysql> SET sql_mode = 'name-of-modes';