postgrepsql学习

weixin_42412601

已于 2023-09-01 13:20:24 修改

阅读量2.8k

点赞数

分类专栏：数据库文章标签：数据库 kafka java

于 2021-06-01 22:25:52 首次发布

本文链接：https://blog.csdn.net/weixin_42412601/article/details/117455238

版权

数据库专栏收录该内容

12 篇文章 1 订阅

订阅专栏

1、postgresql----索引失效
2、postgrep查看sql是否使用索引
3、模糊查询like优化
4、postgrep自增主键
5、`on CONFLICT`语法
6、Postgrep使用json字段，存储json数据
7、postgrep json字段查询
- 8, postgrep 按天，按小时统计数据
- 9, 使用pgsql时添加了参数stringtype=unspecified，报无法确定参数类型的错

1、postgresql----索引失效

https://www.cnblogs.com/alianbog/p/5648455.html

2、postgrep查看sql是否使用索引

EXPLAIN ANALYZE  select * FROM sys_order WHERE is_deleted = 1;

3、模糊查询like优化

一、模糊查询没有使用索引：
PG中使用索引进行模糊查询
现在数据库中存在一个tb_user（id, name，age）表,并且已经针对name字段创建了索引tb_user_name_idx。

explain analyze select * from tb_user where name like '%aaa%'；

发现该SQL语句在name字段上使用的是Seq Scan而不是Index Scan或Bitmap Heap Scan，执行时间在1s以上。

二、模糊查询使用索引：
使用pg_trgm插件
想要在模糊查询时在name字段上使用索引，需要执行如下SQL语句

CREATE EXTENSION pg_trgm;
CREATE INDEX tb_user_name_trgm_gist_idx ON tb_user USING GiST(name GiST_trgm_ops);

此时，再执行：

explain analyze select * from tb_user where name like '%aaa%'；

发现该SQL语句在name字段上使用了Bitmap Heap Scan，使用到了新创建的索引，而且执行时间减少到20-30ms。

实际测试：

-- 使用索引： 4ms 5ms
-- 不使用索引：200ms 192ms 218ms
explain analyze select * from sys_order so where order_no like '%9ig_213858%';

4、postgrep自增主键

主键自增长的方式有三种：sequence，serial，identity

https://www.cnblogs.com/wy123/p/13367486.html

postgrep,使用identity自增时，主键设置为GENERATED BY DEFAULT AS IDENTITY,插入数据时，如果主键id=null,它会优先使用用户传的id作为主键，这就会报错了，可以再insert的时候，加上OVERRIDING USER VALUE，覆盖用户输入的值（使用系统列定义的自增值）
identity详解

https://www.izhangchao.com/internet/internet_228042.html

实际上identify列，也使用了序列，如下：生成的序列默认名为表名_id_seq
获取identify列自增长的下一个值，通过函数nextval

select nextval('test_onduplicatekey_id_seq');

在DBeaver中直接点序列，就能看到各个表主键identity生成的默认序列了，注意，是主键使用了identity的方式自增时，才会有。

查询当前表的序列

-- 查询表public.collection的列id的序列
select pg_get_serial_sequence('public.collection','id');
select  nextval('public.collection_id_seq')
-- 查询数据库中有哪些序列
select *  from pg_class where relkind='S';
select *  from pg_class where relkind='S' and relname like '%line_vs_spot%';

5、`on CONFLICT`语法

insert into sys_user (username,password,email,org_id,is_deleted ,create_time,update_time,create_user_id,create_by) 
		values('lzh123','12345','12345678@qq.com',3,0,'2021-02-19 16:32:08','2021-02-19 16:32:08',1,'admin')
		ON conflict(username,is_deleted) do update set email='66666666@qq.com',password='6666';
https://blog.csdn.net/nmgcfyxl/article/details/105773010
https://vegetable-chicken.blog.csdn.net/article/details/103733927

6、Postgrep使用json字段，存储json数据

postgrep支持同时存储结构化数据和非结构化数据。字段支持json、jsonb的数据类型，一般使用jsonb。
表字段中有json的字段，数据源的url，要加上?stringtype=unspecified
存储json数据时,入参可以使用Object/Map接受json数据，接受到后，再用fastjson转成json数据，与数据库映射的实体类,json字段就使用String去接受json数据就行，然后就当做正常数据插入到数据库中就行了

