mysql update 有锁 mysql无锁更新

转载

柳随风 2023-11-23 13:18:41

文章标签 mysql update 有锁 mysql mysql优化 sql 安全更新 文章分类 MySQL 数据库

哎，总是会有些误操作或代码bug(特别是动态SQL)，会执行一些无条件(或无有效条件 where 1=1 )更新或删除操作,让人防不胜防，特别是在大表上发生这样的事故的时候，搞得人好心累。

mysql有一个动态变量(sql_safe_updates)，可以有效避免(不能完成避免)这种情况的发生。

下面将对此变量进行一些测试：mysql5.7

# 进行测试数据准备
mysql> drop table IF EXISTS test_safe;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE table IF NOT EXISTS  test_safe(
    -> id int auto_increment primary key ,    #主键列
    -> vname  varchar(32) ,
    -> age tinyint default 0,
    -> create_time datetime default now(),
    -> key idx_vname(vname)      #给vname列添加索引
    -> );
insert into test_safe(vname,age) values("AAA",1),("BBB",2),("CCC",3),("DDD",4),("EEE",5),("FFF",6),("GGG",7),("HHH",8),("III",9),("JJJ",10);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test_safe(vname,age) values("AAA",1),("BBB",2),("CCC",3),("DDD",4),("EEE",5),("FFF",6),("GGG",7),("HHH",8),("III",9),("JJJ",10);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> desc test_safe;
+-------------+-------------+------+-----+-------------------+----------------+
| Field       | Type        | Null | Key | Default           | Extra          |
+-------------+-------------+------+-----+-------------------+----------------+
| id          | int(11)     | NO   | PRI | NULL              | auto_increment |
| vname       | varchar(32) | YES  | MUL | NULL              |                |
| age         | tinyint(4)  | YES  |     | 0                 |                |
| create_time | datetime    | YES  |     | CURRENT_TIMESTAMP |                |
+-------------+-------------+------+-----+-------------------+----------------+
4 rows in set (0.01 sec)

mysql> select * from test_safe;
+----+-------+------+---------------------+
| id | vname | age  | create_time         |
+----+-------+------+---------------------+
|  1 | AAA   |    1 | 2020-11-13 13:38:37 |
|  2 | BBB   |    2 | 2020-11-13 13:38:37 |
|  3 | CCC   |    3 | 2020-11-13 13:38:37 |
|  4 | DDD   |    4 | 2020-11-13 13:38:37 |
|  5 | EEE   |    5 | 2020-11-13 13:38:37 |
|  6 | FFF   |    6 | 2020-11-13 13:38:37 |
|  7 | GGG   |    7 | 2020-11-13 13:38:37 |
|  8 | HHH   |    8 | 2020-11-13 13:38:37 |
|  9 | III   |    9 | 2020-11-13 13:38:37 |
| 10 | JJJ   |   10 | 2020-11-13 13:38:37 |
+----+-------+------+---------------------+
10 rows in set (0.00 sec)






#在全局开启安全更新模式
mysql> set global sql_safe_updates=1;
Query OK, 0 rows affected (0.00 sec)

#在当前会话开启安全更新模式
mysql> set sql_safe_updates=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | ON    |
+------------------+-------+
1 row in set (0.01 sec)







#测试1:无条件更新。(执行失败)
mysql> UPDATE test_safe SET age = age+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. 

#测试2:WHERE 1=1 更新。(执行失败)
mysql> UPDATE test_safe SET age = age+1 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. 

#测试3:根据非索引列条件更新。(执行失败)
mysql> UPDATE test_safe SET age = age+1 WHERE create_time<NOW();
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 

#测试4:根据索引列条件更新。(执行成功)
mysql> UPDATE test_safe SET age = age+1 WHERE vname>'000';
Query OK, 10 rows affected (0.01 sec)
Rows matched: 10  Changed: 10  Warnings: 0

#测试5:limit 更新。(执行成功)
mysql> UPDATE test_safe SET age = age+1 WHERE 1=1 LIMIT 10;
Query OK, 10 rows affected (0.01 sec)
Rows matched: 10  Changed: 10  Warnings: 0


mysql> UPDATE test_safe SET age = age+1 LIMIT 10;
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10  Changed: 10  Warnings: 0

#测试5:like '%XX' 更新。(执行失败)
mysql> UPDATE test_safe SET age = age+1 WHERE vname LIKE '%000';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 

#测试6:like 'XX%' 更新。(执行成功)
mysql> UPDATE test_safe SET age = age+1 WHERE vname LIKE '000%';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 0  Changed: 0  Warnings: 0


#测试7:无条件删除。(执行失败)
mysql> DELETE FROM test_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. 

#测试8:where 1=1 删除。(执行失败)
mysql> DELETE FROM test_safe 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. 

#测试9:根据非索引列条件删除。(执行失败)
mysql> DELETE FROM  test_safe WHERE create_time<NOW();
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 


#测试10:limit 删除。(执行成功)
mysql> DELETE FROM  test_safe LIMIT 1;
Query OK, 1 row affected (0.00 sec)

#测试11:根据索引列进行删除。(执行成功)
mysql> DELETE FROM test_safe WHERE vname='CCC';
Query OK, 1 row affected (0.00 sec)

#测试12:根据索引列进行删除。(执行成功)
mysql> DELETE FROM test_safe WHERE vname <> 'CCC';
Query OK, 8 rows affected (0.01 sec)

开启安全更新模式后，update或delete语句必须满足如下条件之一才能执行成功：

1.使用了limit条件

2.where条件中包含索引列，且可以使用到索引。(这个地方不好表述，建议看上面的测试例子进行意会)

3.where条件中不包含索引列，但是使用了limit。

虽然开启安全更新可以避免无条件(where 1=1等)的更新(或删除)操作，但是也不能完成避免全表更新(或删除)操作，像测试4、测试9、测试12中，使用到了索引列，但是给出的条件表中所有的行都满足条件，一样实现了全表更新(或删除)。

注意 : 阿里云 RDS(mysql5.7)不支持此参数，8.0的不知道，没有用过。