huahuibao/sql.txt

2019.12.1
ALTER TABLE `xhUser` ADD INDEX mobile(merchantId,`mobile`);
ALTER TABLE xhUserAsset DROP COLUMN `point`;
ALTER TABLE xhUserAsset MODIFY `integral` INT NOT NULL DEFAULT 0 COMMENT '积分';
ALTER TABLE xhUser MODIFY birthday DATE NOT NULL DEFAULT '0000-00-00' COMMENT '生日';
ALTER TABLE xhUserAsset DROP COLUMN `uniqueQrCode`;
ALTER TABLE xhUserAsset DROP COLUMN `uniqueCode`;
ALTER TABLE xhMerchantExtend ADD gradeSwitch TINYINT NOT NULL DEFAULT 1 COMMENT '会员等级修改开关 0不可再修改 1可修改' AFTER gradeList;

shish 2019.11.30
ALTER TABLE xhGoods DROP COLUMN `multiPrice`;
RENAME TABLE xhGoodsPrice TO xhGoodsStyle;
ALTER TABLE xhGoodsStyle COMMENT '商品款式';
ALTER TABLE xhGoodsStyle ADD goodsId INT NOT NULL DEFAULT 0 COMMENT '商品id' AFTER `id`;
DROP TABLE IF EXISTS `xhGoodsUsage`;
ALTER TABLE xhOrder ADD INDEX merchant(merchantId,`status`);
ALTER TABLE xhOrder ADD INDEX userId(userId,`status`);
ALTER TABLE xhUserAsset ADD inviteNum INT NOT NULL DEFAULT 0 COMMENT '邀请新人数量' AFTER `discount`;
ALTER TABLE xhAdmin MODIFY `password` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '密码';
ALTER TABLE xhAdmin MODIFY `confirmPassword` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '确认密码';

shish 2019.11.27
ALTER TABLE xhOrder ADD refund TINYINT NOT NULL DEFAULT 0 COMMENT '1已退款 0正常订单，没有退款' AFTER luckyNum;
ALTER TABLE xhGoods MODIFY multiPrice VARCHAR(255) NOT NULL DEFAULT '' COMMENT '多款式商品，有多种价格，例(1,2,3,4)';

shish 2019.11.27
!!注意第一家全部订单已经完成，并将全部订单置为已完成
ALTER TABLE xhOrder MODIFY `status` TINYINT NOT NULL DEFAULT 0 COMMENT '订单状态 0待付款 1待配送 2配送中 3待通知 4已取消 5已完成';
!!注意第一家店分类与用途迁移的问题
DROP TABLE IF EXISTS xhCategoryRelate;
ALTER TABLE xhCategory ADD merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id' AFTER parentId;
DROP TABLE IF EXISTS xhGoodsUsage;
DROP TABLE IF EXISTS xhUsage;
DROP TABLE IF EXISTS xhUsageRelation;
DROP TABLE IF EXISTS xhStatIncomeUsage;
DROP TABLE IF EXISTS xhStatIncomeUsageMonth;

shish 2019.11.26
ALTER TABLE xhAdminRole DROP COLUMN auth;
ALTER TABLE xhAdminRole CHANGE authority auth VARCHAR(500) NOT NULL DEFAULT '' COMMENT '权限id列表，存在*表示有所有权限';

shish 2019.11.25
ALTER TABLE xhAuth CHANGE front_url frontUrl VARCHAR(30) NOT NULL DEFAULT '' COMMENT '前端路由';
ALTER TABLE xhAuth CHANGE back_url backUrl VARCHAR(30) NOT NULL DEFAULT '' COMMENT '后端路由';

shish 2019.11.24
ALTER TABLE xhAdminRoleAuth ADD endId TINYINT NOT NULL DEFAULT 0 COMMENT '业务端id' AFTER roleId;
ALTER TABLE xhMerchant ADD setDeadline INT NOT NULL DEFAULT 0 COMMENT '套餐到期时间' AFTER `setId`;
ALTER TABLE xhMerchant ADD setId TINYINT NOT NULL DEFAULT 0 COMMENT '套餐id' AFTER `wxRefreshToken`;

shish 2019.11.23
DROP TABLE IF EXISTS xhAuth;
CREATE TABLE xhAuth(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
endId TINYINT NOT NULL DEFAULT 0 COMMENT '业务端，零售1',
parentId INT NOT NULL DEFAULT 0 COMMENT '权限父id',
authName VARCHAR(30) NOT NULL DEFAULT '' COMMENT '权限名称',
front_url VARCHAR(30) NOT NULL DEFAULT '' COMMENT '前端路由',
back_url VARCHAR(30) NOT NULL DEFAULT '' COMMENT '后端路由',
`addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
)COMMENT='各业务端的权限';
DROP TABLE IF EXISTS xhSetAuth;
CREATE TABLE xhSetAuth(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
setId TINYINT NOT NULL DEFAULT 0 COMMENT '套餐id',
endId TINYINT NOT NULL DEFAULT 0 COMMENT '业务端id',
auth VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '权限',
`addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
)COMMENT='套餐在各业务端的权限';
CREATE TABLE xhAdminRoleAuth(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
roleId INT NOT NULL DEFAULT 0 COMMENT '角色id',
auth VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '权限',
`addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
)COMMENT='员工角色权限'


shish 2019.11.23
!!jwt的过期时间要设置得短点

shish 2019.11.22
!!注意要将成积分移到成长值里

shish 2019.11.22
ALTER TABLE xhUserAsset ADD growth INT NOT NULL DEFAULT 0 COMMENT '成长值' AFTER `point`;