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 

mysql> set sql_safe_updates=1;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t;
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> delete from t where 1=1;
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进行判断后决定是否执行，代码调用关系：

dispatch_command
|->mysql_parse
  |->mysql_execute_command
    ->mysql_update/mysql_delete

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

/* If running in safe sql mode, don't allow updates without keys */
  if (table->quick_keys.is_clear_all())
  {
    thd->server_status|=SERVER_QUERY_NO_INDEX_USED;
    if (safe_update && !using_limit)
    {
      my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
         ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
      goto err;
    }
  }

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

  const_cond= (!conds || conds->const_item());
  safe_update=test(thd->variables.option_bits & OPTION_SAFE_UPDATES);
  if (safe_update && const_cond)
  {
    my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
               ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
    DBUG_RETURN(TRUE);
  }

...

/* If running in safe sql mode, don't allow updates without keys */
  if (table->quick_keys.is_clear_all())
  {
    thd->server_status|=SERVER_QUERY_NO_INDEX_USED;
    if (safe_update && !using_limit)
    {
      delete select;
      free_underlaid_joins(thd, select_lex);
      my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
                 ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
      DBUG_RETURN(TRUE);
    }
  }

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

mysql> update t set str='hello' where 1=1 limit 1;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from t where 1=1 limit 1;
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条件可以走索引，否则会报错