MySQL第五讲：工作中常用的sql语句

MySQL第五讲：工作中常用的sql语句，主要是商品中心和标准中心相关sql

1、类目属性相关

1.1、查询语句

1.1.1、查询前台类目(3级类目)

select a.`name` AS '一级类目', a.id, a.tag_id, b.`name` AS '二级类目', b.id, b.tag_id, c.`name` AS '三级类目', c.`id` AS cid, c.tag_id 
from `parana_front_categories` a, `parana_front_categories` b, `parana_front_categories` c
where c.level= 3 and c.status= 1 and b.level= 2
and a.level= 1 and c.pid= b.id and b.pid= a.id
1
2
3
4

1.1.2、查询后台类目(3级或4级)

# 三级类目
select  a.id, a.`name` AS '一级类目', b.`name` AS '二级类目', c.`name` AS '三级类目' 
from `parana_back_categories` a, `parana_back_categories` b, `parana_back_categories` c
where c.level= 3 and c.status= 1 and b.level= 2
and a.level= 1 and c.pid= b.id and b.pid= a.id and c.`id` in (xxx);
1
2
3
4
5

# 四级类目
select a.`name` AS '一级类目', a.id, b.`name` AS '二级类目', b.id,  c.`name` AS '三级类目', c.`id` AS cid ,d.`name` as '四级类目' 
from `parana_back_categories` a, `parana_back_categories` b, `parana_back_categories` c, parana_back_categories d
where c.level= 3 and c.status= 1 and b.level= 2
and a.level= 1 and c.pid= b.id and b.pid= a.id and d.`pid` = c.`id` and  a.`id` = 2551;
1
2
3
4
5

1.1.3 查询某一前台类目实例下的前、后台类目

select a.`name` AS '前台一级类目', a.id, b.`name` AS '前台二级类目', b.id, c.`name` AS '前台三级类目', c.`id` AS cid, c.tag_id ,a1.id, a1.`name` AS '后台一级类目', b1.`name` AS '后台二级类目', c1.`name` AS '后台三级类目' 
from 
`parana_front_categories` a, `parana_front_categories` b, `parana_front_categories` c, `parana_category_bindings` d,
`parana_back_categories` a1, `parana_back_categories` b1, `parana_back_categories` c1
where c.level= 3 and c.status= 1 and b.level= 2
and a.level= 1 and c.pid= b.id and b.pid= a.id and c.`tag_id` = 1100013 and d.`front_category_id`  = c.id and d.`back_category_id` = c1.`id` 
and c1.level= 3 and c1.status= 1 and b1.level= 2 and a1.level= 1 and c1.pid= b1.id and b1.pid= a1.id;
1
2
3
4
5
6
7

---

1.2、更新语句

参考：https://www.jb51.net/article/54345.htm

# replace into 语句
1. replace into table(col, ...) values(...)
2. replace into table(col, ...) select ...
3. replace into table set col=value, …
1
2
3
4

replace into 跟insert的功能类似，先尝试将数据插入表中，如果发现表中已经有此行数据（如何判断：基于主键？），则先删除此行数据，然后插入新的数据，否则，直接插入新数据。
如果是使用insert，会有重复数据的风险，如果带有主键，数据插入失败

---

1.3、批量插入数据

INSERT INTO `zcy_category_configuration` (`category_id`, `property_id`, `attr_key`, `group`, `index`, `status`, `attr_metas_json`,`attr_vals_json`, `created_at`, `updated_at`, `config_id`) select id, 82544, '计量单位', '普通属性', -1, 1, '{\"PROPERTY_TYPE\":\"ITEM\",\"REQUIRED\":\"false\",\"USER_DEFINED\":\"false\"}', '[\"件\"]', '2020-01-11 00:00:00', '2020-01-11 00:00:00', (select id from `zcy_category_instance_config` where platform = 'false' and industry_code = '006' and instance_code = 'TYSL') as config_id  from (
     -- 真线如果不允许的话，提前把这个所有的类目id导出来
        select id from `parana_back_categories`  where has_children = 0 and status != -1 and tags like '%tyddlm%'
    ) as t1;
1
2
3
4

1.4、查询接口方法级，被其他应用的调用量及平均耗时。用来判断性能问题

* and appName: item-microservice and invokeType: provider 
| select serviceName, methodName, split_part(traceName, ':', 1) as outerApp,COUNT(*) as count, avg(spendTime) as maxSpendTime  
FROM prod-tracelog 
where spendTime > 200 
group by serviceName, methodName, outerApp 
order by maxSpendTime desc
1
2
3
4
5
6

1.5、批量插入属性分组

• 需求是：计算类目属性中存在，但是在属性分组中不存在的分组名称以及类目id
  • 超级复杂，通过程序来计算更合适

