mysql sql_safe_updates 分析

排名前5的SQL悲剧中肯定有：

delete from table t /* where true */;

update t set col='new_value' /* where true */

 

由于漏掉where条件或者拼接SQL后的where条件部分为true，这时整个表都被删除/更新了...

 

在使用mysql的应用中，避免此类低级错误的方法：

1、应用仔细检查（小心才能使得万年船啊~）

2、开启sql_safe_updates 

点击(此处)折叠或打开

1. mysql> set global sql_safe_updates=1;
2. Query OK, 0 rows affected (0.00 sec)
3. mysql> delete from t;
4. ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
5. mysql> delete from t where 1=1;
6. ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

 

本文根据mysql源码介绍一下sql_safe_updates的逻辑

 

sql_safe_update开启后，mysql server层在调用mysql_update/mysql_delete时对where和limit进行判断后决定是否执行，代码调用关系：

点击(此处)折叠或打开

1. dispatch_command
   
2. |->mysql_parse
   
3.   |->mysql_execute_command
   
4.     ->mysql_update/mysql_delete

 

mysql_update：（sql/sql_update.cc）
当where条件中column没有索引可用且无limit限制时会拒绝更新

点击(此处)折叠或打开

1.  
2. /* If running in safe sql mode, don't allow updates without keys */
   
3.   if (table->quick_keys.is_clear_all())
   
4.   {
   
5.     thd->server_status|=SERVER_QUERY_NO_INDEX_USED;
   
6.     if (safe_update && !using_limit)
   
7.     {
   
8.       my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
   
9.          ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
   
10.       goto err;
    
11.     }
    
12.   }
13.  

mysql_delete中：(sql/sql_delete.cc)
当where条件为常量或者为空，或者where条件中column没有索引可用且无limit限制时拒绝删除

点击(此处)折叠或打开

1.   
2.   const_cond= (!conds || conds->const_item());
   
3.   safe_update=test(thd->variables.option_bits & OPTION_SAFE_UPDATES);
   
4.   if (safe_update && const_cond)
   
5.   {
   
6.     my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
   
7.                ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
   
8.     DBUG_RETURN(TRUE);
   
9.   }
   
10.  
11. ...
    
12.  
13. /* If running in safe sql mode, don't allow updates without keys */
    
14.   if (table->quick_keys.is_clear_all())
    
15.   {
    
16.     thd->server_status|=SERVER_QUERY_NO_INDEX_USED;
    
17.     if (safe_update && !using_limit)
    
18.     {
    
19.       delete select;
    
20.       free_underlaid_joins(thd, select_lex);
    
21.       my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
    
22.                  ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
    
23.       DBUG_RETURN(TRUE);
    
24.     }
    
25.   }
26.  

 

PS: update在有limit时是可以执行更新的，而delete严格一些，只要where条件为常量或者为空是会被拒绝的，如：

点击(此处)折叠或打开

1. mysql> update t set str='hello' where 1=1 limit 1;
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. mysql> delete from t where 1=1 limit 1;
   
4. ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

不知道这是基于怎样的考虑...

 

不过开启sql_safe_updates后，update和delete在修改数据时，如果不带limit，需要where条件可以走索引，否则会报错