数据迁移

圆圆汇数据迁移流程

圆圆汇数据迁移流程

Posted by YANKEBIN on December 17, 2020

一、数据迁移流程

1.用户数据迁移流程

1.1 biz_user表数据整合

  1. 在千千建好存储圆圆汇biz_user表数据的备份表biz_user_yyt_back

    create table biz_user_yyt_back ( id int auto_increment primary key, oldId int default 0 not null, account varchar(50) not null, pwd varchar(50) not null, name varchar(50) not null, createDate timestamp default CURRENT_TIMESTAMP not null, isReservation int default 0 not null comment ‘0：非预约类型商家 1：预约类型商家（不含今日预约、不含批量核销）

    2：预约类型商家（含今日预约、不含批量核销）

    3：非预约类型商家（含批量核销）

    4：预约类型商家（含今日预约、批量核销）

    5：预约类型商家（不含今日预约、不含批量核销、不含确认列表）’, type int default 0 not null comment ‘0 ：非发货商家（不含快递 信息录入）

    1：渠道商复制产品链接 2：超级权限商家（技术测试用、基本已弃用）

    3：发货商家（含快递信息录入） 4.分销商’, isAllot int default 1 null comment ‘0:打开预约确认 1:关闭预约确认’, showCustomer int default 0 null comment ‘核销显示用户信息’, bookingShowCode int default 0 null comment ‘库存管理查看预约是否显示电子码’, userType int(1) default 0 null comment ‘0:分店 1：总店 2：管理员’, contactPhone varchar(50) null comment ‘联系人电话’, contactName varchar(50) default ‘’ null comment ‘ 联系人姓名’, administrativeDivisionId int default 0 null comment ‘省市区ID’, operatorUserId int default 0 null comment ‘业务员userId’, operatorUserName varchar(50) default ‘’ null comment ‘业务员姓名’, status int default 1 null comment ‘1正常 0封禁’, extraType int default 0 null comment ‘0为千千,1为圆圆’ ) charset = utf8;

    create index ix_bizuser_account on biz_user_yyt_back (account);

    create index ix_bizuser_isreservation on biz_user_yyt_back (isReservation);

    create index ix_bizuser_name on biz_user_yyt_back (name);

    create index ix_bizuser_type on biz_user_yyt_back (type);

  2. 先导出圆圆汇biz_user表数据，先在千千插入id为160000的占位数据，让千千后面的数据都大于160000，然后我们在千千的空白段id区域（155000-160000）插入圆圆汇的数据，其他数据不变

    select id + 155000 id, id oldId, concat(‘yyt -‘, account) account, pwd, name, createDate, isReservation, type, isAllot, showCustomer, bookingShowCode, userType from biz_user; – 导出sql insert文件

把圆圆汇导出的biz_user表数据，插入千千的备份表biz_user_yyt_back，以便biz_product和biz_order等表更换bizUid、completeBizUid。

这里有个坑，导出的数据不带表名，要拷贝到编辑器里填上表名（`` 换成biz_user_yyt_back），还要看数据是否有双引号。

