登录
当前位置： 首页 > 工具软件 > MySQL to PostgreSQL > 使用案例 >

MySQL & PostgreSQL 常用数据类型

穆毅然
2023-12-01

MySQL & PostgreSQL

MySQL: 全球最受欢迎的开源数据库

PostgreSQL: 全球最先进的开源关系数据库

本文章所有资料均来自 MySQL 5.7 文档1与 PostgreSQL 10 文档2。

常用数据类型

数字类型

整数类型

数据类型MySQLPostgreSQL
INT2SMALLINT 的别名SMALLINT 的别名
SMALLINT2 字节整数，SMALLINT(6) 的别名*2 字节整数
INT4 字节整数，INT(11) 的别名*INTEGER 的别名
INT4INT 的别名INTEGER 的别名
INTEGERINT 的别名4 字节整数
BIGINT8 字节整数，BIGINT(20) 的别名*8 字节整数
INT8BIGINT 的别名BIGINT 的别名

*在MySQL中，SMALLINT、INT和BIGINT的参数为显示宽度。

字节数范围范围
1(-27, 27-1)(-128, 127)
2(-215, 215-1)(-32768, 32767)
4(-231, 231-1)(-2147483648, 2147483647)
8(-263, 263-1)(-9223372036854775808, 9223372036854775807)

定点数类型（任意精度）

数据类型MySQLPostgreSQL
DECDECIMAL 的别名NUMERIC 的别名
DEC(precision, scale = 0)DECIMAL(precision, scale) 的别名NUMERICL(precision, scale) 的别名
DECIMALDECIMAL(10) 的别名NUMERIC 的别名
DECIMAL(precision, scale = 0)可选精度的精确数字，precision: (0, 65), scale: (0, 30)NUMERICL(precision, scale) 的别名
NUMERICDECIMAL 的别名精确数字
NUMERIC(precision, scale = 0)DECIMAL(precision, scale) 的别名可选精度的精确数字，precision: (0, 1000), scale: (0, 1000)

(precision >= scale)，其中 precision 为数字位数，scale 为小数位数。

浮点数类型

数据类型MySQLPostgreSQL
FLOAT单精度浮点数 (4 字节)DOUBLE PRECISION 的别名
FLOAT(precision)FLOAT 的别名，precision: (0, 24)REAL 的别名，precision: (1, 24)
DOUBLE 的别名*，precision: (25, 53)DOUBLE PRECISION 的别名，precision: (25, 53)
FLOAT4FLOAT 的别名REAL 的别名
REALDOUBLE 的别名*单精度浮点数 (4 字节)
DOUBLE PRECISIONDOUBLE 的别名*双精度浮点数 (8 字节)
FLOAT8DOUBLE 的别名*DOUBLE PRECISION 的别名

precision 为以二进制位表示的最低可接受精度。

*在MySQL中，DOUBLE为双精度浮点数(8字节)。

序数类型

数据类型MySQLPostgreSQL
SERIALBIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别名INTEGER NOT NULL DEFAULT nextval('tablename_colname_seq') 的别名*

*在PostgreSQL中，会在创建表格之前创建名为tablename_colname_seq的序列发生器，创建表格之后将该序列与tablename表的colname列相关联。

布尔类型

数据类型MySQLPostgreSQL
BOOLTINYINT(1) 的别名*BOOLEAN 的别名
BOOLEANTINYINT(1) 的别名*逻辑布尔值（真/假）

状态为真的值为 TRUE* 或 '1'，状态为假的值为 FALSE* 或 '0'。3

*在MySQL中，TINYINT为1字节整数，其参数为显示宽度。

*在MySQL中，TRUE是1的别名，FALSE是0的别名。

字符串类型

