数据字典 mysql创建数据库数据字典表设计

转载

mob64ca13f9a97c 2024-01-08 17:22:41

文章标签 数据字典 mysql创建数据触发器历史数据 文章分类 MySQL 数据库

字典常用于下拉列表选择等,直接在程序中写死不太灵活,因此经常将字典数据放到数据库中,通过后台可以方便的添加/修改/删除字典,前端选择模块中的数据也会相应的发生变化.

字典存储在数据库中也会带来一些问题:

1.有些字典数据非常少(比如是和否),不值得为每一类字典都建一张表,可以将所有字典数据合并在一张表里;

2.应用数据中一般保存的是字典id,使用时根据id到字典表中查询相应的的名称或其它相关数据,但是当字典表发生变化时(例如名称改变),在变更之后的新数据没有问题,但是历史数据会变成新的名称.虽然可以在应用数据中采用直接保存字典名的方法保证历史数据不发生变化,但是使用字典名会带来存储增大/检索速度变慢等问题,权衡下来还是建议采用id的方式保存.

在实际应用中可以结合触发器为避免上述问题,以下为示例:

本文重点

触发器函数返回值分为NEW、OLD、NULL三种,使用时要注意以下规则:

insert动作时只有NEW;
update动作时包含OLD、NEW.其中OLD表示更新前的数据,NEW表示更新的数据;
delete时只有OLD;
触发器函数返回值为NULL时,如果触发器是在before时,不会插入、更新、删除数据.如果是在after时不会有任何操作和没有触发器效果一样;
假设触发表为a,在触发器函数中如果要update a表中的值,直接设置NEW.field=新值,然后返回New即可,同时要将触发器设置为before,在更新之前触发;
配置完成后,就和普通表一样操作(新增\删除\修改),但是保证所有数据都是存在的.

1.创建分类表

分类表不是必须的,直接使用字典表也可以达到相同的功能.创建分类表的目的是降低程序的复杂程度,方便管理.

drop function if exists catalogs_save(bigint,bigint,text,integer,text);

drop trigger if exists tri_del_catalogs on catalogs;
drop function  if exists  trif_del_catalogs();

drop function if exists catalogs_ischilds(bigint);

drop table if exists  catalogs;
/****************************************************************************************
    分类表
    不允许用户添加分类,分类由开发人员添加
    本表数据量预计<10000,因此只要主键索引
    catalogs表不允许物理删除,删除时将revoke改为true
    如果删除的节点包含子类则不允许删除
drop table if exists  catalogs;
****************************************************************************************/
create table catalogs(
    objectid bigint not null,                                           --唯一编号,手动设置
    parentid bigint not null,                                       --父级编号,指向本表的objectid,没有父级设置为0(递归)
    name text not null,                                             --名称
    describe text,                                                      --备注
    sort integer not null,                                              --排序,相对于parentid的排序
    category integer not null,                                      --类别,用于取不同的目录(1:表示字典目录,2表示标准目录,3表示xx,具体由开发人员自己定义)
    revoke boolean default(false) not null,                 --是否作废  
    constraint pk_catalogs_objectid primary key(objectid) with (fillfactor=80)
)with (fillfactor=80,
        autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=300,
        autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=300);
/****************************************************************************************
    检查是否包含子类函数
drop function if exists catalogs_ischilds(bigint);
****************************************************************************************/
create or replace function catalogs_ischilds(
    bigint
) returns boolean
as $$
	with recursive cte(objectid,parentid) as(
		select t1.objectid,t1.parentid from catalogs t1 where t1.parentid=$1 and revoke=false
		union all
		select t2.objectid,t2.parentid from catalogs t2 
			inner join cte on t2.parentid=cte.objectid
			where  revoke=false
	),childs as(
		select count(*) as icount from cte
	)select 
		(case when icount > 1 then
			true
		else
			false
		end) from childs;
$$ language sql strict;
/****************************************************************************************
    当删除数据里,通过触发器取消删除动作(返回NULL)
    同时将是否作废标志改为ture,表示已经作废
    操作和普通表一样
    示例: delete from catalogs where objectid=1;
            不删除编号为1的记录,但是将编号为1记录作废状态修改为true
            如果编号为1的记录包含有子记录,则抛出异常提示,必须先删除子节录
drop trigger if exists tri_del_catalogs on catalogs;
drop function  if exists  trif_del_catalogs();
****************************************************************************************/
create or replace function trif_del_catalogs() 
returns trigger as $$
	declare
    begin 		
        if (TG_OP = 'DELETE' and OLD.objectid is not null)  then
			if catalogs_ischilds(OLD.objectid) then
				raise exception '分类编号"%"包含有下级节点,不能删除!',OLD.objectid;
			end if;
			update catalogs set revoke=true where objectid=OLD.objectid;
        end if;
		return NULL;	
    end; 
$$ language 'plpgsql' SECURITY DEFINER;

create trigger tri_del_catalogs before delete on catalogs 
        for each row execute procedure  trif_del_catalogs();

/****************************************************************************************
    新增分类
drop function if exists catalogs_save(bigint,bigint,text,integer,text);
****************************************************************************************/
create or replace function catalogs_save(
    iobjectid bigint,iparentid bigint,iname text,icategory integer default 1,idescribe text default null
)   returns bigint
as $$
    declare
        v_id bigint;
        v_count bigint;
    begin
        if( (select 1 from catalogs where objectid=iobjectid) is not null ) then
            raise exception '分类编号"%"已经存在,请使用其它编号!',iobjectid;
        end if;

        select count(*) into v_count from catalogs where parentid=iparentid;
        if( 0 = v_count ) then
            v_count := 1;
        else
            v_count := v_count + 1;
        end if;

        insert into catalogs(objectid,parentid,name,describe,category,sort) 
            values(iobjectid,iparentid,iname,idescribe,icategory,v_count) returning objectid into v_id;
        return v_id;
    end;
$$ language plpgsql;

--具体层级没有限制,可以无限递归下去
do $$
    declare v_parentid bigint;
    declare v_parentid1 bigint;
    declare v_parentid2 bigint;
begin
    v_parentid :=catalogs_save(1,0,'xxx主类');
    v_parentid1:=catalogs_save(2,v_parentid,'xxx子类1');
    perform catalogs_save(3,v_parentid,'xxx子类2');

    perform catalogs_save(4,v_parentid1,'xxx子类1的子类');

    perform catalogs_save(5,0,'是否缴费');
    perform catalogs_save(6,0,'计量单位');  
end$$;

--查询指定的分类数据
select objectid,parentid,name,describe from catalogs where category=1 and revoke=false order by parentid,sort

在程序中的实际效果图:

数据字典 mysql创建数据库数据字典表设计_触发器

2.创建字典表和历史字典表

为避免字典数据发生改变里对引用数据的影响问题,同时考虑到字典数据可能会比较多,因此需要创建两个表,一个表为正常使用字典表,另一个为字典历史数据表(任何改变的数据均在这个表里).

注意两个表要一模一样,但是不要使用继承的方式,因为继承稍不注意就会删除两个表的数据,它们之间通过触发器来关联.

drop function if exists dictionarys_name(bigint);
drop function if exists dictionarys_save(bigint,bigint,text,text);

drop trigger if exists tri_chadel_dictionarys on dictionarys;
drop function  if exists  trif_chadel_dictionarys();

drop trigger if exists tri_disable_dicthistorys on dicthistorys;
drop function  if exists  trif_disable_dicthistorys();

drop table if exists  dicthistorys;
drop table if exists  dictionarys;
drop sequence if exists  seqdictionarys;
drop type if exists  tdictionarys;

