作者:清潇静凌 | 来源:互联网 | 2023-09-04 18:41
如下示例,在用户界面上,让用户可以自定义添加列,自己管理列的顺序,自己进行数据的增删改查。这些数据都要保存到数据库,所以具体有多少个列、列名称、顺序都是不确定的。这种情况如何创建一个通用的表来
如下示例,在用户界面上,让用户可以自定义添加列,自己管理列的顺序,自己进行数据的增删改查。
这些数据都要保存到数据库,所以具体有多少个列、列名称、顺序都是不确定的。
这种情况如何创建一个通用的表来管理数据?
7 个解决方案
id userid colname values
1 1 性别 男,女
2 1 姓名 张三,李四
或者
id colname
1 性别
2 姓名
userid colid value
1 1 男
2 1 女
1 2 张三
2 2 李四
以最多的列来设计 数据表。如果有数据就都存在里面。
比如性别,姓名,身高,年龄,体重等。
再设立一个配置表,存放客户定义了列的信息。比如
Uid 列
1 性别,姓名
在程序里面,如果打开界面前,先查询出来列的信息,显示在前面上,以供编辑。然后根据这些列去更新数据表.
USE tempdb
GO
IF OBJECT_ID('userInfo') IS NOT NULL DROP TABLE userInfo
GO
create table userInfo(
userId int identity(1,1) primary key,
userName nvarchar(20) not null, --姓名 必填
birthday datetime not null, --生日,避免年龄总是变化
age as datediff(year, birthday, getdate()), --自动产生年龄
gender nchar(1), --性别
c1 nvarchar(max), --自定义列
c2 nvarchar(max), --自定义列
c3 nvarchar(max), --自定义列
c4 nvarchar(max), --自定义列
c5 nvarchar(max), --自定义列
c6 nvarchar(max), --自定义列
c7 nvarchar(max), --自定义列
c8 nvarchar(max), --自定义列
c9 nvarchar(max), --自定义列
c10 nvarchar(max) --自定义列
)
GO
IF OBJECT_ID('userInfoConfig') IS NOT NULL DROP TABLE userInfoConfig
GO
create table userInfoConfig(
configId int identity(1,1) primary key,
colName varchar(50),
colCustomeName nvarchar(50),
colSeq int
)
GO
SET NOCOUNT ON
insert into userInfoConfig (colName,colCustomeName,colSeq)
select 'userName','姓名',2
union all select 'age','年龄',5
union all select 'gender','性别',1
union all select 'c1','身高',3
union all select 'c2','体重',4
DECLARE @sql NVARCHAR(MAX)
SET @sql ='SELECT ' + STUFF(
(SELECT ',[' + colName + '] AS [' + colCustomeName+']'
FROM userInfoConfig ORDER BY colSeq
FOR XML PATH(''))
,1,1,'') + ' FROM dbo.userInfo'
PRINT @sql
EXEC (@sql)
/*
SELECT [gender] AS [性别],[userName] AS [姓名],[c1] AS [身高],[c2] AS [体重],[age] AS [年龄] FROM dbo.userInfo
性别 姓名 身高 体重 年龄 ---- -------- ------ ----- -----
*/
不明白现在为什么无法贴长的代码了, 只好分了两段
在网站(或其它界面)设置每个列的顺序、名称等并保存, 后面要查询时, 组合一下动态SQL就好。
几种方法:
1. 动态添加属性字段。(不肯能,DDL影响很大)
2. 先预留字段,到时再用。(浪费很多空间,IO开销大,有限)
3. 使用 XML 字段保存。(非主流,XML读取麻烦)
4. JSON 格式保存。(可行,子文档存储自定义字段)
5. 属性字段行存储(设计复杂,省、无限扩展)
以前粗略写的,请参考:
数据库字段动态扩展设计