//使用fastjson将对象转成json数据
String detail=JSON.toJSONString(sysOrder.getDetail());

7、postgrep json字段查询

学习文档：https://www.postgresql.org/docs/current/functions-json.html

JSON 输入和输出语法

以下是所有有效的 json（或 jsonb）表达式
-- 简单标量/基元值
-- 基元值可以是数字、带引号的字符串、真、假或空
SELECT '5'::json;
SELECT '5'::jsonb;

-- 零个或多个元素的数组（元素不必是同一类型）
SELECT '[1, 2, "foo", null]'::json;

-- 包含键和值对的对象
-- 请注意，对象键必须始终使用带引号的字符串
SELECT '{"bar": "baz", "balance":    7.77, "active": false}'::json;
SELECT '{"bar": "baz", "balance":    7.77, "active": false}'::jsonb;

-- 数组和对象可以任意嵌套
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

-- 在jsonb中，将根据底层数字类型的行为打印数字
SELECT '{"reading": 1.230e-5}'::json;
select '{"reading": 1.230e-5}'::jsonb;


jsonb 包含性和存在

包含测试@>: 一个json是否被另一个json包含.

-- 简单标量/基元值仅包含相同的值：
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

-- 右侧的数组包含在左侧的数组中，数组元素的顺序并不重要，重复的数组元素也无关紧要
SELECT '[1, 2, 3]'::jsonb @> '[4]'::jsonb;
SELECT '[1, 2, 3]'::jsonb @> '[2,2,3]'::jsonb;
SELECT '[1, 2, 3]'::jsonb @> '[2]'::jsonb;

-- 包含右侧有一对的对象,在左侧的对象中
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;

-- 右侧的数组不被视为包含在数组在左侧，即使其中嵌套了一个类似的数组：
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- 产生false

-- 但是通过一层嵌套，它包含
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

-- 同样，此处未报告包含
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- 产生false

-- 包含顶级键和空对象：
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

-- 此数组包含基元字符串值：
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

-- 此例外不是互惠的 - 此处报告了非包含
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- 产生false

存在操作符?，它是包含的变体,它测试一个字符串（作为文本值给出）是否出现在jsonb值的顶层作为对象的键或数组元素

-- 字符串作为数组元素存在
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';
-- 字符串作为对象键存在：
SELECT '{"foo": "bar"}'::jsonb ? 'foo';
-- 不考虑对象值
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- 返回false
-- 与包含一样，存在必须在顶层匹配：
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- 返回false
-- 如果字符串与原始 JSON 字符串匹配，则认为该字符串存在：
SELECT '"foo"'::jsonb ? 'foo';




-- json数据，可以看成是表里的json字段
-- ::JSON 表示声明前面的字符串或字段为一个JSON字符串对象
-- ->方式获取到的元素依然是json对象
-- ->>方式获取到的元素不是json了，是一个json字符串

-- ->2表示从json数组中，取索引下标为2的元素
select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> 2;
select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json ->> 2;
-- ->'c'表示取出key是'c'的元素的值
select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json -> 2->'c';
-- 会报错，因为->> 2获取到的是一个字符串了，已经不是json对象，不能在通过->'c'获取数据
select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json ->> 2->'c';

-- #> '{a,b,1}'表示获取a下的b,b下的索引为1的元素。
-- #>方式获取到的数据，依然是json对象
-- #>>方式获取到的数据，是字符串
select '{"a": {"b": ["foo","bar"]}}'::json #> '{a,b,1}' 
select '{"a": {"b": ["foo","bar"]}}'::json #>> '{a,b,1}' 

-- 是否第一个json对象{"a":1, "b":2}，包含第二个json对象，返回布尔值
select '{"a":1, "b":2}'::jsonb @> '{"b":2}'
-- 是否第一个json对象被包含在第二个json对象中
select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'

