Mysql数据库实现一个字段关联多张表

Mysql数据库实现一个字段关联多张表

• Ps:由于我的数据库功底不是很扎实，所以如果有什么设计的不好的地方欢迎指出

我用设计数据库的时候为了看清楚就用draw.io画了个图:
关于draw.io的web端搭建，欢迎来看看我写的另一篇：Linux搭建draw.io的Web端

• 这是我数据库设计的第一阶段画的图，总共17张表，从中很容易看出问题：

问题所在：一个字段外键绑定多张表（comment和source）

• 一开始我很可笑的以为如果一个字段通过外键绑定多张表后，只有某一张表有值即为满足条件，后来我在写Java类的时候越想越不对，外键的设定好像不是这么玩的，于是的就去实验了一下~
• 果不其然，这种情况需要满足多张表同时拥有才可以~（哭）

面临的抉择：推到重做，或者另谋出路

• 我不知道别人是咋样的，反正个人是属于不到万不得已不会重来一遍的那种类型（懒）

自定义设计

• 首先对于comment和source的外键（后来好像还有一个praise也是）
  总之：不该有的外键统统干掉

但是干掉之后怎么保证数据库的完整性呢

• 我仔细考虑了外键的作用：在插入的时候一定要关联表中存在了外键插入的值才能成功插入
• 于是我就自然而然地想到了一个思路：模拟外键，但是要去掉所有表都满足这个条件。即标题指出的，一个字段关联多张表。

一个字段关联多张表的设计思路

为了在插入的时候验证数据在某一张表中存在与否，我使用了触发器,关于触发器的概念在此不多赘述（主要是怕说不清，如果不是很了解可以看一下这位博主的文章）

触发器的设计

-- praise		发生在之前，存在，则插入，不存在则抛出异常

DROP TRIGGER IF EXISTS `praise_beforeInsert`;
DELIMITER ;;
CREATE TRIGGER `praise_beforeInsert` BEFORE INSERT ON `praise` FOR EACH ROW BEGIN
IF new.type_id=1 THEN 
	IF (SELECT COUNT(*) FROM user_comment WHERE comment_id =new.foreign_id)=1 THEN 
		UPDATE user_comment SET like_num=like_num+1 WHERE comment_id =new.foreign_id;
	ELSE 
		signal SQLSTATE 'HY000' SET message_text = '手动抛出异常' ;
	END IF;
ELSEIF new.type_id=2 THEN
	IF (SELECT COUNT(*) FROM dish WHERE dish_id =new.foreign_id)=1 THEN 
		UPDATE dish SET like_num=like_num+1 WHERE dish_id =new.foreign_id;
	ELSE 
		signal SQLSTATE 'HY000' SET message_text = '手动抛出异常2' ;
	END IF;
END IF;
END
;;
DELIMITER ;
insert into praise(user_id,foreign_id,type_id) value(1,16,2);

-- user_comment			测试id是否存在，若不存在则报错

DROP TRIGGER IF EXISTS `comment_beforeInsert`;
DELIMITER ;;
CREATE TRIGGER `comment_beforeInsert` BEFORE INSERT ON `user_comment` FOR EACH ROW BEGIN
IF (SELECT COUNT(*) FROM user_comment WHERE new.parent_comment_id =comment_id)=0 THEN 
		signal SQLSTATE 'HY000' SET message_text = '上级评论不存在' ;
ELSE
	IF new.type_id=1 THEN 
		IF (SELECT COUNT(*) FROM `dish_custom` WHERE custom_id =new.foreign_id)=0 THEN 
			signal SQLSTATE 'HY000' SET message_text = '菜品习俗id不存在' ;
		END IF;
	ELSEIF new.type_id=2 THEN
		IF (SELECT COUNT(*) FROM `dish_history` WHERE history_id =new.foreign_id)=0 THEN 
			signal SQLSTATE 'HY000' SET message_text = '菜品历史id不存在' ;
		END IF;
	ELSEIF new.type_id=3 THEN
		IF (SELECT COUNT(*) FROM `dish_literary` WHERE literary_id =new.foreign_id)=0 THEN 
			signal SQLSTATE 'HY000' SET message_text = '菜品典故id不存在' ;
		END IF;
	ELSEIF new.type_id=4 THEN
		IF (SELECT COUNT(*) FROM `dish_recipes` WHERE recipes_id =new.foreign_id)=0 THEN 
			signal SQLSTATE 'HY000' SET message_text = '菜品做法id不存在' ;
		END IF;
	ELSEIF new.type_id=5 THEN
		IF (SELECT COUNT(*) FROM `restaurant` WHERE restaurant_id =new.foreign_id)=0 THEN 
			signal SQLSTATE 'HY000' SET message_text = '菜馆id不存在' ;
		END IF;
	END IF;
