Ìwé Directory
MySQL databaseIyasọtọ rollback iṣowo / ero-iṣiro / alaye ṣiṣatunṣe ipele ipele
MySQL Awọn ọrọ
Awọn iṣowo MySQL ni a lo ni akọkọ lati ṣe ilana data pẹlu iye nla ti awọn iṣẹ ṣiṣe ati idiju giga.Fun apẹẹrẹ, ninu eto iṣakoso eniyan, ti o ba pa eniyan rẹ, o nilo lati paarẹ mejeeji alaye ipilẹ ti eniyan ati alaye ti o jọmọ eniyan, gẹgẹbi awọn apoti ifiweranṣẹ, awọn nkan, ati bẹbẹ lọ Ni ọna yii, awọn alaye iṣiṣẹ data wọnyi. jẹ idunadura kan!
- Awọn iṣowo ni atilẹyin ni MySQL nikan fun awọn apoti isura data tabi awọn tabili ti o lo ẹrọ data Innodb.
- Ṣiṣeduro iṣowo le ṣee lo lati ṣetọju iduroṣinṣin ti data data, ni idaniloju pe awọn ipele ti awọn alaye SQL jẹ boya gbogbo wọn ti ṣiṣẹ tabi ko si ọkan ninu wọn ti a ṣe.
- Awọn iṣowo ni a lo lati ṣakoso fifi sii, imudojuiwọn, paarẹ awọn alaye
Ni gbogbogbo, idunadura kan gbọdọ pade awọn ipo 4 (ACID): Atomity (atomicity), Iduroṣinṣin (iduroṣinṣin), Iyasọtọ (ipinya), Agbara (igbẹkẹle)
- 1 、Atomity ti awọn iṣowo:Eto awọn iṣowo ti boya ṣaṣeyọri tabi yọkuro.
- 2 、iduroṣinṣin:Awọn data arufin wa (awọn idiwọ bọtini ajeji ati bii), ati pe a yọkuro idunadura naa.
- 3 、Ìyàraẹniṣọ́tọ̀:Awọn iṣowo nṣiṣẹ ni ominira.Ti abajade iṣowo kan ba ni ipa lori awọn iṣowo miiran, lẹhinna awọn iṣowo miiran yoo yọkuro.100% ipinya ti awọn iṣowo ni laibikita iyara.
- 4 、igbẹkẹle:Lẹhin sọfitiwia ati jamba ohun elo, awakọ tabili data InnoDB yoo lo faili log lati tun ṣe ati yipada.Igbẹkẹle ati iyara giga ko le ni awọn mejeeji, aṣayan innodb_flush_log_at_trx_commit pinnu nigbati lati fipamọ awọn iṣowo si akọọlẹ naa.
Labẹ awọn eto aiyipada ti laini aṣẹ MySQL, awọn iṣowo jẹ ifaramo laifọwọyi, iyẹn ni, iṣẹ COMMIT yoo ṣiṣẹ lẹsẹkẹsẹ lẹhin ti alaye SQL ti ṣiṣẹ.Nitoribẹẹ, lati ṣii idunadura kan ni gbangba gbọdọ lo aṣẹ naa BERE tabi BẸRẸ IWỌWỌRỌ, tabi ṣiṣẹ pipaṣẹ SET AUTOCOMMIT=0, lati mu lilo adaṣe adaṣe fun igba lọwọlọwọ ṣiṣẹ.
Gbólóhùn iṣakoso iṣowo:
- BẸRẸ tabi BẸẸRẸ IJỌWỌWỌRỌ; bẹrẹ idunadura kan ni gbangba;
- ṢE; ṢE IṢẸ tun le ṣee lo, ṣugbọn awọn meji jẹ deede. COMMIT ṣe idunadura naa ati mu ki gbogbo awọn iyipada ti a ṣe si ibi ipamọ data yẹ;
- ROLLBACK; o ṣee ṣe lati lo ROLLBACK WORK, ṣugbọn awọn meji jẹ deede.Yipopada pari iṣowo olumulo ati ki o ṣe atunṣe gbogbo awọn iyipada ti ko ni ifaramọ ni ilọsiwaju;
- Idanimọ SAVEPOINT; SAVEPOINT ngbanilaaye lati ṣẹda aaye ipamọ kan ninu idunadura kan, ati pe ọpọlọpọ awọn SAVEPOINT le wa ninu idunadura kan;
- TUTU SAVEPOINT idamo; pa aaye ipamọ ti idunadura kan, nigbati ko ba si aaye ipamọ kan pato, ṣiṣe alaye naa yoo jabọ imukuro;
- ROLLBACK TO idamo; yi idunadura naa pada si aaye;
- SET TRANSACTION; lo lati ṣeto ipele ipinya ti idunadura naa. Ẹrọ ibi-itọju InnoDB n pese awọn ipele ipinya idunadura ti KA UNCOMMITTED, KA COMMITTED, REPEATABLE KA, ati SERIALIZABLE.
Awọn ọna akọkọ meji lo wa ti sisẹ idunadura MYSQL:
1. Lo Ibẹrẹ, ROLLBACK, COMMIT lati ṣaṣeyọri
- BERÈ bẹrẹ idunadura
- Yipada idunadura rollback
- ṢE Ijẹrisi iṣowo
2. Lo SET taara lati yi ipo ifaramọ aifọwọyi MySQL pada:
- ṢETO AUTOCOMMIT=0 Pa aifọwọyi ṣiṣẹ
- ṢETO AUTOCOMMIT=1 Tan ifaramọ aifọwọyi
idunadura igbeyewo
mysql> use chenweiliang; Database changed mysql> CREATE TABLE chenweiliang_transaction_test( id int(5)) engine=innodb; # 创建数据表 Query OK, 0 rows affected (0.04 sec) mysql> select * from chenweiliang_transaction_test; Empty set (0.01 sec) mysql> begin; # 开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into chenweiliang_transaction_test value(5); Query OK, 1 rows affected (0.01 sec) mysql> insert into chenweiliang_transaction_test value(6); Query OK, 1 rows affected (0.00 sec) mysql> commit; # 提交事务 Query OK, 0 rows affected (0.01 sec) mysql> select * from chenweiliang_transaction_test; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql> begin; # 开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into chenweiliang_transaction_test values(7); Query OK, 1 rows affected (0.00 sec) mysql> rollback; # 回滚 Query OK, 0 rows affected (0.00 sec) mysql> select * from chenweiliang_transaction_test; # 因为回滚所以数据没有插入 +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql>
Lilo Awọn ipo Iṣowo ni PHP
Ibere MySQL NIPA idanwo:
<? php $dbhost = 'localhost:3306'; // mysql服务器主机地址 $dbuser = 'root'; // mysql用户名 $dbpass = '123456'; // mysql用户名密码 $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('连接失败: ' . mysqli_error($conn)); } // 设置编码,防止中文乱码 mysqli_query($conn, "set names utf8"); mysqli_select_db( $conn, 'chenweiliang' ); mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交,因为MYSQL默认立即执行 mysqli_begin_transaction($conn); // 开始事务定义 if(!mysqli_query($conn, "insert into chenweiliang_transaction_test (id) values(8)")) { mysqli_query($conn, "ROLLBACK"); // 判断当执行失败时回滚 } if(!mysqli_query($conn, "insert into chenweiliang_transaction_test (id) values(9)")) { mysqli_query($conn, "ROLLBACK"); // 判断执行失败时回滚 } mysqli_commit($conn); //执行事务 mysqli_close($conn); ?>
Ireti Chen Weiliang Blog ( https://www.chenweiliang.com/ ) pín “MySQL Database Transaction Rollback Isolation/Ṣiṣii Agbekale/Ṣiṣe Ipele ti Awọn Gbólóhùn PHP”, eyiti o ṣe iranlọwọ fun ọ.
Kaabo lati pin ọna asopọ ti nkan yii:https://www.chenweiliang.com/cwl-494.html
Kaabọ si ikanni Telegram ti bulọọgi Chen Weiliang lati gba awọn imudojuiwọn tuntun!
📚 Itọsọna yii ni iye nla, 🌟Eyi jẹ aye to ṣọwọn, maṣe padanu rẹ! ⏰⌛💨
Pin ati fẹran ti o ba fẹ!
Pinpin rẹ ati awọn ayanfẹ jẹ iwuri wa lemọlemọfún!