Overview
In this tutorial, we will learn about MySQL SQL Modes. MySQL server can operate in different SQL modes, and can apply these modes differently for different clients.
MySQL SQL Modes
SQL modes control aspects of server operations such as what SQL syntax MySQL should and what kind of data validation checks it should perform. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers. This capability allows each application to tailor the server’s operating mode to its own requirements.
Get sql_mode
We can retrieve the current global or session sql_mode value with the following statements:
mysql> SELECT @@global.sql_mode;
+——————————————–+
| @@global.sql_mode |
+——————————————–+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+——————————————–+
1 row in set (0.00 sec)
mysql> SELECT @@session.sql_mode;
+——————————————–+
| @@session.sql_mode |
+——————————————–+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+——————————————–+
1 row in set (0.00 sec)
We can set the default SQL mode by starting mysqld with –sql-mode=”mode_value” option.
We can change the SQL mode at runtime by using a SET [GLOBAL|SESSION]
We can also change the mode at runtime by setting the sql_mode system variable with:
mysql> SET [SESSION|GLOBAL] sql_mode=’mode_value’
sql_mode=’modes’ statement to set the sql_mode system value. Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time.
Some important sql_mode values are:
TRADITIONAL
Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “given an error instead of a warning” when attempting to insert an incorrect value into a column.
ANSI
Change syntax and behavior to be more conformant to standard SQL.
STRICT_TRANS_TABLES, STRICT_ALL_TABLES
If a value could not be inserted as given into a transactional table, abort the statement. For a non-transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-rows statement. More detail if given in this section. These values enable”strict mode”.
—
MySQL Tutorials
MySQL Tutorials on this website:
https://www.testingdocs.com/mysql-tutorials-for-beginners/
For more information on MySQL Database: