huahuibao/sql.txt

2019.12.8 shish
DROP TABLE IF EXISTS xhPic;
CREATE TABLE IF NOT EXISTS xhPic(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
img VARCHAR(500) NOT NULL DEFAULT '' COMMENT '图片地址',
price DECIMAL(9,2) NOT NULL DEFAULT 0.00 COMMENT '价格',
`name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商品名称',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='图库；商品图库';
DROP TABLE IF EXISTS xhPicCategory;
CREATE TABLE IF NOT EXISTS xhPicCategory(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
merchantId INT NOT NULL DEFAULT 0 COMMENT '商家id',
pictureId INT NOT NULL DEFAULT 0 COMMENT '图片id',
categoryId INT NOT NULL DEFAULT 0 COMMENT '分类id'
)COMMENT='商品图与分类关系';
ALTER TABLE xhPic ADD inTurn INT NOT NULL DEFAULT 0 COMMENT '排序' AFTER `name`;
ALTER TABLE xhPicCategory ADD inTurn INT NOT NULL DEFAULT 0 COMMENT '排序' AFTER categoryId;
ALTER TABLE xhPicCategory CHANGE pictureId picId INT NOT NULL DEFAULT 0 COMMENT '图片id';
ALTER TABLE xhGoodsCategory ADD `status` TINYINT NOT NULL DEFAULT 1 COMMENT '上下架状态 0下架 1正常' AFTER `merchantId`;
ALTER TABLE xhCategory ADD goodsNum INT NOT NULL DEFAULT 0 COMMENT '商品数' AFTER `cover`;
ALTER TABLE xhCategory ADD sold INT NOT NULL DEFAULT 0 COMMENT '销量' AFTER `goodsNum`;
ALTER TABLE xhCategory CHANGE cover `img` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '分类图片';

shizhongqi 2019.12.7
ALTER TABLE xhAd ADD `status` TINYINT NOT NULL DEFAULT 0 COMMENT '启用状态 0 未启用 1 启用' AFTER `adName`;
ALTER TABLE xhAd ADD `inTurn` INT NOT NULL DEFAULT 0 COMMENT '排序' AFTER `status`;
ALTER TABLE xhAd ADD `viewTimes` INT NOT NULL DEFAULT 0 COMMENT '点击次数' AFTER `adImg`;
ALTER TABLE `huahuibao`.`xhAd` CHANGE `startTime` `startTime` DATETIME NULL COMMENT '开始时间', CHANGE `endTime` `endTime` DATETIME NULL COMMENT '结束时间';
ALTER TABLE xhAd ADD `forever` TINYINT NOT NULL DEFAULT 0 COMMENT '永久有效：0 未启用 1 永久有效' AFTER `goodsId`;

2019.12.6 shish
增加xhRegion表结构和数据！！
ALTER TABLE xhOrder MODIFY `payStatus` TINYINT NOT NULL DEFAULT 0 COMMENT '0待付款 1已付款';
RENAME TABLE xhUserIntegral TO xhUserGrowth;
ALTER TABLE xhUserGrowth CHANGE `integral` `growth` INT NOT NULL DEFAULT 0 COMMENT '当前成长值';
ALTER TABLE xhUserGrowth MODIFY `num` INT NOT NULL DEFAULT 0 COMMENT '增加或减少的成长值';
ALTER TABLE xhOrder DROP COLUMN `payStyle`;
ALTER TABLE xhGoods MODIFY `status` TINYINT NOT NULL DEFAULT 1 COMMENT '商品状态 0下架 1正常';
ALTER TABLE xhGoods DROP COLUMN `delStatus`;
ALTER TABLE xhGoodsCategory ADD INDEX `gId`(`gId`);
ALTER TABLE xhGoods ADD autoRise TINYINT NOT NULL DEFAULT 1 COMMENT '自动涨价 0不需要 1需要' AFTER `goodsStyle`;
ALTER TABLE `xhGoodsStyle` CHANGE `title` `styleName` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '款式名称';

2019.12.5 shish
ALTER TABLE xhOrderGoods CHANGE `multipleTitle` `goodsStyleName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '款式名称';
ALTER TABLE xhOrderGoods CHANGE `multiPriceId` `goodsStyleId` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '款式id';
ALTER TABLE xhCoupon MODIFY `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0未使用 1使用 2过期';
ALTER TABLE xhOrder DROP COLUMN `luckyNum`;
ALTER TABLE xhOrder DROP COLUMN `uniCode`;
ALTER TABLE xhOrder DROP COLUMN `useType`;

2019.12.4 11:44 shish
ALTER TABLE xhShop CHANGE `businessName` `shopName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '门店名称';
ALTER TABLE xhShop CHANGE `branchName` `merchantName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商家名称';
ALTER TABLE xhShop DROP COLUMN `poiId`;
ALTER TABLE xhMerchantExtend ADD `freight` CHAR(10) NOT NULL DEFAULT '5_2' COMMENT '运费计算方式 5公里内免运费，超过每增加一公里+2' AFTER `alipayAccountName`;
ALTER TABLE xhMerchantExtend DROP COLUMN `giveCoupon`;
ALTER TABLE xhMerchantExtend DROP COLUMN `giveCouponPrice`;
ALTER TABLE xhMerchantExtend DROP COLUMN `couponMeetPrice`;
ALTER TABLE xhMerchantExtend DROP COLUMN `couponValidity`;
ALTER TABLE xhMerchantExtend ADD newCouponSwitch TINYINT NOT NULL DEFAULT 1 COMMENT '新人送优惠券 0未开启 1开启' AFTER `freight`;
ALTER TABLE xhMerchantExtend ADD newCouponAmount DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '新人优惠券金额' AFTER `newCouponSwitch`;
ALTER TABLE xhMerchantExtend ADD newCouponMiniCost DECIMAL(9,2) NOT NULL DEFAULT '0.00' COMMENT '新人优惠券最低消费金额' AFTER `newCouponAmount`;
ALTER TABLE xhMerchantExtend ADD newCouponValidTime INT NOT NULL DEFAULT 0 COMMENT '新人优惠券有效期，时长为秒，0永不过期' AFTER `newCouponMiniCost`;
ALTER TABLE xhGoods DROP COLUMN `isCharge`;
ALTER TABLE xhGoods DROP COLUMN `sendArea`;
ALTER TABLE xhGoods CHANGE `sendType` needSend TINYINT NOT NULL DEFAULT 0 COMMENT '是否要配送 0免配送 1需要配送';
ALTER TABLE xhOrder CHANGE `needSend` `sendType` TINYINT NOT NULL DEFAULT 0 COMMENT '配送方式 0免配送 1自己送 2别人送';
ALTER TABLE xhOrder MODIFY reachPeriod TINYINT NOT NULL DEFAULT 0 COMMENT '0上午1下午2晚上';
ALTER TABLE xhOrder DROP COLUMN `blessing`;
ALTER TABLE xhOrder ADD cardInfo VARCHAR(500) NOT NULL DEFAULT '' COMMENT '贺卡内容' AFTER `needCard`;
ALTER TABLE xhOrder MODIFY `payWay` TINYINT NOT NULL DEFAULT 0 COMMENT '支付方式 0微信 1支付宝 2余额';

2019.12.4 shish
!!执行下面的sql，全部的会员折扣要纠正
UPDATE xhUserAsset SET `discount`=1;
ALTER TABLE xhUserAsset MODIFY discount FLOAT(3,2) NOT NULL DEFAULT 1.00 COMMENT '折扣';
ALTER TABLE xhCoupon CHANGE `meetAmount` `miniCost` INT NOT NULL DEFAULT 0 COMMENT '最低消费金额，0表示任何情况下可用';

！！国兰的商品款式表要给对应上，注意xhGoods表里的goodsStyle也要改
ALTER TABLE xhGoods CHANGE `style` `goodsStyle` TINYINT NOT NULL DEFAULT 0 COMMENT '商品款式 0单款式 1多款式';
ALTER TABLE xhGoodsStyle CHANGE `order` `inTurn` INT NOT NULL DEFAULT 0 COMMENT '排序';
ALTER TABLE xhGoodsStyle DROP COLUMN `softDel`;

2019.12.3 shish
ALTER TABLE xhGoods ADD sendType TINYINT NOT NULL DEFAULT 0 COMMENT '配送方式 0需要配送 1到店自提' AFTER sendArea;
ALTER TABLE xhGoods ADD freightType TINYINT NOT NULL DEFAULT 0 COMMENT '运费计算方式，0按距离计算 1免运费' AFTER sendType;
DROP TABLE IF EXISTS `xhCouponModel`;
DROP TABLE IF EXISTS `xhCouponReward`;
DROP TABLE IF EXISTS `xhCouponRight`;
ALTER TABLE xhCoupon DROP COLUMN `type`;
ALTER TABLE xhCoupon DROP COLUMN `property`;
ALTER TABLE xhCoupon DROP COLUMN `fitType`;
ALTER TABLE xhCoupon DROP COLUMN `rightId`;
ALTER TABLE xhCoupon DROP COLUMN `modelId`;
ALTER TABLE xhCoupon DROP COLUMN `reward`;
ALTER TABLE xhCoupon DROP COLUMN `urlQrCode`;
ALTER TABLE xhCoupon CHANGE howToGet getType TINYINT NOT NULL DEFAULT 0 COMMENT '获得优惠券的方式';
ALTER TABLE xhCoupon DROP COLUMN `targetId`;
ALTER TABLE xhCoupon MODIFY deadline INT NOT NULL DEFAULT 0 COMMENT '过期时间；值4102416000表示永不过期，即2100-01-01 00:00:00';
ALTER TABLE xhCoupon ADD INDEX userId(userId,`status`);
ALTER TABLE xhCoupon ADD INDEX merchantId(merchantId,`status`);

2019.12.2 19:18
DROP TABLE IF EXISTS `xhCategoryRelate`;
ALTER TABLE xhCategory ADD indexShow TINYINT NOT NULL DEFAULT 0 COMMENT '首页上显示 0不显示 1显示' AFTER categoryName;
ALTER TABLE xhCategory ADD menuShow TINYINT NOT NULL DEFAULT 0 COMMENT '菜单显示 0不显示 1显示' AFTER `indexShow`;
ALTER TABLE xhCategory ADD INDEX indexShow(merchantId,indexShow);
ALTER TABLE xhCategory ADD INDEX menuShow(merchantId,menuShow);
ALTER TABLE xhAd ADD adImg VARCHAR(800) NOT NULL DEFAULT '' COMMENT '广告图' AFTER adName;
ALTER TABLE xhAd MODIFY endTime INT NOT NULL DEFAULT 0 COMMENT '结束时间；方便查询，永不过期情况，使用 开始值0 结束值4102416000即2100-01-01 00:00:00';
ALTER TABLE xhGoodsCategory MODIFY `cId` INT NOT NULL DEFAULT 0 COMMENT '分类id';
ALTER TABLE xhGoods DROP COLUMN `categoryId`;
ALTER TABLE xhGoodsCategory ADD INDEX merchantId(merchantId);
ALTER TABLE xhCategory DROP COLUMN `indexShow`;
ALTER TABLE xhCategory DROP COLUMN `menuShow`;
ALTER TABLE xhCategory ADD `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态 0停用1启用';
ALTER TABLE xhCategory DROP INDEX `indexShow`;
ALTER TABLE xhCategory DROP INDEX `menuShow`;
ALTER TABLE xhCategory ADD INDEX merchantId(merchantId,`status`);
ALTER TABLE xhGoodsCategory DROP INDEX merchantId;
ALTER TABLE xhGoodsCategory ADD INDEX merchantId(merchantId,cId);
ALTER TABLE xhGoods DROP COLUMN `cover`;

2019.12.2 13:00 shish 广告管理
DROP TABLE IF EXISTS xhAd;
CREATE TABLE IF NOT EXISTS xhAd(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`adName` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '广告名称',
`merchantId` INT NOT NULL DEFAULT 0 COMMENT '商家id',
`type` TINYINT NOT NULL DEFAULT 0 COMMENT '广告类型 0商城首页轮播图 1门店轮播图',
`style` TINYINT NOT NULL DEFAULT 0 COMMENT '0 url 1商品id',
`url` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '链接',
`goodsId` INT NOT NULL DEFAULT 0 COMMENT '商品id',
`startTime` INT NOT NULL DEFAULT 0 COMMENT '开始时间',
`endTime` INT NOT NULL DEFAULT 0 COMMENT '结束时间，方便查询，永不过期 使用值4102416000即2100-01-01 00:00:00',
`addTime` INT NOT NULL DEFAULT 0 COMMENT '添加时间'
)COMMENT='广告管理';
ALTER TABLE xhAd ADD INDEX `merchantId`(`merchantId`,`endTime`);

2019.12.2 shish
DROP TABLE IF EXISTS xhUsage;
CREATE TABLE `xhUsage` (
  `id` INT(11) NOT NULL,
  `usageName` VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用途名称',
  `addTime` INT(11) NOT NULL DEFAULT '0' COMMENT '添加时间'
) COMMENT='商品用途';
INSERT INTO `xhUsage` (`id`, `usageName`, `addTime`) VALUES
(1, '送爱人/恋人', 1567955291),
(2, '开业', 1567955291),
(3, '婚庆', 1567955291),
(4, '家养', 1567955291),
(5, '生日', 1568038612),
(6, '会议', 1568038612),
(7, '送母亲', 1568038612),
(8, '送老师', 1568038612),
(9, '其它', 1568038612);

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;
DROP TABLE IF EXISTS `xhUserOldData`;
!!!!!!老客户没给国兰导进来的要导进来客户表里

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`;