数据类型MySQLPostgreSQL
CHAR(length = 1)定长字符串，length: (0, 255)CHARACTER(length) 的别名
CHARACTER(length = 1)CHAR(length) 的别名定长字符串，length: (1, 10485760)
NATIONAL CHAR(length = 1)CHAR(length) CHARACTER SET utf8 的别名4CHARACTER(length) 的别名
NATIONAL CHARACTER(length = 1)CHAR(length) CHARACTER SET utf8 的别名4CHARACTER(length) 的别名
NCHAR(length = 1)CHAR(length) CHARACTER SET utf8 的别名4CHARACTER(length) 的别名
CHAR VARYING(length)VARCHAR(length) 的别名CHARACTER VARYING(length) 的别名
CHARACTER VARYING(length)VARCHAR(length) 的别名变长字符串，length: (1, 10485760)
NATIONAL CHAR VARYING(length)VARCHAR(length) CHARACTER SET utf8 的别名4CHARACTER VARYING(length) 的别名
NATIONAL CHARACTER VARYING(length)VARCHAR(length) CHARACTER SET utf8 的别名4CHARACTER VARYING(length) 的别名
NCHAR VARYING(length)VARCHAR(length) CHARACTER SET utf8 的别名4CHARACTER VARYING(length) 的别名
VARCHAR(length)变长字符串，(length >= 0)CHARACTER VARYING(length) 的别名
TEXT字符串，最大长度为 65535 (216-1) 个字节无限长字符串

length 为字符个数。

在单引号前加 n 或 N 前缀表示 NCHAR 类型字符串，例如 n'Hello, World!' 或 N'Hello, World!'。3

日期 / 时间类型

数据类型MySQLPostgreSQLISO 8601 格式值
DATE日期日期'YYYY-MM-DD'
TIMETIME(0) 的别名TIME(6) 的别名'hh:mm:ss'
TIME(precision)时间时间'hh:mm:ss.ssssss'
TIMESTAMPTIMESTAMP(0) 的别名TIMESTAMP(6) 的别名'YYYY-MM-DDThh:mm:ss'
TIMESTAMP(precision)时间戳时间戳'YYYY-MM-DDThh:mm:ss.ssssss'

precision: (0, 6)，其为秒的小数位数。

通过使用类型关键字，可直接将字面量指定为关键字对应的类型。3

类型关键字MySQL 对应类型PostgreSQL 对应类型字面量实例值
DATEDATE 类型DATE 类型DATE '2038-01-19'
TIMETIME 类型TIME 类型TIME '03:14:07'
TIMESTAMPDATETIME 类型*TIMESTAMP 类型TIMESTAMP '2038-01-19T03:14:07'

*在MySQL中，DATETIME类型和TIMESTAMP类型都表示时间戳。其中DATETIME类型的年份的范围为(0001,9999)，TIMESTAMP类型的年份的范围为(1970,2038)。

时间间隔

在时间运算中，使用 INTERVAL 关键字表示时间间隔，间隔单位如下表所示。5

单位描述实例表达式
YEAR年INTERVAL '1' YEAR
MONTH月INTERVAL '1' MONTH
DAY日INTERVAL '1' DAY
HOUR小时INTERVAL '1' HOUR
MINUTE分钟INTERVAL '1' MINUTE
SECOND秒INTERVAL '1' SECOND

JSON 类型

数据类型实例值
JSON'[{"key":"value"}]'

可直接将字符串转换为 JSON 类型，例如 CAST('[{"key":"value"}]' AS JSON)。6

位串类型

数据类型MySQLPostgreSQL2 进制实例值316 进制实例值3
BIT(length = 1)length: (1, 64)length: (1, 83886080)b'00111111' 或 B'00111111'x'3F' 或 X'3F'

length 为位个数。

时间运算示例

可以使用 + 和 - 运算符在表达式中执行时间运算。其中 + 运算符的一侧应为日期 / 时间类型值，另一侧应为时间间隔表达式。- 运算符的左侧只能是日期 / 时间类型值，右侧只能是时间间隔表达式。5

SELECT 
    INTERVAL '1' YEAR + DATE '1970-01-01' AS interval_year,
    DATE '1970-01-01' - INTERVAL '1' MONTH AS interval_month,
    TIMESTAMP '1970-01-01T00:00:00' + INTERVAL '1' DAY AS interval_day,
    TIMESTAMP '1970-01-01T00:00:00' - INTERVAL '1' HOUR AS interval_hour,
    INTERVAL '1' MINUTE + TIME '00:00:00' AS interval_minute,
    TIME '00:00:00' - INTERVAL '1' SECOND AS interval_second;

