SQL_Mode is a MySQL system variable that allows you to configure operational features of MySQL server. Mostly, SQL_mode is set for each client/session to customize MySQL performance for that user. But sometimes database administrators may need to set global SQL mode in MySQL for all clients and sessions. In this article, we will learn how to set global SQL mode in MySQL.
By default sql_mode is set to empty string that means non-strict mode whereby it gives errors in case input values are not conforming to SQL standards, converts them to nearest proper value and continues to process further. For example, if you try to store negative value in UNSIGNED integer, it will be converted to zero and stored.
How to Set Global SQL Mode in MySQL
There are several ways to set global SQL mode in MySQL.
You can log into MySQL console and run the following query to set global SQL_mode.
SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION'; SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
This will set global sql_mode for the session but it will revert back to default settings after session ends or MySQL restarts.
If you want to permanently set global sql_mode parameter, you need to set it in /etc/mysql/my.conf file. Open it in a text editor.
$ vi /etc/mysql/my.cnf
Add the following line to it under [mysqld] section.
[mysqld] sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
If you are using MySQL 5.7+, you need to modify the above command as shown below. Please note, in this case, you need to set sql-mode below and not sql_mode as shown above. Also the variable value needs to be in double quotes.
[mysqld] sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
Save and close the file. Restart server to apply changes.
$ sudo service mysql stop $ sudo service mysql start
In this article, we have learnt how to set global sql_mode in MySQL. By setting sql_mode, client can instruct MySQL server whether to be strict or lenient about accepting inputs, enable or disable as per SQL standards, as well as define compatibility with other database systems. Here are some of the commonly used values for sql_mode.
- ANSI_QUOTES- causes double quotes character (‘”‘) to be interpreted as an identifier-quoting character instead of as a string-quoting character.
- IGNORE_SPACE -By default, functions must have no space between the function name and the following parenthesis. This mode makes MySQL ignore spaces after function names. This allows spaces to appear between the name and the parenthesis as well, and also allows function names to be reserved words.
- ERROR_FOR_DIVISION_BY_ZERO – By default, division by zero produces a result of NULL and is not treated specially. This mode will produce a warning, or an error in strict mode.
- STRICT_TRANS_TABLES, STRICT_ALL_TABLES – Enabling strict mode causes bad values to be treated as erroneous. STRICT_TRANS_TABLES enables strict mode for transactional tables, and STRICT_ALL_TABLES enables strict mode for all tables.
- TRADITIONAL – This is a composite mode that enables both strict modes + several additional restrictions on acceptance of input data.
- ANSI – This is a composite mode that causes MySQL Server to be more conformant to ANSI standards.
Also read:
How to Check if Column is Empty or Null in MySQL
How to Create Please Wait Loading Animation in jQuery
How to Get Current URL in jQuery
How to Get Class List for DOM element
How to Move Element into Another Element in jQuery