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;
