Chinaunix首页 | 论坛 | 博客
  • 博客访问: 151332
  • 博文数量: 12
  • 博客积分: 226
  • 博客等级: 二等列兵
  • 技术积分: 221
  • 用 户 组: 普通用户
  • 注册时间: 2012-11-10 23:15
文章分类

全部博文(12)

分类: Mysql/postgreSQL

2012-12-26 14:57:18

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

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. /* If running in safe sql mode, don't allow updates without keys */
  12.   if (table->quick_keys.is_clear_all())
  13.   {
  14.     thd->server_status|=SERVER_QUERY_NO_INDEX_USED;
  15.     if (safe_update && !using_limit)
  16.     {
  17.       delete select;
  18.       free_underlaid_joins(thd, select_lex);
  19.       my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
  20.                  ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
  21.       DBUG_RETURN(TRUE);
  22.     }
  23.   }


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条件可以走索引,否则会报错

阅读(13562) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~