MySQL可见_mysql db记录可见性(设计和选择帮助)

这是表结构。请注意，根据您的MySQL版本，您可能需要将type = INNODB更改为ENGINE = INNODB：

drop table if exists prjusers;

drop table if exists proj_users;

drop table if exists project_regions;

drop table if exists projects;

drop table if exists regions;

/*==============================================================*/

/* Table: prjusers */

/*==============================================================*/

create table prjusers

(

id_prjuser int not null auto_increment,

id_region int not null,

user_name varchar(100) not null,

primary key (id_prjuser)

)

type = InnoDB;

/*==============================================================*/

/* Table: proj_users */

/*==============================================================*/

create table proj_users

(

id_proj_user int not null auto_increment,

id_prjuser int not null,

id_project int not null,

primary key (id_proj_user)

)

type = InnoDB;

/*==============================================================*/

/* Table: project_regions */

/*==============================================================*/

create table project_regions

(

id_project_region int not null auto_increment,

id_project int not null,

id_region int not null,

primary key (id_project_region)

)

type = InnoDB;

/*==============================================================*/

/* Table: projects */

/*==============================================================*/

create table projects

(

id_project int not null auto_increment,

prj_code varchar(100) not null,

global_project char(1) not null,

primary key (id_project)

)

type = InnoDB;

/*==============================================================*/

/* Table: regions */

/*==============================================================*/

create table regions

(

id_region int not null auto_increment,

region_name varchar(100) not null,

primary key (id_region)

)

type = InnoDB;

alter table prjusers add constraint FK_relationship_12 foreign key (id_region)

references regions (id_region) on delete restrict on update restrict;

alter table proj_users add constraint FK_relationship_10 foreign key (id_prjuser)

references prjusers (id_prjuser) on delete restrict on update restrict;

alter table proj_users add constraint FK_relationship_11 foreign key (id_project)

references projects (id_project) on delete restrict on update restrict;

alter table project_regions add constraint FK_relationship_8 foreign key (id_project)

references projects (id_project) on delete restrict on update restrict;

alter table project_regions add constraint FK_relationship_9 foreign key (id_region)

references regions (id_region) on delete restrict on update restrict;选择语句：

select id_project, prj_code from projects where global_project='Y'

UNION

select projects.id_project, projects.prj_code

from projects join project_regions on projects.id_project = project_regions.id_project

join prjusers on prjusers.id_region = project_regions.id_region

where prjusers.id_prjuser = {$_SESSION['id_prjuser']}

UNION

select projects.id_project, projects.prj_code

from projects join proj_users on projects.id_project = proj_users.id_project

where proj_users.id_prjuser = {$_SESSION['id_prjuser']}上面将做3个UNIONS，并提出全局(1)的项目ID和项目代码，它们属于登录用户的区域，其ID保存在会话变量(2)中，所有项目都是明确的分配给该用户。