dataformat mysql 分钟 mysql按时间分表

转载

JAVA小侠影 2023-07-20 14:02:52

文章标签 dataformat mysql 分钟 mysql把表数据按日期 SQL 分表 sql 文章分类 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;

dataformat mysql 分钟 mysql按时间分表_分表

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

dataformat mysql 分钟 mysql按时间分表_mysql把表数据按日期_02

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

分表后有很多问题要处理，简单做两个，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]);
}

结果如下：

dataformat mysql 分钟 mysql按时间分表_sql_03