MySQL syntax memo

1. Stored Procedure and cursor
   Core:

   # Variables must be declared at the beginning
    Declare Nomorerows bool Default False;
    Declare _ Id Int ;
    Declare _ Name Varchar ( 25 );
   
   # Declare a cursor
    Declare Xxrows Cursor   For  Select   *   From XX;
   # Set nomorerows to true when the cursor moves to the last empty row
    Declare   Continue Handler For   Not Found Set Nomorerows = True;
   
   # Open a cursor
    Open Xxrows;
   
   # Creating a temporary memory table
    Drop  Table   If   Exists Xxtem;
    Create   Temporary   Table Xxtem
   (ID Int , Name Varchar ( 25 ), Primary   Key (ID ))
   Engine = Memory;
   Truncate   Table Xxtem;
   
   # Traversal cursor
   Repeat
   # Retrieving data to variables
    Fetch Xxrows Into _ Id, _ name;
   # Filter out duplicate rows and insert the cursor results to the temporary table
    If   Not   Exists ( Select   1   From XxtemWhere ID = _ Id) Then 
    Insert   Into Xxtem (ID, name) Values (_ Id, _ name );
    End   If ;
   Until nomorerows =  1 
    End Repeat;
   
   # Closing a cursor
   Close Xxrows;
   # Cropping a temporary table based on parameters
    Select   *   From Xxtem limit 'skip ',' Count ';

   Complete:Complete code

    Create   Table 'Xx '(
   'Id' Int ( 11 ) Not   Null ,
   'Name' Varchar ( 45 ) Null   Default   Null ,
    Primary   Key ('Id ')
   )
   Collate =  '  Utf8_general_ci  ' 
   Engine = InnoDB;
   
   Create Definer = 'Root' @ 'localhost' Procedure 'Proc1 '( In 'Skip' Int , In ' Count ' Int )
   Language SQL
    Not Deterministic
    Contains SQL
   SQL Security definer
   Comment'' 
    Begin     
   # Variables must be declared at the beginning
    Declare Nomorerows bool Default False;
    Declare _ Id Int ;
    Declare _ Name Varchar ( 25 );
   
   # Declare a cursor
    Declare XxrowsCursor   For   Select   *   From XX;
   # Set nomorerows to true when the cursor moves to the last empty row
    Declare   Continue Handler For   Not Found Set Nomorerows = True;
   
   # Open a cursor
    Open Xxrows;
   
   # Creating a temporary memory table
    Drop   Table   If   Exists Xxtem;
    Create   Temporary   Table Xxtem
   (ID Int , Name Varchar ( 25 ), Primary   Key (ID ))
   Engine= Memory;
    Truncate   Table Xxtem;
   
   # Traversal cursor
   Repeat
   # Retrieving data to variables
    Fetch Xxrows Into _ Id, _ name;
   # Filter out duplicate rows and insert the cursor results to the temporary table
    If   Not   Exists ( Select   1  From Xxtem Where ID = _ Id) Then 
    Insert   Into Xxtem (ID, name) Values (_ Id, _ name );
    End   If ;
   Until nomorerows =  1 
    End Repeat;
   
   # Closing a cursor
    Close Xxrows;
   # Cropping a temporary table based on parameters
    Select   *   From Xxtem limit 'skip ',' Count ';
    End 

2. Dynamic SQL: Random query of several rows in a table
   Below is the Stored Procedure Code . The process contains three parameters: Table Name, row number, and primary key ID. Only tables with single-Primary keys of the int type are supported. The primary key can be omitted as null. In this way, the primary key is automatically searched, but this is slow. Rand_data

    1   Create Definer= 'Root '@' % ' Procedure 'Rand _ data '( In 'Tbname' Varchar ( 50 ), In 'Rowcnt' Int , In 'Tbkey' Varchar ( 50 ))
    2 Language SQL
   3       Not Deterministic
    4       Contains SQL
    5 SQL Security definer
    6 Comment '  Random acquisition of several records, only applicable to a single primary key table  ' 
    7   Begin 
    8  
    9 # Obtain the primary key name
   10       If Tbkey Is   Not   Null   Then 
    11           Set   @ Tbkey  = Tbkey; # The parameter already exists. This situation is faster.
    12       Else 
    13 # No In the parameter. It is time consuming to query the primary key from the system table.
    14           Select  @ Tbkey : = C. column_name
    15           From 
    16 Information_schema.table_constraints As T,
    17 Information_schema.key_column_usage As C
    18           Where 
    19 T. table_name = C. table_name
    20                 And T. table_schema = C. table_schema
    21               And T. table_schema =   Database ()
    22               And T. table_name = Tbname
    23               And T. constraint_type =  '  Primary Key  ' ;
    24       End   If ;
    25          
    26 # Obtain the maximum ID, minimum ID, and number of records
    27       Set   @ Getmaxidsql   = Concat ( '  Select @ maxid: = max (  ' , @ Tbkey , '  ),  ' ,
    28           '  @ Minid: = min (  ' , @ Tbkey , '  ),  ' ,
    29           '  @ Totalcnt: = count ( ' , @ Tbkey , '  )  ' ,
    30           '  From'  ' , Tbname, '  ';  ' );
    31       Prepare Getmaxid From  @ Getmaxidsql ;
    32       Execute Getmaxid;
    33       Deallocate   Prepare Getmaxid;
    34      
    35 # Creating a temporary table
    36       Drop   Table   If   Exists Rand_tt;
   37       Set   @ Temtbsql   = Concat ( '  Create temporary table rand_tt select 0 aid, TB. * from'  ' , Tbname, '  'Tb limit 0;  ' );
    38       Prepare Temtb From   @ Temtbsql ;
   39       Execute Temtb;
    40       Deallocate   Prepare Temtb;
    41      
    42 # Construct an SQL statement to obtain a record
    43       Set   @ Randrowsql   = Concat ( '  Insert into rand_tt select @ CNT: = @ CNT + 1 aid, TB. * from  ' ,
    44 Tbname, '  TB where TB.  ' , @ Tbkey , '  = ?;  ' );
    45       Prepare Addrow From   @ Randrowsql ;
    46      
    47 # Generate random records
    48       Set   @ CNT  =  0 ;
    49 Insertloop: loop
    50           Set   @ ID  =  Floor ( Rand () * ( @ Maxid -  @ Minid ) +  @ Minid );
    51           If   Not   Exists ( Select ID From Rand_tt Where ID =  @ ID ) Then 
    52              Execute Addrow using @ ID ;
    53               If   @ CNT   > = Rowcnt Or   @ CNT   > =   @ Totalcnt   Then 
    54 Leave insertloop;
    55               End   If ;
    56           End   If ;
    57       End Loop insertloop;
    58       Deallocate   Prepare Addrow;
    59      
    60 # Returned data
    61       Alter   Table Rand_ttDrop   Column Aid;
    62       Select   *   From Rand_tt;
    63   End 

4. ...