mysql非主键删除记录

这几天在使用mysql过程中发现了这样一个有趣问题：如果在执行delete操作时，where子句不是直接指定条件，mysql就会报错，内容如下：

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

查阅官方的手册后，官方时如此介绍的：

For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which has the same effect). It is helpful for cases when you might have issued a DELETE FROM *tbl_name* statement but forgotten the WHERE clause. Normally, such a statement deletes all rows from the table. With --safe-updates, you can delete rows only by specifying the key values that identify them. This helps prevent accidents.

原来mysql为了避免出现初学者在执行delete操作时未制定where条件导致整个表被删除，因此在系统变量中开启了SAVE-UPDATES模式，这样就能有效减少错误的产生了。

SAVE-UPDATES下有3个作用：这里就直接po出文档了

• You are not permitted to execute an UPDATE or DELETE statement unless you specify a key constraint in the WHERE clause or provide a LIMIT clause (or both)
• The server limits all large SELECT results to 1,000 rows unless the statement includes a LIMIT clause
• The server aborts multiple-table SELECT statements that probably need to examine more than 1,000,000 row combinations

综上，可以使用如下办法解决

1

SET SQL_SAVE_UPDATES = 0;

并不建议全局设置该变量，小心驶得万年船，尤其对经验不足童鞋来说~

更多内容请看这里，传送门