1.2 com_user、user表数据整合

  1. 在千千建好存储圆圆汇com_user、user表数据的备份表com_user_yyt_back、user_yyt_back

      create table com_user_yyt_back
  (
  id                       int auto_increment
      primary key,
  phoneNumber              varchar(50) collate utf8mb4_unicode_ci                            not 			null,
  name                     varchar(50) collate utf8mb4_unicode_ci                            not null,
  nickName                 varchar(200) collate utf8mb4_unicode_ci default ''             not null,
  headimgurl               varchar(500) collate utf8mb4_unicode_ci default ''             not null,
  status                   int                                     default 1                 not null,
  companyId                int                                     default 0                 not null,
  dept                     varchar(50)                                                       null,
  position                 varchar(50)                                                       null,
  jobNoIncr                int                                     default 0                 not null,
  jobNo                    varchar(20)                                                       null,
  idCard                   varchar(50)                                                       null,
  race                     varchar(32)                                                       null,
  gender                   int(4)                                                            null,
  birthday                 datetime                                                          null,
  address                  varchar(200)                                                      null,
  wechatId                 varchar(32)                                                       null,
  probationMinimumSalary   int                                                               null,
  officialMinimumSalary    int                                                               null,
  remind                   int                                     default 1                 null,
  emergencyContactPhone    varchar(32)                                                       null,
  emergencyContactName     varchar(32)                                                       null,
  emergencyContactRelation varchar(32)                                                       null,
  entryDate                timestamp                                                         null,
  createDate               timestamp                               default CURRENT_TIMESTAMP not 			null,
  sort                     int                                     default 0                 not null,
  alias                    varchar(32)                                                       null,
  ddUserId                 varchar(31)                                                       null,
  ddDeptId                 varchar(50)                                                       null,
  newDdDeptId              varchar(50)                                                       null,
  readStatus               tinyint                                 default 0                 null,
  bankId                   varchar(100)                                                      null,
  bankName                 varchar(200)                                                      null,
  bankUserName             varchar(200)                                                      null   )
  charset = utf8;

    create index ix_com_user_company on com_user_yyt_back (companyId);

    create index ix_com_user_name on com_user_yyt_back (name);

    create index ix_com_user_phone on com_user_yyt_back (phoneNumber);

    create index ix_com_user_status on com_user_yyt_back (status);

user_yyt_back

create table user_yyt_back
(
uid           int(11) unsigned auto_increment
    primary key,
userId        int          default 0                 not null,
nickName      varchar(50)                            null,
phoneNumber   varchar(20)                            null,
password      varchar(50)                            null,
maxMoney      int          default 0                 not null,
headIconUrl   varchar(100) default 'http://cdn.lianlianlvyou.com/app/crm_head_defuat.png' null,
status        int          default 1                 not null,
groupId       int          default 0                 not null,
deptId        int          default 0                 not null,
level         int                                    null,
superiorId    int                                    null,
joinDate      timestamp                              null,
leaveDate     timestamp                              null,
baseSalary    int                                    null,
serialNumber  varchar(11)                            null,
type          int          default 1                 not null,
openid        varchar(32)  default ''             not null,
unionid       varchar(32)  default ''             not null,
createDate    timestamp    default CURRENT_TIMESTAMP null,
statisticType int          default 1                 not null
)
charset = utf8;

create index ix_user_status
on user_yyt_back (status);

  1. 先导出圆圆汇com_user、user表数据

    select * from com_user;– 导出sql insert文件 select *from user;– 导出sql insert文件

把圆圆汇导出的com_user、user表数据，插入千千的备份表com_user_yyt_back、user_yyt_back，以便 biz_product等表更换userId. 导入sql insert文件插入千千！！！！可能需要核对两边的电话号码是否一致！！！！

这里有个坑，导出的数据表名要拷贝到编辑器里填上表名（com_user 换成com_user_yyt_back,user换成user_yyt_back)

###1.3 agent_user、wx_user相关数据都是公用的，无需整合，只有部分扩展数据需要整合

  1. wx_user_address 整合，先查出圆圆汇的数据，去掉id直接插入千千(最好还是在千千预留一个id空白段155000-160000)——-已处理

    select id+155000 id, payOpenId, name, phoneNumber, province, provinceId, provinceCode, city, cityId, cityCode, region, regionId, regionCode, addressDetail, enable, defaultSelect, tag, createTime from wx_user_address;– 导出sql insert文件

导入sql insert文件插入千千。

##2.产品数据整合

