Test SQL and SQL processing strings

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 ');

# The SQL data is a list of files under multiple directories and their directories.
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;

# --- Obtain the list of files that a (ID = 1) member has permissions on
# Instr (Concat (',', f_mids, ','), ', 1,')> 0 indicates that this ID exists in the member field associated with this file,
# Indicates that the member ID is 1 and you can view this file.

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;