Dynamic table creation and data table sharding In the MySQL Stored Procedure

BEGINDECLARE '@ I' int (11); DECLARE '@ siteCount' int (11); DECLARE '@ sqlstr 'varchar (2560); DECLARE' @ sqlinsert 'varchar (2560 ); // The preceding declared variable 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 the 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 the dynamically created new table SET '@ I' = '@ I' + 1; END WHILE; END

VeryMule focuses on creating its own online store