###2.1 biz_product表数据整合

  1. 为了保险起见，先在千千数据库创建圆圆汇产品数据备份表biz_product_yyt_back

    create table biz_product_yyt_back ( id int auto_increment primary key, bizUserId int default 0 not null, locationId int default 1 not null, name varchar(200) default ‘’ not null, onlyName varchar(200) default ‘’ not null, title varchar(500) default ‘’ not null, posterUrl varchar(100) default ‘’ not null, shareText varchar(200) collate utf8mb4_unicode_ci default ‘’ not null, shareNine int(2) default 0 not null, shareImg varchar(200) collate utf8mb4_unicode_ci default ‘’ not null, faceImg varchar(200) default ‘’ not null, salePrice int not null, originPrice int not null, itemMoney int default 0 null, itemStock int default 0 null, baseAmount int default 0 not null, saleAmount int not null, stockAmount int not null, codeType int default -1 not null, codeDelay int default 0 null, codeTime datetime null, isShowEndTime int default 0 not null, eCommerce int default 0 not null, beginTime datetime null, endTime datetime null, validBeginDate datetime null, validEndDate datetime null, bookingBeginDate datetime null, address varchar(200) default ‘’ not null, tel varchar(200) default ‘’ not null, booking int default 1 not null, aheadBooking int default 1 not null, bookingText text collate utf8mb4_unicode_ci not null, bookingShowMemo int default 1 not null, bookingShowAddress int default 0 not null, bookingShowPostTime int default 0 not null, orderShowIdCard int default 0 null, orderShowDate int default 0 null, attention varchar(500) default ‘’ null comment ‘温馨提示 注意事项’, superFirstMoney int default 0 not null, firstMoney int not null, secondMoney int not null, singleMin int default 1 null, singleMax int default 10 not null, distribution int default 1 not null, isSoldOut int default 0 not null, releaseTime timestamp null, soldOutTime timestamp null, soldOutTimeInt int default 0 not null, enable int default 1 null, smsTemplate varchar(500) default ‘’ not null, editUserId int default 0 null, editorConfirmDate timestamp null, pmUserId int default 0 null, designerUserId int default 0 null, editName varchar(32) collate utf8mb4_unicode_ci default ‘’ null, pmName varchar(200) collate utf8mb4_unicode_ci default ‘’ null, designer varchar(200) collate utf8mb4_unicode_ci default ‘’ null, contractId int default 0 not null, confirmDate timestamp null, createDate timestamp default CURRENT_TIMESTAMP not null, productCategoryId int default 0 null, orderShowDeliveryTime tinyint unsigned default 0 null comment ‘下单是否选择配送时间’ ) charset = utf8;

    create index ix_biz_product_biz_user_id on biz_product_yyt_back (bizUserId);

    create index ix_biz_product_contractid on biz_product_yyt_back (contractId);

    create index ix_biz_product_designeruserid on biz_product_yyt_back (designerUserId);

    create index ix_biz_product_ecommerce on biz_product_yyt_back (eCommerce);

    create index ix_biz_product_edituserid on biz_product_yyt_back (editUserId);

    create index ix_biz_product_issoldout on biz_product_yyt_back (isSoldOut);

    create index ix_biz_product_locationid on biz_product_yyt_back (locationId);

    create index ix_biz_product_pmuserid on biz_product_yyt_back (pmUserId);

    create index ix_biz_product_sharenine on biz_product_yyt_back (shareNine);

    create index ix_biz_product_soldouttime on biz_product_yyt_back (soldOutTimeInt);

  2. 导出圆圆汇的产品数据，插入biz_product_yyt_back并更新biz_product_yyt_back的productCateGoryId。

圆圆

select * from biz_product WHERE bizUserId > 0;-- 导出sql insert文件,过滤无效产品

导入sql insert文件插入千千。 因为圆圆那边有站点id为3的产品，必须执行如下语句

update biz_product_yyt_back set locationId=745;

  1. 更新biz_product_yyt_back的productCateGoryId(人工更新，只有几个分类)。

    update biz_product_yyt_back set productCategoryId=1000005 where productCategoryId = 15;– 生活 update biz_product_yyt_back set productCategoryId=1000004 where productCategoryId = 14;– 美食 update biz_product_yyt_back set productCategoryId=1000003 where productCategoryId = 12;– 居家 update biz_product_yyt_back set productCategoryId=1000002 where productCategoryId = 11;– 美妆

  2. 更新biz_product_yyt_back的bizUserId、editUserId、pmUserId、designerUserId。

    • 更改新产品的bizUserId

      update biz_product_yyt_back a inner join biz_user_yyt_back b on a.bizUserId = b.oldId set a.bizUserId=b.id where a.bizUserId in (select oldId from biz_user_yyt_back);

    !!!!必须加where条件 !!!!!这里会出现bizUserId为0的产品!!!!!

    • 更新产品的editUserId、editName

      update biz_product_yyt_back a inner join com_user_yyt_back b on a.editUserId = b.id inner 		join 	com_user c on c.phoneNumber = b.phoneNumber
