PostgreSQL 兼容 MySQL tinyint , unsigned int , zerofill 等 - domain , lpad

作者

digoal

日期

2020-01-05

标签

PostgreSQL , MySQL , int , tinyint , unsigned int , zerofill

背景

PG 与 MySQL的数值类型详细说明如下

https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

https://www.postgresql.org/docs/12/datatype-numeric.html

关于固定整型的区别：

mysql支持int 1,2,3,4,8 字节，同时支持有符号，无符号。zerofill。

pg支持 int 2,4,8 字节，有符号。

先不管在数据库内部消耗多少容量，如果用户有这样的需求，必须使用1，3字节整型，或者必须使用无符号整型，怎么处理？

PG使用domain新建类型，实现 1，3字节整型，无符号整型

1、例子，创建uint8，8字节无符号整型。

db1=# create domain uint8 as numeric(20,0) check (value <= ((2^64::numeric)::numeric(20,0)-1) and value>=0::numeric(20,0)); CREATE DOMAIN

使用domain，约束在整型，大于等于0，小于2^64范围。

```
db1=# create table t5(c1 uint8);
CREATE TABLE

db1=# insert into t5 values (-1);
ERROR: value for domain uint8 violates check constraint "uint8_check"

db1=# insert into t5 values (0);
INSERT 0 1

db1=# insert into t5 values (2^64::numeric);
ERROR: value for domain uint8 violates check constraint "uint8_check"

db1=# insert into t5 values (2^64::numeric-1);
INSERT 0 1
db1=# select 2^64::numeric-1;
?column?

18446744073709551615.0000000000000000
(1 row)

db1=# insert into t5 values (18446744073709551615);
INSERT 0 1

db1=# insert into t5 values (18446744073709551616);
ERROR: value for domain uint8 violates check constraint "uint8_check"

db1=# select * from t5;
c1

                0
复制

18446744073709551615
18446744073709551615
(3 rows)
```

所有domain如下：1,3字节有无符号整型，2,4,8字节无符号。

create domain int1 as int2 CHECK (VALUE <= 127 AND VALUE >= (-128)); create domain uint1 as int2 CHECK (VALUE <= 255 AND VALUE >= 0); create domain uint2 as int4 CHECK (VALUE <= 65535 AND VALUE >= 0); create domain int3 as int4 CHECK (VALUE <= 8388607 AND VALUE >= (-8388608)); create domain uint3 as int4 CHECK (VALUE <= 16777215 AND VALUE >= 0); create domain uint4 as int8 CHECK (VALUE <= 4294967295 AND VALUE >= 0); create domain uint8 as numeric(20,0) check (value <= ((2^64::numeric)::numeric(20,0)-1) and value>=0::numeric(20,0));

db1=# \dD List of domains Schema | Name | Type | Collation | Nullable | Default | Check --------+---------+---------------+-----------+----------+---------+--------------------------------------------------------------------------------------------------------- public | int1 | smallint | | | | CHECK (VALUE <= 127 AND VALUE >= '-128'::integer) public | int3 | integer | | | | CHECK (VALUE <= 8388607 AND VALUE >= '-8388608'::integer) public | uint1 | smallint | | | | CHECK (VALUE <= 255 AND VALUE >= 0) public | uint2 | integer | | | | CHECK (VALUE <= 65535 AND VALUE >= 0) public | uint3 | integer | | | | CHECK (VALUE <= 16777215 AND VALUE >= 0) public | uint4 | bigint | | | | CHECK (VALUE <= '4294967295'::bigint AND VALUE >= 0) public | uint8 | numeric(20,0) | | | | CHECK (VALUE <= ((2::numeric ^ 64::numeric)::numeric(20,0) - 1::numeric) AND VALUE >= 0::numeric(20,0))

lpad 补充 zerofill 兼容

如果用户需要在输出时，未达长度在左侧填0，可以在返回时使用lpad补齐。超出长度则按真实长度返回。

例如长度不足4时，左侧补齐0。

```
db1=# select lpad(10::text,greatest(4,length(10::text)),'0');
lpad

0010
(1 row)

db1=# select lpad(199::text,greatest(4,length(199::text)),'0');
lpad

0199
(1 row)

db1=# create table t6(id int);
CREATE TABLE
db1=# insert into t6 values (123),(123456);

db1=# select lpad(id::text, greatest(4, length(id::text)), '0'), id from t6;
lpad | id
--------+--------
0123 | 123
123456 | 123456
(2 rows)
```

使用了lpad函数。

db1=# \df lpad List of functions Schema | Name | Result data type | Argument data types | Type ------------+------+------------------+---------------------+------ pg_catalog | lpad | text | text, integer | func pg_catalog | lpad | text | text, integer, text | func (2 rows)

参考

https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

https://www.postgresql.org/docs/12/datatype-numeric.html

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.