你的位置:首页 > 数据库

[数据库]MySQL transaction

MySQL事务操作

 

  1. 事务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)

     

  2. 事务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)

     

  3. 计算上一条语句影响了多少行的两个相关函数

 

MySQL 有两个函数来计算上一条语句影响了多少行

  1. 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)

 

  1. 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)