Partage de technologie

Paramètre MySQL sql_safe_updates

2024-07-12

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

sql_safe_updates est une variable système dans MySQL qui contrôle si le serveur MySQL autorise les opérations de mise à jour ou de suppression sur les instructions UPDATE ou DELETE qui n'utilisent pas de clause KEY ou LIMIT. Lorsque cette variable est définie sur ON, MySQL rejettera les instructions UPDATE ou DELETE susceptibles d'affecter un grand nombre de lignes de la table, à moins que ces instructions n'utilisent explicitement la KEY (telle qu'une clé primaire ou un index unique) dans la clause WHERE ou dans le Clause LIMIT pour limiter le nombre de lignes affectées.

Le but est d'éviter la perte ou la modification accidentelle de grandes quantités de données en raison d'instructions SQL imprudentes ou mal écrites.

Comment définir sql_safe_updates

Vous pouvez définir sql_safe_updates de plusieurs manières :

Niveau mondial :

Vous pouvez définir cette variable de manière permanente en modifiant le fichier de configuration MySQL (tel que my.cnf ou my.ini, selon votre système d'exploitation et la version de MySQL). Cependant, veuillez noter que la définition de sql_safe_updates directement dans le fichier de configuration peut ne pas être prise en charge par toutes les versions de MySQL, ou peut devoir être configurée différemment (par exemple via un plug-in ou d'autres variables système).
Une approche plus courante consiste à le définir au moment de l'exécution à l'aide de l'instruction SET GLOBAL de MySQL, mais cela n'affecte que les nouvelles connexions. Par exemple:

SET GLOBAL sql_safe_updates = 1;

  • 1

Cependant, veuillez noter que la définition directe de variables globales peut nécessiter des privilèges d'administrateur et que cette modification n'affectera pas les sessions existantes.

Niveau de la séance :

Vous pouvez définir sql_safe_updates en exécutant l'instruction SQL suivante dans votre session MySQL :

SET SESSION sql_safe_updates = 1;

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

mysql -uroot -p --safe-updates 

  • 1
  • 2
  • 3
  • 4
  • 5

Cela affecte les actions ultérieures dans la session en cours, mais pas les autres sessions ou les paramètres globaux.

Précautions

  • Avec sql_safe_updates activé, si vous essayez d'exécuter une instruction UPDATE ou DELETE sans KEY ou LIMIT, MySQL rejettera l'opération et renverra une erreur.
(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

  • Tous les déploiements MySQL n'activent pas sql_safe_updates par défaut. Il est généralement configuré par un administrateur de base de données ou un développeur en fonction d'exigences de sécurité spécifiques.

  • Dans certains cas, vous devrez peut-être désactiver temporairement sql_safe_updates pour effectuer des opérations de mise à jour ou de suppression groupées spécifiques. Dans ce cas, vous pouvez définir sql_safe_updates = 0 au niveau de la session, mais veillez à ce que vos instructions SQL soient sécurisées et n'affectent pas accidentellement de grandes quantités de données.

En résumé, sql_safe_updates est une fonctionnalité de sécurité utile qui peut aider à prévenir la perte de données due à une inadvertance ou à une erreur. Cependant, cela oblige également les développeurs et les administrateurs de bases de données à accorder plus d’attention à leurs instructions SQL afin de garantir leur sécurité et leur exactitude.

explication officielle

Si la valeur est définie sur 1, MySQL abandonne les instructions UPDATE ou DELETE qui n'utilisent pas de clé dans la clause WHERE ou dans une clause LIMIT. (Plus précisément, les instructions UPDATE doivent avoir une clause WHERE qui utilise une clé ou une clause LIMIT, ou les deux. Les instructions DELETE doivent avoir les deux.) Cela permet d'intercepter les instructions UPDATE ou DELETE où les clés ne sont pas utilisées correctement et qui modifieraient ou supprimeraient probablement un grand nombre de lignes. La valeur par défaut est 0.

Lorsque sql_safe_updates est défini sur 1.

  • L'instruction de mise à jour doit remplir l'une des conditions suivantes pour être exécutée avec succès :
    • L'instruction de mise à jour utilise Where et il doit y avoir une colonne d'index dans la condition Where ;
    • L'instruction update utilise la limite ;
    • L'instruction update utilise Where et Limit en même temps. À l'heure actuelle, la condition Where n'a pas besoin d'avoir une colonne d'index ;
(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
  • L'instruction delete doit remplir les conditions suivantes pour être exécutée avec succès :
    • L'instruction delete utilise également des colonnes d'index dans la condition Where.
    • L'instruction delete utilise à la fois la colonne d'index et la limite dans la condition Where.
    • L'instruction delete utilise Where et Limit en même temps. À l'heure actuelle, la condition Where n'a pas besoin d'avoir une colonne d'index ;
(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

Si la condition Where apporte une colonne d'index, mais que l'optimiseur analyse et sélectionne finalement la table entière au lieu de l'index, nous pouvons utiliser force index([index_name]) pour indiquer à l'optimiseur quel index utiliser pour éviter la possibilité de verrouiller l'intégralité de la table. table. dangers cachés causés par la table.

Profil personnel

Il se consacre à la recherche technologique depuis plus de 30 ans et maîtrise divers langages tels que java, linux, javascript, php, css, etc. Il a apporté de nombreuses contributions dans le domaine de l'open source. station de documentation pour les développeurs pour partager certains problèmes de développement technologique pour référence future.

mes coordonnées

Mail