mysql 多表删除和更新_mysql 如何避免全表的更新和删除

简单一句话，开启sql_safe_updates属性。

sql_safe_updates参数可以限制不带where条件的update/delete语句执行失败，这个参数设置后，可以防止业务bug/漏洞导致把整个表都更新或者删除(线上发生过的案例)，也可以防止DBA在线误操作更新/删除整张表。

If set to 1, MySQL aborts UPDATE or DELETE statements

that do not use a key in the WHERE clause or a LIMIT clause.

(Specifically, UPDATE statements must have a WHERE

clause that uses a key or a LIMIT clause, or both. DELETE

statements must have both.) This makes it possible to catch

UPDATE or DELETE statements where keys are not used

properly and that would probably change or delete a large

number of rows. The default value is 0.

意思是说

当sql_safe_updates设置为1时，UPDATE :要有where，且查

询条件必须使用为索引字段，或者使用limit，或者两个条件同

时存在，才能正常执行。DELETE:where条件中带有索引字段

可删除，where中查询条件不是索引，得必须有limit。主要是

防止UPDATE和DELETE 没有使用索引导致变更及删除大量数

据。系统参数默认值为0

为了防止线上业务出现以下3种情况影响线上服务的正常使用和不小心全表数据删除:

1:没有加where条件的全表更新操作

2:加了where 条件字段，但是where 字段 没有走索引的表更新

3:全表delete 没有加where 条件 或者where 条件没有 走索引

如果业务开发存在如上的操作，数据库会出现如下错误:

MySQL 报错如下：

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

DDB 报错如下:

Caused by: java.sql.SQLException: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)

建议: 开启此参数限制 ，可以避免线上业务数据误删除操作，但是需要先在测试库开启，这样可以可以先在测试库上补充短缺的表索引，测试验证没问题再部署到线上库

测试案例: 全局开启 sql_safe_updates 限制

[test]> show variables like 'sql_safe_updates';

| Variable_name | Value |

| sql_safe_updates | ON

CREATE TABLE `test_sql_safe` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`uid` bigint(20) NOT NULL,

`status` int(1) DEFAULT '0',

`amount` int(11) NOT NULL DEFAULT '0',

`nuid` bigint(20) NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_uid` (`uid`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

[test]> update test_sql_safe set amount = amount +100;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

test]> update test_sql_safe set amount = amount +100 limit 2;

Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0

[test]> update test_sql_safe set amount = amount +100 where uid = 2111; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

[test]> delete from test_sql_safe;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

delete from test_sql_safe where status = 0;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

delete from test_sql_safe where uid =3111;

Query OK, 1 row affected (0.00 sec)

[test]> delete from test_sql_safe where amount = 500 limit 1;

Query OK, 1 row affected (0.00 sec)

[test]> delete from test_sql_safe 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