How to implement a dynamic MySQL View

The implementation method of MySQL dynamic view should be all we need to know. The following describes how to implement MySQL dynamic view, hoping to help you.

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.

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 MySQL dynamic view.

Required. The MySQL dynamic view cannot be modified, or a layer of query cannot be nested outside the attempt.

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 MySQL dynamic view.

Back up and restore a database using MySQL Command Line

How to delete duplicate records in MySQL

About MySQL query Cache Mechanism

Use MySQL temporary tables to accelerate queries

How to properly use MySQL Indexes