drop table if exists category;
create table if not exists category
(
c_Id bigint not null,
c_name varchar(255) default \'\',
c_type int default 1,
primary key (c_ID)
);

drop table if exists files;
create table if not exists files
(
f_Id bigint not null,
c_id bigint not null,
f_name varchar(255) default \'\',
f_mids text,
primary key (f_ID)
);

drop table if exists members;
create table if not exists members
(
m_Id bigint not null,
m_name varchar(255) default \'\',
primary key (m_ID)
);

insert into category(c_id,c_name,c_type) values (1,\'public\',1);
insert into category(c_id,c_name,c_type) values (2,\'private\',2);
insert into category(c_id,c_name,c_type) values (3,\'upload\',3);
insert into category(c_id,c_name,c_type) values (4,\'member001\',4);
insert into category(c_id,c_name,c_type) values (5,\'member002\',4);

insert into files(f_id,c_id,f_name,f_mids) values (1,1,\'F_public\',\'1,2\');
insert into files(f_id,c_id,f_name,f_mids) values (2,1,\'F_public\',\'1\');
insert into files(f_id,c_id,f_name,f_mids) values (3,1,\'F_public\',\'3,4\');

insert into files(f_id,c_id,f_name,f_mids) values (4,2,\'F_private\',\'1,2\');
insert into files(f_id,c_id,f_name,f_mids) values (5,2,\'F_private\',\'1\');
insert into files(f_id,c_id,f_name,f_mids) values (6,2,\'F_private\',\'3,4\');

insert into files(f_id,c_id,f_name,f_mids) values (7,3,\'F_upload\',\'1,2\');
insert into files(f_id,c_id,f_name,f_mids) values (8,3,\'F_upload\',\'1\');
insert into files(f_id,c_id,f_name,f_mids) values (9,3,\'F_upload\',\'3,4\');

insert into files(f_id,c_id,f_name,f_mids) values (10,4,\'F_upload\',\'1,2\');
insert into files(f_id,c_id,f_name,f_mids) values (11,4,\'F_upload\',\'1\');
insert into files(f_id,c_id,f_name,f_mids) values (12,4,\'F_upload\',\'3,4\');

insert into files(f_id,c_id,f_name,f_mids) values (13,5,\'F_upload\',\'1,2\');
insert into files(f_id,c_id,f_name,f_mids) values (14,5,\'F_upload\',\'1\');
insert into files(f_id,c_id,f_name,f_mids) values (15,5,\'F_upload\',\'3,4\');

#此SQL数据就为多目录及其目录下面的文件列表
select * from category as A,files as B,members as C Where A.c_id=B.c_id order by B.c_type,B.c_id;

insert into members (m_id,m_name) values (1,\'A\');
insert into members (m_id,m_name) values (2,\'B\');
insert into members (m_id,m_name) values (3,\'C\');
insert into members (m_id,m_name) values (4,\'D\');

SELECT * FROM members;

#---取得A(id=1)会员有权限的文件列表
#INSTR(concat(\',\',f_mids ,\',\'),\',1,\') >0 表示此文件关联的Member字段里面存在此ID，
#即表示会员ID为1会员可以查看此文件

SELECT LOCATE(\',1,\', \',1,2,3,\');
Select f_id,f_name,f_mids,
INSTR(concat(\',\',f_mids ,\',\'),\',1,\') AS checked
From files
where INSTR(concat(\',\',f_mids ,\',\'),\',1,\')>0;