创建表DDL示例
DROP TABLE IF EXISTS `t_products`;
CREATE TABLE `t_products` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '产品类型ID',
`object_id` bigint(20) NULL DEFAULT NULL COMMENT '物模型ID',
`name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`description` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '产品类型描述',
`creator_id` int(11) NULL DEFAULT NULL COMMENT '创建者的工号',
`category_id` int(11) NOT NULL COMMENT '产品领域ID',
`category_name` varchar(255) NOT NULL COMMENT '产品领域名称',
`node_type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '节点类型,0:直连设备,1:网关设备,2:网关子设备',
`link_type` varchar(128) CHARACTER SET utf8mb4_bin COLLATE utf8mb4_general_ci NOT NULL COMMENT '联网方式',
`auth_type` varchar(128) CHARACTER SET utf8mb4_bin COLLATE utf8mb4_general_ci NOT NULL COMMENT '认证方式',
`state` int(11) NOT NULL COMMENT '发布状态,0:未发布,1:发布',
`create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime(0) NOT DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
`inner_type` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '内部型号',
`product_ref_num` int(11) NULL DEFAULT NULL COMMENT '关联产品规格个数',
`object_category_id` bigint(20) NULL DEFAULT NULL COMMENT '关联物模型模板时有意义',
`creator_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建者的loginName',
`deleted` tinyint(2) NOT NULL DEFAULT 0 COMMENT '删除标志,1:已删除',
PRIMARY KEY (`id`, `update_time`) USING BTREE COMMENT '联合主键,意义不大',
UNIQUE INDEX `IDX_INNER_TYPE`(`inner_type`, `deleted`) USING BTREE COMMENT '全网唯一,最小长度1,最大64',
UNIQUE INDEX `IDX_NAME_CATEGORY_UNI`(`name`, `category_id`, `deleted`) USING BTREE COMMENT '同一产品领域下,产品类型唯一',
INDEX `IDX_UPDATE_TIME`(`update_time`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '产品类型' ROW_FORMAT = Compact;
CREATE TABLE `tb_estate` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL COMMENT '楼盘名称',
`province` varchar(10) DEFAULT NULL COMMENT '所在省',
`city` varchar(10) DEFAULT NULL COMMENT '所在市',
`area` varchar(10) DEFAULT NULL COMMENT '所在区',
`address` varchar(100) DEFAULT NULL COMMENT '具体地址',
`year` varchar(10) DEFAULT NULL COMMENT '建筑年代',
`type` varchar(10) DEFAULT NULL COMMENT '建筑类型',
`property_cost` varchar(10) DEFAULT NULL COMMENT '物业费',
`property_company` varchar(20) DEFAULT NULL COMMENT '物业公司',
`developers` varchar(20) DEFAULT NULL COMMENT '开发商',
`created` datetime DEFAULT NULL COMMENT '创建时间',
`updated` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=utf8mb4 COMMENT='楼盘表';
-- 初始数据
INSERT INTO `tb_estate` (`id`, `name`, `province`, `city`, `area`, `address`, `year`, `type`, `property_cost`, `property_company`, `developers`, `created`, `updated`) VALUES ('1001', '中远两湾城', '上海市', '上海市', '普陀区', '远景路97弄', '2001', '塔楼/板楼', '1.5', '上海中远物业管理发展有限公司', '上海万业企业股份有限公司', '2018-11-06 23:00:20', '2018-11-06 23:00:23');
INSERT INTO `tb_estate` (`id`, `name`, `province`, `city`, `area`, `address`, `year`, `type`, `property_cost`, `property_company`, `developers`, `created`, `updated`) VALUES ('1002', '上海康城', '上海市', '上海市', '闵行区', '莘松路958弄', '2001', '塔楼/板楼', '1.5', '盛孚物业', '闵行房地产', '2018-11-06 23:02:30', '2018-11-27 23:02:33');
INSERT INTO `tb_estate` (`id`, `name`, `province`, `city`, `area`, `address`, `year`, `type`, `property_cost`, `property_company`, `developers`, `created`, `updated`) VALUES ('1003', '保利西子湾', '上海市', '上海市', '松江区', '广富林路1188弄', '2008', '塔楼/板楼', '1.75', '上海保利物业管理', '上海城乾房地产开发有限公司', '2018-11-06 23:04:22', '2018-11-06 23:04:25');
INSERT INTO `tb_estate` (`id`, `name`, `province`, `city`, `area`, `address`, `year`, `type`, `property_cost`, `property_company`, `developers`, `created`, `updated`) VALUES ('1004', '万科城市花园', '上海市', '上海市', '松江区', '广富林路1188弄', '2002', '塔楼/板楼', '1.5', '上海保利物业管理', '上海城乾房地产开发有限公司', '2018-11-13 16:43:40', '2018-11-13 16:43:42');
INSERT INTO `tb_estate` (`id`, `name`, `province`, `city`, `area`, `address`, `year`, `type`, `property_cost`, `property_company`, `developers`, `created`, `updated`) VALUES ('1005', '上海阳城', '上海市', '上海市', '闵行区', '罗锦路888弄', '2002', '塔楼/板楼', '1.5', '上海莲阳物业管理有限公司', '上海莲城房地产开发有限公司', '2018-11-06 23:23:52', '2018-11-06 23:23:55');
CREATE TABLE `tb_house_resources` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(100) DEFAULT NULL COMMENT '房源标题',
`estate_id` bigint(20) DEFAULT NULL COMMENT '楼盘id',
`building_num` varchar(5) DEFAULT NULL COMMENT '楼号(栋)',
`building_unit` varchar(5) DEFAULT NULL COMMENT '单元号',
`building_floor_num` varchar(5) DEFAULT NULL COMMENT '门牌号',
`rent` int(10) DEFAULT NULL COMMENT '租金',
`rent_method` tinyint(1) DEFAULT NULL COMMENT '租赁方式,1-整租,2-合租',
`payment_method` tinyint(1) DEFAULT NULL COMMENT '支付方式,1-付一押一,2-付三押一,3-付六押一,4-年付押一,5-其它',
`house_type` varchar(255) DEFAULT NULL COMMENT '户型,如:2室1厅1卫',
`covered_area` varchar(10) DEFAULT NULL COMMENT '建筑面积',
`use_area` varchar(10) DEFAULT NULL COMMENT '使用面积',
`floor` varchar(10) DEFAULT NULL COMMENT '楼层,如:8/26',
`orientation` varchar(2) DEFAULT NULL COMMENT '朝向:东、南、西、北',
`decoration` tinyint(1) DEFAULT NULL COMMENT '装修,1-精装,2-简装,3-毛坯',
`facilities` varchar(50) DEFAULT NULL COMMENT '配套设施, 如:1,2,3',
`pic` varchar(200) DEFAULT NULL COMMENT '图片,最多5张',
`house_desc` varchar(200) DEFAULT NULL COMMENT '描述',
`contact` varchar(10) DEFAULT NULL COMMENT '联系人',
`mobile` varchar(11) DEFAULT NULL COMMENT '手机号',
`time` tinyint(1) DEFAULT NULL COMMENT '看房时间,1-上午,2-中午,3-下午,4-晚上,5-全天',
`property_cost` varchar(10) DEFAULT NULL COMMENT '物业费',
`created` datetime DEFAULT NULL,
`updated` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='房源表';
批量插入 注意小括号()
create table stock(
id int auto_increment not null,
product_id int not null comment '商品ID',
category_id int not null comment '分类ID',
warehouse_id int not null comment '仓库ID',
count int not null comment '库存数量',
primary key(id)
) comment '库存表';
insert into stock(product_id,category_id,warehouse_id,count)
values(2030,9,1,10),(2030,9,2,15),(2030,9,3,20),(2040,8,1,30),
(2040,8,2,20);
select product_id,warehouse_id,sum(count) from stock group by product_id;
以表中已有的字段递增作为新记录的值
/**
* INSERT INTO t_product_inner_identifier ( product_id, identifier, ref ) SELECT
* 1,
* '22',
* ref + 1
* FROM
* t_product_inner_identifier
* WHERE
* product_id = 1
* ORDER BY
* ref DESC
* LIMIT 1
* @param entity
* @return
*/
@Insert("insert into t_product_inner_identifier(product_id, identifier, ref) " +
"select #{productId}, #{identifier},ref + 1 from t_product_inner_identifier where product_id = #{productId} order by ref desc limit 1")
@Options(useGeneratedKeys = true, keyProperty = "id")
int insertEntity(ProductInnerIdentifierEntity entity);
分页查询的mapper
select
distinct
p.id,
p.object_id,
p.name,
p.description,
p.creator_id,
p.category_id,
p.node_type,
p.link_type,
p.auth_type,
p.state,
p.inner_type,
p.object_category_id,
p.create_time,
p.product_ref_num,
p.creator_name,
p.update_time,
pc.name as categoryName
from t_products p
left join t_product_category pc
on p.category_id = pc.id
<where>
<if test="queryDTO.productStatus != null">
AND product_status = #{queryDTO.productStatus}
</if>
<if test="queryDTO.name != null and queryDTO.name != ''">
AND INSTR(name,#{queryDTO.name})
</if>
</where>
order by
update_time desc
</select>
mysql在不给定order by条件的时候,得到的数据结果的顺序是跟查询列有关的。 因为在不同的查询列的时候,可能会使用到不同的索引条件。 Mysql在使用不同索引的时候,得到的数据顺序是不一样的。
SELECT DISTINCT
ps.id,
ps.product_id,
ps.identifier,
ps.name,
ps.description,
ps.system_call,
ps.original_required,
ps.update_required,
ps.custom,
ps.method,
ps.create_time,
ps.update_time,
(SELECT count(*)
FROM t_product_services_params
WHERE ref_id = ps.id AND type = 0) AS inputData,
(SELECT count(*)
FROM t_product_services_params
WHERE ref_id = ps.id AND type = 1) AS outputData
FROM t_product_services ps LEFT JOIN t_product_services_params psp ON ps.id = psp.ref_id
WHERE ps.product_id = ?
ORDER BY update_time DESC
LIMIT ?, ?
concat函数,模糊查询时需要注意单个字符为'%'和'_'的情况,否则会全匹配,控制层参数需要进行相关拦截!
SELECT
t1.*,COUNT(t2.id)AS alarmCount
FROM TB_EXAMPLE_RESOURCE t1 LEFT JOIN tb_alarm t2 on t1.exampleID = t2.exampleId
<where>
t1.device_flag = #{deviceFlag} ## 必传字段
<if test="frontDevType != null and frontDevType.size>0">
AND (<foreach collection="frontDevType" item="item" index="index" separator="OR">
t1.FrontDevType = #{item, jdbcType=VARCHAR}
</foreach>)
</if>
<if test="keyword != null and keyword != ''">
AND (t1.exampleName LIKE concat('%',#{keyword},'%')
OR t1.fileName LIKE concat('%',#{keyword},'%'))
</if>
<if test="exampleId > 0">
AND t1.exampleID = #{exampleId}
</if>
<if test="userName != null and userName != ''">
AND t1.CREATE_NAME = #{userName, jdbcType=VARCHAR}
</if>
</where>
GROUP BY t1.EXAMPLEID
ORDER BY t1.EXAMPLEID DESC