MySQL 按日期分表 |
您所在的位置：网站首页 › mysql按照日期创建表分区 › MySQL 按日期分表 |
MySQL 按日期分表
|
一、表不存在时则创建
之前做项目实在是太赶了，很多东西都没记录。是时候补回来了 MySQL做一个大表，由于要存历史记录，所以数据量很大，查询很慢。恰好查询的时候，又不需要时间太久的冷数据。现在将其实现原理提取成一个控制台小程序。 首先，创建一个简单的数据库访问类。 
public static class CommonDao
{
private static MySqlConnection conn = new MySqlConnection(ConfigurationManager.AppSettings["DB"]); //创建连接
///
/// Insert Update Delete语句的执行
///
/// SQL语句
/// 返回影响行数
public static int ExecuteNonQuerySQL(string SQL)
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = SQL;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
int Count = cmd.ExecuteNonQuery();
conn.Close();
return Count;
}
}
由于是按分钟分表，所以写个定时器，每一分钟往里面插入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 as MySqlException;
//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 NULL AUTO_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(string SQL)
{
DataSet ds = new DataSet();
MySqlDataAdapter Msda = new MySqlDataAdapter(SQL, conn);
Msda.Fill(ds);
DataTable dt = new DataTable();
if (ds.Tables.Count > 0)
{
dt = ds.Tables[0];
}
return dt;
}
数据定位的方法：
public static string GetTableName(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";
return TableName;
}
}
测试查询： 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 in dt.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列的值如下：
简单示例就是如此 |
【本文地址】
今日新闻 |
推荐新闻 |