基于MySQL的任意字段的数据库表的实现

dorainm不擅长于数据库.

今天比较突发奇想, 如果一个表, 比如存储帐户密码的表, account, 可能程序当前只需要存储 username和password帐户信息, 但是哪天没准又要增加 email, 或者程序升级后需要有上次登陆时间, IP等字段, 怎么在不改变数据库表的前提下, 可以任意"扩展"数据库字段?

下面的方法是这么实现的, DORA_ACCOUNT表去DORA_ATTR_OWNER里注册一个扩展,然后用关系的ID ATTR_OWNER_ID, 来添加其名下的一些字段和类型, 任意添加. KIND_ID决定字段的类型, 这样子可以实现表字段的任意扩展了. ATTR_COLUMN_ORDER甚至可以给这些字段进行排序.

DORA_ATTR_VALUE是存储扩展字段相应的数值的, 根据 ATTR_OWNER_ID, OWNER_DEFINE_ID来确定某个"row"的数据,根据 ATTR_COLUMN_ID来区分列, 把表关联后, 还是能查询出很直观的数据呢.

利用这种思想, 设计出了针对某个表的扩展的方法.


那么, 我们能不能完全利用这种思想, 来建立数据库呢!!
当然可以, DORA_TABLE就是这么样子的数据库, 至关重要的是 TABLE_ID, 如果觉得 TABLE的其它字段不够用, 比如我要增加一个建立表的日期, 最后修改日期, 访问权限等字段, 可以参照上段文章中所述.

有了 TABLE_ID后, 然后为 TABLE定义字段. 就是在 DORA_TABLE_FIELD里面, 任何增加字段个数, 当然, 配置相应的属性, 如果觉得字段caption没法来完成你对字段复杂度的要求, 当然你还可以去扩展中注册一个ID来,为字段增加最后访问时间, 总共访问次数等意义的字段扩展.

有了表结构,现在要插入一条数据了. 我们首先在 DORA_TABLE_RECORD中插入一条RECORD, 获取到一条 隶属于 TABLE_ID 的 RECORD_ID, 我们就用 RECORD_ID 和 FIELD_ID当横竖坐标, 在 DORA_TABLE_CELL表里面增加表的单元内容了~~ hoho~~

设计表的SQL在下面, 最后还有演示的SQL代码,供大家参考, 希望有更好的想法 :)


--- 注册登陆的用户，只需要邮箱和密码
DROP TABLE IF EXISTS `DORA_ACCOUNT`;
CREATE TABLE `DORA_ACCOUNT` (
       `ACCOUNT_ID` bigint(20) unsigned NOT NULL auto_increment,
       `ACCOUNT_USER` varchar(32) NOT NULL,
       `ACCOUNT_PASSWD` char(32) NOT NULL,
       PRIMARY KEY (`ACCOUNT_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;



--- 日志表，里面有用户日志表的ID及所属 ------------------------------------
DROP TABLE IF EXISTS `DORA_TABLE`;
CREATE TABLE `DORA_TABLE` (
       `TABLE_ID` bigint(20) unsigned NOT NULL auto_increment,
       `ACCOUNT_ID` bigint(20) unsigned NOT NULL,
       `TABLE_CAPTION` varchar(256) DEFAULT '(NULL)',
       `TABLE_ORDER` tinyint(3) unsigned DEFAULT 0,
       PRIMARY KEY (`TABLE_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;


--- 日志表记录
DROP TABLE IF EXISTS `DORA_TABLE_RECORD`;
CREATE TABLE `DORA_TABLE_RECORD` (
       `TABLE_RECORD_ID` bigint(20) unsigned NOT NULL auto_increment,
       `TABLE_ID` bigint(20) unsigned NOT NULL,
       `TABLE_RECORD_DATE` datetime DEFAULT '0000-00-00 00:00:00',
       PRIMARY KEY (`TABLE_RECORD_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;


--- 日志表字段-名
DROP TABLE IF EXISTS `DORA_TABLE_FIELD`;
CREATE TABLE `DORA_TABLE_FIELD` (
       `TABLE_FIELD_ID` bigint(20) unsigned NOT NULL auto_increment,
       `TABLE_ID` bigint(20) unsigned NOT NULL,
       `TABLE_FIELD_CAPTION` varchar(256) DEFAULT '(NULL)',
       `KIND_ID` bigint(20) unsigned NOT NULL,
       `TABLE_FIELD_ORDER` tinyint(3) unsigned DEFAULT 0,
       PRIMARY KEY (`TABLE_FIELD_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

--- 日志表字段-内容-按照年月日生成_YYYYMMDD
DROP TABLE IF EXISTS `DORA_TABLE_CELL`;
CREATE TABLE `DORA_TABLE_CELL` (
       `TABLE_CELL_ID` bigint(20) unsigned NOT NULL auto_increment,
       `TABLE_RECORD_ID` bigint(20) unsigned NOT NULL,
       `TABLE_FIELD_ID` bigint(20) unsigned NOT NULL,
       `TABLE_CELL_VALUE` longblob,
       PRIMARY KEY (`TABLE_CELL_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;






---------- 公共 ---------------------
--- 类型表
DROP TABLE IF EXISTS `DORA_KIND`;
CREATE TABLE `DORA_KIND` (
       `KIND_ID` bigint(20) unsigned NOT NULL auto_increment,
       `KIND_CAPTION` varchar(32) NOT NULL,
       `KIND_DESC` varchar(256),
       PRIMARY KEY (`KIND_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

-- 类型表-扩展 过滤规则
DROP TABLE IF EXISTS `DORA_KIND_FILTER`;
CREATE TABLE `DORA_KIND_FILTER` (
       `KIND_FILTER_ID` bigint(20) unsigned NOT NULL auto_increment,
       `KIND_ID` bigint(20) unsigned NOT NULL,
       `KIND_FILTER_SETP` tinyint(3) NOT NULL,
       `KIND_OPERATION` varchar(32),
       `KIND_ARG_1` varchar(32),
       `KIND_ARG_2` varchar(32),
       PRIMARY KEY (`KIND_FILTER_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;


-- 属性扩展表-注册
DROP TABLE IF EXISTS `DORA_ATTR_OWNER`;
CREATE TABLE `DORA_ATTR_OWNER` (
       `ATTR_OWNER_ID` bigint(20) unsigned NOT NULL auto_increment,
       `TABLE_NAME` varchar(32) NOT NULL,
       PRIMARY KEY (`ATTR_OWNER_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

--- 属性扩展表-属性名称
DROP TABLE IF EXISTS `DORA_ATTR_COLUMN`;
CREATE TABLE `DORA_ATTR_COLUMN` (
       `ATTR_COLUMN_ID` bigint(20) unsigned NOT NULL auto_increment,
       `ATTR_OWNER_ID` bigint(20) unsigned NOT NULL,
       `ATTR_COLUMN_CAPTION` varchar(32) DEFAULT '(NULL)',
       `KIND_ID` bigint(20) unsigned NOT NULL,
       `ATTR_COLUMN_ORDER` tinyint(3) DEFAULT 0,
       PRIMARY KEY (`ATTR_COLUMN_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

-- 日志表属性-值
DROP TABLE IF EXISTS `DORA_ATTR_VALUE`;
CREATE TABLE `DORA_ATTR_VALUE` (
       `ATTR_VALUE_ID` bigint(20) unsigned NOT NULL auto_increment,
       `ATTR_OWNER_ID` bigint(20) unsigned NOT NULL,
       `ATTR_COLUMN_ID` bigint(20) unsigned NOT NULL,
       `OWNER_DEFINE_ID` bigint(20) unsigned DEFAULT '0',
       `ATTR_VALUE` longblob,
       PRIMARY KEY (`ATTR_VALUE_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;




-- 建立一个新用户
INSERT INTO `DORA_ACCOUNT` VALUES (NULL,'[e-mail]',md5('dorainm'));

-- 建立一个 boolean的类型, 一个时间日期类型, 一个文本类型, 一个数字类型
INSERT INTO `DORA_KIND` VALUES (NULL,'boolean','只能接受真/假的布朗值');
INSERT INTO `DORA_KIND` VALUES (NULL,'datetime','时间类型, 请书写标准的时间格式YYYY-MM-DD HH:Mi:SS');
INSERT INTO `DORA_KIND` VALUES (NULL,'text','能够接受任何字符串的类型');
INSERT INTO `DORA_KIND` VALUES (NULL,'number','数字类型');
-- 具体程序过滤类型的模板引擎和数据库过滤规则信息还没有想出来, 以待完善
... ...
此处插入实现上面 4种类型的过滤规格信息
... ...

-- 给 DORA_ACCOUNT表注册一个属性扩展帐号
INSERT INTO `DORA_ATTR_OWNER` VALUES (NULL,'DORA_ACCOUNT');
-- 建立 4个属性, 分别是注册状态, 最后登陆时间, 最后登陆 IP地址, 最后登陆物理地址
INSERT INTO `DORA_ATTR_COLUMN` VALUES (NULL,1,'注册状态',1,0);
INSERT INTO `DORA_ATTR_COLUMN` VALUES (NULL,1,'最后登陆时间',2,1);
INSERT INTO `DORA_ATTR_COLUMN` VALUES (NULL,1,'最后登陆IP',3,2);
INSERT INTO `DORA_ATTR_COLUMN` VALUES (NULL,1,'最后登陆地址',3,3);

-- 然后给帐户中的[e-mail]添加这 4种新属性
INSERT INTO `DORA_ATTR_VALUE` VALUES (NULL,1,1,1,'ACTIVE');
INSERT INTO `DORA_ATTR_VALUE` VALUES (NULL,1,1,2,'2009-03-24 21:49:27');
INSERT INTO `DORA_ATTR_VALUE` VALUES (NULL,1,1,3,'111.111.111.111');
INSERT INTO `DORA_ATTR_VALUE` VALUES (NULL,1,1,4,'欧洲');


-- [e-mail]帐户建立一张用户登陆日志表, 一张用户访问日志表
INSERT INTO `DORA_TABLE` VALUES (NULL,1,'后台用户登陆日志表',0);    -- 1
INSERT INTO `DORA_TABLE` VALUES (NULL,1,'网站用户访问日志表',1);    -- 2
-- 为用户登陆日志表建立字段: 用户名, 时间日期, IP地址, 物理地址, 操作系统, 浏览器类型
INSERT INTO `DORA_TABLE_FIELD` VALUES (NULL,1,'用户名',3,0);     -- 1
INSERT INTO `DORA_TABLE_FIELD` VALUES (NULL,1,'时间日期',2,1);     -- 2
INSERT INTO `DORA_TABLE_FIELD` VALUES (NULL,1,'IP地址',3,2);     -- 3
INSERT INTO `DORA_TABLE_FIELD` VALUES (NULL,1,'物理地址',3,3);     -- 4
INSERT INTO `DORA_TABLE_FIELD` VALUES (NULL,1,'操作系统',3,4);     -- 5
INSERT INTO `DORA_TABLE_FIELD` VALUES (NULL,1,'浏览器类型',3,5); -- 6
-- 为用户访问日志表建立字段: 用户, 时间日期, 访问页面ID
INSERT INTO `DORA_TABLE_FIELD` VALUES (NULL,2,'用户名',3,0);    -- 7
INSERT INTO `DORA_TABLE_FIELD` VALUES (NULL,2,'时间日期',2,1);    -- 8
INSERT INTO `DORA_TABLE_FIELD` VALUES (NULL,2,'访问页面ID',4,2); -- 9

-- 插入一条登陆日志, anonymous:'2009-03-12 11:22:33':'123.234.123.234':'山东省青岛市 网通':'Linux':'Lynx'
INSERT INTO `DORA_TABLE_RECORD` VALUES (NULL,1,'2009-03-24 22:22:22');    -- 1
INSERT INTO `DORA_TABLE_CELL` VALUES (NULL,1,1,'anonymous');
INSERT INTO `DORA_TABLE_CELL` VALUES (NULL,1,2,'2009-03-12 11:22:33');
INSERT INTO `DORA_TABLE_CELL` VALUES (NULL,1,3,'123.234.123.234);
INSERT INTO `DORA_TABLE_CELL` VALUES (NULL,1,4,'山东省青岛市 网通');
INSERT INTO `DORA_TABLE_CELL` VALUES (NULL,1,5,'Linux');
INSERT INTO `DORA_TABLE_CELL` VALUES (NULL,1,6,'lynx');
-- 插入两条访问日志, 'anonymous':'2009-03-13 11:44:55':6992
--          'anonymous':'2009-03-13 22:33:44':7003
INSERT INTO `DORA_TABLE_RECORD` VALUES (NULL,2,'2009-03-24 22:22:26');    -- 2
INSERT INTO `DORA_TABLE_CELL` VALUES (NULL,2,7,'anonymous');
INSERT INTO `DORA_TABLE_CELL` VALUES (NULL,2,8,'2009-03-13 11:44:55');
INSERT INTO `DORA_TABLE_CELL` VALUES (NULL,2,9,'6992');
INSERT INTO `DORA_TABLE_RECORD` VALUES (NULL,2,'2009-03-24 22:22:28');    -- 3
INSERT INTO `DORA_TABLE_CELL` VALUES (NULL,3,7,'anonymous');
INSERT INTO `DORA_TABLE_CELL` VALUES (NULL,3,8,'2009-03-13 22:33:44');
INSERT INTO `DORA_TABLE_CELL` VALUES (NULL,3,9,'7003