PostgreSQL MySQL 数据类型映射

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
简介:

标签

PostgreSQL , MySQL , 类型映射


背景

通常一家企业会有比较多的数据库品种,最为常见的如MySQL, PostgreSQL。

那么在不同的产品之间,如果有数据的相互同步,就涉及到类型的映射了。

对于PostgreSQL来说,可以使用PostgreSQL的mysql_fdw外部插件来实现同步,产品设计会简化很多。

而对于MySQL来说,可能就比较复杂,需要自己写程序来实现,会涉及到类型的转换。

PostgreSQL类型 转换为 MySQL类型

https://dev.mysql.com/doc/workbench/en/wb-migration-database-postgresql-typemapping.html

PostgreSQL Type MySQL Type Comment
INT INT -
SMALLINT SMALLINT -
BIGINT BIGINT -
SERIAL INT Sets AUTO_INCREMENT in its table definition.
SMALLSERIAL SMALLINT Sets AUTO_INCREMENT in its table definition.
BIGSERIAL BIGINT Sets AUTO_INCREMENT in its table definition.
BIT BIT -
BOOLEAN TINYINT(1) -
REAL FLOAT -
DOUBLE PRECISION DOUBLE -
NUMERIC DECIMAL -
DECIMAL DECIMAL -
MONEY DECIMAL(19,2) -
CHAR CHAR/LONGTEXT Depending on its length. MySQL Server 5.5 and above can have CHAR columns with a length up to 255 characters. Anything larger is migrated as LONGTEXT
NATIONAL CHARACTER CHAR/LONGTEXT Depending on its length. MySQL Server 5.5 and above can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types. In MySQL, character set of strings depend on the column character set instead of the datatype.
VARCHAR VARCHAR/MEDIUMTEXT/LONGTEXT Depending on its length. MySQL Server 5.5 and above can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types.
NATIONAL CHARACTER VARYING VARCHAR/MEDIUMTEXT/LONGTEXT Depending on its length. MySQL Server 5.5 and above can have VARCHAR columns with a length up to 65535 characters. Anything larger is migrated to one of the TEXT blob types. In MySQL, character set of strings depend on the column character set instead of the datatype.
DATE DATE -
TIME TIME -
TIMESTAMP DATETIME -
INTERVAL TIME -
BYTEA LONGBLOB -
TEXT LONGTEXT -
CIDR VARCHAR(43) -
INET VARCHAR(43) -
MACADDR VARCHAR(17) -
UUID VARCHAR(36) -
XML LONGTEXT -
JSON LONGTEXT -
TSVECTOR LONGTEXT -
TSQUERY LONGTEXT -
ARRAY LONGTEXT -
POINT POINT -
LINE LINESTRING Although LINE length is infinite, and LINESTRING is finite in MySQL, it is approximated
LSEG LINESTRING A LSEG is like a LINESTRING with only two points
BOX POLYGON A BOX is a POLYGON with five points and right angles
PATH LINESTRING -
POLYGON POLYGON -
CIRCLE POLYGON A POLYGON is used to approximate a CIRCLE
TXID_SNAPSHOT VARCHAR -

PostgreSQL还有很多类型,上面并没有列出来,比如图像,化学,raster, 基因等。

MySQL类型 转换为 PostgreSQL类型

取自mysql_fdw插件的代码

http://api.pgxn.org/src/mysql_fdw/mysql_fdw-2.1.2/mysql_fdw.c

https://github.com/EnterpriseDB/mysql_fdw/blob/master/mysql_fdw.c

    /*  
     * Fetch all table data from this schema, possibly restricted by  
     * EXCEPT or LIMIT TO.  
     */  
    appendStringInfo(&buf,  
                     " SELECT"  
                     "  t.TABLE_NAME,"  
                     "  c.COLUMN_NAME,"  
                     "  CASE"  
                     "    WHEN c.DATA_TYPE = 'enum' THEN LOWER(CONCAT(c.COLUMN_NAME, '_t'))"  
                     "    WHEN c.DATA_TYPE = 'tinyint' THEN 'smallint'"  
                     "    WHEN c.DATA_TYPE = 'mediumint' THEN 'integer'"  
                     "    WHEN c.DATA_TYPE = 'tinyint unsigned' THEN 'smallint'"  
                     "    WHEN c.DATA_TYPE = 'smallint unsigned' THEN 'integer'"  
                     "    WHEN c.DATA_TYPE = 'mediumint unsigned' THEN 'integer'"  
                     "    WHEN c.DATA_TYPE = 'int unsigned' THEN 'bigint'"  
                     "    WHEN c.DATA_TYPE = 'bigint unsigned' THEN 'numeric(20)'"  
                     "    WHEN c.DATA_TYPE = 'double' THEN 'double precision'"  
                     "    WHEN c.DATA_TYPE = 'float' THEN 'real'"  
                     "    WHEN c.DATA_TYPE = 'datetime' THEN 'timestamp'"  
                     "    WHEN c.DATA_TYPE = 'longtext' THEN 'text'"  
                     "    WHEN c.DATA_TYPE = 'mediumtext' THEN 'text'"  
                     "    WHEN c.DATA_TYPE = 'blob' THEN 'bytea'"  
                     "    WHEN c.DATA_TYPE = 'mediumblob' THEN 'bytea'"  
                     "    ELSE c.DATA_TYPE"  
                     "  END,"  
                     "  c.COLUMN_TYPE,"  
                     "  IF(c.IS_NULLABLE = 'NO', 't', 'f'),"  
                     "  c.COLUMN_DEFAULT"  
                     " FROM"  
                     "  information_schema.TABLES AS t"  
                     " JOIN"  
                     "  information_schema.COLUMNS AS c"  
                     " ON"  
                     "  t.TABLE_CATALOG <=> c.TABLE_CATALOG AND t.TABLE_SCHEMA <=> c.TABLE_SCHEMA AND t.TABLE_NAME <=> c.TABLE_NAME"  
                     " WHERE"  
                     "  t.TABLE_SCHEMA = '%s'",  
                     stmt->remote_schema);  

类型映射如下


                     "  CASE"  
                     "    WHEN c.DATA_TYPE = 'enum' THEN LOWER(CONCAT(c.COLUMN_NAME, '_t'))"  
                     "    WHEN c.DATA_TYPE = 'tinyint' THEN 'smallint'"  
                     "    WHEN c.DATA_TYPE = 'mediumint' THEN 'integer'"  
                     "    WHEN c.DATA_TYPE = 'tinyint unsigned' THEN 'smallint'"  
                     "    WHEN c.DATA_TYPE = 'smallint unsigned' THEN 'integer'"  
                     "    WHEN c.DATA_TYPE = 'mediumint unsigned' THEN 'integer'"  
                     "    WHEN c.DATA_TYPE = 'int unsigned' THEN 'bigint'"  
                     "    WHEN c.DATA_TYPE = 'bigint unsigned' THEN 'numeric(20)'"  
                     "    WHEN c.DATA_TYPE = 'double' THEN 'double precision'"  
                     "    WHEN c.DATA_TYPE = 'float' THEN 'real'"  
                     "    WHEN c.DATA_TYPE = 'datetime' THEN 'timestamp'"  
                     "    WHEN c.DATA_TYPE = 'longtext' THEN 'text'"  
                     "    WHEN c.DATA_TYPE = 'mediumtext' THEN 'text'"  
                     "    WHEN c.DATA_TYPE = 'blob' THEN 'bytea'"  
                     "    WHEN c.DATA_TYPE = 'mediumblob' THEN 'bytea'"  
                     "    ELSE c.DATA_TYPE"  
                     "  END,"  

其他关注点

如果你要的不是数据同步,还有后续的动作,例如从一个产品迁移到另一个产品,那会是一个比较大的动作,所以多啰嗦几句。

