INSERT INTO table2 SELECT * FROM table1;
数据操作时可以使用这条语句使表1的数据全部拷贝到表2中；
鉴于可能现场的数据表数据特别的大（上亿条）的情况，执行这条语句可能使系统长时间的处于崩溃状态，可进行测试下；
可以使用程序多条进行拷贝，使其每次只拷贝多条，App.config配置文件如下，源、目的表名称，源、目的数据库连接；
<!--源表名称-->
<add key="FromSourceTable" value="history_signal_1"/>
<!--目的表名称-->
<add key="ToDestiTable" value="history_signal_2"/>
<!--查询ID间隔，即每次插入的条数-->
<add key="IdSelectInterval" value="100"/>
<!--需要同步的最大ID、最小ID，程序中断后可查看已保存的数据进行写入，从最大开始到最小依次同步-->
<add key="MinId" value="0"/>
<add key="MaxId" value="1000"/>
<!--源数据库连接-->
<add key="SrcConnectionString" value="server=192.168.222.108;port=3306;user=root;password=zdyl@123456;database=cms_v2_xitang;charset=utf8" />
<add key="SrcSqlType" value="MySQL" />
<!--目的数据库连接-->
<add key="DstConnectionString" value="server=192.168.222.108;port=3306;user=root;password=zdyl@123456;database=cms_v2_xitang;charset=utf8" />
<add key="DstSqlType" value="MySQL" />
数据库连接帮助类，就是普通的Dapper帮助类，
1 public class DBConnectManager 2 { 3 /// <summary> 4 /// 数据库连接 5 /// </summary> 6 static private IDbConnection IConnection { get; set; } 7 8 /// <summary> 9 /// 数据库类型 10 /// </summary> 11 static private SimpleCRUD.Dialect EnumDBType { get; set; }35 36 /// <summary> 37 /// 创建数据库连接 38 /// </summary> 39 /// <param name="connectionString"></param> 40 /// <param name="enumDBType"></param> 41 /// <returns></returns> 42 static public IDbConnection CreatDBConnect(string connectionString, SimpleCRUD.Dialect enumDBType) 43 { 44 switch (enumDBType) 45 { 46 case SimpleCRUD.Dialect.SQLServer: 47 IConnection = new SqlConnection(connectionString); 48 break; 49 case SimpleCRUD.Dialect.PostgreSQL: 50 IConnection = new NpgsqlConnection(connectionString); ; 51 break; 52 case SimpleCRUD.Dialect.MySQL: 53 IConnection = new MySqlConnection(connectionString); 54 break; 55 } 56 return IConnection; 57 } 58 59 /// <summary> 60 /// 获取数据库类型 61 /// </summary> 62 /// <returns></returns> 63 static public SimpleCRUD.Dialect GetDBType() 64 { 65 return EnumDBType; 66 } 67 }
数据库操作帮助类，只拷贝了一些需要的代码，
1 public class DbHelper 2 { 3 IDbConnection Connection { set; get; } 4 5 public DbHelper(IDbConnection connection) 6 { 7 Connection = connection; 8 SimpleCRUD.SetDialect(DBConnectManager.GetDBType()); 9 } 10 11 #region 执行SQL 12 13 /// <summary> 14 /// 执行SQL语句并返回结果 15 /// </summary> 16 /// <param name="sql"></param> 17 /// <returns></returns> 18 public IEnumerable<T> Query<T>(string sql, object whereCondition = null, IDbTransaction transaction = null) 19 { 20 try 21 { 22 return Connection.Query<T>(sql, whereCondition, transaction); 23 } 24 catch (Exception ex) 25 { 26 throw (ex); 27 } 28 } 29 30 #endregion 31 32 }
主程序，就是查询插入到新表中，
1 public class InsertSelectAll 2 { 3 #region 属性 4 5 private volatile static InsertSelectAll _instance = null; 6 private static readonly object lockHelper = new object(); 7 8 /// <summary> 9 /// 源表 10 /// </summary> 11 private string FromSourceTable { set; get; } 12 13 /// <summary> 14 /// 目的表 15 /// </summary> 16 private string ToDestiTable { set; get; } 17 18 /// <summary> 19 /// 表中最大的id值 20 /// </summary> 21 private int SqlMaxId { set; get; } 22 23 /// <summary> 24 /// 表中最小的id值 25 /// </summary> 26 private int SqlMinId { set; get; } 27 28 /// <summary> 29 /// 查询ID间隔 30 /// </summary> 31 private int IdSelectInterval { set; get; } 32 33 /// <summary> 34 /// Mysql连接字符串，源 35 /// </summary> 36 public string ConnectStringSrc { set; get; } 37 38 /// <summary> 39 /// 数据库帮助实体，源 40 /// </summary> 41 public DbHelper DbHelpSrc { set; get; } 42 43 /// <summary> 44 /// Mysql连接字符串，目的 45 /// </summary> 46 public string ConnectStringDst { set; get; } 47 48 /// <summary> 49 /// 数据库帮助实体，目的 50 /// </summary> 51 public DbHelper DbHelpDst { set; get; } 52 53 #endregion 54 55 #region 构造函数 56 57 /// <summary> 58 /// 构造 59 /// </summary> 60 private InsertSelectAll() 61 { 62 InitAttribute(); 63 64 new Task(SynTableTask).Start(); 65 } 66 67 /// <summary> 68 /// 单例 69 /// </summary> 70 /// <returns></returns> 71 public static InsertSelectAll GetInstance() 72 { 73 if (_instance == null) 74 { 75 lock (lockHelper) 76 { 77 if (_instance == null) 78 _instance = new InsertSelectAll(); 79 } 80 } 81 return _instance; 82 } 83 84 #endregion 85 86 #region 初始化 87 88 /// <summary> 89 /// 初始化表中最大的id值 90 /// </summary> 91 private void InitSqlMaxId() 92 { 93 try 94 { 95 //string sql = string.Format("SELECT MAX(id) FROM {0}", FromSourceTable); 96 //SqlMaxId = DbHelpSrc.Query<int>(sql).FirstOrDefault(); 97 98 //sql = string.Format("SELECT MIN(id) FROM {0}", FromSourceTable); 99 //SqlMinId = DbHelpSrc.Query<int>(sql).FirstOrDefault(); 100 101 SqlMaxId = int.Parse(ConfigurationManager.AppSettings["MaxId"]); 102 SqlMinId = int.Parse(ConfigurationManager.AppSettings["MinId"]); 103 104 LogEvent.LogInfo.InfoFormat("SqlMaxId = {0}, SqlMinId = {1}.", SqlMaxId, SqlMinId); 105 } 106 catch(Exception ex) 107 { 108 LogEvent.Loger.Fatal(ex.ToString()); 109 SqlMaxId = -1; 110 SqlMinId = -1; 111 } 112 } 113 114 /// <summary> 115 /// 初始化属性 116 /// </summary> 117 private void InitAttribute() 118 { 119 try 120 { 121 FromSourceTable = ConfigurationManager.AppSettings["FromSourceTable"]; 122 ToDestiTable = ConfigurationManager.AppSettings["ToDestiTable"]; 123 IdSelectInterval = int.Parse(ConfigurationManager.AppSettings["IdSelectInterval"]); 124 125 InitDBHelper(); 126 InitSqlMaxId(); 127 } 128 catch (Exception ex) 129 { 130 LogEvent.Loger.Fatal(ex.ToString()); 131 } 132 } 133 134 /// <summary> 135 /// 初始化数据连接 136 /// </summary> 137 private void InitDBHelper() 138 { 139 try 140 { 141 SimpleCRUD.Dialect enumDBTypeSrc = (SimpleCRUD.Dialect)Enum.Parse(typeof(SimpleCRUD.Dialect), ConfigurationManager.AppSettings["SrcSqlType"].ToString()); 142 ConnectStringSrc = ConfigurationManager.AppSettings["SrcConnectionString"].ToString(); 143 IDbConnection IConnectionSrc = DBConnectManager.CreatDBConnect(ConnectStringSrc, enumDBTypeSrc); 144 DbHelpSrc = new DbHelper(IConnectionSrc); 145 146 SimpleCRUD.Dialect enumDBTypeDst = (SimpleCRUD.Dialect)Enum.Parse(typeof(SimpleCRUD.Dialect), ConfigurationManager.AppSettings["DstSqlType"].ToString()); 147 ConnectStringDst = ConfigurationManager.AppSettings["DstConnectionString"].ToString(); 148 IDbConnection IConnectionDst = DBConnectManager.CreatDBConnect(ConnectStringDst, enumDBTypeDst); 149 DbHelpDst = new DbHelper(IConnectionDst); 150 } 151 catch (Exception ex) 152 { 153 LogEvent.Loger.Fatal(ex.ToString()); 154 } 155 } 156 157 #endregion 158 159 #region 公共函数 160 161 /// <summary> 162 /// 同步表数据 163 /// </summary> 164 private void SynTableTask() 165 { 166 if (SqlMaxId == -1 || SqlMinId == -1) 167 { 168 LogEvent.Loger.Fatal("SqlMaxId == -1 || SqlMinId == -1, return."); 169 return; 170 } 171 172 int maxId = SqlMaxId + 1; 173 int minId = maxId - IdSelectInterval; 174 175 while (true) 176 { 177 Thread.Sleep(1000); 178 if(maxId <= SqlMinId) 179 { 180 LogEvent.LogInfo.InfoFormat("maxId <= SqlMinId, return."); 181 return; 182 } 183 try 184 { 185 //string sql = string.Format("INSERT INTO {0} (SELECT * FROM {1} WHERE id >= {2} AND id < {3})", ToDestiTable, FromSourceTable, minId, maxId); 186 187 string sqlSrc = string.Format("select * from {0} where id >= {1} and id < {2}", FromSourceTable, minId, maxId); 188 var srcVar = DbHelpSrc.Query<object>(sqlSrc).ToList(); 189 190 foreach (object signal in srcVar) 191 { 192 string st = signal.ToString(); 193 194 List<int> indexS = GetAllIndex(st, ‘\‘‘); 195 if(indexS == null || (indexS.Count % 2) != 0) 196 { 197 continue; 198 } 199 200 List<string> infoS = GetAllInfo(st, indexS); 201 202 string insertSql = string.Format("insert into {0} values(", ToDestiTable); 203 foreach (string str in infoS) 204 { 205 // 拼接成sql 206 insertSql = string.Format("{0} {1},", insertSql, str); 207 } 208 // 去除最后个一个‘，’ 209 insertSql = insertSql.TrimEnd(‘,‘); 210 insertSql = string.Format("{0});", insertSql); 211 DbHelpDst.ExecSql(insertSql); 212 } 213 214 LogEvent.LogInfo.InfoFormat("minId = {0}, maxId = {1}.", minId, maxId); 215 216 maxId = minId; 217 minId -= IdSelectInterval; 218 219 // IdSelectInterval = 10, maxid = 20, minid = 10, SqlMinId = 10, [10, 20) -> maxid = 10, return. 220 // IdSelectInterval = 12, maxid = 20, minid = 8, minid = 10, SqlMinId = 10, [10, 20) -> maxid = 10, return. 221 // IdSelectInterval = 8, maxid = 20, minid = 12, SqlMinId = 10, [12, 20) -> maxid = 12, minid = 4, minid = 10 ->[10.12) 222 if (minId < SqlMinId) 223 { 224 minId = SqlMinId; 225 } 226 } 227 catch (Exception ex) 228 { 229 maxId = minId; 230 minId -= IdSelectInterval; 231 232 LogEvent.Loger.Fatal(ex.ToString()); 233 Thread.Sleep(1000); 234 } 235 } 236 } 237 238 private List<string> GetAllInfo(string str, List<int> indexS) 239 { 240 List<string> result = new List<string>(); 241 242 for(int i = 0; i < indexS.Count; i += 2) 243 { 244 result.Add(str.Substring(indexS[i], indexS[i + 1] - indexS[i] + 1)); 245 } 246 247 return result; 248 } 249 250 /// <summary> 251 /// 查找指定字符的全部索引 252 /// </summary> 253 /// <param name="str"></param> 254 /// <param name="ch"></param> 255 /// <returns></returns> 256 private List<int> GetAllIndex(string str, char ch) 257 { 258 List<int> result = new List<int>(); 259 260 result.Add(str.IndexOf(ch)); 261 262 for (int i = str.IndexOf(ch) + 1; i <= str.LastIndexOf(ch); i++) 263 { 265 i = str.IndexOf(ch, i); 266 result.Add(i); 267 } 268 269 return result; 270 } 271 272 #endregion 273 274 }