【MySQL】记录一次业务SQL

SQL

1.记录一次业务SQL

-- 修改表
ALTER TABLE `meituan_bill` MODIFY `sync_state` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '同步状态' AFTER `bill_end_account_balance`;
ALTER TABLE `meituan_bill` ADD COLUMN `diff_state` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '差异状态 0-无差异 1-有差异' AFTER `sync_state`;
ALTER TABLE `meituan_bill` ADD COLUMN `diff_content` varchar(255) DEFAULT '' COMMENT '差异内容' AFTER `diff_state`;
ALTER TABLE `meituan_bill` ADD COLUMN `diff_remark` longtext COMMENT '差异备注' AFTER `diff_content`;
ALTER TABLE `meituan_bill` ADD COLUMN `bill_state` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '账单状态 0-未确认 1-已确认 2-已生成付款单' AFTER `diff_remark`;
ALTER TABLE `meituan_bill_order` ADD COLUMN `bill_period` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '账期' AFTER `company_subject`;

-- 修改索引
ALTER TABLE `meituan_bill_order` DROP INDEX `uniq_1`;
ALTER TABLE `meituan_bill_order` ADD UNIQUE `uniq_1` ( `bill_period`, `sqt_biz_order_id` );

-- 新增表
DROP TABLE IF EXISTS `meituan_account`;
CREATE TABLE `meituan_account` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表自增主键',
    `bill_period` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '账期',
    `account_id` varchar(24) NOT NULL DEFAULT '' COMMENT '账户ID',
    `available_balance` varchar(24) NOT NULL DEFAULT '0' COMMENT '账户可用余额',
    `created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
    `updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_1` (`bill_period`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='美团账户表';

DROP TABLE IF EXISTS `meituan_diff_order_count`;
CREATE TABLE `meituan_diff_order_count` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表自增主键',
  `bill_period` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '账期',
  `sqt_biz_order_id` varchar(64) NOT NULL DEFAULT '' COMMENT '订单编号',
  `order_source` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '订单来源 0-订单表 1- 账单关联订单表',
  `order_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '下单时间戳，单位秒',
  `create_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT  '创建时间戳，单位秒',
  `update_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT  '更新时间戳，单位秒',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`bill_period`,`sqt_biz_order_id`,`order_source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='美团订单数量差异表';

DROP TABLE IF EXISTS `meituan_diff_order_amount`;
CREATE TABLE `meituan_diff_order_amount` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表自增主键',
  `bill_period` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '账期',
  `sqt_biz_order_id` varchar(64) NOT NULL DEFAULT '' COMMENT '订单编号',
  `order_source` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '订单来源 0-订单表 1- 账单关联订单表',
  `order_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '下单时间戳，单位秒',
  `diff_amount` VARCHAR(24) NOT NULL DEFAULT '0' COMMENT '差异金额',
  `create_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间戳，单位秒',
  `update_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间戳，单位秒',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`bill_period`,`sqt_biz_order_id`,`order_source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='美团订单金额差异表';

DROP TABLE IF EXISTS `sys_config`;
CREATE TABLE `sys_config` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表自增主键',
  `user_id` varchar(24) NOT NULL DEFAULT '' COMMENT '员工id',
  `config_key` varchar(64) NOT NULL DEFAULT '' COMMENT '配置键名',
  `config_value` longtext NOT NULL COMMENT '配置键值',
  `create_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间戳，单位秒',
  `update_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间戳，单位秒',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_1` (`user_id`,`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';