一、表不存在时则创建
之前做项目实在是太赶了，很多东西都没记录。是时候补回来了
MySQL做一个大表，由于要存历史记录，所以数据量很大，查询很慢。恰好查询的时候，又不需要时间太久的冷数据。现在将其实现原理提取成一个控制台小程序。
首先，创建一个简单的数据库访问类。
public static class CommonDao
{
private static MySqlConnection conn = new MySqlConnection(ConfigurationManager.AppSettings["DB"]); //创建连接 /// <summary>
/// Insert Update Delete语句的执行
/// </summary>
/// <param name="SQL">SQL语句</param>
/// <returns>返回影响行数</returns>
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();
t.Enabled = true; //到达时间就执行一次，或者是持续执行
t.AutoReset = true; //一直执行 t.Elapsed += (sender, eea) =>
{
for (int i = ; i < ; 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 == )
{
//如果表不存在，则先创建这个表
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、数据跨表了，那么就需要表连接；
通常对这种大表，都肯定要加时间的了，所以要根据时间来判断查哪张表和如何表连接。
增加一个查询的方法：
/// <summary>
/// 根据SQL语句查询DataTable
/// </summary>
/// <param name="SQL">SQL语句</param>
/// <returns>返回DataTable</returns>
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 > )
{
dt = ds.Tables[];
}
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();
while(dt.ToString("yyyyMMddHHmm") != dtEnd.ToString("yyyyMMddHHmm"))
{
TableName += " UNION ALL SELECT * FROM person" + dt.ToString("yyyyMMddHHmm");
dt = dt.AddMinutes();
}
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[] + " " + dr[] + " " + dr[]);
}
结果如下：
很明显上面的代码暴露出了一个问题，ID重复了。
本处提供的可选解决方案是。
1、ID使用GUID。
2、自增数字的前面加上表名的日期前缀，如，如果你是按月分表，自增Id设置long类型，然后起止值设置为"201604000000000000000 + Id"。这样就不会重复啦。
自定数字的方式只需多执行以下一条SQL语句：
ALTER TABLE person201604211757 AUTO_INCREMENT=2016042100;
然后生成的ID列的值如下：
简单示例就是如此