首页 > 数据库 >PostgreSQL 从熊灿灿一个获取固定字符的SQL 分析巧妙之处

PostgreSQL 从熊灿灿一个获取固定字符的SQL 分析巧妙之处

时间:2023-06-22 12:07:05浏览次数:52  
标签:0000 WHEN numeric pg SQL PostgreSQL atttypmod 熊灿灿


PostgreSQL  从熊灿灿一个获取固定字符的SQL 分析巧妙之处_postgresql

开头还是介绍一下群,如果感兴趣polardb ,mongodb ,mysql ,postgresql ,redis 等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。

某天群里一个同学抛出一个问题,关于获取字段类型中的设置的值,随即熊老师在群里抛出以下的一个SQL  (秒抛)

PostgreSQL  从熊灿灿一个获取固定字符的SQL 分析巧妙之处_SQL_02

SELECT
  CASE atttypid
         WHEN 21 /*int2*/ THEN 16
         WHEN 23 /*int4*/ THEN 32
         WHEN 20 /*int8*/ THEN 64
         WHEN 1700 /*numeric*/ THEN
              CASE WHEN atttypmod = -1
                   THEN null
                   ELSE ((atttypmod - 4) >> 16) & 65535     
                   END
         WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
         WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
         ELSE null
  END   AS numeric_precision,
  CASE 
    WHEN atttypid IN (21, 23, 20) THEN 0
    WHEN atttypid IN (1700) THEN            
        CASE 
            WHEN atttypmod = -1 THEN null       
            ELSE (atttypmod - 4) & 65535             
        END
       ELSE null
  END AS numeric_scale,
  *
FROM 
    pg_attribute
 where attrelid = 'xcc.xcc'::regclass and attname = 'number_b';



这个问题实际是对字段中的一种特殊的类型numberic 进行分析,并获取其中字段中的两个数字,并进行展示,听上去很简单。

首先我们从两个层面去分析这个SQL

1   结构层
这个SQL 主要展示的有两个层面  1  numeric_scale  2 numeric_scale
在基于 numeric_scale 的部分 7 个大判断,中包含1个子判断
numeric_scale 3个判断,包含1个子判断

2   判断逻辑
首先针对 atttypid 的值进行判断,这里需要说明的是 atttpid 的部分的值是需要搞清楚每个值代表的意义。

PostgreSQL  从熊灿灿一个获取固定字符的SQL 分析巧妙之处_SQL_03

这里就产生一个问题了,atttpid 的值 与实际的字段的名字之间的对应关系,这里我个人通过各种方式并未找到 值与字段类型之间的对应关系表。

我个人能想到的方法就是建立一个包含大部分字段类型然后通过这个表来查到字段与atttypid 之间的关系。

所以写这个SQL 的人必然是做过这个基础性的工作,这实际上体现了撰写这个SQL 的人对于技术的严谨和认真。

判断逻辑中首先过滤了整形的数据类型部分,int2 int4 int8 等都会被显示,而本次提出问题的 numeric 中的字段进行判断在  atttypmod

这里对于实际的计算部分进行一个解释

numeric(5,4) => 327688   0101 0000 0000 0000 1000
numeric(5,5) => 327689   0101 0000 0000 0000 1001
numeric(2,2) => 393222   0110 0000 0000 0000 0110
numeric(7,2) => 458758   0111 0000 0000 0000 0110
numeric(8,2) => 524294   1000 0000 0000 0000 0110
numeric(9,2) => 589830   1001 0000 0000 0000 0110

第一个字节为 numeric (n,m) 的N, 最后一个字节为 m+4,即precision为第一个字节,scale为最后一个字节-4

计算公式:

atttypmod=-1表示null

precision: ((atttypmod - 4) >> 16) & 65535

scale: (atttypmod - 4) & 65535

所以通过上面的计算公式可以解决这个同学的问题。

另外为什么atttypmod 中为什么要减 4 ,这里我们我们通过一个实验可以获得,举例我们产生一个字段是varchar类型 ,这里给的值是200, 那么我们可以看下图atttypmod 是里面的值是204 ,那么从这里就可以了解到为什么上面的一个解释中要 m+4  ,而给出的SQL 中要进行一个 atttymod -4 的工作。

最后,通过这个SQL 实际上可以看出撰写者的对系统表的深刻理解,以及对于一些深层次系统表中 数字计算的部分,比如将  atttypmod -4 后变为16进制后与65535 进行位与运算。

实际上这一个SQL 可以研究的地方还不少,截止目前,我也觉得并未对一些更深的问题有更深的理解。

PostgreSQL  从熊灿灿一个获取固定字符的SQL 分析巧妙之处_SQL_04

