Geek Answers Handbook

MySQL command line and transactions

I have a question about MySQL and could not find the answer. I know that auto-commit is enabled by default in MySQL. I need to run several update requests from the command line in one transaction, but I do not know how MySQL handles them. If I have something like this:

mysql -uroot -proot -e 'QUERY 1; QUERY 2; QUERY3'

will it be executed as a single transaction or will MySQL automatically commit each expression separately? I need to ensure atomicity.

+4
source share
2 answers

The MySQL START TRANSACTION syntax can be used to create a transaction commit:

Source: http://dev.mysql.com/doc/refman/5.0/en/commit.html

 START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT;

You can also write your query in a .sql file and pass it to mysql:

 $ cat query.sql | mysql -uroot -proot
+7
source

The pipe is a great idea!

 echo "START TRANSACTION;" > start.sql echo "COMMIT;" > commit.sql cat start.sql yourScript.sql commit.sql | mysql -uroot -proot

or

 cat start.sql yourScript.sql - | mysql -uroot -proot

and so you can do o rollback manually according to yourScript result

Good luck

+2
source

More articles:


All Articles