//============================已执行======================= *****************************员工表迁移************************************* alter table sys_users add SIGN_ORDER int null; alter table sys_users add sign_date datetime null; ALTER TABLE `sys_users` ADD COLUMN `degree` varchar(255) DEFAULT NULL; ALTER TABLE `sys_users` ADD COLUMN `work_exp` varchar(1000) DEFAULT NULL; ALTER TABLE `sys_users` ADD COLUMN `entry_date` datetime DEFAULT NULL; ALTER TABLE `sys_users` ADD COLUMN `wx_no` varchar(100) DEFAULT NULL; ALTER TABLE `sys_users` ADD COLUMN `en_name` varchar(50) DEFAULT NULL; alter table sys_users add emerg_tel varchar(20) null; alter table sys_users add service_signature varchar(200) null; update sys_users a set a.su_id=(select id from sys_shopstaff_info b where a.su_name=b.STAFF_NAME ) where a.su_id not in (1001,1002,65,75,79,78); *******************************修改字段********************************** ALTER TABLE `sys_role` ADD COLUMN `is_default` int(1) NULL COMMENT '是否默认' AFTER `plates`; ALTER TABLE `sys_beautician_state` ADD COLUMN `puse_id` bigint(20) NULL DEFAULT NULL COMMENT '项目使用情况id' AFTER `extract`; alter table sys_shop_info add company_id bigint null comment '公司id'; ALTER TABLE `sys_beautician_state` ADD COLUMN `count` int NULL COMMENT '项目次数' AFTER `puse_id`; ALTER TABLE `sys_beautician_state` ADD COLUMN `proj_id` bigint NULL COMMENT '项目id' AFTER `count`; ALTER TABLE `sys_proj_services` DROP COLUMN `PROJ_USE_ID`; ALTER TABLE `sys_beautician_state` DROP COLUMN `STAFF_COMMENT`; alter table sys_proj_services add consume_time datetime null comment '划扣时间'; alter table sys_order add pay_time datetime null comment '收款时间'; /** * 经营数据 * @author * @date 2020-02-08 10:18 */ CREATE TABLE sys_business_data ( create_by varchar(100) NOT NULL COMMENT '创建人', create_time datetime NOT NULL COMMENT '创建时间', update_by varchar(100) NOT NULL COMMENT '更新人', update_time datetime NOT NULL COMMENT '更新时间', id int NOT NULL AUTO_INCREMENT COMMENT '主键', time date COMMENT '日期', company_id int COMMENT '公司', shop_id int COMMENT '门店', name varchar(50) COMMENT '字段名称', value varchar(1000) COMMENT '值', code varchar(50) COMMENT '唯一编码', sequence int COMMENT '排序角标', data_type int COMMENT '数据类型', t1 varchar(50) COMMENT '扩展字段1', t2 varchar(50) COMMENT '扩展字段2', t3 varchar(50) COMMENT '扩展字段3', t4 varchar(50) COMMENT '扩展字段4', t5 varchar(50) COMMENT '扩展字段5', t6 varchar(50) COMMENT '扩展字段6', t7 varchar(50) COMMENT '扩展字段7', t8 varchar(50) COMMENT '扩展字段8', t9 varchar(50) COMMENT '扩展字段9', t10 varchar(50) COMMENT '扩展字段10', t11 varchar(50) COMMENT '扩展字段11', t12 varchar(50) COMMENT '扩展字段12', t13 varchar(50) COMMENT '扩展字段13', t14 varchar(50) COMMENT '扩展字段14', t15 varchar(50) COMMENT '扩展字段15', PRIMARY KEY (id) ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT ='经营数据'; *************库存表修改************** alter table sys_goods add alarm_num int null; alter table sys_goods add volume int null; alter table sys_goods add price decimal(10,2) null; alter table sys_goods add wholesale decimal(10,2) null; 库存产品数据迁移 update sys_goods b set alarm_num=(select alarm_num from sku a where a.goods_id=b.ID); update sys_goods b set volume=(select volume from sku a where a.goods_id=b.ID); update sys_goods b set price=(select price from sku a where a.goods_id=b.ID); update sys_goods b set wholesale=(select wholesale from sku a where a.goods_id=b.ID); 把产品主键替换成了sku主键，保障库存的正确性 update sys_store_info a set a.SKU_ID=( select b.id from sys_goods b left join sku c on c.goods_id=b.ID where c.id=a.SKU_ID ); update sys_goods a set a.NAME= (select b.name from sku b where b.goods_id=a.ID ); 要切换主键才能执行成功 update sys_goods a set a.id= (select b.id from sku b where b.goods_id=a.ID ); ********************服务单******************************* ALTER TABLE `sys_beautician_state` DROP COLUMN `STAFF_COMMENT`; ALTER TABLE `service_bea_proj` ADD COLUMN `STAFF_ID` bigint(20) NULL AFTER `ser_proj_id`, ADD COLUMN `STATE` varchar(20) NULL AFTER `STAFF_ID`, ADD COLUMN `BEGIN_TIME` datetime NULL AFTER `STATE`, ADD COLUMN `END_TIME` datetime NULL AFTER `BEGIN_TIME`, ADD COLUMN `SERVICES_ID` bigint NULL AFTER `END_TIME`, ADD COLUMN `SHOP_ID` bigint NULL AFTER `SERVICES_ID`, ADD COLUMN `EXC_TIME` int NULL AFTER `SHOP_ID`, ADD COLUMN `extract` varchar(255) NULL AFTER `EXC_TIME`, ADD COLUMN `puse_id` bigint(20) NULL AFTER `extract`, ADD COLUMN `count` int(255) NULL AFTER `puse_id`; update service_bea_proj a , sys_ser_proj b set a.puse_id=b.PRO_USEID, a.count=1 where a.ser_proj_id=b.id; update service_bea_proj a , sys_proj_use b set a.proj_id=b.PROJ_ID, a.count=1 where a.puse_id=b.id; DELETE from sys_beautician_state; update service_bea_proj a , sys_beautician_state b set a.STAFF_ID=b.STAFF_ID, a.STATE=b.STATE, a.BEGIN_TIME=b.BEGIN_TIME, a.END_TIME=b.END_TIME, a.SERVICES_ID=b.SERVICES_ID, a.SHOP_ID=b.SHOP_ID, a.EXC_TIME=b.EXC_TIME, a.extract=b.extract, a.count=1 where a.bea_state_id=b.id; INSERT INTO sys_beautician_state (STAFF_ID, STATE, BEGIN_TIME, END_TIME, SERVICES_ID, EXC_TIME, extract, puse_id, count, proj_id, SHOP_ID) select STAFF_ID, STATE, BEGIN_TIME, END_TIME, SERVICES_ID, EXC_TIME, extract, puse_id, count, proj_id, SHOP_ID from service_bea_proj; update sys_proj_services a set a.consume_time=a.CREATE_TIME; update sys_order set pay_time=ORDER_TIME; ******************************删除多余表************************************* drop table sys_shopstaff_info;