mysql5.6設置sql_mode方法詳解

本文章來給大家介紹一下mysql5.6設置sql_mode方法詳解，希望此方法對各位同學會有所幫助哦。

首先登錄mysql終端

 代碼如下 復制代碼

mysql -u xxx -pxxxxx

查看當前mysql的sql_mode

 代碼如下 復制代碼 mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |

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

將mysql的sql_mode 設為 MYSQL40 也就是 NO_FIELD_OPTIONS,HIGH_NOT_PRECEDENCE

 代碼如下 復制代碼

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)

需要注意的是 上面的修改  sql_mode 只是會話級別的，只影響當前會話,退出終端後既失效

下面這個才是設置全局級別的變量，適用於所有會話,但僅限於本次mysql進程,下次重啟還要重新設置

 代碼如下 復制代碼 set global sql_mode=`NO_FIELD_OPTIONS,HIGH_NOT_PRECEDENCE`

詳細用法

 代碼如下 復制代碼

SET [GLOBAL|SESSION] sql_mode='modes'

每次啟動自動設置sql_mode可以在啟動腳本裡面加上

 代碼如下 復制代碼

mysql --sql-mode="NO_FIELD_OPTIONS,HIGH_NOT_PRECEDENCE"

sql_mode默認設置問題 .

編譯並配置完MySQL5.6版本，導入之前在MySQL5.5中正常運行的數據庫卻發生錯誤，原來從MySQL5.6版本開始，在執行mysql_install_db命令時（該命令一般在編譯完mysql後運行並對數據庫進行初始配置)會將默認的一個my.cnf文件寫入到安裝路徑中(CentOS下默認安裝路徑為/usr/local/mysql/），其包含的信息如下：

 代碼如下 復制代碼

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total 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

# 作者按: 見這裡的配置
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES