PostgreSQL , MySQL , 类型映射
通常一家企业会有比较多的数据库品种,最为常见的如 MySQL, PostgreSQL。
那么在不同的产品之间,如果有数据的相互同步,就涉及到类型的映射了。
对于 PostgreSQL 来说,可以使用 PostgreSQL 的 mysql_fdw 外部插件来实现同步,产品设计会简化很多。
而对于 MySQL 来说,可能就比较复杂,需要自己写程序来实现,会涉及到类型的转换。
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_fdw 插件的代码
- /*
- * 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 支持) 支持例子
2. 窗口查询, window over
PostgreSQL 支持例子
3. rollup, grouping sets, cube
PostgreSQL 支持例子
4. 高级聚合 (json, 数组, 相关性, 标准差 (采样, 全局), 截距, 斜率, 方差 (采样, 全局),mode,percentile_cont,distc,rank,dense_rank,percent_rank,cume_dist,grouping)
PostgreSQL 支持例子
4. hash join, merge join, nestloop join
PostgreSQL 支持例子
5. 哈希聚合
PostgreSQL 支持例子
6. 事务间共享事务快照
PostgreSQL 支持例子
7. 展开式索引 (支持多列任意组合查询)
PostgreSQL 支持例子
1. foreign key
PostgreSQL 支持例子
2. for no key update, for key share 粒度锁
PostgreSQL 支持例子
3. check 约束
PostgreSQL 支持例子
4. exclusion 约束
PostgreSQL 支持例子
1. 表空间
2. alter 列值转表达式
(alter table alter column c1 type newtype using (expression(...)) )
PostgreSQL 支持例子
3. alter table 需要重组表的操作
PostgreSQL 少量操作需要重组
vacuum full, cluster, 修改字段数据类型, (修改长度不需要重组表)
4. 分区表
5. 物化视图
PostgreSQL 支持例子
6. 物化视图增量刷新
PostgreSQL 支持例子
7. 表继承关系
PostgreSQL 支持例子
8. 使用 like 建结构类似的表
PostgreSQL 支持例子
1. 客户端开发语言支持
C, java, python, ...
2. 函数
返回 void, 单行,SRF,事件触发器 (MySQL 不支持),触发器
例子
3. 2PC
4. 服务端绑定变量
PostgreSQL 支持例子
5. savepoint
6. 异步消息
PostgreSQL 支持例子
7. 游标
数组 FOR 循环,query FOR 循环,游标 FOR 循环
PostgreSQL 全面支持例子
(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......)
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 支持
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 支持
1. 函数 (过程) 语言
PostgreSQL 支持几乎所有的地球编程语言,你可以用他们在数据库中编写 UDF
plpgsql, sql, c, c++, java, javascript, R, python, perl, php, tcl, ruby, lua, ...
1. PostgreSQL 完全支持以下下推功能
支持聚合算子下推
支持 WHERE 条件下推
支持 JOIN 下推
支持 SORT 下推
支持 SELECT 子句下推
支持跨数据源 JOIN
来源: