MySQL by date sub-table

one, when the table does not exist, create

Before doing the project is too rush, a lot of things are not recorded. It's time to make it back.

MySQL makes a big table, because to save history, so the amount of data is very large, the query is very slow. It does not take too long for cold data to be queried exactly. Now the implementation principle is extracted into a console applet.

First, create a simple database access class.

     Public Static classCommondao {Private StaticMysqlconnection conn =NewMysqlconnection (configurationmanager.appsettings["DB"]);//Create a connection        /// <summary>        ///execution of the Insert Update DELETE statement/// </summary>        /// <param name= "SQL" >SQL statements</param>        /// <returns>returns the number of affected rows</returns>         Public Static intExecutenonquerysql (stringSQL) {Mysqlcommand cmd=Conn.            CreateCommand (); Cmd.commandtext=SQL; if(Conn. State! =ConnectionState.Open) {conn.            Open (); }            intCount =cmd.            ExecuteNonQuery (); Conn.            Close (); returnCount; }    }

Since it is a minute table, write a timer and insert 5 of data into it every minute:

        Static voidMain (string[] args) {            //executes once per minute, inserting 5 dataTimer T =NewTimer (60000); T.enabled=true;//The arrival time is executed once, or it is continuously executedT.autoreset =true;//always executet.elapsed+ = (sender, EEA) = =            {                 for(inti =0; I <5; i++) {DateTime dt=DateTime.Now; stringTableName =" Person"+ dt. ToString ("mm"); stringsql ="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; //The 1146 code name is the error that the table does not exist.                        if(ex. Number = =1146)                        {                            //If the table does not exist, the table is created first                            stringSqlcreatetable ="CREATE TABLE"+ TableName +" like person";//to create a new table from a Template table, the benefit is that indexing, self-increment, etc. can be built at once .Commondao.executenonquerysql (sqlcreatetable);            }                    }                }            };        Console.readkey (); }

The first Person Template table is formatted as follows:

CREATE TABLE' Person ' (' Id ')int( One) not NULLauto_increment, ' Name 'varchar(255)DEFAULT NULL, ' Operatetime 'datetime DEFAULT NULL,  PRIMARY KEY(' Id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8;

　　

The table is created every minute and the data is added.

　　

second, the problem of cross-table query after the sub-table

After the table has a lot of problems to deal with, simply do two, maybe will have other problems.

1, the data in which table;

2, the data across the table, then you need to connect the table;

MySQL by date sub-table