PostgreSql和MySql数据类型之间的比较以及推荐 |
您所在的位置：网站首页 › oracle的numeric长度 › PostgreSql和MySql数据类型之间的比较以及推荐 |
PostgreSql和MySql数据类型之间的比较以及推荐
|
文章介绍了 postgresql 和 MySql 之间数据类型的比较，以及推荐使用。因为存在数据库表迁移等场景，会更需要此类情况的对比 1. 数值类型的比较 整数：mysql中的整数类型和 pg 相比，两者有以下区别： mysql：mysql中支持int 1,2,3,4,8 字节，同时支持有符号，无符号。并且mysql中支持在数值列中指定zerofill，用来将存储的数值通过填充0的方式达到指定数据类型的长度(mysql8开始不建议使用ZEROFILL属性，并且在将来的MySQL版本中将不再支持该属性)。 pg：pg支持 int 2,4,8 字节，且数值都是有符号的。 mysql整数类型：
pg整数类型：
那么对于mysql中的1，3字节整型，或者无符号整型以及zerofill特性，在pg中该如何实现呢？ 在pg中我们可以使用domain来实现mysql中的1，3字节整数以及无符号整型。 创建uint8，8字节无符号整型 bill=# create domain uint8 as numeric(20,0) check (value =0::numeric(20,0)); CREATE DOMAIN使用domain，插入整型数据，且大于等于0，小于2^64 bill=# create table t5(c1 uint8); CREATE TABLE bill=# insert into t5 values (-1); ERROR: value for domain uint8 violates check constraint "uint8_check" bill=# insert into t5 values (0); INSERT 0 1 bill=# insert into t5 values (18446744073709551615); INSERT 0 1 bill=# insert into t5 values (18446744073709551616); ERROR: value for domain uint8 violates check constraint "uint8_check" bill=# select * from t5; c1 ---------------------- 0 18446744073709551615 (2 rows)同样我们也可以来创建domain实现1,3字节有无符号整型，2,4,8字节无符号等等： create domain int1 as int2 CHECK (VALUE = (-128)); create domain uint1 as int2 CHECK (VALUE = 0); create domain uint2 as int4 CHECK (VALUE = 0); create domain int3 as int4 CHECK (VALUE = (-8388608)); create domain uint3 as int4 CHECK (VALUE = 0); create domain uint4 as int8 CHECK (VALUE = 0); create domain uint8 as numeric(20,0) check (value =0::numeric(20,0));而对于mysql中的zerofill，我们可以使用lpad函数来实现，并且这也是mysql官方文档中推荐的一种方式。 mysql中zerofill使用方式： mysql> create table t1(id int1 zerofill); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values(4); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+ ' id ' +------+ ' 004 ' +------+ 1 row in set (0.00 sec)pg中使用lpad函数替代： bill=# create table t1(id int); CREATE TABLE bill=# insert into t1 values (123),(123456); INSERT 0 2 bill=# select lpad(id::text, greatest(4, length(id::text)), '0'), id from t1; lpad ' id --------+-------- 0123 ' 123 123456 ' 123456 (2 rows) numeric类型：pg和mysql一样都支持decimal，numeric类型来表示浮点数。两者的区别在于：mysql中的numeric类型整数和小数部分均最大支持65digits。 而pg中numeric类型支持的最大范围是： [左131072,右16383]digits。 例如: mysql中 mysql> create table t1(id numeric(66,1)); ERROR 1426 (42000): Too-big precision 66 specified for 'id'. Maximum is 65. mysql> create table t1(id numeric(65,1)); Query OK, 0 rows affected (0.01 sec) pg中 bill=# create table t4(id numeric(66,1)); CREATE TABLE 浮点类型：mysql和pg中的浮点数类型基本一致。mysql中4 bytes的浮点数类型有real，float4，4 bytes的浮点数类型double。pg中对应的也有real，float，float4，float8以及double precision，两者基本兼容。 bit类型：mysql中bit类型一般都是使用整数类型表示，所以支持的bit位数最大只能是64位。而在pg中有专门的bit类型bit(范围1～83886080)，以及可变长度的bit类型varbit。 序列：mysql中创建表时可以使用auto_increment来创建自增列，从而生成一个和该列相关的序列，这个和pg中创建serial类型的列类似，但是两者仍然有明显区别： mysql：使用auto_increment的自增列必须要建索引，不然会报错。序列的默认初始值是1，步长为1.可以通过修改auto_increment_increment和auto_increment_offset来修改初始值和步长。 pg：pg中创建serial类型的列时会创建相应的序列，支持的数据类型有serial2，serial4，serial8。同时pg创建序列时可以直接指定初始值，步长，maxvalue，cache，circle等参数。其中序列cache预取多个值，可以保证没有性能问题。circle可以指定序列达到最大值后从初始值开始重新计数。 mysql mysql> create table t4 (id int auto_increment primary key);Query OK, 0 rows affected (0.06 sec) PostgreSQL bill=# create table t4(id serial);CREATE TABLE 2. 时间类型mysql：mysql中时间相关的类型有日期date、时间time以及datetime、timestamp和year类型。 pg：pg中的时间数据类型基本和mysql一致。区别在于pg中支持timez类型，即带时区的时间类型，这个mysql中不支持，但是pg中不支持mysql中的year类型，不过我们仍然可以通过创建domain的方式来在pg中实现year类型。 mysql中的year类型表示年份从 1901年到2155。 pg中实现mysql中year类型的方式： bill=# create domain year as int2 check(value >=1901 and value >>2018 extract(month from now());>>>9 extract(month from timestamp '2018-09-10 13:59:59');>>>9（3）对时间截取trunc()和date_trunc() oracle--trunc()的用法： trunc(sysdate，'yyyy');//返回当前年的第一天>>>2018-01-01 trunc(sysdate, 'mm');//返回当前月的第一天>>>2018-09-01 trunc(sysdate, 'dd');//返回当前时间的年月日>>>2018-09-14 trunc(sysdate, 'hh');//返回当前小时>>>2018-09-14 13:30:50postgreSQL--date_trunc()用法： date_trunc('year',now());//返回当前时间年的第一天>>>2018-01-01 00:00:00 date_trunc('month',now());//返回当前月的第一天>>2018-09-01 00:00:00 date_trunc('day',now()); //返回当前时间的年月日>>2018-09-14 00:00:00 date_trunc('second',now()); //返回当前时间的年月日时分秒>>2018-09-14 13:30:50（4）条件判断 Oracle： Select DECODE (payments_info,'CR','Credit','DB','Debit', null) FROM dual; PostgreSQL： Select CASE WHEN foo = 'CR' THEN 'Credit' WHEN foo = 'DB' THEN 'Debit' ELSE 'default' END FROM t2;3、DDL语法差异 oracle和pgSQL操作表结构语法基本一致： 修改表名：alter table tbl_user rename tbl_user2; 添加约束：alter table 表名 add constraint 表名_列名_nn check (is not null) 添加列：alter table tbl_user add age number(3) default 18 not null; alter table tbl_user add age number(3) default 18 not null after sex;（在指定列后添加列） 删除列：alter table tbl_user drop column age; 修改列：alter table tbl_user modify password default'000000' not null;（修改约束） 修改列名：alter table tbl_user rename column password to pwd; 只有更改列的数据类型写法有些差异 Oracle：ALTER TABLE table_name modify column_name datatype; PostgreSQL：ALTER TABLE table_name ALTER column_name TYPE datatype; 4、DML语法差异 oracle和pgSQL增删改查语法基本一致，只有upsert有差异 Oracle：有自带的merge into功能（一个强大的操作）
PostgreSQL：不支持merge操作，可以使用on conflict() do 例： insert into TargetTable select id,desc from SourceTable on conflict (id) do update set desc = exclude.desc 5、查询语句差异 （1）查询表中最新n条数据（Oracle有rownum，postgreSQL有limit） postgreSQL： select * from olc.olc_member_intebid_info order by create_time desc limit n;注意：limit必须用于 order by 之后 Oracle： 写法一： select t.* from (select * from nwd.tc_inte_bid_record order by create_time desc) t where rownum |
【本文地址】
今日新闻 |
推荐新闻 |