mysql5.6 Set Sql_mode Method detailed

First login to MySQL terminal

The code is as follows	Copy Code
Mysql-u xxx-pxxxxx

View the current MySQL Sql_mode

The code is as follows	Copy Code
Mysql> SELECT @ @sql_mode; +--------------------------------------------+ | @ @sql_mode | +--------------------------------------------+ | strict_trans_tables,no_engine_substitution |

+--------------------------------------------+

The MySQL Sql_mode is set to MYSQL40, which is no_field_options,high_not_precedence.

code is as follows	copy code
MySQL > SET sql_mode= ' no_field_options,high_not_precedence '; Query OK, 0 rows Affected (0.00 sec) Mysql> SELECT @ @sql_mode +--------------------------------------+ | @ @sql_mode                              | +--------------------------------------+ | No_field_options,high_not_precedence | +--------------------------------------+ 1 row in Set (0.00 sec)

It should be noted that the above modification Sql_mode is only session level, only affects the current session, after exiting the terminal both invalid

The following is the setting of the global level of variables, applicable to all sessions, but only for this MySQL process, the next reboot will be reset

The code is as follows	Copy Code
Set global sql_mode= ' No_field_options,high_not_precedence '

Detailed usage

The code is as follows	Copy Code
SET [global| Session] sql_mode= ' modes '

Sql_mode can be added to the startup script each time you start the automatic setup

The code is as follows	Copy Code
MySQL--sql-mode= "No_field_options,high_not_precedence"

sql_mode default settings issue.

After compiling and configuring the MySQL5.6 version, the database that ran normally in MySQL5.5 before the import was wrong, originally starting with the MySQL5.6 version, in the execution Mysql_install_ DB command (which typically runs after MySQL is compiled and the database is initially configured) writes the default my.cnf file to the installation path (CentOS the default installation path is/usr/local/mysql/) and contains the following information:

The code is as follows

Copy Code

# for advice on I-Change settings # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html [Mysqld] # Remove Leading # and set to the amount's RAM for the most important data # Cache in MySQL. Start at 70% all RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove Leading # to turn on a very important data integrity option:logging # Changes to the binary log between backups. # Log_bin # These are commonly set, remove the # and set as required. # Basedir = ... # DataDir = ... # port = ..... # server_id = ... # socket = ... # Remove Leading # To set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast selects. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # Author Press: See the configuration here Sql_mode=no_engine_substitution,strict_trans_tables