Mysql如何自动每日建表？

只能说一下思路，你按照步骤去网上搜答案

一、新建事件每天调用存储过程

二、存储过程里面建表

1、获取当前时间，转换字符串

2、拼接sql语句建表

我正好有楼主类似的需求，每个季度为几个表增加一个分区，表的基本名称是在一个叫设备类型的表里，每天计划执行一个过程，在过程里从系统表中判断是否已经创建了相关的分区，如果没创建就创建它楼主可以参考一下，记得在my.ini 文件里配置event_scheduler=on

/**定时每天检查各个设备类型的历史数据表，如果历史数据表的所在分区已经接近当前日期，则为此设备类型追加分区*/ -- 得到按月分区的日期值delimiter drop function if exists fnGetPartitionDateForMonthdelimiter create function fnGetPartitionDateForMonth() returns INTbegindeclare v_today datetime default date_add(now(), INTERVAL 2 month) return year(v_today) * 100 + month(v_today)end-- 得到按季度分区的日期值delimiter drop function if exists fnGetPartitionDateForQuarterdelimiter create function fnGetPartitionDateForQuarter() returns intbegindeclare v_today datetime default date_add(now(), interval 3 month) declare v_month intset v_month = month(v_today) if v_month = 1 or v_month = 2 or v_month = 3 then set v_today = DATE_ADD(v_today, INTERVAL (4 - v_month) month) elseif v_month = 4 or v_month = 5 or v_month = 6 THENset v_today = DATE_ADD(v_today, INTERVAL (7 - v_month) month) elseif v_month = 7 or v_month = 8 or v_month = 9 THENset v_today = date_add(v_today, INTERVAL (10 - v_month) month) ELSEset v_today = date_add(v_today, INTERVAL (13 - v_month) month) end ifreturn year(v_today) * 100 + month(v_today)end-- 得到按半年分区的日期值delimiter drop function if exists fnGetPartitionDateForHalfYeardelimiter create function fnGetPartitionDateForHalfYear() returns intbegindeclare v_today datetime default date_add(now(), interval 6 month) declare v_month intset v_month = month(v_today)if v_month <= 6 THENset v_today = date_add(v_today, INTERVAL (7 - v_month) month) elseset v_today = DATE_ADD(v_today, INTERVAL (13 - v_month) month) end ifreturn year(v_today) * 100 + month(v_today)end-- 维护按年分区delimiter drop function if exists fnGetPartitionDateForYeardelimiter create function fnGetPartitionDateForYear() returns intbegindeclare v_today datetime default date_add(now(), INTERVAL 2 year) return year(v_today) * 100end delimiter drop procedure if exists spMaintainPartitionsdelimiter create procedure spMaintainPartitions()BEGIN declare v_sql varchar(2000) declare v_cnt int declare v_deviceTypeId int declare v_tablename varchar(50) declare v_tablename_analog varchar(50) declare v_tablename_digital varchar(50) declare v_partitionType int declare v_fileDir varchar(1000) declare v_tablenames varchar(1000) default '' declare v_intDate int declare v_partitionName varchar(100) declare done int default 0 declare c_deviceType cursor for select Id, TableName, PartitionType, DataFileDirfrom tbDeviceType where Generated = 1 declare continue handler for not found set done = 1insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)Values(Now(), 'spMaintainPartitions start......')open c_deviceType deviceType_loop: LOOP fetch c_deviceType into v_deviceTypeId, v_tablename, v_partitionType, v_fileDirset v_fileDir = replace(v_fileDir, '\\', '/') if locate(':', v_fileDir) >0 and locate(':/', v_fileDir) = 0 thenset v_fileDir = replace(v_fileDir, ':', ':/') end ifif done = 1 then leave deviceType_loop end ifset v_intDate = null if v_partitionType = 1 then set v_intDate = fnGetPartitionDateForMonth() ELSEIF v_partitionType = 2 THENset v_intDate = fnGetPartitionDateForQuarter() ELSEIF v_partitionType = 3 then set v_intDate = fnGetPartitionDateForHalfYear() elseif v_partitionType = 4 then set v_intDate = fnGetPartitionDateForYear() end ifif v_intDate is null theninsert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`) values(Now(), Concat('DeviceTypeId = ', cast(v_deviceTypeId As char(10)), ' did not define paritition schedule')) else set v_partitionName = concat('p', cast(v_intDate as char(6)))-- 模拟量表set v_tablename_analog = concat(v_tablename, '_Analog') select count(*) into v_cntfrom information_schema.`TABLES` where `TABLE_SCHEMA` = database() and `table_name` = v_tablename_analog if v_cnt >0 then select count(*) into v_cntfrom information_schema.`PARTITIONS` where TABLE_SCHEMA = database() and table_name = v_tablename_analog and partition_name = v_partitionNameif v_cnt = 0 thenset v_sql = CONCAT('alter table ', v_tablename_analog, ' add partition (partition ', v_partitionName, ' values less than (', cast(v_intDate as char(6)), ') data directory = ''', v_fileDir, ''' index directory = ''', v_fileDir , ''')') insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)Values(Now(), concat('sql = ''', v_sql))set @sql = v_sql prepare cmd from @sql execute cmd deallocate prepare cmdinsert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`) values(Now(), concat('execute complete: ', v_sql)) end if end if-- 数字量表set v_tablename_digital = concat(v_tablename, '_Digital') select count(*) into v_cntfrom information_schema.`TABLES` where `TABLE_SCHEMA` = database() and `table_name` = v_tablename_digitalif v_cnt >0 then select count(*) into v_cntfrom information_schema.`PARTITIONS`where TABLE_SCHEMA = database() and table_name = v_tablename_digital and partition_name = v_partitionNameif v_cnt = 0 then set v_sql = CONCAT('alter table ', v_tablename_digital, ' add partition (partition ', v_partitionName, ' values less than (', cast(v_intDate as char(6)), ') data directory = ''', v_fileDir, ''' index directory = ''', v_fileDir , ''')')insert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`)Values(Now(), concat('sql = ''', v_sql))set @sql = v_sql prepare cmd from @sql execute cmd deallocate prepare cmdinsert into tbPartitionMaintainLog(`CreatedDateTime`, `LogContent`) values(Now(), concat('execute complete: ', v_sql))end if end ifend ifend loop deviceType_loop close c_deviceTypeENDdelimiter drop event if exists e_DataPartitionMaintaincreate event e_DataPartitionMaintainon SCHEDULE every 60 Secondon completion PRESERVEdo call spMaintainPartitions()set global event_scheduler = on

---