MYSQL的安全模式：sql_safe_updates介紹

什麼是安全模式

在mysql中，如果在update和delete沒有加上where條件，資料將會全部修改。不只是初識mysql的開發者會遇到這個問題，工作有一定經驗的工程師難免也會忘記寫入where條件。為了避免失誤造成的資料全部修改和刪除，可開啟mysql的安全模式。

安全模式的開啟與關閉

連線到資料庫後，檢視當前mysql的安全模式的狀態

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

上面查詢命令例項表示當前mysql處於安全模式開啟的狀態。
set sql_safe_updates=1; //安全模式開啟狀態
set sql_safe_updates=0; //安全模式關閉狀態

在update操作中：當where條件中列（column）沒有索引可用且無limit限制時會拒絕更新。where條件為常量且無limit限制時會拒絕更新。

在delete操作中： 當①where條件為常量，②或where條件為空，③或where條件中 列（column）沒有索引可用且無limit限制時拒絕刪除。

安全模式UPDATE操作例項

1、無where條件的update

mysql> update users set status=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

操作失敗，提示需要使用where條件作為主鍵。

2、無where條件但是有limit的update

mysql> update users set status=1 limit 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

操作成功，雖然沒有where條件，但是加入了limit限制。

3、使用非索引欄位作為條件進行更新

mysql> update users set status=1 where reg_time>`2018-01-01 00:00:00`;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

操作失敗，因為條件欄位不是索引欄位並且沒有加入limit限制。

4、使用非索引欄位作為條件並且加入limit進行更新

mysql> update users set status=1 where reg_time>`2018-01-01 00:00:00` limit 10;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 10  Changed: 0  Warnings: 0

操作成功，雖然條件欄位不是索引欄位，但是有加入limit限制，所以執行成功。

5、使用索引欄位作為條件並且不加入limit進行更新

mysql> update users set status=1 where phone_num=`13800138000`;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

操作成功，雖然沒有加入limit限制，但是條件欄位為索引欄位，所以執行成功。

安全模式DELETE操作例項

1、無where條件的delete

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

操作失敗，沒有where條件，直接不行。

2、非索引鍵進行條件刪除

mysql> delete from users where reg_time=`2018-06-28 17:35:37`;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

操作失敗，因為reg_time欄位不是索引鍵。

3、非索引鍵作為條件並且加入limit進行刪除

mysql> delete from users where reg_time=`2018-06-28 17:35:37` limit 1;
Query OK, 1 row affected (0.00 sec)

操作成功，即使不是索引鍵，因為加入了limit。

4、使用索引鍵並且不加limit限制進行刪除。

mysql> delete from users where user_id=100000;
Query OK, 1 row affected (0.01 sec)

操作成功，因為user_id是索引鍵。

5、加入limit但是不使用where條件刪除。

mysql> delete from users 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

操作失敗，因為沒有加入where檢索條件。

總結

如果設定了sql_safe_updates=1，那麼update語句必須滿足如下條件之一才能執行成功

1)使用where子句,並且where子句中列必須為prefix索引列
2)使用limit
3)同時使用where子句和limit(此時where子句中列可以不是索引列)

delete語句必須滿足如下條件之一才能執行成功
1)使用where子句,並且where子句中列必須為prefix索引列
2)同時使用where子句和limit(此時where子句中列可以不是索引列)

更多文章請檢視簡書部落格頁