昨天在Mysql上干的一件蠢事

  昨天需要用到表的级联操作,标签的处理之类的工作,于是乎大胆的开始写起了存储过程,花了很久,调试了N次,终于把一些问题搞定了,拿个简单的例子吧,如果需要删除某个标签,删除关系表的同时还得更新标签表,所以就遇到了一些问题。

DELIMITER $$

CREATE PROCEDURE `picture_del_tag`(IN tag_name VARCHAR(100), IN picture_id INT,OUT out_status INT)
TOP:BEGIN
    DECLARE t_count INT;

    SELECT COUNT(*) INTO t_count FROM dual WHERE EXISTS(SELECT 1 FROM `pictures` WHERE `id`=picture_id);
    IF t_count = 0 THEN
        -- 图片存在性判断
        SET out_status = -1;
        leave TOP;
    END IF;

    SELECT COUNT(*) INTO t_count FROM dual WHERE EXISTS(SELECT 1 FROM `pictures_has_tags` WHERE `pictures_id`=picture_id AND `tags_name`=tag_name);
    IF t_count = 0 THEN
        -- 该标签存在性判断
        SET out_status = -2;
        leave TOP;
    END IF;

    SELECT SUM(`count`) INTO t_count FROM `tags` WHERE `name`=tag_name LIMIT 0,1;
    IF t_count IS NULL THEN
        -- 不存在标签
        SET out_status = -3;
        leave TOP;
    END IF;
   
    START TRANSACTION;
    -- 开始删除数据
    DELETE FROM `pictures_has_tags` WHERE  `pictures_id`=picture_id AND `tags_name`=tag_name;

    SET t_count = t_count -1;
    IF t_count = 0 THEN
        DELETE FROM `tags` WHERE `name`=tag_name;
    ELSE
        UPDATE `tags` SET `count`=t_count WHERE `name`=tag_name;
    END IF;
    commit;
    SET out_status = 1;
END

  这是写的一个存储过程,但是感觉不是很难,可是呢,实际上用到了很多操作,效率是非常低的,今天一早起来想到了用触发器,问题瞬间解决了,效率啊。所以一天就这样浪费了。
  问题的关键就是数据库不熟,唉,触发器如下,上面的操作机会可以省略了,唉。

DELIMITER $$

USE `pitus`$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `pitus`.`pictures_has_tags_ADEL`
AFTER DELETE ON `pitus`.`pictures_has_tags`
FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
BEGIN
DECLARE t_count INT DEFAULT 0;
SELECT SUM(`count`) INTO t_count FROM `tags` WHERE `name`=OLD.`tags_name` LIMIT 0,1;
IF t_count = 1 THEN
    DELETE FROM `tags` WHERE `name`=OLD.`tags_name`;
ELSE
    UPDATE `tags` SET `name` = `name`-1 WHERE `name`=OLD.`tags_name`;
END IF;
END$$

8条评论在“昨天在Mysql上干的一件蠢事”

回复 阳光   取消