这篇http://boylook.blog.51cto.com/7934327/1325025提到了sql_safe_update的参数及相关结论,下面从源码上给出比较细节的分析
1.在SQL解析时,如果开启这个参数,发现谓词为空则抛异常:
if ((thd->variables.option_bits & OPTION_SAFE_UPDATES) && !select_lex->where)
{
my_message(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE,
ER(ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE), MYF(0));
DBUG_RETURN(TRUE);
}
2.在Delete时:
2.1如果full Join则抛异常
if ((thd->variables.option_bits & OPTION_SAFE_UPDATES) && error_if_full_join(join))
DBUG_RETURN(1);
2.2如果谓词为常数则抛异常
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);
}
2.3如果谓词无可用索引并且没有limit抛异常
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);
}
}
3.在Update时:
3.1如果full join则抛异常
同上
3.2如果谓词无可用索引并且没有limit抛异常
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;
}
}
结论:Delete比update有更强的约束,即where条件不能为空或“永真”
转载于:https://blog.51cto.com/boylook/1328084