mysql中safe-updates的限制

mysql中safe-updates模式可以限制不加条件对表的更新或删除，这样对数据安全有一定的好处，
可以有效的防止误操作，但更新删除也有一定的限制。


测试结果如下：


[mysql@bjdev01 ~]$ mysql -uroot -p -U
Enter password: 

a)safe-updates模式下，不加条件对表update，报错ERROR 1175 (HY000)

mysql> update  test_atuo set  name='aaa';
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)

mysql> update  test_atuo set  name='abc' where name='aaa';
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