文档章节

mysql 每月自动创建表结构

heavan2010
 heavan2010
发布于 2014/03/06 16:21
字数 705
阅读 722
收藏 2

业务数据量比较大的时候,可能对数据按月建表。随之而来的是每月都需要创建对应表结构。如果忘记了,恐怕对业务影响比较大吧!这种表结构的特点:表字段全部相同,索引也是一致的,表的名称随着月份自动变化。


步骤:    

    1、创建存储过程

    2、创建定时任务(需要开启全局的event_scheduler)

DELIMITER $$


DROP PROCEDURE IF EXISTS `pro_autocre_month_table`$$

CREATE DEFINER=`root`@`%` PROCEDURE `pro_autocre_month_table`()
BEGIN
	DECLARE old_table_name VARCHAR(128);
	DECLARE new_table_name VARCHAR(128);
	DECLARE done INT DEFAULT 0;
	
	DECLARE table_cursor CURSOR FOR SELECT TABLE_NAME FROM `information_schema`.`TABLES` WHERE TABLE_SCHEMA = '****' AND TABLE_NAME REGEXP DATE_FORMAT(CURDATE(), '%Y%m');
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
	
	OPEN table_cursor;   
	REPEAT
		
		FETCH table_cursor INTO old_table_name;
		IF NOT done THEN 
			
			SELECT  REPLACE(old_table_name,DATE_FORMAT(CURDATE(), '%Y%m'),DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 1 MONTH), '%Y%m')) INTO new_table_name;
			SET @sqlCmd = CONCAT('create table if not exists `',new_table_name,'` like `' , old_table_name,'`');
			PREPARE preStmt FROM @sqlCmd;  
			EXECUTE preStmt;
		END IF ;
	UNTIL done END REPEAT;
	CLOSE table_cursor;
    END$$

DELIMITER ;
-- ---------------------------------------------------------------------
DELIMITER $$

SET GLOBAL event_scheduler = ON$$   

CREATE	/*[DEFINER = { user | CURRENT_USER }]*/	EVENT `test`.`auto_create_table`

ON SCHEDULE
	ON SCHEDULE EVERY 1 WEEK STARTS '2014-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE 
	DO BEGIN
		CALL pro_autocre_month_table();
	END$$

DELIMITER ;

备注:    

    1、请将“ TABLE_SCHEMA = '****'”中的"****"修改为表所在的数据库的名称

    2、该sql 会对数据库下表名称为:201401这种类型的表结构有效。其它类型的结构可参考下面的说明:

    

Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th1st2nd3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal % character
%x x, for any x not listed above

    3、计划任务、存储过程、游标 不熟悉的,请参考官方文档。

    4、如果怕定时任务执行失败,可以每半个月执行一次。这样能防止第一次执行失败!

    5、oracle、sql server 也有对应的管理数据库,可通过类似方法创建表结构

    6、请确保用户有访问information_schema数据库的权限



© 著作权归作者所有

heavan2010
粉丝 0
博文 22
码字总数 5291
作品 0
昌平
私信 提问
Mysql用存储过程和事件每月定时创建一张数据库表

业务需求,把app用户开机写入一张日志表appopenlog。 上线7个月来,有74万条记录了。 现考虑要分库分表了。每个月初创建一张以appopenlog为前缀,日期年月为后缀的数据库表,比如:appopenlo...

phpervip
2018/07/10
0
0
使用Hibernate的 hbm2ddl.auto 属性自动建表

hbm2ddl.auto 是Hibernate自动建表的核心属性有四个可选值,create,create-drop,update,validate; 四个值分别表示如下: create:启动时删数据库中的表,然后创建,退出时不删除数据表 ...

kimyeongnam
2015/12/22
187
3
hive与mysql的数据分区的异同

数据分区是什么? 数据库分区是一种物理数据库设计技术,DBA和数据库建模人员对其相当熟悉。 分区把一大块数据分成了n小块,这样查询的时候很快定位到某一小块上,在小块中寻址要快很多; 多大...

婷瑄
2015/03/26
868
0
MySQL专题9之MySQL索引、MySQL临时表、MySQL复制表

1、MySQL索引 - MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 - 打个比方,如果合理的设计并使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索...

极客微信条
2017/11/19
0
0
Navicat for MySQL 有哪些常用功能

Navicat for MySQL 是一套管理和开发 MySQL 或 MariaDB 的理想解决方案,支持单一程序,可同时连接到 MySQL和MariaDB。这个功能齐备的前端软件为数据库管理、开发和维护提供了直观而强大的图...

Navicat数据库管理工具
2016/06/02
304
1

没有更多内容

加载失败,请刷新页面

加载更多

CentOS7.6中安装使用fcitx框架

内容目录 一、为什么要使用fcitx?二、安装fcitx框架三、安装搜狗输入法 一、为什么要使用fcitx? Gnome3桌面自带的输入法框架为ibus,而在使用ibus时会时不时出现卡顿无法输入的现象。 搜狗和...

技术训练营
昨天
5
0
《Designing.Data-Intensive.Applications》笔记 四

第九章 一致性与共识 分布式系统最重要的的抽象之一是共识(consensus):让所有的节点对某件事达成一致。 最终一致性(eventual consistency)只提供较弱的保证,需要探索更高的一致性保证(stro...

丰田破产标志
昨天
8
0
docker 使用mysql

1, 进入容器 比如 myslq1 里面进行操作 docker exec -it mysql1 /bin/bash 2. 退出 容器 交互: exit 3. mysql 启动在容器里面,并且 可以本地连接mysql docker run --name mysql1 --env MY...

之渊
昨天
10
0
python数据结构

1、字符串及其方法(案例来自Python-100-Days) def main(): str1 = 'hello, world!' # 通过len函数计算字符串的长度 print(len(str1)) # 13 # 获得字符串首字母大写的...

huijue
昨天
6
0
PHP+Ajax微信手机端九宫格抽奖实例

PHP+Ajax结合lottery.js制作的一款微信手机端九宫格抽奖实例,抽奖完成后有收货地址添加表单出现。支持可以设置中奖概率等。 奖品列表 <div class="lottery_list clearfix" id="lottery"> ......

ymkjs1990
昨天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部