content_image/851225.jpg content_image/851222.jpg
To change the SQL mode at runtime, set the global or session sql_mode  system variable using a SET statement: 

SET GLOBAL sql_mode = ’ modes ’; SET SESSION sql_mode = ’ modes ’; 

Setting the GLOBAL  variable requires the SYSTEM_VARIABLES_ADMIN  privilege (or the deprecated SUPER privilege) and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Each client can change its session sql_mode  value at any time. 

To determine the current global or session sql_mode  setting, select its value: 

SELECT @@GLOBAL.sql_mode; SELECT @@SESSION.sql_mode; 

<img src='content_image/851225.jpg'>

## Important 

SQL mode and user-defined partitioning.  Changing the server SQL mode after creating and inserting data into partitioned tables can cause major changes in the behavior of such tables, and could lead to loss or corruption of data. It is strongly recommended that you never change the SQL mode once you have created tables employing user-defined partitioning. 

When replicating partitioned tables, differing SQL modes on the source and replica can also lead to problems. For best results, you should always use the same server SQL mode on the source and replica. 

## The Most Important SQL Modes 

The most important sql_mode  values are probably these: 

• ANSI 

For more information, see Section 24.6, “Restrictions and Limitations on Partitioning” . 

This mode changes syntax and behavior to conform more closely to standard SQL. It is one of the special combination modes listed at the end of this section. 

• STRICT_TRANS_TABLES 

If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement. More details are given later in this section. 

• TRADITIONAL 

Make MySQL behave like a “traditional”  SQL database system. A simple description of this mode is “give an error instead of a warning”  when inserting an incorrect value into a column. It is one of the special combination modes listed at the end of this section. 

<img src='content_image/851222.jpg'>

## Note 

With TRADITIONAL  mode enabled, an INSERT  or UPDATE  aborts as soon as an error occurs. If you are using a nontransactional storage engine, this may not be what you want because data changes made prior to the error may not be rolled back, resulting in a “partially done”  update. 

