当前位置：首页 > article >正文

MYSQL8-sql语句使用集合。MYCAT-sql语法使用集合

article 2024/11/21 1:26:50

MYSQL

1.MYSQL事务与锁问题处理

SELECT * FROM information_schema.INNODB_LOCKs; -- 查询锁
 
select * from information_schema.INNODB_LOCK_WAITS; -- 查询等待锁
 
SELECT * FROM information_schema.INNODB_TRX; -- 查询事务
 
select * from information_schema.processlist where id =7149 -- 根据事务中的进程id查询进程
 
kill 7149 -- 根据进程id杀死进程

2.查询所有下级

-- 说明：只需关注u_parent_id,u_id,字段和tb_user表，将这些替换成自己的表和字段即可，其他查询条件可最后拼接
select *,ischild from (
	select t1.*,
	if(find_in_set(u_parent_id, @pids) > 0, @pids := concat(@pids, ',', u_id), 0) as ischild
	from (
			 select * from tb_user t order by u_parent_id, u_id
			) t1,
			(select @pids := 某个节点的id) t2
 ) t3 where ischild != 0

3.jpa报错：check the manual that corresponds to your MySQL server version for the right

原因一：

check the manual that corresponds to your MySQL server version for the right

因为表字段中使用了关键字，private String describe; 

解决方法：

1.将字段describe改为des