除了数据类型,还有SQL语法,函数、索引方法(8种内置hash,btree,gin,gist,bloom,brin,rum,sp-gist),也是迁移需要特别考虑的,PostgreSQL在这方面支持更加全面,如果要从PostgreSQL迁移到MySQL需要特别注意。

列举一些例子,取自如下文档

《数据库选型的关键指标》

功能差异

1. 递归查询, connect by, 树形查询

PostgreSQL 通过(with 或 tablefunc支持)支持例子

https://yq.aliyun.com/articles/240

http://www.postgresql.org/docs/9.5/static/tablefunc.html

2. 窗口查询, window over

PostgreSQL 支持例子

http://blog.163.com/digoal@126/blog/static/16387704020137154137930

http://blog.163.com/digoal@126/blog/static/16387704020121024102312302/

http://blog.163.com/digoal@126/blog/static/16387704020124239390354/

http://www.postgresql.org/docs/9.5/static/functions-window.html

3. rollup, grouping sets, cube

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#QUERIES-GROUPING-SETS

http://blog.163.com/digoal@126/blog/static/16387704020154269591874/

4. 高级聚合(json,数组,相关性,标准差(采样,全局),截距,斜率,方差(采样,全局),mode,percentile_cont,distc,rank,dense_rank,percent_rank,cume_dist,grouping)

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/functions-aggregate.html

http://blog.163.com/digoal@126/blog/static/1638770402015224124337/

http://blog.163.com/digoal@126/blog/static/1638770402015379286873/

http://blog.163.com/digoal@126/blog/static/16387704020153713222764

4. hash join, merge join, nestloop join

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/xoper-optimization.html

http://www.postgresql.org/docs/9.5/static/planner-optimizer.html

5. 哈希聚合

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE

6. 事务间共享事务快照

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION

http://www.postgresql.org/docs/9.5/static/sql-set-transaction.html

http://www.postgresql.org/docs/9.5/static/app-pgdump.html

http://blog.163.com/digoal@126/blog/static/163877040201326829943/

http://blog.163.com/digoal@126/blog/static/163877040201241134721101/

7. 展开式索引(支持多列任意组合查询)

PostgreSQL 支持例子

《宝剑赠英雄 - 任意组合字段等效查询, 探探PostgreSQL多列展开式B树》

约束

1. foreign key

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/ddl-constraints.html

2. for no key update, for key share 粒度锁

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/explicit-locking.html#LOCKING-ROWS

http://blog.163.com/digoal@126/blog/static/16387704020130249109133/

http://blog.163.com/digoal@126/blog/static/16387704020130305109687/

3. check 约束

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/ddl-constraints.html

4. exclusion 约束

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/ddl-constraints.html

易用性

1. 表空间

2. alter 列值转表达式

(alter table alter column c1 type newtype using (expression(...)) )

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/sql-altertable.html

https://yq.aliyun.com/articles/30470

3. alter table 需要重组表的操作

PostgreSQL少量操作需要重组

vacuum full, cluster, 修改字段数据类型, (修改长度不需要重组表)

http://www.postgresql.org/docs/9.5/static/sql-altertable.html

4. 分区表

《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》

《PostgreSQL 10.0 内置分区表》

5. 物化视图

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/sql-creatematerializedview.html

6. 物化视图增量刷新

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/sql-refreshmaterializedview.html

7. 表继承关系

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/tutorial-inheritance.html

8. 使用 like 建结构类似的表

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/sql-createtable.html

开发功能

1. 客户端开发语言支持

C, java, python, ...

2. 函数

返回 void, 单行,SRF,事件触发器(MySQL 不支持),触发器

例子

http://blog.163.com/digoal@126/blog/static/16387704020132131361949/

http://www.postgresql.org/docs/9.5/static/event-triggers.html

3. 2PC

4. 服务端绑定变量

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/sql-prepare.html

5. savepoint

6. 异步消息

PostgreSQL 支持例子

http://www.postgresql.org/docs/9.5/static/sql-notify.html

http://www.postgresql.org/docs/9.5/static/sql-listen.html

