数据库自定义字段及相关检索

应用开发过程中，有时候会有用户可以自定义字段的要求。比如我们对于人员信息的维护提供了姓名，性别，出生日期，备注等基本信息，但客户可能希望自己可以自定义一些字段来保存特定的信息，并对这些信息进行检索。

这个时候通常可能有两个方案：

1、对原有人员表增加若干备用字段，由用户来定义其含义；

2、增加表，来存储自定义字段结构以及相应数据。

在不同的背景情况下，两个方案各有优缺点，方案2的优点是可以方便的扩展，满足所有表增加自定义字段的需求。

这里描述方案2的一个实现。

假设有如下表TableA：

 

TableA         
Id    Name    Address
1    张三    沈阳
2    李四    丹东
3    王五    大连

 

现在希望可以添加多个自定义字段，所以在数据库中增加以下两个表：

 

CustomField                   
Id    TableName    FiledLable    FiledName    FieldType
1    TableA    自定义1    F1    varchar
2    TableA    自定义2    F2    varchar
3    TableB    自定义3    F3    Date
4    TableB    自定义4    F4    Number

 

说明：用于存储用户自定义的字段

         TableName：自定义所属表的名称

         FieldLable：自定义字段在界面上显示的名称

         FieldName：用于描述自定义字段的名称，用于SQL操作字段别名，非必要字段

         FieldType：描述字段的类型，用于控制界面上控件的操作行为。比如如果是Date类型，则可以控制用户只能输入日期格式的信息

 

FieldData              
Id    EntityID    FieldId    data
1    1    1    abc
2    1    2    efg
3    2    1    bcd

 

说明：用于存储所有自定义字段的数据

         EntityID：主表的ID，这里对应TableA表中某条记录的Id

         Field点：自定义字段的ID，这里对应CustomField表中的Id

         data：用于存储自定义字段的内容

 

以上结构及数据表示TableA这张表有两个自定义字段，其中“张三”这条数据的两个自定义字段都已经填写数据，“李四”这条数据只填写了一个自定义数据。

假设查询条件为：

       姓名：张三

       自定义1：abc

       自定义2：g

查询SQL：

1、检索自定义字段列表：

select id,fieldName,fieldLable,fieldType from CustomField where TableName='TableA' order by Id

该语句将检索出TableA的所有自定义字段信息。

 

2、自定义字段联合查询(一个自定义字段的情况)：

select a.*,fd1.data as F1,fd2.data as F2 from TableA a
left outer join (select entityId,data from fieldData where fieldId=1) fd1 on a.id=fd1.entityId
left outer join (select entityId,data from fieldData where fieldId=2) fd2 on a.id=fd2.entityId
where a.name like '%张三%' and fd1.data like '%abc%';

检索结果如下：

 

3、自定义字段联合查询(两个自定义字段的情况)：

select a.*,fd1.data as F1,fd2.data as F2 from TableA a
left outer join (select entityId,data from fieldData where fieldId=1) fd1 on a.id=fd1.entityId
left outer join (select entityId,data from fieldData where fieldId=2) fd2 on a.id=fd2.entityId
where a.name like '%张三%'
and fd1.data like '%abc%'
and fd2.data like '%g%' ;

检索结果如下：

 

4、检索主表及所有自定义字段信息：

select a.*,fd1.data as F1,fd2.data as F2 from TableA a
left outer join (select entityId,data from fieldData where fieldId=1) fd1 on a.id=fd1.entityId
left outer join (select entityId,data from fieldData where fieldId=2) fd2 on a.id=fd2.entityId

检索结果如下：