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)