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 c
olumns 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 t of strings depend on the
column character t 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 t of strings depend on the
column character t 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 ud 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.
电视机十大品牌
" 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 ts, cube
PostgreSQL ⽀持例⼦
4. ⾼级聚合(json，数组，相关性，标准差(采样，全局)，截距，斜率,⽅差(采样，全
局),mode,percentile_cont,distc,rank,den_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，⽹
冬天来了作文
