Обмен технологиями

Параметр MySQL sql_safe_updates

2024-07-12

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

sql_safe_updates — это системная переменная в MySQL, которая контролирует, разрешает ли сервер MySQL операции обновления или удаления в операторах UPDATE или DELETE, которые не используют предложение KEY или LIMIT. Когда для этой переменной установлено значение ON, MySQL будет отклонять инструкции UPDATE или DELETE, которые могут повлиять на большое количество строк в таблице, если только эти инструкции явно не используют KEY (например, первичный ключ или уникальный индекс) в предложении WHERE или Предложение LIMIT для ограничения количества затронутых строк.

Целью этого является предотвращение случайной потери или изменения больших объемов данных из-за небрежного или неправильно написанного оператора SQL.

Как установить sql_safe_updates

Вы можете установить sql_safe_updates несколькими способами:

Глобальный уровень:

Вы можете установить эту переменную навсегда, изменив файл конфигурации MySQL (например, my.cnf или my.ini, в зависимости от вашей операционной системы и версии MySQL). Однако обратите внимание, что установка sql_safe_updates непосредственно в файле конфигурации может поддерживаться не всеми версиями MySQL или может потребоваться настроить по-другому (например, через плагин или другие системные переменные).
Более распространенный подход — установить его во время выполнения с помощью инструкции MySQL SET GLOBAL, но это влияет только на новые соединения. Например:

SET GLOBAL sql_safe_updates = 1;

  • 1

Однако обратите внимание, что для установки глобальных переменных напрямую могут потребоваться права администратора, и это изменение не повлияет на существующие сеансы.

Уровень сеанса:

Вы можете установить sql_safe_updates, выполнив следующий оператор SQL в сеансе MySQL:

SET SESSION sql_safe_updates = 1;

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

mysql -uroot -p --safe-updates 

  • 1
  • 2
  • 3
  • 4
  • 5

Это влияет на последующие операции в текущем сеансе, но не на другие сеансы или глобальные настройки.

Меры предосторожности

  • При включенном sql_safe_updates, если вы попытаетесь выполнить оператор UPDATE или DELETE без KEY или LIMIT, 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 прерывает операторы UPDATE или DELETE, которые не используют ключ в предложении WHERE или предложении LIMIT. (В частности, операторы UPDATE должны иметь предложение WHERE, которое использует ключ или предложение LIMIT, или оба. Операторы 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
  • Для успешного выполнения инструкция удаления должна соответствовать следующим условиям:
    • Оператор удаления также использует индексные столбцы в условииwhere.
    • Оператор удаления использует как столбец индекса, так и ограничение в условии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 приводит к появлению столбца индекса, но оптимизатор в конечном итоге сканирует и выбирает всю таблицу вместо индекса, мы можем использовать Force index([index_name]), чтобы сообщить оптимизатору, какой индекс использовать, чтобы избежать возможности блокировки всей таблицы. стол. Скрытые опасности, вызванные столом.

Личный профиль

Он посвятил себя исследованию технологий более 30 лет и владеет различными языками, такими как Java, Linux, Javascript, php, css и т. д. Он внес большой вклад в область открытого исходного кода. Станция документации для разработчиков, где можно поделиться некоторыми проблемами в разработке технологий для дальнейшего использования. Все ознакомьтесь.

моя контактная информация

Почта