V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
GopherDaily
V2EX  ›  MySQL

MySQL's Safe Update

  •  
  •   GopherDaily · 2022-12-15 18:05:55 +08:00 · 1086 次点击
    这是一个创建于 720 天前的主题,其中的信息可能已经有所发展或是发生改变。

    [mysql] MySQL's safe update

    Link: https://github.com/j2gg0s/j2gg0s/blob/main/20221215_mysql_safe.md

    在使用和维护 MySQL 的过程中, 难免会遇到某些折翼的天使在 UPDATE/DELETE 时没有带 WHERE, 导致大量数据被非预期的更新或删除. 幸运的时, 我们可以通过 MySQL 的配置项 sql_safe_updates 来简单的规避这个问题.

    在开启了 sql_safe_updates 后,

    • UPDATE 和 DELETE 语句必须在 WHERE 条件中指定 key_column, 或者提供 LIMIT.
    • 如果 SELECT 没有指定 LIMIT, 则最多返回 sql_select_limit 行数据.
    • 涉及多张表的 SELECT, 如果扫描的行数超过 max_join_size 则会被拒绝执行并返回错误.

    需要注意, UPDATE/DELETE 即使有 WHERE, 但如果 WHERE 中的列不是 key_column 的话, 依然会报错. 按我的理解, key_column 是指索引字段, 但并不限制必须是 unique.

    具体可以参考 Using Safe-Updates Modeexamples/mysql-safe 中的例子:

    sql_safe_updates -> 1, sql_select_limit -> 2
    
    CREATE TABLE `user` (
      `id` bigint(20) NOT NULL,
      `name` varchar(125) NOT NULL,
      `age` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`),
      KEY `name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    
    UPDATE/DELETE without key_column in WHERE
    UPDATE user SET age = 32; -> Error 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
    UPDATE user SET age = 32 WHERE age = 27; -> Error 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
    DELETE FROM user; -> Error 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
    
    UPDATE/DELETE with key_column is ok
    UPDATE user SET age = 32 WHERE id < 10; -> ok
    UPDATE user SET age = 32 WHERE name = 'foo'; -> ok
    
    SELECT without LIMIT can return up to sql_select_limit(2) rows
    SELECT * FROM user; -> 2 rows
    SELECT * FROM user LIMIT 1000; -> 3 rows
    

    MySQL 的绝大多数配置项都允许在两个层面配置:

    • SESSION, 针对当前链接, 可以覆盖全局配置.
    • GLOBAL, 也就是在 server 设置, 并做为链接的默认配置.

    sql_safe_updates 默认不开启, 需要主动设置. sql_select_limti 的默认值也非常的大(18446744073709551615), 等同不限制. 最好的选择自然是在 server 直接设置 safe update, 但如果有时候需要对已运行的项目增加相关功能时, 让客户端灰度增加相关配置会更安全.

    go-sql-driver/mysql 允许直接在 DSN 中设置 MySQL 的配置项, 如果需要开启 safe update, 仅需要增加如下两个参数, root:root@tcp(127.0.0.1:3306)/j2gg0s?sql_safe_updates=1&sql_select_limit=1024.

    目前尚无回复
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   4814 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 04:01 · PVG 12:01 · LAX 20:01 · JFK 23:01
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.