Compartir tecnología

Parámetro MySQL sql_safe_updates

2024-07-12

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

sql_safe_updates es una variable del sistema en MySQL que controla si el servidor MySQL permite operaciones de actualización o eliminación en declaraciones UPDATE o DELETE que no utilizan una cláusula KEY o LIMIT. Cuando esta variable está activada, MySQL rechazará las declaraciones ACTUALIZAR o ELIMINAR que puedan afectar una gran cantidad de filas en la tabla, a menos que estas declaraciones utilicen explícitamente la CLAVE (como una clave principal o un índice único) en la cláusula WHERE o la Cláusula LIMIT para limitar el número de filas afectadas.

El propósito de esto es evitar la pérdida o modificación accidental de grandes cantidades de datos debido a declaraciones SQL escritas incorrectamente o descuidadas.

Cómo configurar sql_safe_updates

Puede configurar sql_safe_updates de varias maneras:

Nivel global:

Puede configurar esta variable de forma permanente modificando el archivo de configuración de MySQL (como my.cnf o my.ini, según su sistema operativo y la versión de MySQL). Sin embargo, tenga en cuenta que es posible que la configuración de sql_safe_updates directamente en el archivo de configuración no sea compatible con todas las versiones de MySQL o que deba configurarse de manera diferente (por ejemplo, mediante un complemento u otras variables del sistema).
Un enfoque más común es configurarlo en tiempo de ejecución usando la declaración SET GLOBAL de MySQL, pero esto solo afecta las conexiones nuevas. Por ejemplo:

SET GLOBAL sql_safe_updates = 1;

  • 1

Sin embargo, tenga en cuenta que configurar variables globales directamente puede requerir privilegios de administrador y este cambio no afectará las sesiones existentes.

Nivel de sesión:

Puede configurar sql_safe_updates ejecutando la siguiente instrucción SQL en su sesión de MySQL:

SET SESSION sql_safe_updates = 1;

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

mysql -uroot -p --safe-updates 

  • 1
  • 2
  • 3
  • 4
  • 5

Esto afecta las operaciones posteriores en la sesión actual, pero no a otras sesiones ni configuraciones globales.

Precauciones

  • Con sql_safe_updates habilitado, si intenta ejecutar una instrucción ACTUALIZAR o ELIMINAR sin una CLAVE o LÍMITE, MySQL rechazará la operación y devolverá un error.
(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

  • No todas las implementaciones de MySQL habilitan sql_safe_updates de forma predeterminada. Por lo general, lo configura un administrador o desarrollador de bases de datos según requisitos de seguridad específicos.

  • En algunos casos, es posible que necesite deshabilitar temporalmente sql_safe_updates para realizar operaciones de eliminación o actualización masiva específicas. En este caso, puede establecer sql_safe_updates = 0 en el nivel de sesión, pero tenga cuidado de asegurarse de que sus declaraciones SQL sean seguras y no afecten accidentalmente grandes cantidades de datos.

En resumen, sql_safe_updates es una característica de seguridad útil que puede ayudar a prevenir la pérdida de datos debido a inadvertencia o error. Sin embargo, también requiere que los desarrolladores y administradores de bases de datos presten más atención a sus declaraciones SQL para garantizar su seguridad y precisión.

explicación oficial

Si se establece en 1, MySQL cancela las sentencias UPDATE o DELETE que no utilizan una clave en la cláusula WHERE o una cláusula LIMIT. (Específicamente, las sentencias UPDATE deben tener una cláusula WHERE que utilice una clave o una cláusula LIMIT, o ambas. Las sentencias DELETE deben tener ambas). Esto permite detectar sentencias UPDATE o DELETE en las que las claves no se utilizan correctamente y que probablemente cambiarían o eliminarían una gran cantidad de filas. El valor predeterminado es 0.

Cuando sql_safe_updates se establece en 1.

  • La declaración de actualización debe cumplir una de las siguientes condiciones para ejecutarse correctamente:
    • La declaración de actualización usa dónde y debe haber una columna de índice en la condición dónde;
    • La declaración de actualización usa límite;
    • La declaración de actualización usa dónde y límite al mismo tiempo. En este momento, la condición dónde no necesita tener una columna 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
  • La declaración de eliminación debe cumplir las siguientes condiciones para que se ejecute correctamente:
    • La declaración de eliminación también utiliza columnas de índice en la condición donde.
    • La declaración de eliminación utiliza tanto la columna de índice como el límite en la condición donde.
    • La declaración de eliminación usa dónde y límite al mismo tiempo. En este momento, la condición dónde no necesita tener una columna 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

Si la condición donde trae una columna de índice, pero el optimizador finalmente escanea y selecciona la tabla completa en lugar del índice, podemos usar force index([index_name]) para indicarle al optimizador qué índice usar para evitar la posibilidad de bloquear todo el índice. mesa. Peligros ocultos causados ​​por la mesa.

Perfil personal

Se ha dedicado a la investigación de tecnología durante más de 30 años y domina varios lenguajes como java, linux, javascript, php, css, etc. Ha realizado muchas contribuciones en el campo del código abierto. Estación de documentación para desarrolladores para compartir algunos problemas en el desarrollo de tecnología para referencia futura.

Mi información de contacto

Correo