7. 游标

数组FOR循环,query FOR循环,游标FOR循环

PostgreSQL 全面支持例子

http://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html

http://www.postgresql.org/docs/9.5/static/plpgsql-cursors.html

(MySQL 暂不支持数组)

类型

1. 数据类型

1.1 PostgreSQL

高精度numeric, 浮点, 自增序列,货币,字节流,时间,日期,时间戳,布尔,枚举,平面几何,立体几何,多维几何,地球,PostGIS,网络,比特流,全文检索,UUID,XML,JSON,数组,复合类型,域类型,范围,树类型,化学类型,基因序列,FDW, 大对象, 图像

1.2 MySQL

数字,时间,字符串,简单的GIS,JSON

2. 支持索引的数据类型

2.1 PostgreSQL

高精度numeric, 浮点, 自增序列,货币,字节流,时间,日期,时间戳,布尔,枚举,平面几何,立体几何,多维几何,地球,PostGIS,网络,比特流,全文检索,UUID,XML,JSON,数组,复合类型,域类型,范围,树类型,化学,基因序列

2.2 MySQL

数字,字符串,比特流,时间,全文检索,GIS

索引支持

1. 索引方法

PostgreSQL 支持 btree, hash, gist, sp-gist, gin, brin , bloom , rum 索引

MySQL 支持 btree, gis类型索引

2. 规则表达式、前后模糊查询,支持索引检索

PostgreSQL 支持

3. 数组支持索引

PostgreSQL 支持

4. 全文检索支持索引

5. 索引扩展功能

5.1 PostgreSQL 支持 表达式索引,部分索引,联合索引

5.2 PostgreSQL 支持图像相似度、文本相似度搜索

5.3 自定义索引访问方法(bloom, rum, ......)

PostgreSQL支持

多引擎和外部源支持

1. 多引擎支持

1.1 PostgreSQL

内置heap, 通过插件实现内存表, 列存储, 压缩存储, 流式存储, 非关系存储等。

1.2 MySQL

MyISAM, innodb, ...

2. 外部表

PostgreSQL支持任意外部数据源, (例如jdbc, file, odbc, oracle, mysql, db2, redis, mongo, ES, hadoop......)

https://wiki.postgresql.org/wiki/Fdw

3. dblink

PostgreSQL 支持

4. 内存表

PostgreSQL 通过外部表支持,或者通过插件支持

5. ES(弹性搜索引擎) 引擎

PostgreSQL 支持

安全

1. 数据加密

PostgreSQL 支持加密数据类型,可选GPG加密算法

2. 认证方法

PostgreSQL 支持 密码、LDAP、AD、GSSAPI、SSPI、Ident、Peer、RADIUS、PAM、签名认证

3. 数据传输加密

PostgreSQL 支持

4. 行安全策略

PostgreSQL 支持

5. 数据库内部支持libselinux接口, (美国国家安全局制定的安全加强标准)

PostgreSQL 支持

优化器

http://www.postgresql.org/docs/9.5/static/runtime-config-query.html

1. GPU 并行计算支持

PostgreSQL 支持

2. 遗传优化器算法

PostgreSQL 支持CBO、CRO、遗传算法

3. HINT PLAN

4. CPU 并行计算

PostgreSQL 支持 (线性性能提升)

5. 自定义成本因子

PostgreSQL 支持

编程扩展

1. 是否支持采样查询

PostgreSQL 支持

2. 是否支持扩展采样算法

PostgreSQL 支持

3. 自定义数据类型

PostgreSQL 支持

4. 自定义索引方法

PostgreSQL 支持

5. 字符集自动转换, C扩展接口

PostgreSQL 支持

6. 自定义聚合

PostgreSQL 支持

7. 自定义窗口

PostgreSQL 支持

扩展能力

1. 类型扩展,操作符扩展,函数扩展,索引方法扩展,索引扩展,

PostgreSQL 支持

2. C触发器函数, C事件触发器函数

PostgreSQL 支持

