MySQL事務操作 事務rollback mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into tbtest(stu_ID,stu_Name) values(201,'stu_1'); Qu... ...
MySQL事務操作
- 事務rollback
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tbtest(stu_ID,stu_Name) values(201,'stu_1');
Query OK, 1 row affected (0.04 sec)
mysql> select * from tbtest;
+--------+-----------+----------+
| stu_ID | stu_Name | stu_test |
+--------+-----------+----------+
| 123 | xiaoMing | NULL |
| 124 | xiaoZhang | NULL |
| 125 | xiaoLi | NULL |
| 201 | stu_1 | NULL |
+--------+-----------+----------+
4 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from tbtest;
+--------+-----------+----------+
| stu_ID | stu_Name | stu_test |
+--------+-----------+----------+
| 123 | xiaoMing | NULL |
| 124 | xiaoZhang | NULL |
| 125 | xiaoLi | NULL |
+--------+-----------+----------+
3 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
- 事務commit
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tbtest(stu_ID,stu_Name) values(201,'stu_1');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tbtest;
+--------+-----------+----------+
| stu_ID | stu_Name | stu_test |
+--------+-----------+----------+
| 123 | xiaoMing | NULL |
| 124 | xiaoZhang | NULL |
| 125 | xiaoLi | NULL |
| 201 | stu_1 | NULL |
+--------+-----------+----------+
4 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from tbtest;
+--------+-----------+----------+
| stu_ID | stu_Name | stu_test |
+--------+-----------+----------+
| 123 | xiaoMing | NULL |
| 124 | xiaoZhang | NULL |
| 125 | xiaoLi | NULL |
| 201 | stu_1 | NULL |
+--------+-----------+----------+
4 rows in set (0.00 sec)
- 計算上一條語句影響了多少行的兩個相關函數
MySQL 有兩個函數來計算上一條語句影響了多少行
- found_rows()
判斷Select得到的行數用 found_rows();
mysql> select * from tbtest;
+--------+-----------+----------+
| stu_ID | stu_Name | stu_test |
+--------+-----------+----------+
| 123 | xiaoMing | NULL |
| 124 | xiaoZhang | NULL |
| 125 | xiaoLi | NULL |
| 201 | stu_1 | NULL |
| 202 | stu_2 | NULL |
| 203 | stu_3 | NULL |
| 204 | stu_4 | NULL |
| 205 | stu_4 | NULL |
| 206 | stu_4 | NULL |
+--------+-----------+----------+
9 rows in set (0.00 sec)
mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
| 9 |
+--------------+
1 row in set (0.00 sec)
- row_count()
判斷Update Delete影響的行數用row_count(); 註：MySQL中Update前後的值如果一樣，row_count()為0，不像在SqlServer中的@@rowcount/Oracle中的rowcount,只要update到行，影響的行數就會大於0(無論前後欄位的值是否發生了變化)
mysql> insert into tbtest(stu_id, stu_name) values(207, 'stu_46');
Query OK, 1 row affected (0.02 sec)
mysql> select row_count();
+-------------+
| row_count() |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