-- json对象中是否包含键为'b'的元素
select '{"a":1, "b":2}'::jsonb ? 'b'
-- json数组中是否包含元素a
select '["a", "b", "c"]'::jsonb ? 'a'
这个语句很坑,问号在mybatis里是占位符。
写的时候，要在对应的mapper方法上加@InterceptorIgnore(tenantLine = "true")注解。
且?要写成两个??
如：
<select id="findRuleByDimension" resultType="com.boe.aiot.park.device.bean.dto.DeviceRepairDTO" parameterType="java.lang.String">
    SELECT * FROM device_repair ps WHERE ps.space_code::jsonb ?? #{dimension, jdbcType=VARCHAR}
</select>
@InterceptorIgnore(tenantLine = "true")
List<DeviceRepairDTO> findRuleByDimension(@Param("dimension") String dimension);
如果是mybatis-plus:
@InterceptorIgnore(tenantLine = "true")
@Override
List<DeviceRepairDO> selectList(@Param(Constants.WRAPPER) Wrapper<DeviceRepairDO> queryWrapper);
lambdaQueryWrapper.apply("space_code ?? {0}", query.getSpaceCode());

-- 文本数组array['e', 'a']中是否有元素，存在于json对象或数组中
select '{"a":1, "b":2, "c":3}'::jsonb ?| array['e', 'a'] 
select '["a", "b", "c"]'::jsonb ?| array['e', 'd'] 

-- 文本数组array['a', 'd']中是否所有元素，存在于json对象或数组中
select '["a", "b", "c"]'::jsonb ?& array['a', 'd'] 
select '{"a":1, "b":2, "c":3}'::jsonb ?& array['a', 'b'] 

应用：
# 模糊查询，extra_info ->> 'blacklistName'表示从json中取出key为blacklistName的元素
SELECT * FROM alarm WHERE is_deleted = 0 AND extra_info ->> 'blacklistName' like '%张三%'
# 另一种写法，字符串精确查询
SELECT * FROM alarm WHERE is_deleted = 0 AND (extra_info @@ '$.blacklistId == "644247106d951d3d56364d67"')
# 数值精确查询
SELECT * FROM alarm WHERE is_deleted = 0 AND (extra_info @@ '$.blacklistGroupId == 1378073802088448')
# 代码层面编写
this.alarmDOQueryWrapper.apply("extra_info ->> 'blacklistName' like '%" + blacklistName + "%'");
this.alarmDOQueryWrapper.apply("extra_info @@ {0}", "$.blacklistId == \"" + blacklistId + "\"");
this.alarmDOQueryWrapper.apply("extra_info @@ {0}", "$.blacklistGroupId == " + blacklistGroupId);

这种方式，在使用mybatis-plus的apply拼接sql的时候，会把？当成动态参数。
select * from sys_order where detail @? '$.hotelMessage.scheduledDate ? (@ > "2001-12-04 11:10:32" && @ < "2020-12-04 11:10:32" )'
select * from sys_order where detail @? '$.residentsMessage[*].residentName ? (@ starts with "入住")'

这种方式，在使用mybatis-plus的apply拼接sql的时候，会存在sql注入的风险
select * from sys_order where detail @@ '$.residentsMessage[*].residentName starts with "入住"'
select * from sys_order where detail @@ '$.hotelMessage.scheduledDate > "2001-12-04 11:10:32" &&  $.hotelMessage.scheduledDate < "2020-12-04 11:10:32" '
如何避免sql注入的风险？
	.apply(ObjectUtil.isNotEmpty(sysOrderQO.getResidentName()), "detail @@ {0}", "$.residentsMessage[*].residentName starts with \"" + sysOrderQO.getResidentName() + "\"")
	.apply(ObjectUtil.isNotNull(sysOrderQO.getScheduledDateStart()), "detail @@ {0}", "$.hotelMessage.scheduledDate >= \"" + minimumOfLocalDate(sysOrderQO.getScheduledDateStart()) + "\"")
	.apply(ObjectUtil.isNotNull(sysOrderQO.getScheduledDateEnd()), "detail @@ {0}", "$.hotelMessage.scheduledDate < \"" + maximumOfLocalDate(sysOrderQO.getScheduledDateEnd()) + "\""); 