最后这里顺道将一个大家都在用的表字段类型表格打印的语句粘贴上,PG的系统表奥妙无穷。

PostgreSQL  从熊灿灿一个获取固定字符的SQL 分析巧妙之处_SQL_05

select
c.relname as 表名,
a.attname as 列名,
(case
when a.attnotnull = true then true
else false end) as 非空,
(case
when (
select
count(pg_constraint.*)
from
pg_constraint
inner join pg_class on
pg_constraint.conrelid = pg_class.oid
inner join pg_attribute on
pg_attribute.attrelid = pg_class.oid
and pg_attribute.attnum = any(pg_constraint.conkey)
inner join pg_type on
pg_type.oid = pg_attribute.atttypid
where
pg_class.relname = c.relname
and pg_constraint.contype = 'p'
and pg_attribute.attname = a.attname) > 0 then true
else false end) as 主键,
concat_ws('', t.typname) as 字段类型,
(case
when a.attlen > 0 then a.attlen
when t.typname='bit' then a.atttypmod
else a.atttypmod - 4 end) as 长度,
col.is_identity as 自增,
col.column_default as 默认值,
(select description from pg_description where objoid = a.attrelid
and objsubid = a.attnum) as 备注
from
pg_class c,
pg_attribute a ,
pg_type t,
information_schema.columns as col
where
a.attnum>0
and a.attrelid = c.oid
and a.atttypid = t.oid
and col.table_name=c.relname and col.column_name=a.attname
order by
c.relname desc,
a.attnum asc;

PostgreSQL  从熊灿灿一个获取固定字符的SQL 分析巧妙之处_SQL_06

标签:0000,WHEN,numeric,pg,SQL,PostgreSQL,atttypmod,熊灿灿
From: https://blog.51cto.com/u_14150796/6534542

相关文章

  • Polardb 如何替换MYSQL 之 IMCI 列式(1)建立一个列式引擎
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。讲了那么多期,都是在力量上进行论述,本期开始进入到正式的POALRDB的内部操作中,POLARDB与MYSQL在登录中最大的不同是,你可以通过代......
  • POSTGRESQL SQL 语句案例,一场由LIMIT 1 引发的“奇怪异像”
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。最近一段工作很少优化SQL,实际上7-8年前的确有一段疯狂优化的“美好时光”。 最近一个同事提出一个问题,他的一个POSTGRESQL的SQ......
  • PostgreSQL 15 stats collector 在取消后是如何实现的原有功能的
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。在POSTGRESQL15有一个重要的功能去掉了statscollector在说为什么去掉这个statscollector的问题前,我们先得弄清出statscoll......
  • Polardb 如何替换MYSQL 之 IMCI 列式攻略
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。MYSQL是ORACLE后面经常被提到要替换的数据库,MYSQL为什么要被替换,嗯这点是一言难尽,但是可以说明的是,替换MYSQL的数据库类型还......
  • MYSQL 5.7 升级 8.0 后的 由于字符集导致的大问题 ?
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。MYSQL8.0已经很多年了,但是,但是,但是,还有很多公司和业务项目在MYSQL5.6,5.7上继续奋斗,这还不是一个重要的问题,重要的问题是早期......
  • POSTGRESQL 和 MYSQL 到底应该不应该控制活跃连接
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql ,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。最近群里某个同学的提问,引起的本篇文章,关于数据库连接的部分,没有废话,我们先针对MYSQL来说说数据库连接的部分。首先MYSQL的客......
  • POSTGRESQL 自动搜索所有逻辑库中的无用索引自动化脚本实现
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。前两天腾出点时间,打算整理一下POSTGRESQL公司的数据库的无用的索引的问题,写了一个SQL通过SQL来获取这些数据库的无用索引,但头......
  • 我也不知道该怎么回答这个问题,还学MYSQL 吗?
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题最近一个同学偶然问了我一个这样的问题,实话说我不知道该怎么回答这个问题。但我知道问这个问题的同学,他思考了,不是在追风,一会儿MYSQ......
  • PostgreSQL 15 让多年被DISS的PG 安全画上圆满的句号
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题。提起POSTGRESQL中的安全问题其中最容易被人Diss的最大BUG并不是autovacuum 之类的部分,排在首位的被DISS的最大的问题是安全的......
  • POSTGRESQL postgresql 升级的需求来自哪里
    开头还是介绍一下群,如果感兴趣polardb,mongodb,mysql,postgresql,redis等有问题,有需求都可以加群群内有各大数据库行业大咖,CTO,可以解决你的问题(本篇的思路来自于,盘古云课堂PG152023年2月18日晚,PG15升级问题大讨论稿)说起POSTGRESQL的升级问题,很多同学会问,升级POSTGRESQL......