/****************************************************************************************
    字典表(平时我们使用这个表)
    考虑到经常发生变化,因此将字典表页填充率设置为60%
    同时禁用autovacuum系数,当表中300个元组(Tuple)发生变化是自动触发autovacuum
    修改和删除字典表时,将历史数据转移到字典历史数据表中,通过触发器控制
drop table if exists  dictionarys;
****************************************************************************************/
create table dictionarys(
    objectid bigserial not null,            --唯一编号，主键
    parentid bigint not null,               --分类编号,外键
    name text not null,                     --名称
    describe text,                          --备注
    sort integer not null,                  --排序(在本类别中从1开始)
    constraint pk_dictionarys_objectid primary key(objectid) with (fillfactor=60),
    constraint fk_dictionarys_parentid foreign key(parentid)  references catalogs(objectid) on delete cascade
)with (fillfactor=80,
        autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=300,
        autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=300);
create index idx_dictionarys_parentid on dictionarys(parentid) with (fillfactor=60);

/****************************************************************************************
    字典历史数据表
    此表只能新增,禁止更新和删除数据,因此填充率为100%(默认值)
    同时禁用autovacuum系数,当表中300个元组(Tuple)发生变化是自动触发autovacuum
    本表是通过触发器操作的,不要人为操作这个表
drop table if exists  dicthistorys;
****************************************************************************************/
create table dicthistorys(
    objectid bigint not null,                   --唯一编号，主键
    parentid bigint not null,               --分类编号,外键
    name text not null,                     --名称
    describe text,                              --备注
    sort integer not null,                      --排序
    constraint pk_dicthistorys_objectid primary key(objectid) with (fillfactor=100),
    constraint fk_dicthistorys_parentid foreign key(parentid)  references catalogs(objectid) on delete cascade
)with (fillfactor=100,
    autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=300,
    autovacuum_analyze_scale_factor=0,autovacuum_analyze_threshold=300);
create index idx_dicthistorys_parentid on dicthistorys(parentid) with (fillfactor=100);

3.通过触发器关联表

注意在文章开头时的重点内容

3.1创建自定义类型

因为在触器的insert里里不能直接用NEW关键字,因此先创建一个类型

/****************************************************************************************
    字典表序列--注意除了主键(因为自增原因)其它字段名称和类型必须和表一模一样
drop type if exists  tdictionarys;
****************************************************************************************/
create type tdictionarys as (
    parentid bigint,
    name text,
    describe text,
    sort integer
);

3.2创建主表触发器

/****************************************************************************************
    修改和删除时将数据保存到历史表
drop trigger if exists tri_chadel_dictionarys on dictionarys;
drop function  if exists  trif_chadel_dictionarys();
****************************************************************************************/
create or replace function trif_chadel_dictionarys()
returns trigger as $$
	declare
		v_id	bigint;	--获取自增时的编号
		v_row tdictionarys;
    begin
        if TG_OP = 'UPDATE' then
			if( OLD.objectid is not null ) then
				--不更新数据,直接新增一行
				v_row :=  row(NEW.parentid,NEW.name,NEW.describe,NEW.sort);
				insert into dictionarys(parentid,name,describe,sort)
					values(v_row.parentid,v_row.name,v_row.describe,v_row.sort) returning objectid into v_id;
				--删除待更新的数据,删除时会再次触发DELETE,历史数据会在DELETE中新增,因此这里不需要写历史数据新增脚本
				delete from dictionarys where objectid=OLD.objectid;
			end if;
			return NULL;	  --触发器是before,返回NULL则不更新数据(psql message受影响的行数显示为0,实际已经新增了一行)
		elsif TG_OP = 'DELETE'  then
			if( OLD.objectid is not null) then --保存到历史数据表后删除
				insert into dicthistorys(objectid,parentid,name,describe,sort)
					values(OLD.objectid,OLD.parentid,OLD.name,OLD.describe,OLD.sort);
				return OLD;
			else
				return NULL;
			end if;
        end if;
    end;
$$ language 'plpgsql' SECURITY DEFINER;

create trigger tri_chadel_dictionarys before update or delete on dictionarys
        for each row execute procedure  trif_chadel_dictionarys();

3.3创建历史数据表触发器

历史数据表触发器作用是禁止修改和删除历史数据.