注意：{0}默认给加单引号

批量查询：
1，简单json结构。如
'{"deviceId":1373775855075416, "categoryId": 1373775513690127}'
方式一：
select * from test.devices_device dd where extra_properties
->>'ChannelDeviceId' in ('11010500491310100541','11010500491310109069','1');
方式二：
SELECT  * FROM alarm_config WHERE  is_deleted=0 AND (condition_content @? '$.deviceConditions[*].deviceIds ? ( @ == 1 || @ == 1529404051424239713)');
SELECT  * FROM alarm_config WHERE  is_deleted=0 AND condition_content @@ '$.deviceConditions[*].deviceIds == 1 || $.deviceConditions[*].deviceIds == 1529404051424239713' ;
方式三：
select '{"deviceId":1373775855075416, "categoryId": 1373775513690127}'::jsonb->'deviceId' @> ANY (ARRAY ['1373775855075416','123']::jsonb[]);

2，数组json且要批量查询。如：需要批量查询deviceIds里是否元素匹配集合[123,1373775855075416]
{"deviceIds": [1373775855075416], "categoryId": 1373775513690127}
select '{"deviceIds": [1373775855075416], "categoryId": 1373775513690127}'::jsonb->'deviceIds' @> ANY (ARRAY ['1373775855075416','123']::jsonb[]);

https://blog.csdn.net/Vienn_tu/article/details/130178095
mybatis执行上述语句出现：Encountered unexpected token: "->" "->"，将jsqlparser换个版本
<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>4.2</version>
</dependency>

数组嵌套查询：
[
    [
        {
            "key": "SupplyAirHumidity6",  
            "value": "1",         
            "operator": 0,         
            "deviceId": 1,
            "categoryId": 123
        },
        {
            "key": "SupplyAirHumidity6",
            "value": "1",
            "operator": 0,
            "deviceId": 2,
            "categoryId": 456
        },
        {
            "key": "SupplyAirHumidity66",
            "value": "1",
            "operator": 0,
            "deviceId": 4,
            "categoryId": 789
        }
    ],
    [
        {
            "key": "SupplyAirHumidity666",
            "value": "1",
            "operator": 0,
            "deviceId": 5,
            "categoryId": 101112
        }
    ]
]
SELECT * FROM table where condition_content @@ '$[*][*].categoryId == 123';

8, postgrep 按天，按小时统计数据

按小时统计
SELECT (to_char(alarm_start_time, 'yyyy-mm-dd HH24:00:00')) AS key, COUNT(*) FROM alarm 
WHERE is_deleted = 0 AND (alarm_start_time >= '2022-10-21 00:00:00' AND alarm_start_time < '2022-10-22 00:00:00')  and bus_code in('001001','001002')
GROUP BY key ORDER BY key asc;

按天统计
SELECT (to_char(alarm_start_time, 'yyyy-mm-dd')) AS key, COUNT(*) FROM alarm 
WHERE is_deleted = 0 AND (alarm_start_time >= '2022-10-21 00:00:00' AND alarm_start_time < '2022-10-23 00:00:00') 
GROUP BY key ORDER BY key asc;

时间戳的
SELECT id, device_id, device_sn, last_repair_time,to_char(to_timestamp(last_repair_time/1000), 'yyyy-mm-dd hh:mm:ss') AS Format_Time, last_repair_person, is_deleted
FROM public.device_repair;

9, 使用pgsql时添加了参数stringtype=unspecified，报无法确定参数类型的错

ERROR: could not determine data type of parameter $7

只需要对 #{name} 进行类型转换就行，给它一个明确的类型即可
name LIKE CONCAT('%', #{name}::varchar, '%')

weixin_42412601

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
postgrepsql学习

目录1、postgresql----索引失效2、postgrep查看sql是否使用索引3、模糊查询like优化4、postgrep自增主键5、`on CONFLICT`语法6、Postgrep使用json字段，存储json数据7、postgrep json字段查询1、postgresql----索引失效https://www.cnblogs.com/alianbog/p/5648455.html2、postgrep查看sql是否使用索引EXPLAIN ANALYZE select * FROM
复制链接

扫一扫