Compartilhamento de tecnologia

Parâmetro sql_safe_updates do MySQL

2024-07-12

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

sql_safe_updates é uma variável de sistema no MySQL que controla se o servidor MySQL permite operações de atualização ou exclusão em instruções UPDATE ou DELETE que não usam uma cláusula KEY ou LIMIT. Quando esta variável é definida como ON, o MySQL rejeitará instruções UPDATE ou DELETE que possam afetar um grande número de linhas na tabela, a menos que essas instruções usem explicitamente a KEY (como uma chave primária ou índice exclusivo) na cláusula WHERE ou no Cláusula LIMIT para limitar o número de linhas afetadas.

O objetivo disso é evitar perda ou modificação acidental de grandes quantidades de dados devido a instruções SQL descuidadas ou escritas incorretamente.

Como definir sql_safe_updates

Você pode definir sql_safe_updates de diversas maneiras:

Nível global:

Você pode definir esta variável permanentemente modificando o arquivo de configuração do MySQL (como my.cnf ou my.ini, dependendo do seu sistema operacional e da versão do MySQL). No entanto, observe que a configuração de sql_safe_updates diretamente no arquivo de configuração pode não ser suportada por todas as versões do MySQL ou pode precisar ser configurada de forma diferente (como por meio de um plug-in ou outras variáveis ​​de sistema).
Uma abordagem mais comum é configurá-lo em tempo de execução usando a instrução SET GLOBAL do MySQL, mas isso afeta apenas novas conexões. Por exemplo:

SET GLOBAL sql_safe_updates = 1;

  • 1

No entanto, observe que a definição direta de variáveis ​​globais pode exigir privilégios de administrador e esta alteração não afetará as sessões existentes.

Nível da sessão:

Você pode definir sql_safe_updates executando a seguinte instrução SQL em sua sessão MySQL:

SET SESSION sql_safe_updates = 1;

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

mysql -uroot -p --safe-updates 

  • 1
  • 2
  • 3
  • 4
  • 5

Isto afeta as ações subsequentes na sessão atual, mas não afeta outras sessões ou configurações globais.

Precauções

  • Com sql_safe_updates habilitado, se você tentar executar uma instrução UPDATE ou DELETE sem KEY ou LIMIT, o MySQL rejeitará a operação e retornará um erro.
(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

  • Nem todas as implantações do MySQL habilitam sql_safe_updates por padrão. Geralmente é configurado por um administrador de banco de dados ou desenvolvedor com base em requisitos de segurança específicos.

  • Em alguns casos, pode ser necessário desabilitar temporariamente sql_safe_updates para executar operações específicas de atualização ou exclusão em massa. Nesse caso, você pode definir sql_safe_updates = 0 no nível da sessão, mas tome cuidado para garantir que suas instruções SQL sejam seguras e não afetem acidentalmente grandes quantidades de dados.

Em resumo, sql_safe_updates é um recurso de segurança útil que pode ajudar a prevenir a perda de dados devido a inadvertência ou erro. No entanto, também exige que os desenvolvedores e administradores de banco de dados prestem mais atenção às suas instruções SQL para garantir sua segurança e precisão.

explicação oficial

Se definido como 1, o MySQL aborta instruções UPDATE ou DELETE que não usam uma chave na cláusula WHERE ou uma cláusula LIMIT. (Especificamente, instruções UPDATE devem ter uma cláusula WHERE que usa uma chave ou uma cláusula LIMIT, ou ambas. Instruções DELETE devem ter ambas.) Isso torna possível capturar instruções UPDATE ou DELETE onde as chaves não são usadas corretamente e que provavelmente alterariam ou excluiriam um grande número de linhas. O valor padrão é 0.

Quando sql_safe_updates está definido como 1.

  • A instrução de atualização deve atender a uma das seguintes condições para ser executada com sucesso:
    • A instrução update usa where e deve haver uma coluna de índice na condição where;
    • A instrução update usa limite;
    • A instrução update usa where e limit ao mesmo tempo. Neste momento, a condição where não precisa ter uma coluna de índice;
(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
  • A instrução delete deve atender às seguintes condições para ser executada com sucesso:
    • A instrução delete também usa colunas de índice na condição where
    • A instrução delete usa a coluna de índice e o limite na condição where.
    • A instrução delete usa where e limit ao mesmo tempo. Neste momento, a condição where não precisa ter uma coluna de índice;
(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 a condição where trouxer uma coluna de índice, mas o otimizador finalmente varrer e selecionar a tabela inteira em vez do índice, podemos usar force index([index_name]) para informar ao otimizador qual índice usar para evitar a possibilidade de bloquear todo o perigos ocultos causados ​​pela mesa.

Perfil pessoal

Ele se dedica à pesquisa de tecnologia há mais de 30 anos e é proficiente em diversas linguagens como java, linux, javascript, php, css, etc. estação de documentação do desenvolvedor para compartilhar alguns problemas no desenvolvimento de tecnologia para referência futura.

minhas informações de contato

Correspondência