MySQL的sql_mode解析與設定

標籤：配置   log   string   set   ted   col   post   地址   做了   

參考地址： http://blog.csdn.net/ccccalculator/article/details/70432123

因為在MySQL中使用group by 是總是出現1055的錯誤，這就導致了必須去查看是什麼原因了，查詢了相關的資料，現在將筆記記錄下來，以便後面可以參考使用：

sql_mode:簡而言之就是：它定義了你MySQL應該支援的sql文法，對資料的校正等等

select @@sql_mode:使用該命令我們可以查看我們當前資料庫的sql_mode

mysql> select @@sql_mode;+-------------------------------------------------------------------------------------------------------------------------------------------+| @@sql_mode                                                                                                                                |+-------------------------------------------------------------------------------------------------------------------------------------------+| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |+-------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

下面我們來看看sql_mode的各個值的含義：

ONLY_FULL_GROUP_BY：

對於GROUP BY彙總操作，如果在SELECT中的列，沒有在GROUP BY中出現，那麼將認為這個SQL是不合法的，因為列不在GROUP BY從句中

因為有only_full_group_by，所以我們要在MySQL中正確的使用group by語句的話，只能是select column1 from tb1 group by column1(即只能展示group by的欄位，其他均都要報1055的錯)

執行個體：

mysql> select * from tt1;+----+-------+--------+| id | name  | gender |+----+-------+--------+|  1 | xiong |      0 ||  2 | ying  |      0 ||  3 | cai   |      0 ||  4 | zhang |      0 ||  5 | li    |      1 ||  6 | wang  |      1 |+----+-------+--------+6 rows in set (0.00 sec)mysql> select id,name from tt1 group by name;ERROR 1055 (42000):

mysql> select * from tt1;+----+-------+--------+| id | name  | gender |+----+-------+--------+|  1 | xiong |      0 ||  2 | ying  |      0 ||  3 | cai   |      0 ||  4 | zhang |      0 ||  5 | li    |      1 ||  6 | wang  |      1 |+----+-------+--------+6 rows in set (0.00 sec)mysql> select * from tt1 group by name;ERROR 1055 (42000):

mysql> select * from tt1;+----+-------+--------+| id | name  | gender |+----+-------+--------+|  1 | xiong |      0 ||  2 | ying  |      0 ||  3 | cai   |      0 ||  4 | zhang |      0 ||  5 | li    |      1 ||  6 | wang  |      1 |+----+-------+--------+6 rows in set (0.00 sec)mysql> select name from tt1 group by name;+-------+| name  |+-------+| cai   || li    || wang  || xiong || ying  || zhang |+-------+6 rows in set (0.00 sec)

所以我們要使用能正確的使用group by 的話就必須刪除掉only_full_group_by

set sql_mode=(select replace(@@sql_mode,‘ONLY_FULL_GROUP_BY‘,‘‘)); 可以使用該語句來將空格替換掉only_full_group_by，這樣我們就可以使用

mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY‘,‘‘));Query OK, 0 rows affected (0.00 sec)mysql> select id,name from tt1 group by name;+----+-------+| id | name  |+----+-------+|  3 | cai   ||  5 | li    ||  6 | wang  ||  1 | xiong ||  2 | ying  ||  4 | zhang |+----+-------+6 rows in set (0.00 sec)

mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY‘,‘‘));Query OK, 0 rows affected (0.00 sec)mysql> select * from tt1 group by name;+----+-------+--------+| id | name  | gender |+----+-------+--------+|  3 | cai   |      0 ||  5 | li    |      1 ||  6 | wang  |      1 ||  1 | xiong |      0 ||  2 | ying  |      0 ||  4 | zhang |      0 |+----+-------+--------+6 rows in set (0.00 sec)

但是這種方法只是做了暫時的修改，我們可以更改設定檔my.ini

sql_mode= "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

MySQL的sql_mode解析與設定