3. 函数语言扩展

PostgreSQL 支持扩展函数支持接口, erlang, ....

4. 机器学习库

PostgreSQL 支持

5. 流式计算

PostgreSQL 支持

性能

《数据库界的华山论剑 tpc.org》

《facebook linkbench 测试PostgreSQL社交关系图谱场景性能》

《PostgreSQL 主机性能测试方法 - 单机多实例》

《PostgreSQL 主机性能测试方法 - 单机单实例》

《PostgreSQL 源码性能诊断(perf profiling)指南》

《"物联网"流式处理应用 - 用PostgreSQL实时处理(万亿每天)》

《PostgreSQL 百亿地理位置数据 近邻查询性能》

《PostgreSQL 百亿数据 秒级响应 正则及模糊查询》

《PostgreSQL 1000亿数据量 正则匹配 速度与激情》

《恭迎万亿级营销(圈人)潇洒的迈入毫秒时代 - 万亿user_tags级实时推荐系统数据库设计》

《从难缠的模糊查询聊开 - PostgreSQL独门绝招之一 GIN , GiST , SP-GiST , RUM 索引原理与技术背景》

《分析加速引擎黑科技 - LLVM、列存、多核并行、算子复用 大联姻 - 一起来开启PostgreSQL的百宝箱》

服务端编程语言

1. 函数(过程)语言

PostgreSQL支持几乎所有的地球编程语言,你可以用他们在数据库中编写UDF

plpgsql, sql, c, c++, java, javascript, R, python, perl, php, tcl, ruby, lua, ...

scale out

1. PostgreSQL完全支持以下下推功能

支持聚合算子下推

支持WHERE条件下推

支持JOIN下推

支持SORT下推

支持SELECT 子句下推

支持跨数据源JOIN

参考

《数据库选型的关键指标》

《宝剑赠英雄 - 任意组合字段等效查询, 探探PostgreSQL多列展开式B树》

http://api.pgxn.org/src/mysql_fdw/mysql_fdw-2.1.2/mysql_fdw.c

https://dev.mysql.com/doc/workbench/en/wb-migration-database-postgresql-typemapping.html

https://github.com/EnterpriseDB/mysql_fdw/blob/master/mysql_fdw.c

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
20天前
|
存储 关系型数据库 MySQL
|
20天前
|
存储 SQL 关系型数据库
|
21天前
|
存储 关系型数据库 MySQL
|
12天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
10天前
|
缓存 运维 关系型数据库
数据库容灾 | MySQL MGR与阿里云PolarDB-X Paxos的深度对比
经过深入的技术剖析与性能对比,PolarDB-X DN凭借其自研的X-Paxos协议和一系列优化设计,在性能、正确性、可用性及资源开销等方面展现出对MySQL MGR的多项优势,但MGR在MySQL生态体系内也占据重要地位,但需要考虑备库宕机抖动、跨机房容灾性能波动、稳定性等各种情况,因此如果想用好MGR,必须配备专业的技术和运维团队的支持。 在面对大规模、高并发、高可用性需求时,PolarDB-X存储引擎以其独特的技术优势和优异的性能表现,相比于MGR在开箱即用的场景下,PolarDB-X基于DN的集中式(标准版)在功能和性能都做到了很好的平衡,成为了极具竞争力的数据库解决方案。
|
2天前
|
关系型数据库 MySQL Serverless
体验阿里云PolarDB MySQL Serverless集群
体验阿里云PolarDB MySQL Serverless集群
|
15天前
|
关系型数据库 MySQL 数据库
关系型数据库mysql数据增量恢复
【7月更文挑战第3天】
129 2
|
15天前
|
关系型数据库 MySQL Shell
关系型数据库mysql数据完全恢复
【7月更文挑战第3天】
84 2
|
15天前
|
存储 关系型数据库 MySQL
|
17天前
|
存储 关系型数据库 MySQL

相关产品

  • 云数据库 RDS MySQL 版
  • 云原生数据库 PolarDB