mysql中safe-updates模式可以限制不加条件对表的更新或删除,这样对数据安全有一定的好处,
可以有效的防止误操作,但更新删除也有一定的限制。
测试结果如下:
[mysql@bjdev01 ~]$ mysql -uroot -p -U
Enter password:
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
mysql> select * from test_atuo;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | c |
+----+------+
2 rows in set (0.00 sec)
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test_atuo;
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | c |
+----+------+
2 rows in set (0.00 sec)
d)safe-updates模式下,给where条件的列加个索引后,也可以更新。
mysql> ALTER TABLE test_atuo ADD INDEX idx_name (name) USING BTREE ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> update test_atuo set name='abc' where name='c' ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test_atuo;
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | abc |
+----+------+
mysql> show index from test_atuo;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_atuo | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| test_atuo | 1 | idx_name | 1 | name | A | 2 | NULL | NULL | YES | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
f)safe-updates模式下,用主键列做条件,也可以更新。
mysql> ALTER TABLE test_atuo DROP INDEX idx_name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> update test_atuo set name='aaaa' where id=2 ;
如果允许在safe-updates模式下,通过status可以看到下述信息
mysql> status
--------------
mysql Ver 14.14 Distrib 5.6.12, for Linux (x86_64) using EditLine wrapper
Connection id: 13027
Current database: ivr
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.12-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 20 days 23 hours 5 min 10 sec
Threads: 5 Questions: 2247900 Slow queries: 0 Opens: 15625 Flush tables: 1 Open tables: 511 Queries per second avg: 1.241
Note that you are running in safe_update_mode:
UPDATEs and DELETEs that don't use a key in the WHERE clause are not allowed.
(One can force an UPDATE/DELETE by adding LIMIT # at the end of the command.)
SELECT has an automatic 'LIMIT 1000' if LIMIT is not used.
Max number of examined row combination in a join is set to: 1000000
可以有效的防止误操作,但更新删除也有一定的限制。
测试结果如下:
[mysql@bjdev01 ~]$ mysql -uroot -p -U
Enter password:
a)safe-updates模式下,不加条件对表update,报错ERROR 1175 (HY000)
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
mysql> select * from test_atuo;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | c |
+----+------+
2 rows in set (0.00 sec)
b)safe-updates模式下,即使加了条件,没有用limit限制,也报错ERROR 1175 (HY000)
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
c)safe-updates模式下,同样的条件,加了limit限制,可以更新
mysql> update test_atuo set name='abc' where name='aaa' limit 1;Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test_atuo;
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | c |
+----+------+
2 rows in set (0.00 sec)
d)safe-updates模式下,给where条件的列加个索引后,也可以更新。
mysql> ALTER TABLE test_atuo ADD INDEX idx_name (name) USING BTREE ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> update test_atuo set name='abc' where name='c' ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test_atuo;
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | abc |
+----+------+
mysql> show index from test_atuo;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_atuo | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| test_atuo | 1 | idx_name | 1 | name | A | 2 | NULL | NULL | YES | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
f)safe-updates模式下,用主键列做条件,也可以更新。
mysql> ALTER TABLE test_atuo DROP INDEX idx_name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> update test_atuo set name='aaaa' where id=2 ;
如果允许在safe-updates模式下,通过status可以看到下述信息
mysql> status
--------------
mysql Ver 14.14 Distrib 5.6.12, for Linux (x86_64) using EditLine wrapper
Connection id: 13027
Current database: ivr
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.12-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 20 days 23 hours 5 min 10 sec
Threads: 5 Questions: 2247900 Slow queries: 0 Opens: 15625 Flush tables: 1 Open tables: 511 Queries per second avg: 1.241
Note that you are running in safe_update_mode:
UPDATEs and DELETEs that don't use a key in the WHERE clause are not allowed.
(One can force an UPDATE/DELETE by adding LIMIT # at the end of the command.)
SELECT has an automatic 'LIMIT 1000' if LIMIT is not used.
Max number of examined row combination in a join is set to: 1000000