Actual solutions for Oracle MySQL dynamic tables

The following article mainly describes the actual solution to Oracle MySQL dynamic tables. We all know that CRM needs to regularly export relevant data back to its CRM database (destination DB: Oracle; source DB: MySQL); but because the current growth is not very large, it will be relatively large in the future. because the Oracle MySQL database is not built by me.

I decided to log on to the table and check that no field in the table can uniquely identify a ratio record. when I export data, there will be new data input, which will obviously lead to problems. the programmer is not here. He has been using Oracle all the time and is about to play with MySQL. It seems that he has to learn how to use it now. Now he writes an Oracle MySQL stored procedure for implementation. place the table generated by the system on the next day. The system inserts data into different tables according to the time. The following is the execution script:

MySQL Stored Procedure example:

 
 

  
  
declare @t_date varchar(20);  
  
  
set @t_date = '(select concat('readinfo',curdate()+0))';  
  
  
mysql>delimiter && 
 
 

Temporary Terminator

 
 

  
  
mysql>create procedure p()   
  
  
->begin   
  
  
->select * from pet;   
  
  
->end;&&   
  
  
mysql>delimiter ;   
  
  
mysql> call p();  
 
 

Oracle MySQL stored procedure:

 
 

  
  
CREATE TABLE readinfo20070726 (  
  
  
eid int(11) ,  
  
  
guid varchar(36) ,  
  
  
ip varchar(20) ,  
  
  
date varchar(20),  
  
  
bookname varchar(60) ,  
  
  
version int(11) ,  
  
  
isvip int(11) ,  
  
  
vipname varchar(80)  
  
  
) ENGINE=MyISAM DEFAULT CHARSET=utf8   
  
  
CREATE TABLE readinfo20070727 (  
  
  
eid int(11) ,  
  
  
guid varchar(36) ,  
  
  
ip varchar(20) ,  
  
  
date varchar(20),  
  
  
bookname varchar(60) ,  
  
  
version int(11) ,  
  
  
isvip int(11) ,  
  
  
vipname varchar(80)  
  
  
) ENGINE=MyISAM DEFAULT CHARSET=utf8   
  
  
CREATE TABLE readinfo20070728 (  
  
  
eid int(11) ,  
  
  
guid varchar(36) ,  
  
  
ip varchar(20) ,  
  
  
date varchar(20),  
  
  
bookname varchar(60) ,  
  
  
version int(11) ,  
  
  
isvip int(11) ,  
  
  
vipname varchar(80)  
  
  
) ENGINE=MyISAM DEFAULT CHARSET=utf8  
 
 

Stored Procedure:

 
 

  
  
CREATE PROCEDURE sp_readinfo(in eid int,in guid varchar(36),in ip varchar(50))  
  
  
NOT DETERMINISTIC  
  
  
SQL SECURITY DEFINER  
  
  
COMMENT ''  
  
  
BEGIN  
  
  
set @table = concat('readinfo',curdate()+0);  
  
  
set @temp1 = 'insert into ';  
  
  
set @temp2 = '(eid,guid,ip,date) values(';  
  
  
set @value1 = eid;  
  
  
set @value2 = guid;  
  
  
set @value3 = ip;  
  
  
set @value4 = 'now())';  
  
  
set @sql_text:=concat(@temp1,@table,@temp2,@value1,',',@value2,',',@value3,',',@value4);   
  
  
prepare stmt from @sql_text;  
  
  
execute stmt;  
  
  
end; 
 
 

In doing so, it will certainly have an impact on the performance, and it is only temporary ,,,

Appendix:

MYSQL stored procedures use dynamic SQL to create multiple tables

Example 1: Create a table named k1k k2k k3k ........

 
 

  
  
mysql> delimiter //  
  
  
mysql> create procedure ppp (in i int)  
  
  
-> begin  
  
  
-> declare k int;  
  
  
-> set k=1;  
  
  
-> while k<i do  
  
  
-> set @t=k;  
  
  
-> set @tname=concat('k',@t,'k');  
  
  
-> set @dwhe='(id int,name varchar(255))';  
  
  
-> set @sql_text:=concat('create table ',@tname,@dwhe);  
  
  
-> prepare stmt from @sql_text;  
  
  
-> execute stmt;  
  
  
-> set kk=k+1;  
  
  
-> end while;  
  
  
-> end;  
  
  
-> //  
  
  
Query OK, 0 rows affected (0.00 sec)  
  
  
mysql> call ppp(6)//  
  
  
Query OK, 0 rows affected (0.86 sec) 
 
 

Example 2: query parameters as table names

 
 

  
  
mysql> create procedure pp (tname varchar(255))  
  
  
-> begin  
  
  
-> set @na=tname;  
  
  
-> set @sql_text:='select count(*) from ';  
  
  
-> set @sql_text:=concat(@sql_text,@na);  
  
  
-> prepare stmt from @sql_text;  
  
  
-> execute stmt;  
  
  
-> end; 
 
 

The above content is a description of the solution to the dynamic table of Oracle MySQL, hoping to help you in this regard.