if i issue some delete/update command wrongly
There is no such thing as a "wrong" delete or update command unless it is "wrong" in the sense of being syntactically invalid, referencing nonexistent objects, or attempting something for which the user does not have permission... and those aren't going to execute anyway.
So you must be talking about something like this:
DELETE FROM t1; -- not wrong, deletes all of the rows by excluding none
UPDATE users SET email = 'test@localhost'; -- not wrong, updates all rows with same value
There is sure-fire no way for the system to correctly guess "hey, that seems wrong" and ask you to rethink your decision. The DBMS's job is to do what you ask in the most optimal way, not second-guess whether you know what you're doing.
MySQL does have a mode that tries to protect you, by stopping you from executing updates or deletes that aren't using an index to find rows, with the --i-am-a-dummy
flag that enables sql_safe_updates
on a per-connection basis -- or you can enable that setting at a global level on the server.
$ mysql --i-am-a-dummy
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4642931
Server version: 5.5.30 MySQL Community Server (GPL)
mysql> use sakila;
Database changed
mysql> delete from film;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table
without a WHERE that uses a KEY column
I would argue that this fixes the wrong problem, which is that you have privileges granted to people that they are not qualified to hold.
You could, of course, disable autocommit, requiring you to type COMMIT;
to actually commit your DML changes to the database, but that's only true and fully helpful if you're exclusively using a transactional storage engine like InnoDB, and not MyISAM, so I don't know how much that solves.
Those blessed with extra lazyiness will figure out that they can type ; COMMIT;
at the end of every statement (true in the mysql
cli, not universally).
The person who is sufficiently conscientious to think before committing is probably sufficiently conscientious to not type queries that don't make sense.
ROLLBACK
. Anyway, sounds like a silly requirement. If you're unsure whether you want to run the update/delete, don't press enter in the first place!