数据库的数据同步

 

以下为Mysql的示例，sql需要适当修改

 

   #region Mysql数据表的转移和备份

        public ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        /// <summary>
        /// 同步，超过intervalDays天的数据。从表数据表到表记录表
        /// </summary>
        /// <param name="TbName">需要同步的表名</param>
        /// <param name="MinRowCtn">数据表总数据行数必须大于设置数才进行数据同步</param>
        /// <param name="intervalDays">超时天数，默认为7</param>
        /// <param name="PrimaryKey">主键(自增长主键)名称，默认为Id</param>
        public void SynDataToHis(string TbName,string DataBaseName, int MinRowCtn = 5000, int intervalDays = 7, string PrimaryKey = "Id")
        {
            string Msg = $"SynData，表名：【{TbName}】,";
            int LimitRowsCtn = 2000;//每次处理数据条数
            string TbNameHistory = $"{TbName}_History";
            int ExcueCtn = 0;
            try
            {

                _db.Execute($" CREATE TABLE If Not Exists {DataBaseName}.{TbNameHistory} LIKE  {DataBaseName}.{TbName}  ; ");

                int RowsCtn = 1;

                while (RowsCtn > 0)
                {
                    ExcueCtn++;
                    if (ExcueCtn > 500) break; //执行五百次退出循环
                    long LeaveRowCtn= _db.FirstOrDefault<long>($" SELECT count(*) from {DataBaseName}.{TbName}  "); 
                    if (LeaveRowCtn <= MinRowCtn) break;//表剩余行数小于设置行数退出循环


                    List<long> lstOld = _db.Fetch<long>($"  SELECT * from {DataBaseName}.{TbName}  WHERE CreateDate <  date_add(NOW(), interval -{intervalDays.ToString()} day)  ORDER BY `{PrimaryKey}` LIMIT {LimitRowsCtn} ");

                    RowsCtn = lstOld.Count;

                    if (RowsCtn > 0)
                    {

                        Msg = $"SynData，表名：【{TbName}】,";

                        long MaxId = lstOld.Max();
                        try
                        {
                            List<DTc> lstOld2 = _db.Fetch<DTc>($"  select COLUMN_NAME, DATA_TYPE from information_schema.COLUMNS where table_name = '{TbName}' and TABLE_SCHEMA='{DataBaseName}'  ");

                            //防止ID主键重复导致的报错,加符号【`】防止关键字
                            string ColName = string.Join(",", lstOld2.Where(a => a.COLUMN_NAME.ToUpper() != PrimaryKey.ToUpper()).Select(a => "`" + a.COLUMN_NAME + "`"));

                            string sql = $"  INSERT INTO {DataBaseName}.{TbNameHistory} ({ColName}) SELECT {ColName} FROM {DataBaseName}.{TbName} WHERE `{PrimaryKey}` <= {MaxId} ";
                            var d1 = _db.Execute(sql);
                            Msg = Msg + "；新增成功：" + d1;

                            var d2 = _db.Execute($"   DELETE from {DataBaseName}.{TbName} WHERE `{PrimaryKey}` <=  {MaxId} ");
                            Msg = Msg + "；删除成功：" + d2;
                        }
                        catch (Exception ex)
                        {
                            Msg = Msg + "；同步异常：" + ex.Message;
                        }

                        log.Info(Msg);
                    }
                }


            }
            catch (Exception e)
            {
                log.ErrorFormat($"{Msg},异常:{0}", e.Message + e.StackTrace);
            }
        }


        public class DTc
        {
            public string COLUMN_NAME { get; set; }
            public string DATA_TYPE { get; set; }
        }


        #endregion