PostgreSQL MySQL 数据类型映射

背景

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

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

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

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

PostgreSQL类型转换为 MySQL类型

dev.mysql.com/doc/workben…

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插件的代码

api.pgxn.org/src/mysql_f…

github.com/EnterpriseD…

    /*  
     * 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支持)支持例子

PostgreSQL MySQL 数据类型映射

背景

PostgreSQL类型 转换为 MySQL类型

MySQL类型 转换为 PostgreSQL类型

其他关注点

功能差异

约束

易用性

开发功能

类型

索引支持

多引擎和外部源支持

安全

优化器

编程扩展

扩展能力

性能

服务端编程语言

scale out

参考

PostgreSQL类型转换为 MySQL类型

MySQL类型转换为 PostgreSQL类型