Safe MySQL updates

If you use the MySQL command line client a lot, you could consider adding the following to your .bashrc:

alias mysql='mysql --safe-updates'

Log out, then in again, or source your .bashrc file. This will quietly add in the --safe-updates parameter to any call you make for the mysql binary, which will prevent you from performing UPDATE or DELETE operations on any table if you neither specify a LIMIT condition nor a WHERE condition based on an indexed field. This stops you from accidentally deleting or updating every row in a table with queries like DELETE FROM `table` or UPDATE `table` SET `field` = 'value'.

If you really do want to delete a table’s entire contents, TRUNCATE `table` is more efficient anyway, and something you’re considerably less likely to fat-finger.

You can check this is loaded by typing alias with no arguments:

$ alias
alias mysql='mysql --safe-updates'

This very seldom gets in the way of actual operations you intend to run, and at the same time it prevents you from running potentially disastrous queries over your whole table. The MySQL manual lists it as for beginners, but regardless of your experience level in production environments this kind of safety valve can be a lifesaver, particularly if you’re not at your most lucid.

Like any Bash alias, if you want to call mysql directly without going through the alias, perhaps because you really do want to update a field in every single row of a table, then just prepend a backslash:

$ \mysql

Funnily enough, a valid alias of this option is --i-am-a-dummy. Pick whichever you think is more appropriate.

One thought on “Safe MySQL updates

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use Markdown if you want.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>