select k.t1Cate,k.t1Group from (
    select t1.category_id as t1Cate,t1.group as t1Group, t2.group_name as t2Group from 
    (select category_id, `group` from parana_category_attributes) as t1
    left join  (select category_id,group_name from zcy_category_attribute_group) as t2 on t1.category_id = t2.category_id and t1.group = t2.group_name) as k 
    where k.t2Group is null group by k.t1Cate,k.t1Group order by k.t1Cate
1
2
3
4
5

• 初始化类目属性分组

INSERT INTO zcy_category_attribute_group(`category_id`,`group_name`,`index`,`status`,`created_at` ,`updated_at` ,`config_id` )  
SELECT `category_id` ,`group`,1,1,now(),now(),`config_id` 
FROM parana_category_attributes 
WHERE config_id = 3 and status = 1;
1
2
3
4

# 类目属性分组
select `k`.`t1Cate`,`k`.`t1Group` from (
    select `t1`.`category_id` as `t1Cate`,`t1`.`group` as `t1Group`, `t2`.`group_name` as t2Group from 
    (select category_id, `group` from parana_category_attributes) as t1
    left join  (select category_id,group_name from zcy_category_attribute_group) as t2 on t1.category_id = t2.category_id and t1.group = t2.group_name) as k 
    where k.t2Group is null group by k.t1Cate,k.t1Group order by k.t1Cate
1
2
3
4
5
6

# join连接查询类目属性
SELECT b.`category_id` ,a.`name` ,b.`xx` as '销售属性数量' 
FROM  (
	SELECT `category_id`,  COUNT(attr_metas_json) as xx 
	FROM `parana_category_attributes` 
	WHERE `attr_metas_json` like '{"PROPERTY_TYPE":"SKU_CANDIDATE",%' and  `status` =1  
	GROUP BY `category_id` ) as b  
	join `parana_back_categories`  as a 
where a.`id` = b.`category_id` 
ORDER BY  b.`xx` desc ;
1
2
3
4
5
6
7
8
9
10

//商品状态
public enum ItemOperateType {
    CREATE(0, "创建"),
    UPDATE(1, "更新"),
    FROZEN(2, "冻结"),
    UNFREEZE(3, "解冻"),
    DELETE(-3, "删除"),
    ONSHELF(4, "上架"),
    UNDERSHELF(5, "下架"),
    PUBLISHED(10, "发布");
}
1
2
3
4
5
6
7
8
9
10
11

1.6、类目属性数据导出

select a.`name` AS '一级类目', b.`name` AS '二级类目', c.`name` AS '三级类目', d.id ,d.attr_key，d.property_id,d.group,d.attr_metas_json,d.attr_vals_json 
from `db_item`.`parana_back_categories` a, `db_item`.`parana_back_categories` b, `db_item`.`parana_back_categories` c ,`db_item`.`parana_category_attributes` d
where d.category_id = c.id and d.status = 1 and  c.level= 3 and c.status= 1 and b.level= 2
and a.level= 1 and c.pid= b.id and b.pid= a.id and c.id in (7203,1310,4914,7206,5041,6816,7456,6999,6823,5393,5038,5276,7122,5304,6821,7267,5253,6820,4957,4622,7002,5025,5144,5074,7148,7230,5486,4971,12750,5550,5006,6822,1224,5083,5492,6677,6182,6833,6987,5951,6653,1218,4896,4625,5535,5491,7226,6358,5239,6824,6467,5494,5024,5272,1780,5145,7202,6829,5226,7448,5273,7001,8546,5263,8142,1778,6205,4901,6510,7213,6366,6469,5151,5240,1179,5303);
1
2
3
4

1.7 使用excel来批量插入数据

todo

2、MySql模糊搜索结果按匹配度排序

SELECT *
FROM mov_video_info
WHERE title LIKE '%魔兽%'
ORDER BY REPLACE(title,'魔兽','')
LIMIT 10
1
2
3
4
5

• 通过替换关键字为空字符串来改变排序规则

3、商品中心相关

3.1、慢接口优化

1、在当前供应商下的基础商品中是否已存在同关键属性的商品