JSON 类型示例

创建表格

DROP TABLE IF EXISTS my_table CASCADE;
CREATE TABLE my_table
(
    id SERIAL,
    json_data JSON,
    PRIMARY KEY (id)
);
INSERT INTO my_table(json_data) VALUES
    ('{"key":"1''s JSON","value":["A",0,false]}'),
    ('{"key":"2''s JSON","value":["B",1,true]}'),
    ('{"key":"3''s JSON","value":["C",2,null]}');
SELECT * FROM my_table;

检索 JSON 数据

-> 和 ->> 操作符表示获取 JSON 值的指定元素。其中 -> 操作符返回 JSON 对象，->> 操作符返回文本对象。

MySQL

在 MySQL 中，-> 和 ->> 操作符左侧只能是 JSON 类型的列的标识符。右侧的操作数只能是字符串类型，为要匹配的 JSON 路径。7 在路径中使用 $ 符号表示当前 JSON 对象。8

DROP VIEW IF EXISTS my_view CASCADE;
CREATE VIEW my_view AS
(
    SELECT
        -- 不可连续使用 -> 或 ->> 操作符
        json_data->>'$.key' AS string_to_text,
        json_data->'$.value[0]' AS string_to_json,
        json_data->'$.value[1]' AS number_to_json,
        json_data->>'$.value[2]' AS boolean_to_text
    FROM
        my_table
);
SELECT * FROM my_view;

PostgreSQL

在 PostgreSQL 中，-> 和 ->> 操作符左侧为 JSON 类型的列的标识符或 JSON 对象。右侧的操作数为整数类型或字符串类型，只能是一个索引 / 键值。9

DROP VIEW IF EXISTS my_view CASCADE;
CREATE VIEW my_view AS
(
    SELECT
        -- 不可对 ->> 操作符返回的对象使用 -> 或 ->> 操作符
        json_data->>'key' AS string_to_text,
        -- 可连续使用 -> 操作符
        json_data->'value'->0 AS string_to_json,
        json_data->'value'->1 AS number_to_json,
        -- 可对 -> 操作符返回的对象使用 ->> 操作符
        json_data->'value'->>2 AS boolean_to_text
    FROM
        my_table
);
SELECT * FROM my_view;

  1. MySQL 5.7: Data Types ↩︎

  2. PostgreSQL 10: Data Types ↩︎

  3. MySQL 5.7: Literal Values ↩︎ ↩︎ ↩︎ ↩︎ ↩︎

  4. MySQL 5.7: The National Character Set ↩︎ ↩︎ ↩︎ ↩︎ ↩︎ ↩︎

  5. MySQL 5.7: Expressions ↩︎ ↩︎

  6. MySQL 5.7: Cast Functions and Operators ↩︎

  7. MySQL 5.7: JSON Function Reference ↩︎

  8. MySQL 5.7: The JSON Data Type ↩︎

  9. PostgreSQL 10: JSON Functions and Operators ↩︎

 类似资料：

相关阅读

将MySQL转储导入PostgreSQL数据库介绍PostgreSQL中的jsonb数据类型mysql等效数据类型PostgreSQL运算符类“ archar_pattern_ops”不接受整数数据类型在PostgreSQL中等效于Long数据类型？

相关文章

MySQL数据类型的选择mysql常用内置函数PostgreSQL 删除数据库PostgreSQL 选择数据库PostgreSQL 创建数据库

相关问答

H2数据库-MySQL脚本的未知数据类型如何在mysql中使用数组数据类型？[副本]Hibernate with Postgresql未正确加载数据类型为“text”的列的数据Postgresql -创建数据库使用JPA 1.9 (postgresql)无法设置数据类型int的默认值

相关文档

设计数据密集型应用Redis 常用命令Excel 数据透视表应用大全数据科学鲜活的数据数据可视化指南
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.