一、表不存在时则创建
之前做项目实在是太赶了，很多东西都没记录。是时候补回来了
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列的值如下：
简单示例就是如此
