postgresql mysql 兼容_PostgreSQL MySQL 兼容性之 - 字符串类型

char

MySQL

[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length in characters. The range of M is 0 to 255. If M is omitted, the length is 1.

PostgreSQL

[NATIONAL] CHAR[(M)] [COLLATE collation_name] 对应 PostgreSQL

char[(M)] [COLLATE collation_name]

varchar

MySQL

[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,535.

PostgreSQL

[NATIONAL] VARCHAR(M) [COLLATE collation_name] 对应 PostgreSQL

VARCHAR(M) [COLLATE collation_name]

BINARY CHAR BYTE

MySQL

BINARY(M) , CHAR BYTE

The BINARY type is similar to the CHAR type, but stores binary byte strings rather than non-binary character strings. M represents the column length in bytes.

PostgreSQL

char([M])

VARBINARY(M)

MySQL

VARBINARY(M)

The VARBINARY type is similar to the VARCHAR type, but stores binary byte strings rather than non-binary character strings. M represents the maximum column length in bytes.

It contains no character set, and comparison and sorting are based on the numeric value of the bytes.

PostgreSQL

varchar[(M)]

BLOB

MySQL

TINYBLOB A BLOB column with a maximum length of 255 bytes. Each TINYBLOB value is stored using a one-byte length prefix that indicates the number of bytes in the value.

MEDIUMBLOB A BLOB column with a maximum length of 16,777,215 bytes. Each MEDIUMBLOB value is stored using a three-byte length prefix that indicates the number of bytes in the value.

LONGBLOB A BLOB column with a maximum length of 4,294,967,295 bytes or 4GB. The effective maximum length of LONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGBLOB value is stored using a four-byte length prefix that indicates the number of bytes in the value.

BLOB[(M)] A BLOB column with a maximum length of 65,535 bytes. Each BLOB value is stored using a two-byte length prefix that indicates the number of bytes in the value.

PostgreSQL

bytea , upto 1GB (support compression, pglz)

large object , upto 4TB (support compression)

TEXT

MySQL

TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 255 characters. The effective maximum length is less if the value contains multi-byte characters. Each TINYTEXT value is stored using a one-byte length prefix that indicates the number of bytes in the value.

TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 65,535 characters. The effective maximum length is less if the value contains multi-byte characters.

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 16,777,215 characters. The effective maximum length is less if the value contains multi-byte characters.

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 4,294,967,295 or 4GB characters. The effective maximum length is less if the value contains multi-byte characters.

PostgreSQL

不支持设置字段的CHARACTER SET, CHARACTER SET是库级别的属性.

text

upto 1G

OR

varchar[(M)]

upto 1G

enum

MySQL

ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special '' error value.

In theory, an ENUM column can have a maximum of 65,535 distinct values;

in practice, the real maximum depends on many factors. ENUM values are represented internally as integers.

PostgreSQL

不支持设置字段的CHARACTER SET, CHARACTER SET是库级别的属性.

enum

SET

MySQL

SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ...

A SET column can have a maximum of 64 members.

SET values are represented internally as integers.

PostgreSQL

enum