set a.editUserId=c.id,a.editName=c.name;

    • 更新产品的pmUserId、pmName

          update biz_product_yyt_back a inner join com_user_yyt_back b on a.pmUserId = b.id inner 		join 	com_user c on c.phoneNumber = b.phoneNumber
set a.pmUserId=c.id, a.pmName=c.name;

    • 更新产品的designerUserId、designer

       update biz_product_yyt_back a inner join com_user_yyt_back b on a.designerUserId = b.id inner join com_user c on c.phoneNumber = b.phoneNumber
set a.designerUserId=c.id, a.designer=c.name;

  3. 因为千千0-545的id段没有产品，圆圆的产品id才不到200，所以可以直接插入

    insert into biz_product select * from biz_product_yyt_back WHERE bizUserId>0;

  4. 产品相关的其他属性，不需要处理的

    1. biz_product_html、biz_product_ex、biz_product_html_push、biz_product_loop_imgs、 biz_product_shop

      select bizProductId, htmlContent, createDate from biz_product_html;– 导出sql insert文件 select * from biz_product_html_push;– 导出sql insert文件

    2. biz_product_loop_imgs，先删掉前面545个数据

      delete from biz_product_loop_imgs where bizProductId<545;

    在查询出圆圆的插入到千千

        select bizProductId, url, sort from biz_product_loop_imgs;-- 导出sql insert文件

    1. biz_product_shop

      select bizProductId, name, address, phoneNumber, latitude, longitude, memo, scale, sort, createDate from biz_product_shop;– 导出sql insert文件

    导入sql insert文件插入千千！！！！！！如果有冲突，还要先删除千千的！！！！！

4. 产品相关的其他属性，需要处理的biz_product_html_url，要更换editUserId为千千这边的userId。先导出biz_product_html_url	

圆圆

	 select bizProductId, url, editUserId, createDate from biz_product_html_url;-- 导出sql insert文件
	 
更新editUserId

	 update biz_product_html_url a inner join com_user_yyt_back b on a.editUserId = b.id inner join com_user c on c.phoneNumber = b.phoneNumber
    set a.editUserId =b.id where a.editUserId in (select id from com_user_yyt_back);
    
一定要加where条件    
    
 或者
 
       update biz_product_html_url a inner join com_user_yyt_back b on a.editUserId = b.id inner join com_user c on c.phoneNumber = b.phoneNumber
	set a.editUserId =b.id where a.bizProductId < 545;   	 
一定要加where条件
  1. biz_product_ex表数据，直接查询出圆圆的，导入到千千
select * from biz_product_ex;– 导出数据

插入千千数据库!!!!!千千的id大于70000多

  1. 圆圆sku增加的产品扩展数据biz_product_attrs，圆圆那边新增的表，数据导出来，直接插入千千。

    • 千千要先建表

         create table biz_product_attrs
   (
	 	 id              int auto_increment
 		  primary key,
      biz_product_id  int                                null comment '产品id',
 			sku_attr_name   varchar(255)                       null comment 'sku属性名',
 			sku_attr_values varchar(255)                       null comment 'sku值存在json数据',
      attr_hidden     int(2)                             null comment '属性是否隐藏',
 			detail_value    varchar(255)                       null comment '默认的属性',
 			create_date     datetime default CURRENT_TIMESTAMP null,
 			update_date     datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP
   );

    • 查询圆圆数据库

         select * from biz_product_attrs;-- 导出sql insert文件

    – 导入sql insert文件到千千

##3.产品套餐数据整合

###3.1 biz_product_items表数据整合

  1. 因为千千0-1000的id段的套餐没有关联产品（脏数据），圆圆的产品套餐id才不到1000，圆圆导入的产品数据id也没变，所以可以先删除千千id<=1000的套餐，再直接插入。 先从圆圆导出biz_product_items的数据

千千

    delete from biz_product_items where id <= 1000;

安全的，订单表套餐id最小是140000多

圆圆

    select * from biz_product_items;-- 导出sql insert文件

导入sql insert文件到千千

  1. 圆圆sku增加的产品套餐扩展数据biz_product_item_attr，圆圆那边新增的表，数据导出来，直接插入千千。
千千要先建表

	create table biz_product_items_attr
	(
   		 id          int auto_increment
  	 	 primary key,
   	 	items_id    int          default 0                 not null,
   		 sku_attr    varchar(1024)                          null,
		create_date datetime     default CURRENT_TIMESTAMP null,
		update_date datetime     default CURRENT_TIMESTAMP null on update 		CURRENT_TIMESTAMP,
		sku_code    varchar(255) default ''                null comment 'sku图片',
		sku_image   varchar(255)                           null,
		constraint uni_item_id
  	  	unique (items_id)
	);

查询圆圆数据库

select * from biz_product_items_attr;-- 导出sql insert文件

导入sql insert文件到千千

4.订单数据迁移流程

4.1 准备工作

  1. 在圆圆汇数据库建立user_mapping表，查询表的所有字段

    SELECT GROUP_CONCAT(‘a.’, column_name), GROUP_CONCAT(column_name) FROM information_schema.COLUMNS WHERE table_name = ‘biz_order’ AND table_schema = ‘yyt-main’;

  2. 将用户的id关联创建表（用于退款等操作人id关联） user_mapping 先行记录，明天就做统计做出user_mapping 关联

    CREATE TABLE user_mapping ( qquId int NOT NULL, yyuId int NOT NULL, qqUserId int NOT NULL, yyUserId int NOT NULL );

  3. 需要导出的查询语句（通过电话号码做关联）

     SELECT a.`uid` qquId, b.uid yyuId, a.`userId` qqUserId, b.userId yyUserId
 FROM `user` a LEFT JOIN user_yyt_back b on a.`phoneNumber` = b.phoneNumber;

###4.2 biz_order表数据整合

  1. 添加字段 wxOrderId 和cityCode (1、千千的数据wxOrderId是否直接用mOrderId填充，2、cityCode)

    ALTER TABLE biz_order ADD COLUMN wxOrderId varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT ‘’ COMMENT ‘圆圆微信支付唯一orderId’ AFTER secondMoney, ADD COLUMN cityCode varchar(32) DEFAULT ‘’’’ AFTER partnerid, ADD INDEX ix_biz_order_wxOrderId(wxOrderId) USING BTREE;

  2. 导出订单数据，需要在mOrderId、orderId前加Y，需要修改核销biz_userId，在原基础上+155000 导出数据并插入

    SELECT a.customerName, a.customerPhoneNumber, a.address, a.idCard, a.usingDate, a.buyUrl, a.partnerid, a.payAppid, a.payOpenid, a.openid, a.wxUserId, a.a, a.agentMoneyType, a.s, a.c, a.payTimeInt, a.superFirstUserId, a.superFirstMoney, a.firstUserId, a.firstMoney, a.secondUserId, a.secondMoney, a.wxOrderId, a.productItemId, a.code, a.qrCodeUrl, a.sendSms, a.status, a.money, 745 locationId, a.payTime, a.memo, a.bookingTime, a.bookingDay, a.bookingItemId, a.bookingCustomerName, a.bookingCustomerPhoneNumber, a.bookingMemo, a.sendBookingSuccessSms, a.salt, a.detailUrl, a.bookingUrl, a.completeDate, a.refundDate, a.sure, a.createDate, a.expressInfo, a.expressName, a.sendExpressSms, a.cityCode, concat(‘Y’, mOrderId) mOrderId, concat(‘Y’, orderId) orderId, IF (a.completeBizUid = 0 ,a.completeBizUid, a.completeBizUid + 155000 ) completeBizUid FROM biz_order a ORDER BY id desc;

导入到千千数据库。

###4.3 biz_order_prepare表数据整合

  1. 增加wxOrderId（圆圆汇去支付的实际id）

    ALTER TABLE biz_order_prepare ADD COLUMN wxOrderId varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT ‘’ COMMENT ‘圆圆微信支付唯一orderId’ AFTER secondMoney, ADD COLUMN cityCode varchar(32) DEFAULT ‘’’’ AFTER createDate, ADD INDEX ix_biz_order_prepare_wxOrderId(wxOrderId) USING BTREE;

  2. 查询出数据，导入千千数据库（将orderid和mOrderId前加Y）

SELECT customerName, 
		       customerPhoneNumber,
		       address,
		       idCard,
		       usingDate,
		       buyUrl,
		       partnerid,
		       payAppid,
		       payOpenid,
		       openid,
		       wxUserId,
		       a,
		       agentMoneyType,
		       s,
		       c,
		       superFirstUserId,
		       superFirstMoney,
		       firstUserId,
		       firstMoney,
		       secondUserId,
		       secondMoney,
		       wxOrderId,
		       productItemId,
		       `status`,
		       money,
		       745 locationId,
		       memo,
		       cityCode,
		       createDate,
		       concat('Y', `mOrderId`) `mOrderId`,
		       concat('Y', `orderid`)  `orderid`
		       FROM biz_order_prepare;

导入到千千。

###4.4 biz_order_refund表数据整合

  1. 查询出退款订单数据，（主要是操作人和订单前加Y）

    SELECT a.payAppid, a.payOpenid, a.customerName, a.customerPhoneNumber, a.openid, a.wxUserId, a.productItemId, a.totalMoney, a.money, a.payTime, a.payDate, a.refundUserName, a.reqUserName , a.refundDate, a.refundDateInt, a.bankId, a.refundAccountInfo, a.status, a.oldStatus, a.wxRefundDate, a.wxRefundRequest, a.wxRefundResponse, a.refundType, a.batch, a.memo, a.refundNum, a.createDate, a.operateTime, a.refundTypeId, a.refundReason, a.refundImageUrl, b.qquId reqUserId, c.qquId refundUserId, concat(‘Y’, refundOrderId) refundOrderId, concat(‘Y’, mOrderId) mOrderId, concat(‘Y’, orderId) orderId FROM biz_order_refund a LEFT JOIN user_mapping b on a.reqUserId = b.yyuId LEFT JOIN user_mapping c on a.refundUserId = c.yyuId order by id DESC ;

