Mysql 事务句柄 [英] Mysql transaction handle
问题描述
我有一个用户注册程序，步骤如下:
I have an user registration procdedure, with this steps:
如果用户发送注册表，则:
If user send the registraion form then:
1. step: run a QUERY
2. step: run another QUERY
3. make a directory1 on the server
4. make a sub directory2 into the directory1
5. make another sub directory3 into the directory1
6. upload an image into the sub directory2
所以这很好用.但是我担心如果在这个程序运行时发生了什么事情，所以如果程序在第 4 步中断，那么我的表中有一些不必要的行，因为第 1 步.2.
So this is work well. But im afraid if something happen while this procedure is runnin, so if the procedure interrupted on 4. step, then i have some unnecessary row in my table bacause of step 1.; 2.
所以我认为我必须使用 mysql 事务句柄，但我不知道如何使用.我觉得有些像这样:
So i think i have to use mysql transaction handle, but i don't know how. I think somethink like this:
START TRANSACTION (what is the exact format?)
1. step: run a QUERY
2. step: run another QUERY
3. make a directory1 on the server
4. make a sub directory2 into the directory1
5. make another sub directory3 into the directory1
6. upload an image into the sub directory2
COMMIT (if all step ok. but how can i check this?)
ROLLBACK (if something wrong; but how can i check this?)
I this transaction handle not handle the FILE transactions, so i ROLLBACK function is called, then i have to delete manually the directorys if created already?!
Sorry, two more question:
如果程序中断，例如在第2步，并且ROLLBACK被调用，那么sciprt会停止还是从末尾开始?或者我需要写一个这样的回调函数:(这是我必须做的吗?)
If the procedure interrupted, for example in step 2, and ROLLBACK is called, then the sciprt will stop or start from the end? Or i need to write a callback funciton something like this: (is this how i have to do?)
function begin() { @mysql_query("BEGIN");} function commit(){ @mysql_query("COMMIT");} function rollback() { @mysql_query("ROLLBACK"); echo "oops"; exit(); } $inster_query="insert into.... begin(); $result_insert = @mysql_query($inster_query); if(!$result_insert) { rollback(); } $update_query="update.... $result_up = @mysql_query($update_query); if(!$result_up) { rollback(); } . . commit();我如何测试它是否有效?谢谢.
how can i test this if its work or not? Thank you.
推荐答案
您想做的步骤是:
1. step: run a QUERY 2. step: run another QUERY 3. make a directory1 on the server 4. make a sub directory2 into the directory1 5. make another sub directory3 into the directory1 6. upload an image into the sub directory2这里首先我想讨论一下查询...意味着如何发送多个查询，并在一切正常后保存它们...
假设你的数据库名是'database'，表名是'table_1','table_2','table_3'...并且您想执行以下操作:Here first I want to discuss about the queries... means how you can send multiple queries and after everything goes right save them...
suppose your database name is'database', table names are'table_1','table_2','table_3'... and you want to do something like below:step 1: insert a row in table_1 step 2: update a column in table_2 where id is 1 step 3: insert another row in table_3但是您需要完成所有这三个步骤或一个都不完成...在这种情况下，您可以使用此 php 代码...
but you need to complete all these three steps or none of them... In this case your can use this php code...
// first connect and select to your mysql database... // before sending any queries (INSERT, UPDATE etc.) we have send this command else it will not work... mysql_query("START TRANSACTION;"); /* this command will say to the mysql that it only have to calculate the queries that we are sending is right or have problems... if everything is right, it will remember the query and after sending the confirmation it will trigger the queries... besides if the query has any problem then it will return FALSE only... moreover for right query mysql return TRUE...*/ // now I will send the queries and save the return values to different variables... $query1 = "INSERT INTO `database`.`table_1` (col1, col2) VALUES ('value1', 'value2');"; $query1 = mysql_query($query1); $query2 = "UPDATE `database`.`table_2` SET col1='value1', col1='value1' WHERE id='1';"; $query2 = mysql_query($query2); $query3 = "INSERT INTO `database`.`table_3` (col1, col2) VALUES ('value1', 'value2');"; $query3 = mysql_query($query3); // now if anything is wrong, I will get FALSE as return value for each wrong query... // if all of them are TRUE, we will send the confirmation for save them... // else we will say to forget whatever we send before... if($query1 && $query2 && $query3){ // send confirmation mysql_query('COMMIT;'); /* NOTE: after you send COMMIT you can't ROLLBACK any query or data...*/ } else { // order to forget whatever we send before mysql_query('ROLLBACK'); /* NOTE: after you send ROLLBACK you will lose all the queries that you send after you send START TRANSACTION to mysql...*/ }注意:永远记住你不能
ROLLBACK所有mysql_query...这里是你可以的列表t回滚...NOTE: always keep in your mind that you can't
ROLLBACKallmysql_query... here is the list that you can'tROLLBACK...CREATE DATABASE ALTER DATABASE DROP DATABASE CREATE TABLE ALTER TABLE DROP TABLE RENAME TABLE TRUNCATE TABLE CREATE INDEX DROP INDEX CREATE EVENT DROP EVENT CREATE FUNCTION DROP FUNCTION CREATE PROCEDURE DROP PROCEDURE我想你得到了你想要做的答案......但是这里是你项目的php代码......
I think you get your answer that you want to do... However here is the php code for your project...
// connect and select your database... mysql_query("START TRANSACTION;"); /* step 1 */ $query1 = "your query"; $query1 = mysql_query($query1); /* step 2 */ /* you can also use multiple line to build your query */ $query2 = "your query part 1"; $query2 .= "your query part 2"; $query2 .= "your query part 3"; $query2 = mysql_query($query2); /* step 3 */ mkdir(' the path you want to create ', 0700); /* here '0700' is the mode (permission or chmod)... if you don't know about it or want to know about it check the link... www.php.net/manual/en/function.chmod.php */ /* step 4 */ mkdir('<the path you write in step 3>/<the folder name that you want to create in step 4>', 0700); /* step 5 */ mkdir('<the path you write in step 3>/<the folder name that you want to create in step 5>', 0700); /* step 6 */ /* I am really sorry to say that I have on knowledge about the code that need to write for uploading a image or any file... so I am unable to help you for this step */ /* now we need to check is everything is right or not... we will create a variable '$rollback' and set its value as FALSE... if we get any problem we will set it to TRUE*/ $rollback = FALSE; // first we will check the directory !important /* use array and foreach to do that... it will make it easy and if you got more directory later, you just have to add it to the array... nothing more */ $dir = array(); $dir[] = 'your directory 1'; $dir[] = 'your directory 2'; $dir[] = 'your directory 3'; foreach ($dir as & $single_dir) { if(file_exists($single_dir) == FALSE){ $rollback = TRUE; } } // now if the $rollback is false, we unable to create any directory... if($rollback==FALSE){ foreach ($dir as & $single_dir){ if(file_exists($single_dir)){ rmdir($single_dir) /* friend though I use rmdir to remove the directory but it has many limitation... watch this link... www.php.net/manual/en/function.rmdir.php */ } } } // lets check the image that we upload if(file_exists('image directory') == FALSE){ $rollback = TRUE; } else { if($rollback == TRUE){ unlink('image directory'); /* don't use rmdir for deleting file */ } } // now time to check the queries... if($query1 && $query2 $$ $rollback){ mysql_query('COMMIT;'); } else { mysql_query('ROLLBACK;'); }朋友们，希望我的回答对你和有同样问题的人有帮助...如果你发现我的回答有任何错误，请原谅我，如果你能请更正对我来说...此外，如果您认为我的回答对其他人有帮助，请不要忘记为这个答案投票...谢谢...朋友...
Friends, I hope my answer will be very helpful for you and for them who have the same problem... If you find any mistake in my answer, please be kind enough to forgive me for that and if you can please correct it for me... Moreover if you think my answer is helpful for others please don't forget to vote this answer up... thanks you... friends...
这篇关于Mysql 事务句柄的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
