기술나눔

MySQL sql_safe_updates 매개변수

2024-07-12

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

sql_safe_updates는 MySQL 서버가 KEY 또는 LIMIT 절을 사용하지 않는 UPDATE 또는 DELETE 문에 대한 업데이트 또는 삭제 작업을 허용하는지 여부를 제어하는 ​​MySQL의 시스템 변수입니다. 이 변수가 ON으로 설정되면 MySQL은 테이블의 많은 행에 영향을 미칠 수 있는 UPDATE 또는 DELETE 문을 거부합니다. 단, 해당 문이 WHERE 절 또는 영향을 받는 행 수를 제한하는 LIMIT 절입니다.

이는 부주의하거나 잘못 작성된 SQL 문으로 인해 대량의 데이터가 실수로 손실되거나 수정되는 것을 방지하기 위한 것입니다.

sql_safe_updates를 설정하는 방법

여러 가지 방법으로 sql_safe_updates를 설정할 수 있습니다.

글로벌 수준:

MySQL 구성 파일(예: 운영 체제 및 MySQL 버전에 따라 my.cnf 또는 my.ini)을 수정하여 이 변수를 영구적으로 설정할 수 있습니다. 그러나 구성 파일에서 sql_safe_updates를 직접 설정하는 것은 모든 MySQL 버전에서 지원되지 않을 수도 있고 다르게 구성해야 할 수도 있습니다(예: 플러그인 또는 기타 시스템 변수를 통해).
보다 일반적인 접근 방식은 MySQL의 SET GLOBAL 문을 사용하여 런타임에 설정하는 것이지만 이는 새 연결에만 영향을 미칩니다. 예를 들어:

SET GLOBAL sql_safe_updates = 1;

  • 1

그러나 전역 변수를 직접 설정하려면 관리자 권한이 필요할 수 있으며 이러한 변경 사항은 기존 세션에 영향을 미치지 않습니다.

세션 수준:

MySQL 세션에서 다음 SQL 문을 실행하여 sql_safe_updates를 설정할 수 있습니다.

SET SESSION sql_safe_updates = 1;

或者登录时加上--safe-updates 

mysql -uroot -p --safe-updates 

  • 1
  • 2
  • 3
  • 4
  • 5

이는 현재 세션의 후속 작업에 영향을 주지만 다른 세션이나 전역 설정에는 영향을 주지 않습니다.

지침

  • sql_safe_updates가 활성화된 상태에서 KEY 또는 LIMIT 없이 UPDATE 또는 DELETE 문을 실행하려고 하면 MySQL은 작업을 거부하고 오류를 반환합니다.
(root@localhost)[superdb]> show index from dept;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| dept  |          0 | PRIMARY  |            1 | deptno      | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.13 sec)

(root@localhost)[superdb]> update dept set loc='sz';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

  • 모든 MySQL 배포가 기본적으로 sql_safe_updates를 활성화하는 것은 아닙니다. 일반적으로 특정 보안 요구 사항에 따라 데이터베이스 관리자 또는 개발자가 구성합니다.

  • 경우에 따라 특정 대량 업데이트 또는 삭제 작업을 수행하기 위해 sql_safe_updates를 일시적으로 비활성화해야 할 수도 있습니다. 이 경우 세션 수준에서 sql_safe_updates = 0을 설정할 수 있지만 SQL 문이 안전하고 실수로 많은 양의 데이터에 영향을 주지 않도록 주의하세요.

요약하자면, sql_safe_updates는 부주의나 오류로 인한 데이터 손실을 방지하는 데 도움이 되는 유용한 보안 기능입니다. 그러나 개발자와 데이터베이스 관리자는 보안과 정확성을 보장하기 위해 SQL 문에 더 많은 주의를 기울여야 합니다.

공식적인 설명

