mysql年月分表_MySQL 按日期分表

一、表不存在时则创建

之前做项目实在是太赶了，很多东西都没记录。是时候补回来了

MySQL做一个大表，由于要存历史记录，所以数据量很大，查询很慢。恰好查询的时候，又不需要时间太久的冷数据。现在将其实现原理提取成一个控制台小程序。

首先，创建一个简单的数据库访问类。

public static classCommonDao

{private static MySqlConnection conn = new MySqlConnection(ConfigurationManager.AppSettings["DB"]); //创建连接

///

///Insert Update Delete语句的执行///

/// SQL语句

/// 返回影响行数

public static int ExecuteNonQuerySQL(stringSQL)

{

MySqlCommand cmd=conn.CreateCommand();

cmd.CommandText=SQL;if (conn.State !=ConnectionState.Open)

{

conn.Open();

}int Count =cmd.ExecuteNonQuery();

conn.Close();returnCount;

}

}

由于是按分钟分表，所以写个定时器，每一分钟往里面插入5条数据：

static void Main(string[] args)

{//每分钟执行一次，插入5条数据

Timer t = new Timer(60000);

t.Enabled= true; //到达时间就执行一次，或者是持续执行

t.AutoReset = true; //一直执行

t.Elapsed+= (sender, eea) =>{for (int i = 0; i < 5; i++)

{

DateTime dt=DateTime.Now;string TableName = "person" + dt.ToString("yyyyMMddHHmm");string sql = "INSERT INTO" + TableName + "VALUES(null,'superman','" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "')";try{

CommonDao.ExecuteNonQuerySQL(sql);

}catch(Exception e)

{

MySqlException ex= e asMySqlException;//1146的代号就是表不存在的错误

if (ex.Number == 1146)

{//如果表不存在，则先创建这个表

string SQLCreateTable = "CREATE TABLE" + TableName + "LIKE person"; //从模板表来创建新表，这样的好处是索引、自增什么的可以一次性建好

CommonDao.ExecuteNonQuerySQL(SQLCreateTable);

}

}

}

};

Console.ReadKey();

}

第一张Person模板表的格式如下：

CREATE TABLE`person` (

`Id`int(11) NOT NULLAUTO_INCREMENT,

`Name`varchar(255) DEFAULT NULL,

`OperateTime`datetime DEFAULT NULL,PRIMARY KEY(`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

于是就每分钟都会创建表并添加数据。

二、分表后跨表查询的问题

分表后有很多问题要处理，简单做两个，maybe会有其他问题。

1、数据在哪个表里；

2、数据跨表了，那么就需要表连接；

通常对这种大表，都肯定要加时间的了，所以要根据时间来判断查哪张表和如何表连接。

增加一个查询的方法：

///

///根据SQL语句查询DataTable///

/// SQL语句

/// 返回DataTable

public static DataTable GetDataTableBySQL(stringSQL)

{

DataSet ds= newDataSet();

MySqlDataAdapter Msda= newMySqlDataAdapter(SQL, conn);

Msda.Fill(ds);

DataTable dt= newDataTable();if (ds.Tables.Count > 0)

{

dt= ds.Tables[0];

}returndt;

}

数据定位的方法：

public static stringGetTableName(DateTime dtStart, DateTime dtEnd)

{if(dtStart.ToString("yyyyMMddHHmm") == dtEnd.ToString("yyyyMMddHHmm"))

{return "person" + dtStart.ToString("yyyyMMddHHmm");

}else{string TableName = "(SELECT * FROM person" + dtStart.ToString("yyyyMMddHHmm");

DateTime dt= dtStart.AddMinutes(1);while(dt.ToString("yyyyMMddHHmm") != dtEnd.ToString("yyyyMMddHHmm"))

{

TableName+= "UNION ALL SELECT * FROM person" + dt.ToString("yyyyMMddHHmm");

dt= dt.AddMinutes(1);

}

TableName+= "UNION ALL SELECT * FROM person" + dt.ToString("yyyyMMddHHmm") + ") AS T";returnTableName;

}

}

测试查询：

string sql = "SELECT * FROM" + GetTableName(Convert.ToDateTime("2016-04-21 17:54:00"),Convert.ToDateTime("2016-04-21 17:56:00"));

DataTable dt=CommonDao.GetDataTableBySQL(sql);foreach(DataRow dr indt.Rows)

{

Console.WriteLine(dr[0] + " " + dr[1] + " " + dr[2]);

}

结果如下：

很明显上面的代码暴露出了一个问题，ID重复了。

本处提供的可选解决方案是。

1、ID使用GUID。

2、自增数字的前面加上表名的日期前缀，如，如果你是按月分表，自增Id设置long类型，然后起止值设置为"201604000000000000000 + Id"。这样就不会重复啦。

自定数字的方式只需多执行以下一条SQL语句：

ALTER TABLE person201604211757 AUTO_INCREMENT=2016042100;

然后生成的ID列的值如下：

简单示例就是如此