PostgreSQLMySQL数据类型映射标签
PostgreSQL , MySQL , 类型映射
背景
通常⼀家企业会有⽐较多的数据库品种，最为常见的如MySQL, PostgreSQL。
那么在不同的产品之间，如果有数据的相互同步，就涉及到类型的映射了。
对于PostgreSQL来说，可以使⽤PostgreSQL的mysql_fdw外部插件来实现同步，产品设计会简化很多。
⽽对于MySQL来说，可能就⽐较复杂，需要⾃⼰写程序来实现，会涉及到类型的转换。PostgreSQL类型转换为 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 col
umns 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 Type MySQL Type Comment
PostgreSQL还有很多类型，上⾯并没有列出来，⽐如图像，化学，raster, 基因等。
MySQL类型转换为 PostgreSQL类型
取⾃mysql_fdw插件的代码
1.
/*
2.
* Fetch all table data from this schema, possibly restricted by
3.
* EXCEPT or LIMIT TO.
4.
*/
5.
appendStringInfo(&buf,
6.
" SELECT"
7.
" t.TABLE_NAME,"
8.
" c.COLUMN_NAME,"
9.
" CASE"
10.
" WHEN c.DATA_TYPE = 'enum' THEN LOWER(CONCAT(c.COLUMN_NAME, '_t'))"
11.
" WHEN c.DATA_TYPE = 'tinyint' THEN 'smallint'"
12.
" WHEN c.DATA_TYPE = 'mediumint' THEN 'integer'"
13.
" WHEN c.DATA_TYPE = 'tinyint unsigned' THEN 'smallint'"
14.
" WHEN c.DATA_TYPE = 'smallint unsigned' THEN 'integer'"
15.
" WHEN c.DATA_TYPE = 'mediumint unsigned' THEN 'integer'"
16.
" WHEN c.DATA_TYPE = 'int unsigned' THEN 'bigint'"
17.
" WHEN c.DATA_TYPE = 'bigint unsigned' THEN 'numeric(20)'"
18.
" WHEN c.DATA_TYPE = 'double' THEN 'double precision'"
19.
" WHEN c.DATA_TYPE = 'float' THEN 'real'"
20.
" WHEN c.DATA_TYPE = 'datetime' THEN 'timestamp'"
21.
" WHEN c.DATA_TYPE = 'longtext' THEN 'text'"
22.
" WHEN c.DATA_TYPE = 'mediumtext' THEN 'text'"
23.
" WHEN c.DATA_TYPE = 'blob' THEN 'bytea'"
24.
" WHEN c.DATA_TYPE = 'mediumblob' THEN 'bytea'"
27.
" c.COLUMN_TYPE,"
28.
" IF(c.IS_NULLABLE = 'NO', 't', 'f'),"
29.
" c.COLUMN_DEFAULT"
30.
" FROM"
31.
" information_schema.TABLES AS t"
32.
" JOIN"
33.
float up
" information_schema.COLUMNS AS c"
34.
" ON"
35.
" t.TABLE_CATALOG <=> c.TABLE_CATALOG AND t.TABLE_SCHEMA <=> c.TABLE_SCHEMA AND t.TABLE_NAME <=>
c.TABLE_NAME"
36.
" WHERE"
37.
" t.TABLE_SCHEMA = '%s'",
38.
stmt->remote_schema);
39.
类型映射如下
1.
2.
" CASE"
3.
" WHEN c.DATA_TYPE = 'enum' THEN LOWER(CONCAT(c.COLUMN_NAME, '_t'))"
4.
" WHEN c.DATA_TYPE = 'tinyint' THEN 'smallint'"
5.
" WHEN c.DATA_TYPE = 'mediumint' THEN 'integer'"
6.
" WHEN c.DATA_TYPE = 'tinyint unsigned' THEN 'smallint'"
7.
" WHEN c.DATA_TYPE = 'smallint unsigned' THEN 'integer'"
8.
" WHEN c.DATA_TYPE = 'mediumint unsigned' THEN 'integer'"
9.
" WHEN c.DATA_TYPE = 'int unsigned' THEN 'bigint'"
10.
" WHEN c.DATA_TYPE = 'bigint unsigned' THEN 'numeric(20)'"
11.
" WHEN c.DATA_TYPE = 'double' THEN 'double precision'"
12.
" WHEN c.DATA_TYPE = 'float' THEN 'real'"
13.
" WHEN c.DATA_TYPE = 'datetime' THEN 'timestamp'"
14.
" WHEN c.DATA_TYPE = 'longtext' THEN 'text'"
15.
" WHEN c.DATA_TYPE = 'mediumtext' THEN 'text'"
16.
" WHEN c.DATA_TYPE = 'blob' THEN 'bytea'"
17.
" WHEN c.DATA_TYPE = 'mediumblob' THEN 'bytea'"
其他关注点
如果你要的不是数据同步，还有后续的动作，例如从⼀个产品迁移到另⼀个产品，那会是⼀个⽐较⼤的动作，所以多啰嗦⼏句。
除了数据类型，还有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，⽹
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论