MySQL & PostgreSQL 常用数据类型_砂漠的毒虫_mysql和postgresql字段类型

网络 02-07 6781

MySQL & PostgreSQL 常用数据类型 MySQL & PostgreSQL常用数据类型数字类型整数类型定点数类型（任意精度）浮点数类型序数类型布尔类型字符串类型日期 / 时间类型时间间隔 JSON 类型位串类型时间运算示例JSON 类型示例创建表格检索 JSON 数据MySQLPostgreSQL

MySQL & PostgreSQL

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

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

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

常用数据类型数字类型整数类型数据类型MySQLPostgreSQLINT2SMALLINT 的别名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)

定点数类型（任意精度）数据类型MySQLPostgreSQLDECDECIMAL 的别名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 为小数位数。

浮点数类型数据类型MySQLPostgreSQLFLOAT单精度浮点数 (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字节)。

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

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

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

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

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

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

字符串类型数据类型MySQLPostgreSQLCHAR(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!'。³

日期 / 时间类型数据类型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)，其为秒的小数位数。

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

类型关键字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 关键字表示时间间隔，间隔单位如下表所示。⁵

单位描述实例表达式YEAR年INTERVAL '1' YEARMONTH月INTERVAL '1' MONTHDAY日INTERVAL '1' DAYHOUR小时INTERVAL '1' HOURMINUTE分钟INTERVAL '1' MINUTESECOND秒INTERVAL '1' SECOND

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

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

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

length 为位个数。

时间运算示例

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

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 对象。⁸}

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 对象。右侧的操作数为整数类型或字符串类型，只能是一个索引 / 键值。⁹

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;

MySQL 5.7: Data Types ??

PostgreSQL10: Data Types ??

MySQL 5.7: Literal Values ?? ?? ?? ?? ??

MySQL 5.7: The National Character Set ?? ?? ?? ?? ?? ??

MySQL 5.7: Expressions ?? ??

MySQL 5.7: Cast Functions and Operators ??

MySQL 5.7: JSON Function Reference ??

MySQL 5.7: The JSON Data Type ??

PostgreSQL10: JSON Functions and Operators ??