/****************************************************************************************
    禁止修改字典数据历史表
drop trigger if exists tri_disable_dicthistorys on dicthistorys;
drop function  if exists  trif_disable_dicthistorys();
****************************************************************************************/
create or replace function trif_disable_dicthistorys() 
returns trigger as $$
	declare
    begin
		if (TG_OP = 'UPDATE' ) then
			raise exception '禁止修改字典数据历史表';
		elsif ( TG_OP = 'DELETE' )  then
			raise exception '禁止修改字典数据历史表';
        end if;
		return NULL;
    end; 
$$ language 'plpgsql' SECURITY DEFINER;

create trigger tri_disable_dicthistorys before update or delete on dicthistorys
        for each row execute procedure  trif_disable_dicthistorys();

4.操作函数

4.1保存字典数据

/****************************************************************************************
    保存字典
drop function if exists dictionarys_save(bigint,bigint,text,text);
****************************************************************************************/
create or replace function dictionarys_save(
    iobjectid bigint,iparentid bigint,iname text,
    idescribe text default null
)   returns bigint
as $$
    declare
        v_id bigint;
        v_change integer;
    begin
        /*检查关键字是否存在*/
        if( iobjectid is null or (select 1 from dictionarys where objectid=iobjectid) is null ) then

            if( catalogs_ischilds(iparentid) ) then
                raise exception '字典分类编号"%"包含有下级节点,不能添加!',iparentid;
            end if;

            insert into dictionarys(parentid,name,describe,sort) 
                values(iparentid,iname,idescribe,
                    (select (count(*)+1) from dictionarys where parentid=iparentid)
                ) returning objectid into v_id;         
        else
            --因为更新在触发器里新增了一行,并且触发器返回NULL取消更新,因此取不到objectid的值
            --所以直接返回NULL,程序里要注意检查
            update dictionarys set name=iname,describe=idescribe
                where objectid=iobjectid;
            return NULL;
        end if;
        return v_id;
    end;
$$ language plpgsql;

4.2根据ID获取字典数据名称

/****************************************************************************************
    获取指定的字典名称
drop function if exists dictionarys_name(bigint);
****************************************************************************************/
create or replace function dictionarys_name(bigint)
  returns text
as $$
	with cte as(
		select name from dictionarys where objectid=$1
		union all
		select name from dicthistorys where objectid=$1
	)select name from cte limit 1;
$$ language sql strict immutable;

5.导入测试数据

--计量单位
do $$
	declare v_parentid bigint;
begin
	v_parentid := 4;
	perform dictionarys_save(null,v_parentid,'%',null);
	perform dictionarys_save(null,v_parentid,'g/l',null);
	perform dictionarys_save(null,v_parentid,'kcal/㎏',null);
	perform dictionarys_save(null,v_parentid,'㎏',null);
	perform dictionarys_save(null,v_parentid,'㎏/㎡',null);
	perform dictionarys_save(null,v_parentid,'kj/㎏',null);
	perform dictionarys_save(null,v_parentid,'min',null);
	perform dictionarys_save(null,v_parentid,'MPa',null);
	perform dictionarys_save(null,v_parentid,'㎜',null);
	perform dictionarys_save(null,v_parentid,'吨',null);
end$$;

--是否缴费
do $$
	declare v_parentid bigint;
begin
	v_parentid := 5;
	perform dictionarys_save(null,v_parentid,'是',null);
	perform dictionarys_save(null,v_parentid,'否',null);
end$$;

6.测试

--查看字典表和字典历史数据表
select * from dictionarys;
select * from dicthistorys;
--更新是否缴费中的是为xxx
update dictionarys set name='xxx' where objectid=11;
--删除是否缴费中的否
delete from dictionarys where objectid=12;
--更新字典历史数据表,抛出异常
update dicthistorys set name='a';
--删除字典历史数据表,抛出异常
delete from dicthistorys;
--查询objectid为12的字典名称(已经删除)
select dictionarys_name(12)

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。