Requirements:
The user is in a C/S structure. Each user can view different data based on different roles. The system generates a data content based on a specific Identifier and then accesses it through a unified view.
Required. You cannot modify the view or nest a layer of query outside the view.
Design:
The system generates a unique ID (either the application or the uuid of the database) in some way, and then tries to associate it with this 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
Select * from test where id = getSpid ();
-- Test code
-- Enable session 1
Set @ spid = 1;
Select * from v_test;
-- Enable session 2
Set @ spid = 2;
Select * from v_test;
Note:
Maintain the generated ID in the session variable.
Create a UDF and return this variable.
Finally, call this function in the view.