Condivisione della tecnologia

Parametro MySQL sql_safe_updates

2024-07-12

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

sql_safe_updates è una variabile di sistema in MySQL che controlla se il server MySQL consente operazioni di aggiornamento o eliminazione sulle istruzioni UPDATE o DELETE che non utilizzano una clausola KEY o LIMIT. Quando questa variabile è impostata su ON, MySQL rifiuterà le istruzioni UPDATE o DELETE che potrebbero influenzare un gran numero di righe nella tabella, a meno che queste istruzioni non utilizzino esplicitamente la KEY (come una chiave primaria o un indice univoco) nella clausola WHERE o nella clausola Clausola LIMIT per limitare il numero di righe interessate.

Lo scopo è quello di prevenire la perdita o la modifica accidentale di grandi quantità di dati a causa di istruzioni SQL scritte negligentemente o in modo errato.

Come impostare sql_safe_updates

Puoi impostare sql_safe_updates in diversi modi:

Livello globale:

Puoi impostare questa variabile in modo permanente modificando il file di configurazione di MySQL (come my.cnf o my.ini, a seconda del tuo sistema operativo e della versione di MySQL). Tuttavia, tieni presente che l'impostazione di sql_safe_updates direttamente nel file di configurazione potrebbe non essere supportata da tutte le versioni di MySQL o potrebbe essere necessario configurarla in modo diverso (ad esempio tramite un plug-in o altre variabili di sistema).
Un approccio più comune consiste nell'impostarlo in fase di runtime utilizzando l'istruzione SET GLOBAL di MySQL, ma ciò influisce solo sulle nuove connessioni. Per esempio:

SET GLOBAL sql_safe_updates = 1;

  • 1

Tuttavia, tieni presente che l'impostazione diretta delle variabili globali potrebbe richiedere privilegi di amministratore e questa modifica non influirà sulle sessioni esistenti.

Livello della sessione:

Puoi impostare sql_safe_updates eseguendo la seguente istruzione SQL nella sessione MySQL:

SET SESSION sql_safe_updates = 1;

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

mysql -uroot -p --safe-updates 

  • 1
  • 2
  • 3
  • 4
  • 5

Ciò influisce sulle operazioni successive nella sessione corrente, ma non su altre sessioni o impostazioni globali.

Precauzioni

  • Con sql_safe_updates abilitato, se provi a eseguire un'istruzione UPDATE o DELETE senza KEY o LIMIT, MySQL rifiuterà l'operazione e restituirà un errore.
(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

  • Non tutte le distribuzioni MySQL abilitano sql_safe_updates per impostazione predefinita. Di solito viene configurato da un amministratore o sviluppatore di database in base a requisiti di sicurezza specifici.

  • In alcuni casi, potrebbe essere necessario disabilitare temporaneamente sql_safe_updates per eseguire operazioni specifiche di aggiornamento o eliminazione in blocco. In questo caso, puoi impostare sql_safe_updates = 0 a livello di sessione, ma fai attenzione a garantire che le tue istruzioni SQL siano sicure e non influiscano accidentalmente su grandi quantità di dati.

In sintesi, sql_safe_updates è un'utile funzionalità di sicurezza che può aiutare a prevenire la perdita di dati dovuta a inavvertenza o errore. Tuttavia, richiede anche che gli sviluppatori e gli amministratori di database prestino maggiore attenzione alle loro istruzioni SQL per garantirne la sicurezza e l'accuratezza.

spiegazione ufficiale

Se impostato su 1, MySQL interrompe le istruzioni UPDATE o DELETE che non utilizzano una chiave nella clausola WHERE o una clausola LIMIT. (In particolare, le istruzioni UPDATE devono avere una clausola WHERE che utilizza una chiave o una clausola LIMIT, o entrambe. Le istruzioni DELETE devono avere entrambe.) Ciò consente di intercettare le istruzioni UPDATE o DELETE in cui le chiavi non vengono utilizzate correttamente e che probabilmente modificherebbero o eliminerebbero un gran numero di righe. Il valore predefinito è 0.

Quando sql_safe_updates è impostato su 1.

  • Per essere eseguita correttamente, l'istruzione di aggiornamento deve soddisfare una delle seguenti condizioni:
    • L'istruzione update utilizza where e deve essere presente una colonna indice nella condizione where;
    • L'istruzione update utilizza limit;
    • L'istruzione update utilizza where e limit contemporaneamente. Al momento, la condizione where non necessita di una colonna indice;
(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
  • Per essere eseguita correttamente, l'istruzione delete deve soddisfare le seguenti condizioni:
    • L'istruzione delete utilizza anche le colonne indice nella condizione where.
    • L'istruzione delete utilizza sia la colonna indice che il limite nella condizione where.
    • L'istruzione delete utilizza where e limit contemporaneamente. Al momento, la condizione where non necessita di una colonna indice;
(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

Se la condizione dove porta una colonna di indice, ma l'ottimizzatore alla fine scansiona e seleziona l'intera tabella invece dell'indice, possiamo usare force index([index_name]) per dire all'ottimizzatore quale indice usare per evitare la possibilità di bloccare l'intero tabella. pericoli nascosti causati dalla tabella.

Profilo personale

Si dedica alla ricerca tecnologica da più di 30 anni ed è esperto in vari linguaggi come Java, Linux, Javascript, php, css, ecc. Ha dato numerosi contributi nel campo dell'open source stazione di documentazione per gli sviluppatori per condividere alcuni problemi nello sviluppo della tecnologia per riferimento futuro

le mie informazioni di contatto

Posta