select * 
from `parana_items`
where ( ( ( ( ( `status` <> :1 ) and ( `shop_id` = :2 ) ) and ( `category_id` = :3 ) ) and ( `layer` in ( :4 , :5 , :6 , :7 , :8 , :9 ) ) ) and ( `key_attrs_brief` = :10 ) 
1
2
3

3.2、查询疫苗商品数据

select * from db_item.parana_items 
where match(tags_json) against ('vaccine' in NATURAL LANGUAGE mode) and shop_id=112 and status!=-3
1
2

4、SPU相关

4.1、查询发布过商品的spu信息 （使用metabase）

SELECT
    count(*)
FROM
    `ods_db_item`.`sync_zcy_ali_spu` 
WHERE
    spu_id IN (
    SELECT DISTINCT
        ( spu_id ) 
    FROM
        `ods_db_item`.`sync_parana_items` 
    WHERE
    spu_id IN ( SELECT spu_id FROM `ods_db_item`.`sync_zcy_ali_spu` WHERE price < 100 OR price IS NULL AND STATUS = 3 ) 
    );
1
2
3
4
5
6
7
8
9
10
11
12
13

4.2、json中字符串替换

UPDATE zcy_ali_spu 
set key_property_text = REPLACE(key_property_text,"品牌:荣事达;","品牌:荣事达/royalstar;"), brand_id =8885, brand_name = '荣事达/royalstar' 
where spu_id = 100589444;
1
2
3

4.3、统计接口耗时的语法

• 统计rest接口耗时
  • 通过nginx日志

* | select case 
when split_part(upstream_addr,':',2) = '8021' then 'web-protocol' 
when split_part(upstream_addr,':',2) = '9005' then 'web-aggregated' 
when split_part(upstream_addr,':',2) = '8060' then 'web-agreement' 
when split_part(upstream_addr,':',2) = '8082' then 'web-item-admin' 
when split_part(upstream_addr,':',2) = '8109' then 'web-spi' 
end as appname, approx_percentile(request_time, array[0.9]) as rtp90,
approx_percentile(request_time, array[0.95]) as rtp95,
approx_percentile(request_time, array[0.99]) as rtp99,
MAX(request_time) as max,min(request_time) as min,avg(request_time) as avg,
uri from zcy-prod-nginx-access 
where split_part(upstream_addr,':',2) in ('8021', '9005', '8060', '8082', '8109') GROUP by appname,uri 
order by rtp95 desc 
limit 100
1
2
3
4
5
6
7
8
9
10
11
12
13
14

• 统计dubbo接口耗时
  • 通过 tracelog 统计

* | select appName,serviceName,methodName, COUNT(*) as queryCount, approx_percentile(spendTime, 0.99) as rtp99,
approx_percentile(spendTime,0.95) as rtp95,
approx_percentile(spendTime, 0.9) as rtp90,
MAX(spendTime) as max,min(spendTime) as min,avg(spendTime) as avg 
from prod-tracelog 
where appName in ('item-dump-microservice', 'item-search-microservice', 'item-microservice', 'item-service', 'web-item-admin', 'zcy-agreement-center', 'zcy-agreement-web', 'zcy-protocol-web', 'zcy-protocol-center' ) 
GROUP by appName,serviceName,methodName 
order by rtp99 desc 
limit 100
1
2
3
4
5
6
7
8
9

• 例如:查询标准中心接口耗时

* | select appName,serviceName,methodName, COUNT(*) as queryCount, approx_percentile(spendTime, 0.99) as rtp99,
approx_percentile(spendTime,0.95) as rtp95,
approx_percentile(spendTime, 0.9) as rtp90,
MAX(spendTime) as max,min(spendTime) as min,avg(spendTime) as avg 
from prod-tracelog 
where appName in ( 'item-standard-center' ) and serviceName in ('cn.gov.zcy.service.category.facade.FrontCategoryReadServiceFacade')  
GROUP by appName,serviceName,methodName 
order by rtp99 desc 
limit 100
1
2
3
4
5
6
7
8
9

5、metabase

• 查询metabase用得上的sql语句

select category_id, json_extract(attr_metas_json,'$.REQUIRED') as "是否必填" 
from ods_db_item_standard.sync_parana_category_attributes 
where status =1 and category_id = 2287 and config_id = 1;
1
2
3

• 将区划限价表与用户表和后台类目表联合查询

SELECT a.category_id as "类目id",b.name as "类目名称", a.year  as "年份", a.district_id as "区划id", c.name as "区划name", a.price, limit_level as "限制级别 1:强制 2:提醒", a.tags_json as "标签json,  记录标签类型，json 格式，值 1：强控，2：优先",a.gmt_create as "创建时间",a.gmt_modified as "更新时间"  
FROM ods_db_item_standard.sync_zcy_district_category_price as a
inner join ods_db_item_standard.sync_parana_back_categories as b
on (a.category_id = b.id)
inner join ods_db_paas_user.sync_district_t as c  
on cast(a.district_id as varchar) =cast(c.code as varchar)
WHERE a.status != -3 limit 500000
1
2
3
4
5
6
7

• 需求，查询协议商品sku为空且处于正常或上架状态的商品

select *
  from ods_db_item.sync_parana_items as a
where  
 a.publish_channel = 3
and 
a.status in (0,1)
and 
instance_code is not null
and 
a.id not in (select item_id from ods_db_item.sync_parana_skus where status != -3)
1
2
3
4
5
6
7
8
9
10