2.加上注解：@column(name="\"describe"\")

4.mysql delete删除数据之后，数据内存未变，如果是共享表空间，可用optimize table tablename释放无用空间

-- 是否开启独享表空间，否则是共享表空间。独享表空间的表无法进行optimize操作， 因为数据删除时会重组索引并释放对应空间
show variables like 'innodb_file_per_table';
-- 查看前后效果可以使用show table status命令,例如show table status from [database] like '[table_name]';
返回结果中的data_free即为空洞所占据的存储空间。
show table status like "yintong_sys_api_log_0";
-- OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用，这个操作会使MySql锁定表。 对于InnoDB表：
-- OPTIMIZE TABLE 被映射到 ALTER TABLE上，这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。
OPTIMIZE TABLE yintong_sys_api_log_0;

5.sum合计

-- 如果数据为null则使用0
sum(IFNULL(cost,0)*IFNULL(numbers,0)) as costPrice

6.IFNULL

-- 如果为null，则使用0返回
IFNULL(cost,0)

MYCAT

1.mycat创建全局表和单库-分库分表-无主从复制

-- 创建两个数据库-同一个数据源
/*+ mycat:createDataSource{
"name":"dwyt",
"url":"jdbc:mysql://47.106.174.62:3308/mysql",
"user":"root",
"password":"djxNPZNThM2RcDZ6"
} */;

/*+ mycat:createDataSource{
"name":"dryt",
"url":"jdbc:mysql://47.106.174.62:3308/mysql",
"user":"root",
"password":"djxNPZNThM2RcDZ6"
} */;

# name、replicas、masters等请正确填写
# replicas 写法与master一样，写一个或多个数据源，需是master数据源对应的从备份库（便于查询），没有请如下留空
/*! mycat:createCluster{"name":"c0","masters":["dr_128"],"replicas":[]} */;
/*! mycat:createCluster{"name":"c1","masters":["dr106"],"replicas":[]} */;


-- 创建数据库
CREATE DATABASE yintong CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

-- 创建全局表 即在所有节点创建一个sys_user表 注意：这一步可以跳过，不是分库分表的内容
DROP TABLE `sys_user`;

CREATE TABLE `sys_user`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `username` varchar(32) NULL COMMENT '用户名',
  `password` varchar(32) NULL COMMENT '密码',
	`days` date NULL COMMENT '时间',
  PRIMARY KEY (`id`),
  KEY `id` (`id`) # 注意这个KEY
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 BROADCAST;


-- 创建分库分表-2库2表
DROP TABLE `sys_user`;
CREATE TABLE `sys_user`  (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `username` varchar(32) NULL COMMENT '用户名',
  `password` varchar(32) NULL COMMENT '密码',
  PRIMARY KEY (`id`),
	 KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 dbpartition by mod_hash(id) tbpartition by mod_hash(id) tbpartitions 4 dbpartitions 2;

-- 新增数据测试
INSERT INTO `sys_user` (id,username,password)  VALUES (1,'user1','123456');


-- 查看当前数据库中，哪些表数据最多，是重要参考点
SELECT table_name,table_rows FROM information_schema.tables   WHERE TABLE_SCHEMA = 'ryhh' ORDER BY table_rows DESC;


-- 表按月分表
/*+ mycat:createTable{
	"schemaName":"yintong",
	"shardingTable":{
		"createTableSQL":"CREATE TABLE `yintong`.`test` (`name` varchar(10),`create_time` date);",
		"function":{
			"clazz":"io.mycat.router.mycat1xfunction.PartitionByMonth",
				"properties":{
					"beginDate":"2021-01-01",
					"dateFormat":"yyyy-MM-dd",
					"endDate":"2023-10-31",
					"columnName":"create_time"
				},
			"ranges":{}
		},
		"partition":{
			"schemaNames":"yintongtest",
			"tableNames":"test_$202101-202103",
			"targetNames":"prototype"
		}
	},
	"tableName":"test"
} */;

INSERT INTO `test` (name,create_time)  VALUES ('testdd','2022-08-09 00:00:00');
INSERT INTO `test` (name,create_time)  VALUES ('test','2021-12-12 00:00:00');
INSERT INTO `test` (name,create_time)  VALUES ('test','2021-13-12 00:00:00');
INSERT INTO `test` (name,create_time)  VALUES ('test','2021-02-12 00:00:00');
select * from test


//按年月分库分表
create table travelrecord (
 ....
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
dbpartition by YYYYMM(xxx) dbpartitions 8
tbpartition by xxx(xx) tbpartitions 12;

CREATE TABLE user_log2(
userId varchar(32),
name varchar(30),
operation varchar(30),
actionDate DATE(NOW())
) dbpartition by YYYYMM(actionDate) dbpartitions 8 tbpartition by MM(actionDate) tbpartitions 12;
INSERT INTO `user_log2` (userId,actionDate)  VALUES ('1111',now());
INSERT INTO `user_log2` (userId,actionDate)  VALUES ('222','2021-02-12 00:00:00');


-- 维修企业编码 
DROP TABLE `companyinfo`;
CREATE TABLE `companyinfo`  (
  `companyid` int NOT NULL COMMENT '维修企业id',
  `companycode` varchar(20) NOT NULL COMMENT '维修企业编码',
  `companyname` varchar(60) NOT NULL COMMENT '维修企业名称',
  `areaid` int NOT NULL COMMENT '区域id',
  `typeid` smallint NOT NULL COMMENT '类型:0=未知，1=一类，2=二类，3=三类',
  `statetag` smallint NOT NULL COMMENT '状态:0=未用，1=在用，2=删除',
  `lastdate` datetime NOT NULL COMMENT '最后更改时间',
  `latitude` decimal(12, 6) NOT NULL COMMENT '纬度',
  `longitude` decimal(12, 6) NOT NULL COMMENT '经度',
  `repairnum` int NOT NULL COMMENT '维修量',
  `goodnum` int NOT NULL COMMENT '好评量',
  `starlevel` smallint NOT NULL COMMENT '星级',
  `judgenum` int NOT NULL COMMENT '评价量',
  `address` varchar(100) NOT NULL COMMENT '地址',
  `linkman` varchar(20) NOT NULL COMMENT '联系人',
  `linktel` varchar(20) NOT NULL COMMENT '联系电话',
  `companypassword` varchar(20) NOT NULL,
  PRIMARY KEY (`companyid`),
  UNIQUE INDEX `ix1_companyinfo`(`companycode` ASC) USING BTREE
)  COMMENT = '维修企业编码'

-- 区域文件
CREATE TABLE `areacode`  (
  `areaid` int NOT NULL COMMENT '区域id',
  `areaname` varchar(20) NOT NULL COMMENT '区域名称',
  `ordid` smallint NOT NULL COMMENT '序号',
  `statetag` smallint NOT NULL COMMENT '状态',
  `lastdate` datetime NOT NULL COMMENT '最后更改时间',
  `noused` smallint NOT NULL,
  `num1` smallint NOT NULL,
  `num2` smallint NOT NULL,
  `num3` smallint NOT NULL,
  PRIMARY KEY (`areaid`)
) COMMENT = '区域文件';

-- 维修单主表 按 id  uni_HASH 分库(转hash再取模) ，时间分表(超过12月，从头来)
CREATE TABLE`repairbillx`  (
  `id` int NOT NULL COMMENT 'key',
  `companycode` varchar(20) NOT NULL COMMENT '维修企业编码',
  `noteno` varchar(30) NOT NULL COMMENT '单据号',
  `repairdate` datetime NOT NULL COMMENT '送修日期',
  `license` varchar(20) NOT NULL COMMENT '车辆牌照',
  `vin` varchar(20) NOT NULL COMMENT '车辆识别号',
  `wxlx` varchar(20) NOT NULL COMMENT '维修类型',
  `cartype` varchar(20) NOT NULL COMMENT '车型',
  `carcolor` varchar(20) NOT NULL COMMENT '车身颜色',
  `cpcolor` varchar(10) NOT NULL COMMENT '车牌颜色',
  `repairman` varchar(20) NOT NULL COMMENT '送修人',
  `mobile` varchar(20) NOT NULL COMMENT '联系电话',
  `mileage` varchar(16) NOT NULL COMMENT '行驶里程',
  `oil` varchar(10) NOT NULL COMMENT '存油量',
  `fueltype` varchar(10) NOT NULL COMMENT '燃料（能源）类型:A	汽油\r\n         B	柴油\r\n         C	电（以电能驱动的汽车）\r\n         D	混合油\r\n         E	天然气\r\n         F	液化石油气\r\n         L	甲醇\r\n         M	乙醇\r\n         N	太阳能\r\n         O	混合动力(电动机作为辅助驱动的机动车)\r\n         P	氢\r\n         Q	生物燃料\r\n         R	二甲醚\r\n         Y	无（仅限全挂车等无动力的）\r\n         Z	其他\r\n         ',
  `usecharacter` varchar(2) NOT NULL COMMENT '车辆使用性质:01	营运\r\n       02	非营运\r\n       ',
  `wxremark` varchar(200) NOT NULL COMMENT '维修备注:补充说明',
  `faultdescription` varchar(200) NOT NULL COMMENT '故障描述:到店情况，故障描述',
  `overdate` datetime NOT NULL COMMENT '完工日期',
  `jsqdno` varchar(24) NOT NULL COMMENT '结算清单号:以自身11位企业备案编号+ERP服务商编号3位+时间戳6位(年月日220419+排序号3位，生成23位的唯一结算清单号',
  `checkdate` datetime NOT NULL COMMENT '结算日期',
  `totalcurr` decimal(14, 2) NOT NULL COMMENT '总金额',
  `checkcurr` decimal(14, 2) NOT NULL COMMENT '实结金额:checkcurr=totalcurr-freecurr-jfcurr-djjcurr',
  `operant` varchar(20) NOT NULL COMMENT '登记人',
  `lastdate` datetime NOT NULL COMMENT '最后更改时间',
  `ly` smallint NOT NULL COMMENT '来源:1=小管家，2=Top9,0=其它,99=公路院',
  `createdate` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `ix1_repairbillx`(`companycode` ASC, `noteno` ASC) USING BTREE,
  INDEX `ix2_repairbillx`(`repairdate` ASC) USING BTREE
) COMMENT = '维修单主表'
dbpartition by mod_hash (companycode) dbpartitions 3
tbpartition by MM(createdate) tbpartitions 6;

-- 维修评价表
CREATE TABLE `repairevaluatex`  (
  `id` int NOT NULL COMMENT 'key',
  `companycode` varchar(20) NOT NULL COMMENT '维修企业编码',
  `noteno` varchar(20) NOT NULL COMMENT '维修单据号',
  `notedate` datetime NOT NULL COMMENT '维修单日期',
  `license` varchar(20) NOT NULL COMMENT '车辆牌照',
  `vin` varchar(20) NOT NULL COMMENT '车辆识别号',
  `linkman` varchar(20) NOT NULL COMMENT '联系人',
  `mobile` varchar(20) NOT NULL COMMENT '联系电话',
  `evaldate` datetime NOT NULL COMMENT '评价日期',
  `econtent` text NOT NULL COMMENT '评价内容:到店情况，故障描述',
  `egrade` varchar(10) NOT NULL COMMENT '评价等级:1=不满意 3=满意 5=非常满意',
  `lastop` varchar(20) NOT NULL COMMENT '最后操作人',
  `lastdate` datetime NOT NULL COMMENT '最后更改时间',
  `createdate` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `ix1_repairevaluatex`(`companycode` ASC, `noteno` ASC) USING BTREE
) COMMENT = '维修评价表'
dbpartition by mod_hash (companycode) dbpartitions 3
tbpartition by MM(createdate) tbpartitions 6;

-- 维修单配件明细
CREATE TABLE `repairpartsx`  (
  `id` int NOT NULL COMMENT 'key',
  `noteno` varchar(30) NOT NULL COMMENT '工单',
  `companycode` varchar(20) NOT NULL COMMENT '维修企业编码',
  `partcode` varchar(50) NOT NULL COMMENT '配件代号',
  `partname` varchar(100) NOT NULL COMMENT '配件名称',
  `units` varchar(20) NULL COMMENT '计量单位',
  `brand` varchar(30) NULL,
  `amount` decimal(10, 2) NOT NULL COMMENT '数量',
  `price` decimal(14, 2) NOT NULL COMMENT '单价',
  `discount` decimal(10, 2) NOT NULL COMMENT '折扣',
  `curr` decimal(14, 2) NOT NULL COMMENT '金额',
  `remark0` varchar(60) NOT NULL COMMENT '备注',
  `lastdate` datetime NOT NULL COMMENT '最后更改时间',
  INDEX `ix1_repairpartsx`(`companycode` ASC, `noteno` ASC) USING BTREE
) COMMENT = '维修单配件明细'
dbpartition by mod_hash (companycode) dbpartitions 3
tbpartition by MM(lastdate) tbpartitions 6;

-- 维修单项目明细
CREATE TABLE `repairprojectx`  (
  `id` int NOT NULL COMMENT 'key',
  `noteno` varchar(30) NOT NULL COMMENT '工单',
  `projectname` varchar(100) NOT NULL COMMENT '维修项目',
  `companycode` varchar(20) NOT NULL COMMENT '维修企业编码',
  `workhours` decimal(10, 2) NOT NULL COMMENT '维修工时',
  `hourprice` decimal(14, 2) NOT NULL COMMENT '工时单价',
  `hourcurr` decimal(14, 2) NOT NULL COMMENT '工时金额',
  `remark0` varchar(60) NOT NULL COMMENT '备注',
  `lastdate` datetime NOT NULL COMMENT '最后更改时间',
  INDEX `ix1_repairprojectx`(`companycode` ASC, `noteno` ASC) USING BTREE
) COMMENT = '维修单项目明细'
dbpartition by mod_hash (companycode) dbpartitions 3
tbpartition by MM(lastdate) tbpartitions 6;

-- 小管家 配件材料记录表 
CREATE TABLE `yintong_goods_record`  (
  `id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键id',
  `order_no` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '单号',
  `gid` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '配件id',
  `cost_price_pre` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '账面金额-上次结存',
  `numbers_last_pre` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '账面数-上次结存',
  `numbers_last` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '结存数量',
  `numbers` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '本次成交数量/工时量',
  `cost_last` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '结余成本单价',
  `cost_price` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '结存金额',
  `cost` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '本次成本单价/每个工时价格',
  `cost_sale` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '销售单价',
  `price` decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT '本次成交价格',
  `state` int NOT NULL COMMENT '1保存 2提交',
  `pay_state` int NOT NULL DEFAULT 2 COMMENT '1已结 2未结3未结清',
  `version` int NOT NULL DEFAULT 0 COMMENT '版本',
  `tenant_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '租户id',
  `user_id` int NOT NULL COMMENT '开单人id',
  `name` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '开单人姓名',
  `car_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '使用车辆id',
  `car_user_name` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '送修人-默认带出车主姓名，可修改为其他姓名',
  `sale_user_id` int NULL DEFAULT NULL COMMENT '销售人id',
  `sale_user_name` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '销售人姓名',
  `ac_user_id` int NULL DEFAULT NULL COMMENT '施工人id',
  `ac_user_name` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '施工人姓名',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `action_time` datetime NULL DEFAULT NULL COMMENT '操作时间（领料时间，入库时间等等）',
  `type` int NULL DEFAULT NULL COMMENT '类型：1维修 2洗美 3销售 4套餐 5采购 6期初 7盘点',
  `type_state` int NULL DEFAULT NULL COMMENT '类型状态： 1加  2减',
  `record_type` int NOT NULL DEFAULT 1 COMMENT '记录类型：1配件 2项目',
  `price_discount` decimal(10, 2) NULL DEFAULT 1.00 COMMENT '本次折扣',
  `unit` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '单位',
  `spec` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '规格',
  `gname` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '配件名称',
  `sid` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '店鋪id',
  `mcid` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '套餐/模板内容id与配件id/项目id。一对一\"',
  `des` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `pay_time` datetime NULL DEFAULT NULL COMMENT '支付时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic
dbpartition by mod_hash (tenant_id) dbpartitions 3
tbpartition by MM(create_time) tbpartitions 6;

-- 小管家账单表
CREATE TABLE `yintong_user_bill`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `trade_no` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '单号',
  `user_id` int NULL DEFAULT NULL COMMENT '用户id',
  `vm` decimal(10, 2) NULL DEFAULT NULL COMMENT '应结额度',
  `vmed` decimal(10, 2) NULL DEFAULT NULL COMMENT '实际结算额度-暂未用',
  `before_vm` decimal(10, 2) NULL DEFAULT NULL COMMENT '消费前',
  `end_vm` decimal(10, 2) NULL DEFAULT NULL COMMENT '消费后',
  `time` timestamp NULL DEFAULT NULL COMMENT '消费时间',
  `type` int NULL DEFAULT NULL COMMENT '消费类型（1:积分增加，2积分减少，3收入增加，4收入减少）',
  `des` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '描述',
  `state` int NULL DEFAULT NULL COMMENT '交易状态（0创建，1完成，2失败，3关闭）',
  `pay_type` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '支付类型',
  `link_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关联id ',
  `create_time` datetime NULL DEFAULT NULL,
  `update_time` datetime NULL DEFAULT NULL,
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
  `operate_user` int NULL DEFAULT NULL COMMENT '后台操作人员ID',
  `tenant_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '租户id',
  `to_user_id` int NULL DEFAULT NULL,
  `ptrade_no` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '上级单号',
  `car_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '车辆id',
  `pay_method` int NULL DEFAULT 1 COMMENT '结算方式  1现结，2挂账',
  `cus_id` int NULL DEFAULT NULL COMMENT '挂账客户',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1138 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '账单' ROW_FORMAT = Dynamic
dbpartition by mod_hash (tenant_id) dbpartitions 3
tbpartition by MM(create_time) tbpartitions 6;

-- 小管家工单表
CREATE TABLE `yintong_order`  (
  `id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID',
  `user_id` int NOT NULL COMMENT '开单人id',
  `order_no` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '单号',
  `name` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '开单人姓名',
  `state` int NOT NULL DEFAULT 1 COMMENT '1待修2在修3完工4结算',
  `gstate` int NOT NULL DEFAULT 1 COMMENT '1正常 2待料 3挂账',
  `gwhy` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '待料原因',
  `numbers` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '数量',
  `pay_state` int NOT NULL DEFAULT 2 COMMENT '1已结 2未结 3未结清',
  `car_user_id` int NULL DEFAULT NULL COMMENT '会员id-车归属会员。或者采购人',
  `car_cus_name` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '客户名称-车挂账客户名称-默认车辆归属客户名称',
  `car_cus_id` int NULL DEFAULT NULL COMMENT '客户id-车挂账客户-默认车辆归属客户',
  `car_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '使用车辆id',
  `car_user_name` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '送修人-默认带出车主姓名，可修改为其他姓名',
  `mobile` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '送修人联系电话-默认带出车主联系电话',
  `addr` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '送修人地址',
  `omobile` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '送修人其他联系',
  `mileage` double NULL DEFAULT NULL COMMENT '行驶里程 公里',
  `oil` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '油量',
  `datel` date NULL DEFAULT NULL COMMENT '下次保养时间=上次保养时间+180天-otype业务类型为一般保养时填写',
  `mileagel` double NULL DEFAULT NULL COMMENT '下次保养里程=上次保养里程+8000公里-otype业务类型为一般保养时填写',
  `create_time` datetime NULL DEFAULT NULL,
  `update_time` datetime NULL DEFAULT NULL,
  `no_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '挂账金额=实结金额 -第一次支付后的已结金额',
  `price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '已结金额',
  `rel_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '实结金额=应结金额-抹零优惠-项目优惠金额-配件优惠金额-会员优惠金额-其他优惠金额',
  `red_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '减免金额',
  `vip_red_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '会员优惠金额',
  `other_red_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '其他优惠金额',
  `total_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '应结金额=配件金额+项目金额',
  `goods_red_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '配件优惠金额',
  `goods_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '配件金额',
  `pro_red_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '项目优惠金额',
  `pro_price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '项目金额',
  `pay_time` datetime NULL DEFAULT NULL COMMENT '结算时间',
  `from_id` bigint NULL DEFAULT NULL COMMENT '供应商id',
  `from_name` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '供应商名称',
  `car_number` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '车牌号',
  `car_color` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '车牌颜色',
  `vin` varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'VIN',
  `type` int NULL DEFAULT NULL COMMENT '类型：1维修 2洗美 3销售 4套餐 5采购 6期初 7盘点',
  `type_state` int NULL DEFAULT NULL COMMENT '类型状态： 1加库存  2减库存',
  `sid` int NULL DEFAULT NULL COMMENT '店铺id',
  `tenant_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '租户id',
  `des` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `car_brand` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '品牌类型-默认车辆类型',
  `content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '故障说明',
  `imgs` varchar(2550) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '图片',
  `car_end_time` datetime NULL DEFAULT NULL COMMENT '交车时间',
  `car_pre_end_time` datetime NULL DEFAULT NULL COMMENT '预计交车时间',
  `methods` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '到店途径',
  `sign` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '签名',
  `otype` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '维修类型',
  `sex` int NULL DEFAULT NULL COMMENT '送修人性别 1男 2女 默认车主性别',
  `server_user_id` int NULL DEFAULT NULL COMMENT '服务顾问用户id',
  `server_from` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '业务来源',
  `cus_type` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '客户类型',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `orderNo_tenant_id`(`order_no`, `tenant_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '工单' ROW_FORMAT = Dynamic 
dbpartition by mod_hash (tenant_id) dbpartitions 3
tbpartition by MM(create_time) tbpartitions 6;

-- 小管家每日总结表
CREATE TABLE `yintong_goods_day`  (
  `id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `gid` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '配件id',
  `number_last` decimal(10, 2) NULL DEFAULT NULL COMMENT '数量',
  `cost_last` decimal(10, 2) NULL DEFAULT NULL COMMENT '成本单价',
  `cost_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '成本金额',
  `sale_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '零售价',
  `sale_price_last` decimal(10, 2) NULL DEFAULT NULL COMMENT '零售额',
  `create_time` datetime NULL DEFAULT NULL,
  `update_time` datetime NULL DEFAULT NULL,
  `time` date NULL DEFAULT NULL COMMENT '日期',
  `tenant_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '租户id',
  `sid` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '店铺id',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `g-t-s`(`gid`, `time`, `sid`) USING BTREE COMMENT '门店下 配件时间唯一'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic
dbpartition by mod_hash (tenant_id) dbpartitions 3
tbpartition by MM(create_time) tbpartitions 6;


-- 小管家车辆档案修改历史
CREATE TABLE `yintong_car_updrecord`  (
  `id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID',
  `user_id` int NULL DEFAULT NULL COMMENT '所属用户id',
  `sale_user_id` int NULL DEFAULT NULL COMMENT '推广用户id',
  `sale_user_name` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '推广用户姓名',
  `server_user_id` int NULL DEFAULT NULL COMMENT '服务顾问id',
  `server_user_name` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '服务顾问姓名',
  `car_number` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '车牌号',
  `car_color` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '车牌颜色',
  `vin` varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'VIN',
  `color` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '车身颜色',
  `mobile` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '联系号码',
  `car_owner` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '车主姓名',
  `brand_drive` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '品牌车型',
  `fuel_type` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '燃料类型',
  `car_type` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '车类型  1suv 2轿车 3越野',
  `use_type` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '1' COMMENT '使用性质 1非营运  2营运',
  `engine_no` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '发动机号',
  `drive_photo` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '行驶证照片',
  `push_time` datetime NULL DEFAULT NULL COMMENT '发证日期',
  `brand` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '品牌型号',
  `car_user` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '车辆所有人',
  `reg_time` datetime NULL DEFAULT NULL COMMENT '行驶证注册日期',
  `force_no` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '交强险单号',
  `discuss_no` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '商业险单号',
  `Insurance_discuss_date` date NULL DEFAULT NULL COMMENT '商业险日期',
  `Insurance_force_date` date NULL DEFAULT NULL COMMENT '交强险日期',
  `inspect_date` date NULL DEFAULT NULL COMMENT '年检日期',
  `pre_protect` date NULL DEFAULT NULL COMMENT '上次保养时间',
  `next_protect` date NULL DEFAULT NULL COMMENT '下次保养时间',
  `insure_name` varchar(55) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '保险公司',
  `insure_img` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '保险照片',
  `next_mileage` double NULL DEFAULT NULL COMMENT '下次保养里程',
  `before_mileage` double NULL DEFAULT NULL COMMENT '上次保养里程',
  `des` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `state` int NULL DEFAULT 1 COMMENT '车辆状态 1启用 2禁用',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `tenant_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '租户id',
  `other_link` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '其他联系人-jsonarray字符串[{name,mobile}]',
  `imgs` varchar(2550) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '车辆图片',
  `updtime` datetime NOT NULL COMMENT '修改时间',
  `uid` int NOT NULL COMMENT '修改人id',
  `car_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '车辆id',
  `car_number_pre` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '车牌号-修改前',
  `car_color_pre` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '车牌颜色-修改前',
  `vin_pre` varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'VIN-修改前',
  `cretime` datetime NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic
dbpartition by mod_hash (tenant_id) dbpartitions 3
tbpartition by MM(create_time) tbpartitions 6;

-- 小管家日志表
CREATE TABLE `yintong_sys_api_log`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `api_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '接口id(类名+方法名)',
  `user_id` int NOT NULL COMMENT '请求用户id',
  `full_uri` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '请求参数',
  `user_agent` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '请求设备',
  `ip` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '请求ip',
  `total_time` bigint NULL DEFAULT NULL COMMENT '接口耗时',
  `stack_trace` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '异常信息',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime NULL DEFAULT NULL,
  `port` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '请求ip端口',
  `tenant_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '租户id',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `apiId`(`api_id`) USING BTREE,
  INDEX `tenant_id`(`tenant_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 197829 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '系统日志' ROW_FORMAT = Dynamic
dbpartition by mod_hash (tenant_id) dbpartitions 3
tbpartition by MM(create_time) tbpartitions 6;

-- 小管家操作日志表
CREATE TABLE `yintong_sys_ac_log`  (
  `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID',
  `api_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '接口id(类名+方法名)',
  `user_id` int NOT NULL COMMENT '请求用户id',
  `full_uri` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '请求参数',
  `user_agent` varchar(125) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '请求设备',
  `ip` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '请求ip',
	 `acname` varchar(55) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作名称',
  `total_time` bigint NULL DEFAULT NULL COMMENT '接口耗时',
  `stack_trace` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '异常信息',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime NULL DEFAULT NULL,
  `port` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '请求ip端口',
  `tenant_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '租户id',
	`sid` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '门店id',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `apiId`(`api_id`) USING BTREE,
	INDEX `sid`(`sid`) USING BTREE,
  INDEX `tenant_id`(`tenant_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '操作日志' ROW_FORMAT = Dynamic 
dbpartition by mod_hash (tenant_id) dbpartitions 3
tbpartition by MM(create_time) tbpartitions 6;

-- 维修单项目明细
CREATE TABLE `yintong_attach_cost`  (
  `id` VARCHAR(128) NOT NULL COMMENT '主键id',
  `name` varchar(55) NOT NULL COMMENT '名称',
  `price` decimal(10, 2) NOT NULL DEFAULT 0.00  COMMENT '金额',
  `cost` decimal(14, 2) NOT NULL DEFAULT 0.00 COMMENT '成本',
  `des` varchar(255)  COMMENT '备注',
  `create_time` datetime NULL DEFAULT NULL,
  `update_time` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
) COMMENT = '维修单项目明细'

-- 添加主键索引
ALTER TABLE table_name ADD PRIMARY KEY ( column)
-- 添加唯一索引
ALTER TABLE table_name ADD UNIQUE (column) 
-- 添加全文所以
ALTER TABLE table_name ADD FULLTEXT ( column) 
-- 添加复合索引
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )
-- 添加索引
ALTER TABLE yintong_user_bill ADD INDEX tenant_id ( tenant_id ) 
ALTER TABLE repairbillx ADD INDEX  license( license ) 
-- 删除索引
DROP INDEX tenant_id ON yintong_user_bill
-- 修改字段 类型
ALTER TABLE companyinfo MODIFY companyid BIGINT(20) AUTO_INCREMENT;
ALTER TABLE repairpartsx MODIFY id BIGINT(20) AUTO_INCREMENT;
ALTER TABLE repairprojectx MODIFY id BIGINT(20) AUTO_INCREMENT;
ALTER TABLE repairevaluatex MODIFY id BIGINT(20) AUTO_INCREMENT;
ALTER TABLE repairbillx MODIFY id BIGINT(20) AUTO_INCREMENT;

-- 新增数据测试
		INSERT INTO `repairevaluatex` (`id`, `companycode`, `noteno`, `notedate`, `license`, `vin`, `linkman`, `mobile`, `evaldate`, `econtent`, `egrade`, `lastop`, `lastdate`, `createdate`) VALUES (1, 1, 1, "2023-08-09", 1, 1, 1, 1, "2023-08-09", 1, 1, 1, "2023-08-09", "2023-08-09");
		SELECT * FROM 			repairevaluatex

查看全文

http://www.kler.cn/a/105656.html