几个测试SQL，测试SQL处理字符串

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;

原文转自：http://www.ltesting.net