Joyce' Blog
0%

数据库大作业笔记（七）

发表于 更新于

####事务

作业要求写含事务应用的删除操作，这描述，总觉得不是很清晰，是在PHP里写事务还是在MySQL里写呢？最开始考虑到MySQL没法存事务，所以我是改写了delete()函数，代码如下：

Path:common\models\Borrow.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
public function delete()
   {
       $db = Yii::$app->db;
       $transaction = $db->beginTransaction();
       try {
           $result = $this->deleteInternal();
           if ($result === false) {
               $db->createCommand('INSERT INTO audit_borrow VALUES(user(),now(),0)')->execute();
               $transaction->rollBack();
           } else {
               $db->createCommand('INSERT INTO audit_borrow VALUES(user(),now(),1)')->execute();
               $transaction->commit();
           }
           } catch(\Exception $e) {
               $transaction->rollBack();
               throw $e;
           } catch(\Throwable $e) {
               $transaction->rollBack();
               throw $e;
           }
   }

后来想到可以在存储过程里写事务，这样的话应该会更符合要求吧。在这之前还是要先学一下事务的写法：

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_delete`(IN `newborwid` CHAR(2), IN `username` CHAR(20))
    DETERMINISTIC
BEGIN
DECLARE t_error INTEGER;
DECLARE    CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
START TRANSACTION;
-- 由于有外键约束，要注意下面两句的先后顺序！
INSERT INTO audit_borrow VALUES(username,now(),newborwid);
DELETE FROM borrow where borwid=newborwid;
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
select newborwid;
END$$
DELIMITER ;

我们建立的数据库一般都是 InnoDB 数据库，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL 会回滚事务，有：

①如果 BEFORE 触发器执行失败，SQL 无法正确执行②SQL 执行失败时，AFTER 型触发器不会触发③AFTER 类型的触发器执行失败，SQL 会回滚。

declare handler声明异常处理

1
2
3
4
5
6
7
8
9
10
11
DECLARE
-- 处理类型 继续或退出
{EXIT | CONTINUE} 
HANDLER FOR
-- 触发条件
{error-number | SQLSTATE error-string | condition}
-- 错误触发的操作
SQL statement

-- example:
declare continue handler for SQLEXCEPTION set L_error=1;

发生错误的条件有：

1、MYSQL错误代码

2、ANSI-standard SQLSTATE code

3、命名条件。可使用系统内置的SQLEXCEPTION,SQLWARNING和NOT FOUND

参考文章

嗯，好了，外键约束真是令人头大。最开始因为在audit_borrow和audit_equipment上加了外键约束，导致borrow和equipment, category的删除插入总是出问题。现在，重新写事务：（因为要涉及多张表，上面那个不符合要求）

Path:backend\controllers\CategoryController.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public function actionDelete($id)
{

    $username=Yii::$app->user->identity->username;
    $model = $this->findModel($id);
    if (!$model->load(Yii::$app->request->post())) {

        $command= Yii::$app->db
            ->createCommand('call proc_delete(:p0,:p1)')
            ->bindValues([":p0" =>$model->catid,":p1"=>$username]);
        $res=$command->execute();
        if($res>=0)
        {             
            return $this->redirect(['index']);
        }
    }
    return $this->redirect(['index']);
}

数据库存储过程：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_delete`(IN `newcatid` CHAR(2), IN `username` CHAR(20))
    DETERMINISTIC
BEGIN
DECLARE t_error INTEGER;
DECLARE    CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
START TRANSACTION;
INSERT INTO audit_category VALUES(username,now(),newcatid);
DELETE FROM borrow where eqpid IN(
SELECT eqpid from equipment where equipment.catid=newcatid
);
DELETE FROM equipment where catid=newcatid;
DELETE FROM category where catid=newcatid;
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
select newcatid;
END$$
DELIMITER ;
注意写分号！！！

字符串拼接

1
CONCAT(new.catid,"000")

注：如果if条件为NULL的话，会走else if语句