###4.5 biz_order_confirm_delivery表数据整合

  1. 主库创建表

    CREATE TABLE biz_order_confirm_delivery ( id int(11) NOT NULL AUTO_INCREMENT, order_id varchar(20) NOT NULL COMMENT ‘订单id’, super_first_user_id int(11) NOT NULL, super_first_money int(11) NOT NULL, first_user_id int(11) NOT NULL, first_money int(11) NOT NULL, second_user_id int(11) NOT NULL, second_money int(11) NOT NULL, create_date datetime NOT NULL COMMENT ‘处理时间’, status tinyint(1) NOT NULL COMMENT ‘ 1:分佣完成’, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=266 DEFAULT CHARSET=utf8mb4 COMMENT=’商家发货后确认分红表’;

  2. 订单字符上加上Y

    SELECT a.super_first_user_id, a.super_first_money, a.first_user_id, a.first_money, a.second_user_id, a.second_money, a.create_date, a.status, concat(‘Y’, a.order_id) order_id FROM biz_order_confirm_delivery a;

导入到千千数据库

###4.6 biz_order_express表数据整合

  1. 将orderId前加Y

     SELECT a.expressNo,
     a.expressType,
     a.expireTime,
     a.updateDate,
     a.expressResult,
     a.deliveryStatus,
     a.isSign,
     a.createDate,
     concat('Y', a.orderId) orderId   FROM `biz_order_express` a;

导入到千千数据库

###4.7 biz_order_req_json表数据整合

  1. 创建表在主库，然后数据整体导入
 	  CREATE TABLE `biz_order_req_json` (
                                      `id` int(11) NOT NULL AUTO_INCREMENT,
                                      `mOrderId` varchar(50) NOT NULL,
                                      `json` varchar(2000) NOT NULL,
                                      `userAgent` varchar(2000) NOT NULL,
                                      `remoteIp` varchar(100) NOT NULL,
                                      `cityCode` varchar(32) DEFAULT '',
                                      `createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                                      PRIMARY KEY (`id`,`mOrderId`)
	) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

  1. mOrderId保存的是wxOrderId,所以导出数据直接插入

    select mOrderId, json, userAgent, remoteIp, cityCode, createDate from biz_order_req_json;

导入到千千数据库

###4.8 biz_order_req_json表数据整合

  1. 主库创建表，然后直接插入

    CREATE TABLE biz_order_sms_record ( id int(11) NOT NULL AUTO_INCREMENT, productItemId int(11) NOT NULL, phoneNumber varchar(50) NOT NULL, fee int(11) NOT NULL, body varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT ‘’, createDate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, code varchar(10) NOT NULL DEFAULT ‘’, msgId varchar(20) NOT NULL DEFAULT ‘’, errorMsg varchar(50) DEFAULT NULL, PRIMARY KEY (id) USING BTREE, KEY ix_bizordersmsrecord_phonenumber (phoneNumber) USING BTREE, KEY ix_bizordersmsrecord_productitemid (productItemId) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;

  2. 导入到千千

    select phoneNumber, fee, body, createDate, code, msgId, productItemId, errorMsg from biz_order_sms_record;– 导入到千千数据库

###4.9 biz_shopping_cart表数据整合

关联千千对应的分类id，导入购物车数据然后更新对应的分类id( 圆圆过去的分类大于 1000000)

  1. 创建表

                      CREATE TABLE `biz_shopping_cart_back` (
                                `id` bigint(20) NOT NULL AUTO_INCREMENT,
                                `biz_product_id` int(11) DEFAULT NULL COMMENT '产品id',
                                `biz_product_sku_id` int(11) DEFAULT NULL,
                                `biz_product_category_id` int(11) DEFAULT NULL COMMENT '商品分类',
                                `biz_product_name` varchar(255) DEFAULT NULL COMMENT '商品名称',
                                `biz_product_sub_title` varchar(255) DEFAULT NULL COMMENT '商品副标题（卖点）',
                                `biz_product_sn` varchar(255) DEFAULT NULL COMMENT '更改为最大购买数量',
                                `biz_product_attr` varchar(255) DEFAULT NULL COMMENT '商品销售属性:[{"key":"颜色","value":"颜色"},{"key":"容量","value":"4G"}]',
                                `member_id` int(11) DEFAULT NULL COMMENT '商铺id',
                                `biz_product_brand` varchar(255) DEFAULT NULL,
                                `openid` varchar(255) DEFAULT NULL,
                                `pay_openid` varchar(255) DEFAULT NULL,
                                `member_nick_name` varchar(255) DEFAULT NULL COMMENT '会员昵称',
                                `quantity` int(11) DEFAULT NULL COMMENT '购买数量',
                                `biz_product_pic` varchar(5000) DEFAULT NULL,
                                `price` decimal(10,4) DEFAULT NULL COMMENT '添加到购物车的价格',
                                `sp1` varchar(255) DEFAULT NULL COMMENT '销售属性1',
                                `sp2` varchar(255) DEFAULT NULL COMMENT '销售属性2',
                                `sp3` varchar(255) DEFAULT NULL COMMENT '销售属性3',
                                `create_date` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
                                `update_date` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
                                `status` int(3) DEFAULT NULL COMMENT '状态',
                                PRIMARY KEY (`id`)   ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

  2. 导入购物车数据(注意表名)

    SELECT * FROM biz_shopping_cart;

  3. 更新分类信息

    update biz_shopping_cart_back set biz_product_category_id=1000005 where biz_product_category_id = 15;– 生活 update biz_shopping_cart_back set biz_product_category_id=1000004 where biz_product_category_id = 14;– 美食 update biz_shopping_cart_back set biz_product_category_id=1000003 where biz_product_category_id = 12;– 居家 update biz_shopping_cart_back set biz_product_category_id=1000002 where biz_product_category_id = 11;– 美妆

###4.10 cash_order表数据整合

先在千千插入id为320000的占位数据，让千千后面的数据都大于330000，然后我们在千千的空白段id区域（320000-330000）插入圆圆汇的数据，其他数据不变

  1. 在千千建一张备份表cash_order_yyt_back

    create table cash_order_yyt_back ( id int auto_increment primary key, partnerTradeNo varchar(32) default ‘’’’ not null, userId int default 0 not null, openid varchar(50) default ‘’’’ not null, locationId int default 0 not null, payAppid varchar(50) default ‘’’’ null, payOpenid varchar(50) default ‘’’’ null, money int default 0 not null, status int default 0 not null, payType int default 0 not null, payNo varchar(200) default ‘’’’ not null, memo varchar(500) collate utf8mb4_unicode_ci default ‘’’’ not null, cashUserId int default 0 not null, cashUserName varchar(50) default ‘’’’ not null, isBindOrder int default 0 not null, createDate timestamp default CURRENT_TIMESTAMP not null, createDateInt int default 0 not null, wxTransferTimeInt int default 0 not null, wxTransferTime timestamp null, wxTransferRequest varchar(1000) null, wxTransferResponse varchar(1000) null, queryTransferTime timestamp null, queryTransferRequest varchar(1000) null, queryTransferResponse varchar(1000) null, rpStatus varchar(50) null, rpReceiveTime varchar(50) null ) charset = utf8;

    create index ix_cash_order_createdateint on cash_order_yyt_back (createDateInt);

      create index ix_cash_order_id
      on cash_order_yyt_back (id);
		
  create index ix_cash_order_isbindorder
      on cash_order_yyt_back (isBindOrder);
		
  create index ix_cash_order_locationId
      on cash_order_yyt_back (locationId);
		
  create index ix_cash_order_openid
      on cash_order_yyt_back (openid);
		
  create index ix_cash_order_partnertradeno
      on cash_order_yyt_back (partnerTradeNo);
		
  create index ix_cash_order_payappid
      on cash_order_yyt_back (payAppid);
		
  create index ix_cash_order_payno
      on cash_order_yyt_back (payNo);
		
  create index ix_cash_order_payopenid
      on cash_order_yyt_back (payOpenid);
		
  create index ix_cash_order_paytype
      on cash_order_yyt_back (payType);
		
  create index ix_cash_order_status
      on cash_order_yyt_back (status);
		
  create index ix_cash_order_userId
      on cash_order_yyt_back (userId);
		
  create index ix_cash_order_wxtransfertimeint
      on cash_order_yyt_back (wxTransferTimeInt);

  2. 查询出据导入千千

    SELECT * FROM cash_order;

导入到千千。

  1. 更换cash_order表的cashUSerId (user_mapping中的qquId)

    UPDATE cash_order_yyt_back a LEFT JOIN user_mapping b on b.yyuId = a.cashUserId set a.cashUserId=b.qquId;

  2. 插入到千千cash_order表

    insert into cash_order SELECT * from cash_order_yyt_back

###4.11 cash_bind_order表数据整合

  1. cashId 改为cash_order表改动后的id

##5 一般数据

###5.1 nonexistent_wx_openid、wx_event_notice、agent_user_money_refund_log 直接查询出导入。

###5.12 banner_config表数据整合

  1. 先在千千建表

    CREATE TABLE banner_config ( id bigint(20) NOT NULL AUTO_INCREMENT, title varchar(100) NOT NULL COMMENT ‘标题’, sort int(11) NOT NULL COMMENT ‘排序’, imgUrl varchar(255) NOT NULL COMMENT ‘图片地址’, redirectUrl varchar(255) DEFAULT NULL COMMENT ‘跳转地址’, status tinyint(1) NOT NULL COMMENT ‘状态 0：正常 1：禁用’, createDate datetime NOT NULL COMMENT ‘创建时间’, updateDate datetime DEFAULT NULL COMMENT ‘更新时间’, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COMMENT=’banner设置’;

从圆圆查询出数据，导入到千千

SELECT * FROM `banner_config` ;
CATALOG
    FEATURED TAGS
    Android MVP 设计模式 MMVP RxJava2 线程切换 响应式编程 ReactiveX Unity3d Android与Unity3d通信 Unity3d与Android通信
    FRIENDS