首先来帖下SQL代码吧:
/*
SQLyog Ultimate v11.24 (32 bit)
MySQL - 5.5.56 : Database - demostrationtest
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`demostrationtest` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
USE `demostrationtest`;
/*Table structure for table `application_table` */
DROP TABLE IF EXISTS `application_table`;
CREATE TABLE `application_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`application_name_eng` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`application_name_chn` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`application_type` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `application_table` */
insert into `application_table`(`id`,`application_name_eng`,`application_name_chn`,`application_type`) values (1,'application1','应用1',1),(2,'application2','应用2',2),(3,'application3','应用3',4),(4,'application4','应用4',8),(5,'application5','应用5',16);
/*Table structure for table `column_table` */
DROP TABLE IF EXISTS `column_table`;
CREATE TABLE `column_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`table_id` int(11) DEFAULT NULL,
`column_id` int(11) DEFAULT NULL,
`column_name_eng` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`column_name_chn` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`appliction_type` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `column_table` */
insert into `column_table`(`id`,`table_id`,`column_id`,`column_name_eng`,`column_name_chn`,`appliction_type`) values (1,10000,1,'id','表1的ID',1),(2,10000,2,'table1_value1','表1的value1',3),(3,10000,3,'table1_value2','表1的value2',1),(4,10000,4,'table1_value3','表1的value3',1),(5,10000,5,'table1_value4','表1的value4',1),(6,10000,6,'table1_value5','表1的value5',4),(7,10000,7,'table1_value6','表1的value6',8),(8,10001,1,'id','表2的ID',16),(9,10001,2,'table2_value1','表2的value1',16),(10,10001,3,'table2_value2','表2的value2',17),(11,10001,4,'table2_value3','表2的value3',16),(12,10001,5,'table2_value4','表2的value4',16),(13,10001,6,'table2_value5','表2的value5',23),(14,10001,7,'table2_value6','表2的value6',16);
/*Table structure for table `system_table` */
DROP TABLE IF EXISTS `system_table`;
CREATE TABLE `system_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`table_id` int(11) DEFAULT NULL,
`table_name_eng` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`table_name_chn` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `system_table` */
insert into `system_table`(`id`,`table_id`,`table_name_eng`,`table_name_chn`) values (1,10000,'table1','表1'),(2,10001,'table2','表2');
/*Table structure for table `table1` */
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`table1_value1` float DEFAULT NULL,
`table1_value2` double DEFAULT NULL,
`table1_value3` datetime DEFAULT NULL,
`table1_value4` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`table1_value5` time DEFAULT NULL,
`table1_value6` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `table1` */
insert into `table1`(`id`,`table1_value1`,`table1_value2`,`table1_value3`,`table1_value4`,`table1_value5`,`table1_value6`) values (1,123,123.123,'2020-04-17 13:44:29','呵呵','22:00:00',2333333);
/*Table structure for table `table2` */
DROP TABLE IF EXISTS `table2`;
CREATE TABLE `table2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`table2_value1` float DEFAULT NULL,
`table2_value2` double DEFAULT NULL,
`table2_value3` datetime DEFAULT NULL,
`table2_value4` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`table2_value5` time DEFAULT NULL,
`table2_value6` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*Data for the table `table2` */
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
其实也就是这这张表:
在这个demostrationtest库里面有这几张表。
其中有关键的三张表索引表分别是:
application_table:用于记录应用的,如下:
其中application_type为int类型,
int为4字节,转换成二进制即为:
0000 0000 0000 0000
不同的应用是这样进行构造的
0000 0000 0000 0001
0000 0000 0000 0010
0000 0000 0000 0100
这个东西的作用是可以在元组索引表中添加,某表的某个元组属于哪些application。
如果同时满足3个应用则为:
0000 0000 0000 0111
存储的时候转为10进制即可!
下面来看下元组索引表:
这里记录了所有表中元组的英文名,中文名及是哪个application下面的。
下面是表索引表:
通过这种方式,可以更好的进行数据库的管理。