Implementation of dynamic view in MySQL

Demand:

User is the C/s structure, each user can see different data depending on the role. The system generates a data content based on an identity, which is then accessed through a unified view.

requirements, you cannot modify the view, and you cannot nest a query outside of the attempt.

Design:

The system generates a unique ID (either the application side or the UUID of the database) in some way, and then attempts to associate with the ID.

Code:

drop　table　if　exists　test;
create　table　test　(
　　　　id　int　not　null,

　　　　name　varchar(20)　not　null
);
insert　into　test　values(1,'test1');
insert　into　test　values(1,'test11');
insert　into　test　values(1,'test111');
insert　into　test　values(2,'test2');
insert　into　test　values(2,'test22');
drop　function　if　exists　getSpid;
delimiter　|
CREATE　function　getSpid()
　　　　RETURNS　int
　　RETURN　@spid;
|
delimiter　;
drop　view　if　exists　v_test;
create　view　v_test　as
　　select　*　from　test　where　id=getSpid();

--　测试代码
--　开启session　1
set　@spid=1;
select　*　from　v_test;
--　开启session　2
set　@spid=2;
select　*　from　v_test;

Description

Keep the generated ID inside the session variable

Then create a custom function that returns this variable

Finally call this function in the view