MySQL dynamically creates a table and stores data in table shards. For more information, see.
MySQL dynamically creates a table and stores data in table shards. For more information, see.
The Code is as follows:
BEGIN
DECLARE '@ I' int (11 );
DECLARE '@ siteCount' int (11 );
DECLARE '@ sqlstr' VARCHAR (2560 );
DECLARE '@ sqlinsert' VARCHAR (2560); // DECLARE the variable above
Select count (0) into '@ siteCount' FROM tbl_base_site; // calculates the total number of records in the tbl_base_site table.
Set '@ I' = 1;
WHILE ('@ I'-1) * 300 <'@ siteCount' DO // while LOOP execution
SET @ sqlstr = CONCAT ('create TABLE tbl_base_syslog ',' @ I ',' (syslog_id INT (11) AUTO_INCREMENT PRIMARY KEY,
Create_user VARCHAR (32 ),
Description text,
Create_time datetime,
Site_id INT (11 ),
Ip VARCHAR (64 ),
Version_id SMALLINT (2 ),
Module_identity VARCHAR (64 ),
Right_name VARCHAR (64)
)');
Prepare stmt from @ sqlstr;
Execute stmt;
// The above implementation dynamically creates a table
SET @ sqlinsert = CONCAT ('insert INTO tbl_base_syslog ',' @ I ','(
Syslog_id,
Create_user,
Description,
Create_time,
Site_id,
Ip,
Version_id,
Module_identity,
Right_name)
SELECT syslog_id,
Create_user,
Description,
Create_time,
Site_id,
Ip,
Version_id,
Module_identity,
Right_name
FROM tbl_base_syslog
WHERE site_id IN (select tbs. site_id from (select site_id from tbl_base_site limit ',' @ I '* 300,', 300) as tbs)
Order by syslog_id ');
Prepare stmt from @ sqlinsert;
Execute stmt;
// The above implementation inserts a query record from a table into a dynamically created new table
SET '@ I' = '@ I' + 1;
End while;
END