1로 설정하면 MySQL은 WHERE 절이나 LIMIT 절에서 키를 사용하지 않는 UPDATE 또는 DELETE 문을 중단합니다. (구체적으로 UPDATE 문은 키나 LIMIT 절 또는 둘 다를 사용하는 WHERE 절이 있어야 합니다. DELETE 문은 둘 다 있어야 합니다.) 이렇게 하면 키가 제대로 사용되지 않고 많은 수의 행을 변경하거나 삭제할 가능성이 있는 UPDATE 또는 DELETE 문을 잡을 수 있습니다. 기본값은 0입니다.

sql_safe_updates가 1로 설정된 경우.

  • 업데이트 문이 성공적으로 실행되려면 다음 조건 중 하나를 충족해야 합니다.
    • 업데이트 문은 where를 사용하며 where 조건에 인덱스 열이 있어야 합니다.
    • 업데이트 문은 제한을 사용합니다.
    • 업데이트 문에서는 where와limit를 동시에 사용합니다. 이때 where 조건에는 인덱스 열이 필요하지 않습니다.
(root@localhost)[superdb]> update dept set loc='sz' limit 1;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(root@localhost)[superdb]> select * from dept;
+--------+------------+---------+
| deptno | dname      | loc     |
+--------+------------+---------+
|     10 | ACCOUNTING | sz      |
|     20 | RESEARCH   | DALLAS  |
|     30 | SALES      | CHICAGO |
|     40 | OPERATIONS | BOSTON  |
+--------+------------+---------+
4 rows in set (0.00 sec)

(root@localhost)[superdb]> update dept set loc='NEW YORK' limit 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(root@localhost)[superdb]> update dept set loc='NEW YORK' where deptno=10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

(root@localhost)[superdb]> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

(root@localhost)[superdb]> update dept set loc='NEW YORK' where deptno=10 limit 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

(root@localhost)[superdb]> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 삭제 문이 성공적으로 실행되려면 다음 조건을 충족해야 합니다.
    • delete 문은 where 조건의 인덱스 열도 사용합니다.
    • delete 문은 인덱스 열과 where 조건의 제한을 모두 사용합니다.
    • 삭제 문은 where와 Limit을 동시에 사용합니다. 이때 where 조건에는 인덱스 열이 필요하지 않습니다.
(root@localhost)[superdb]> insert into dept values(50,'sz','hk');
Query OK, 1 row affected (0.01 sec)

-- 同时使用 where 和 limit，此时 where 条件中可以有索引列
(root@localhost)[superdb]> delete from dept where deptno=50 limit 1;
Query OK, 1 row affected (0.00 sec)

(root@localhost)[superdb]> insert into dept values(50,'sz','hk');
Query OK, 1 row affected (0.00 sec)

-- 仅使用 where条件中是索引列
(root@localhost)[superdb]> delete from dept where deptno=50;
Query OK, 1 row affected (0.01 sec)

(root@localhost)[superdb]> insert into dept values(50,'sz','hk');
Query OK, 1 row affected (0.00 sec)

-- dname不是索引列，因此无法删除操作
(root@localhost)[superdb]> delete from dept where dname='sz';
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 和 limit，此时 where 条件中没有索引列
(root@localhost)[superdb]> delete from dept where dname='sz' limit 1;
Query OK, 1 row affected (0.05 sec)

(root@localhost)[superdb]> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

(root@localhost)[superdb]> show index from dept;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| dept  |          0 | PRIMARY  |            1 | deptno      | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.13 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43

where 조건이 인덱스 열을 가져오지만 최적화 프로그램이 궁극적으로 인덱스 대신 전체 테이블을 검색하고 선택하는 경우 강제 인덱스([index_name])를 사용하여 전체 잠금 가능성을 피하기 위해 사용할 인덱스를 최적화 프로그램에 알릴 수 있습니다. 테이블로 인한 숨겨진 위험.

개인 프로필

그는 30년 이상 기술 연구에 전념해 왔으며, java, linux, javascript, php, css 등 다양한 언어에 능숙하며, 오픈 소스 분야에서 많은 공헌을 했습니다. 나중에 참조할 수 있도록 기술 개발의 일부 문제를 공유하는 개발자 문서 스테이션입니다.

내 연락처 정보

우편