Mysql creates dynamic table names for the database through event timing
# check whether the event is enabled
Show variables like 'event_scheduler'
# enable event (preferably set in my.init, because it will change back to the default value OFF after restart)
Set global event_scheduler = on
# create an event (executed once a day from November 24)
Create EVENT eve_createTableON SCHEDULE EVERY 1 DAYSTARTS '2016-11-24 00 ON COMPLETION PRESERVE ENABLEDOCALL pro_createTable ()
Note:
1. ON COMPLETION PRESERVE ENABLE starts automatic execution as soon as this event is created
2. SCHEDULE EVERY 1 DAY STARTS '2016-11-24 00 00' is executed once a day from the specified time.
# create a stored procedure (dynamic table name)
CREATE PROCEDURE pro_createTable () BEGINDECLARE str VARCHAR (20000) Set str= CONCAT ('CREATE TABLE member_network_',DATE_FORMAT (now (),'% Y% m% d'),'(`id`bigint (20) NOT NULL AUTO_INCREMENT, `member_ id` bigint (20) NULL DEFAULT NULL, `host_ ip` varchar (50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `serv_ crc` varchar (2000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, `app_ crc` varchar (2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sync_ time` timestamp NULL DEFAULT NULL, `online_ time` datetime NULL DEFAULT NULL `type` varchar (500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `up_stream_ flux` varchar (50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `down_stream_ flux` varchar (50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `total_stream_ flux` varchar (50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `line_ no` varchar (50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `url` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL `action` varchar (50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sor_ port` varchar (50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sor_ port` varchar (50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `regionCode` varchar (50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `memo` varchar (500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `policy` varchar (100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL `dns` varchar CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `name` varchar CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `idcard` varchar, `client_ name` varchar (255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,PRIMARY KEY (`id`), INDEX `dept_ type` (`regionCode`, `serv_ crc`, `online_ time`, `name`, `type`) ENGINE=InnoDBDEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ciAUTO_INCREMENT=1ROW_FORMAT=COMPACT') SET @ sqlstr=str;PREPARE stmt from @ sqlstr;EXECUTE stmt;deallocate prepare stmt;END
Execution Times error because CONCAT splicing exceeds the maximum
# sql statement to view this parameter, modify and restart mysql
Show VARIABLES like'% max_allowed_packet%';set global max_allowed_packet = 25600
The day before yesterday, today, and the day after tomorrow of mysql
# 2016-12-09DATE_FORMAT (date_sub (curdate (), interval 1 day),'% Y% m% d') # 2016-12-10DATE_FORMAT (date_sub (curdate (), interval 0 day),'% Y% m% d') # 2016-12-11DATE_FORMAT (date_sub (curdate (), interval-1 day),'% Y% m% d')
