MySQL中参数sql_safe_updates在生产环境怎么用

2025-03-25 技术教程

这篇文章主要介绍了MySQL中参数sql_safe_updates在生产环境怎么用，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

前言

在应用 BUG或者 DBA误操作的情况下，会发生对全表进行更新：update delete 的情况。MySQL提供 sql_safe_updates 来限制次操作。

setsql_safe_updates=1;

设置之后，会限制update delete 中不带 where 条件的SQL 执行，较严格。会对已有线上环境带来不利影响。对新系统、应用做严格审核，可以确保不会发生全表更新的问题。

CREATETABLEworking.test01(idINTNOTNULLAUTO_INCREMENT,NAMEVARCHAR(20),ageINT,gmt_createdDATETIME,PRIMARYKEY(id));insertintotest01(name,age,gmt_created)values('xiaowang',2,now());insertintotest01(name,age,gmt_created)values('huahua',5,now());insertintotest01(name,age,gmt_created)values('gougou',9,now());insertintotest01(name,age,gmt_created)values('heihei',12,now());insertintotest01(name,age,gmt_created)values('baibai',134,now());#过滤字段上没有索引updateupdatetest01setname='xiaoxiao'whereage=2；ERROR1175(HY000):YouareusingsafeupdatemodeandyoutriedtoupdateatablewithoutaWHEREthatusesaKEYcolumn#全表更新updatetest01setname='xiaoxiao';ERROR1175(HY000):YouareusingsafeupdatemodeandyoutriedtoupdateatablewithoutaWHEREthatusesaKEYcolumn#加入limit的更新updatetest01setname='xia'limit1;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0#新增索引createindexidx_ageontest01(age);updatetest01setname='xiaoxiao'whereage=2;QueryOK,1rowaffected(0.01sec)Rowsmatched:1Changed:1Warnings:0updatetest01setname='hhh'whereage=9limit10;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0altertabletest01dropindexidx_age;createindexidx_age_nameontest01(age,name);updatetest01setage=100wherename='hhh';ERROR1175(HY000):YouareusingsafeupdatemodeandyoutriedtoupdateatablewithoutaWHEREthatusesaKEYcolumnupdatetest01setage=100wherename='hhh'limit10;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0

由此，update 时，在没有 where 条件或者where 后不是索引字段时，必须使用 limit ；在有 where 条件时，为索引字段

最近在工作中又发现了一个问题，mysql sql_safe_updates 不支持子查询的更新。

考虑到开发人员有时候不小心误更新数据，要求线上库的 MySQL 实例都设置 sql_safe_updates=1 来避免没有索引的 update、delete。

结果有一天开发发现下面的一个SQL 没法正确执行：

updatet1setcol2=1wherekey1in(selectcol2fromt2wherekey2='ABcD');

错误如下：

ERROR1175(HY000):YouareusingsafeupdatemodeandyoutriedtoupdateatablewithoutaWHEREthatusesaKEYcolumn

也就是说没法对没有走到索引的where条件进行更新。搜索了下发现，的确不行。及时 key1 和key2 分别是 t1、t2 的索引[我换成主键都不行] 。说明是不支持子查询的update。

google 了一下发现人家也问过这个问题。。

http://stackoverflow.com/questions/24314830/query-not-getting-executed-if-supplied-a-nested-sub-query

最后解决方法：

1）修改 session 级别的参数： set sql_safe_updates=0; 执行 update 操作。退出终端。