排名前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 columnmysql> 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条件可以走索引，否则会报错.