END IF;
END
;;
DELIMITER ;


INSERT INTO `testcc`.`user_comment`
            (`comment_id`,
             `user_id`,
             `foreign_id`,
             `parent_comment_id`,
             `time`,
             `content`,
             `like_num`,
             `status`,
             `type_id`)
VALUES (NULL,
        1,
        1,
        1,
        NULL,
        'content',
        0,
        1,
        5);
		
INSERT INTO `testcc`.`user_comment`
            (`comment_id`,
             `user_id`,
             `foreign_id`,
             `parent_comment_id`,
             `time`,
             `content`,
             `like_num`,
             `status`,
             `type_id`)
VALUES (NULL,
        1,
        100,
        1,
        NULL,
        'content',
        0,
        1,
        5);
		
		

		
- source 只有在id存在的时候才能插入，否则报错

DROP TRIGGER IF EXISTS `source_beforeInsert`;
DELIMITER ;;
CREATE TRIGGER `source_beforeInsert` BEFORE INSERT ON `source` FOR EACH ROW BEGIN
IF new.type_id=1 THEN 
	IF (SELECT COUNT(*) FROM `dish_custom` WHERE custom_id =new.foreign_id)=0 THEN 
		signal SQLSTATE 'HY000' SET message_text = '菜品习俗id不存在' ;
	END IF;
ELSEIF new.type_id=2 THEN
	IF (SELECT COUNT(*) FROM `dish_history` WHERE history_id =new.foreign_id)=0 THEN 
		signal SQLSTATE 'HY000' SET message_text = '菜品历史id不存在' ;
	END IF;
ELSEIF new.type_id=3 THEN
	IF (SELECT COUNT(*) FROM `dish_literary` WHERE literary_id =new.foreign_id)=0 THEN 
		signal SQLSTATE 'HY000' SET message_text = '菜品典故id不存在' ;
	END IF;
ELSEIF new.type_id=4 THEN
	IF (SELECT COUNT(*) FROM `dish_recipes` WHERE recipes_id =new.foreign_id)=0 THEN 
		signal SQLSTATE 'HY000' SET message_text = '菜品做法id不存在' ;
	END IF;
ELSEIF new.type_id=5 THEN
	IF (SELECT COUNT(*) FROM `restaurant` WHERE restaurant_id =new.foreign_id)=0 THEN 
		signal SQLSTATE 'HY000' SET message_text = '菜馆id不存在' ;
	END IF;
ELSEIF new.type_id=6 THEN
	IF (SELECT COUNT(*) FROM `user_comment` WHERE comment_id =new.foreign_id)=0 THEN 
		signal SQLSTATE 'HY000' SET message_text = '评论id不存在' ;
	END IF;
ELSEIF new.type_id=7 THEN
	IF (SELECT COUNT(*) FROM `game_step` WHERE id =new.foreign_id)=0 THEN 
		signal SQLSTATE 'HY000' SET message_text = '游戏步骤id不存在' ;
	END IF;
ELSEIF new.type_id=8 THEN
	IF (SELECT COUNT(*) FROM `commodity` WHERE commodity_id =new.foreign_id)=0 THEN 
		signal SQLSTATE 'HY000' SET message_text = '商品id不存在' ;
	END IF;
ELSEIF new.type_id=0 THEN
	IF (SELECT COUNT(*) FROM `dish` WHERE dish_id =new.foreign_id)=0 THEN 
		signal SQLSTATE 'HY000' SET message_text = '菜品id不存在' ;
	END IF;
END IF;
END
;;
DELIMITER ;


INSERT INTO `testcc`.`source`
            (`source_id`,
             `foreign_id`,
             `url`,
             `source_preview_url`,
             `type_id`)
VALUES (NULL,
        1,
        'http://www.aliyouth.cn/repository/Image/CodeResource.png',
        'http://www.aliyouth.cn/repository/Image/CodeResource.png',
        '0');

INSERT INTO `testcc`.`source`
            (`source_id`,
             `foreign_id`,
             `url`,
             `source_preview_url`,
             `type_id`)
VALUES (NULL,
        100,
        'http://www.aliyouth.cn/repository/Image/CodeResource.png',
        'http://www.aliyouth.cn/repository/Image/CodeResource.png',
        '0');

• 以上我我设计的三个触发器，实现思路很简单在插入之前（BEFORE INSERT ）利用IF函数根据type_id判断foreign_id的值在要关联的表中是否存在，若不存在则报错，也就阻止了插入，满足了我目前的需求。

检讨与反思

• 用触发器，这增加了数据库的负担，在大型项目尤其是对数据库压力比较大的项目中是不可取的行为，能多建几张表解决的问题尽量不要用到触发器~