PostgreSQL与MySQL的JSON类型及Go语言处理

关系型数据库存 JSON 早就不稀奇了，但把它用对没那么简单。你得先分清 PostgreSQL 里 json 和 jsonb 到底差在哪、什么时候该建什么索引，再决定 Go 代码里怎么读写才不别扭。这篇就把这几件事讲清楚，代码都能直接抄进项目。

---

一、三种类型的本质区别

维度	PostgreSQL json	PostgreSQL jsonb	MySQL json
存储形式	原始文本（逐字节保存）	分解后的二进制	分解后的二进制
保留空格/键序	保留	不保留	不保留
保留重复键	保留	只留最后一个	只留最后一个
写入速度	快（不解析）	稍慢（要解析）	稍慢（要解析）
查询速度	慢（每次重解析）	快	快
支持索引	否（需表达式索引）	GIN 索引	函数索引 / 多值索引
去重/规范化	否	是	是
一句话选型：

• PostgreSQL 绝大多数场景直接用 jsonb。只有当你需要"原样存回、包括空格和键顺序"这种审计类需求时才用 json。
• MySQL 只有 json 一种（内部实现类似 jsonb，二进制存储、支持部分更新）。

---

二、PostgreSQL 中的 json 与 jsonb

2.1 建表与写入

CREATE TABLE events (
    id      BIGSERIAL PRIMARY KEY,
    payload JSONB NOT NULL
);

INSERT INTO events (payload) VALUES
    ('{"user": "alice", "tags": ["go", "db"], "score": 42}');

2.2 常用操作符

PostgreSQL 处理 JSON 靠的是一套操作符，比函数调用简洁得多：

-- -> 取子对象（返回 jsonb），->> 取文本（返回 text）
SELECT payload -> 'user'    FROM events;   -- "alice"（带引号，jsonb）
SELECT payload ->> 'user'   FROM events;   -- alice（纯文本）

-- 路径取值 #> / #>>
SELECT payload #>> '{tags, 0}' FROM events;  -- go

-- 包含判断 @>（jsonb 专属，配合 GIN 索引极快）
SELECT * FROM events WHERE payload @> '{"user": "alice"}';

-- 键是否存在 ?（注意：? 只作用于「顶层」键）
SELECT * FROM events WHERE payload ? 'score';
-- 判断嵌套键，要先下钻到那一层再 ?
SELECT * FROM events WHERE payload -> 'dimensions' ? 'weight';

-- 任一键存在 ?| / 所有键存在 ?&
SELECT * FROM events WHERE payload ?| array['score', 'level'];

-- 用 JSON 内的值做过滤：->> 取出的是 text，需显式类型转换
SELECT * FROM events WHERE (payload ->> 'score')::numeric < 100;

两个高频踩坑点：? 系列操作符只能匹配顶层键，判断嵌套键必须先用 -> 下钻到对应层级；->> 取出的值始终是 text，参与数值/时间比较前要 ::numeric、::timestamptz 等强制转换，否则要么报错、要么变成字符串比较。

2.3 索引

-- 通用 GIN 索引，支持 @> ? ?| ?& 等
CREATE INDEX idx_payload ON events USING GIN (payload);

-- jsonb_path_ops：更小更快，但只支持 @>
CREATE INDEX idx_payload_path ON events USING GIN (payload jsonb_path_ops);

-- 针对单个字段的表达式 B-Tree 索引
CREATE INDEX idx_user ON events ((payload ->> 'user'));

2.4 修改与聚合

-- 更新某个键（jsonb_set）
UPDATE events SET payload = jsonb_set(payload, '{score}', '100') WHERE id = 1;

-- 删除键
UPDATE events SET payload = payload - 'tags' WHERE id = 1;

-- 合并两个 jsonb（|| 后者覆盖前者）
SELECT '{"a":1}'::jsonb || '{"b":2}'::jsonb;   -- {"a":1,"b":2}

-- 展开数组元素
SELECT jsonb_array_elements_text(payload -> 'tags') FROM events;

-- JSONPath（PG12+）
SELECT jsonb_path_query(payload, '$.tags[*] ? (@ == "go")') FROM events;

---

三、MySQL 中的 json 类型

MySQL 5.7 引入 json，8.0 大幅增强（多值索引、->> 语法糖、部分更新优化）。

3.1 建表与写入

CREATE TABLE events (
    id      BIGINT AUTO_INCREMENT PRIMARY KEY,
    payload JSON NOT NULL
);

INSERT INTO events (payload) VALUES
    ('{"user": "alice", "tags": ["go", "db"], "score": 42}');

3.2 常用函数与路径

MySQL 走的是"函数 + JSONPath"路线，而非操作符：

-- 取值：JSON_EXTRACT 或 -> 语法糖
SELECT JSON_EXTRACT(payload, '$.user')  FROM events;  -- "alice"
SELECT payload -> '$.user'              FROM events;  -- "alice"
SELECT payload ->> '$.user'             FROM events;  -- alice（去引号，等价 JSON_UNQUOTE）

-- 数组元素
SELECT payload ->> '$.tags[0]' FROM events;  -- go

-- 是否包含
SELECT * FROM events WHERE JSON_CONTAINS(payload, '"alice"', '$.user');

-- 路径是否存在
SELECT * FROM events WHERE JSON_CONTAINS_PATH(payload, 'one', '$.score');

-- 键集合 / 长度
SELECT JSON_KEYS(payload), JSON_LENGTH(payload -> '$.tags') FROM events;

3.3 修改

-- 设置（存在则改，不存在则加）
UPDATE events SET payload = JSON_SET(payload, '$.score', 100) WHERE id = 1;

-- 仅新增（已存在不动） / 仅替换（不存在不加）
UPDATE events SET payload = JSON_INSERT(payload,  '$.level', 5);
UPDATE events SET payload = JSON_REPLACE(payload, '$.score', 0);

-- 删除
UPDATE events SET payload = JSON_REMOVE(payload, '$.tags');

-- 数组追加
UPDATE events SET payload = JSON_ARRAY_APPEND(payload, '$.tags', 'sql');

-- 合并（保留两边所有值）
SELECT JSON_MERGE_PRESERVE('{"a":1}', '{"a":2}');  -- {"a":[1,2]}
SELECT JSON_MERGE_PATCH('{"a":1}', '{"a":2}');     -- {"a":2}（RFC 7396）

3.4 索引（虚拟列 / 多值索引）

MySQL 不能直接给 JSON 列建索引，需借助生成列或 8.0 多值索引：

-- 方式一：生成列 + 普通索引
ALTER TABLE events
    ADD COLUMN user_name VARCHAR(64)
        AS (payload ->> '$.user') STORED,
    ADD INDEX idx_user (user_name);

-- 方式二：函数索引（MySQL 8.0.13+）
ALTER TABLE events ADD INDEX idx_score ((CAST(payload ->> '$.score' AS UNSIGNED)));

-- 方式三：多值索引，专为 JSON 数组设计（8.0.17+）
ALTER TABLE events
    ADD INDEX idx_tags ((CAST(payload -> '$.tags' AS CHAR(32) ARRAY)));
-- 之后可用 MEMBER OF / JSON_CONTAINS 命中索引
SELECT * FROM events WHERE 'go' MEMBER OF (payload -> '$.tags');

---

四、Go 语言处理 JSON 列

Go 没有内建的 JSON 列类型，但也不需要。database/sql 留了两个口子：写的时候走 driver.Valuer，读的时候走 sql.Scanner。任何类型只要实现这两个接口，就能当 JSON 列用。下面从最省事的写法开始，逐步过渡到类型安全的方案。

4.1 最简单：字符串进出

JSON 列本质是文本，可以直接用 []byte / string 读写：

var raw []byte
err := db.QueryRow("SELECT payload FROM events WHERE id = $1", 1).Scan(&raw)
// raw 里就是 {"user":"alice",...}，再自行 json.Unmarshal

写入同理，把 json.Marshal 的结果作为参数传进去即可。简单，但不够类型安全。

小技巧：用 json.RawMessage（本质就是 []byte）替代裸 []byte 更语义化，还能延迟解析——先原样 Scan 出来，需要时再 json.Unmarshal 到目标 struct：

var raw json.RawMessage
db.QueryRow("SELECT payload FROM events WHERE id = ?", 1).Scan(&raw)
var p Payload
_ = json.Unmarshal(raw, &p)

4.2 推荐：自定义类型 + Scanner/Valuer

把某个结构体或 map 直接映射成 JSON 列，最通用的写法是定义一个泛型包装器。下面的 jsontype 是我们自己写的包（不是第三方库），你把它放进项目里任意一个目录即可；若不想自己维护，可直接跳到 4.6 用 GORM 的 datatypes.JSONType[T]。

package jsontype

import (
	"database/sql/driver"
	"encoding/json"
	"fmt"
)

// JSON[T] 让任意类型 T 可直接作为 JSON/JSONB 列读写。
type JSON[T any] struct {
	Val T
}

func New[T any](v T) JSON[T] { return JSON[T]{Val: v} }

// Value 实现 driver.Valuer：写入数据库时调用。
func (j JSON[T]) Value() (driver.Value, error) {
	b, err := json.Marshal(j.Val)
	if err != nil {
		return nil, err
	}
	return b, nil // 返回 []byte，pq / mysql 驱动都接受
}

// Scan 实现 sql.Scanner：从数据库读取时调用。
func (j *JSON[T]) Scan(src any) error {
	if src == nil {
		var zero T
		j.Val = zero
		return nil
	}
	var b []byte
	switch v := src.(type) {
	case []byte:
		b = v
	case string:
		b = []byte(v)
	default:
		return fmt.Errorf("jsontype: 不支持的类型 %T", src)
	}
	return json.Unmarshal(b, &j.Val)
}

使用：

type Payload struct {
	User  string   `json:"user"`
	Tags  []string `json:"tags"`
	Score int      `json:"score"`
}

// 写入
p := jsontype.New(Payload{User: "alice", Tags: []string{"go", "db"}, Score: 42})
_, err := db.Exec("INSERT INTO events (payload) VALUES ($1)", p)

// 读取
var got jsontype.JSON[Payload]
err = db.QueryRow("SELECT payload FROM events WHERE id = $1", 1).Scan(&got)
fmt.Println(got.Val.User) // alice

说明：Value() 返回 []byte。lib/pq、jackc/pgx 和 go-sql-driver/mysql 都会把 []byte 正确地作为 JSON/JSONB 参数处理。若用 pgx 且想让服务端按 jsonb 类型校验，可显式转换：... VALUES ($1::jsonb)。

一个关键坑：Scan 里千万别只断言 []byte。lib/pq 把 jsonb 作为 []byte 传入，而 pgx 传入的是 string；MySQL 驱动同样可能给 string。上面的 switch 同时处理了这两种情况——很多只抄了 []byte 分支的代码一换驱动就 panic，原因就在这里。

4.3 处理 NULL：用指针或 sql.Null 包装

列可能为 NULL 时，让包装类型支持空值：

type NullJSON[T any] struct {
	Val   T
	Valid bool // 为 false 表示数据库中是 NULL
}

func (j NullJSON[T]) Value() (driver.Value, error) {
	if !j.Valid {
		return nil, nil
	}
	return json.Marshal(j.Val)
}

func (j *NullJSON[T]) Scan(src any) error {
	if src == nil {
		j.Valid = false
		return nil
	}
	j.Valid = true
	b, ok := src.([]byte)
	if !ok {
		b = []byte(src.(string))
	}
	return json.Unmarshal(b, &j.Val)
}

4.4 结构未知时：映射到 map

如果 JSON 的键是用户自定义的、编译期无法确定结构（典型的"属性袋 / attributes bag"场景），可以把包装器的类型参数直接设成 map[string]any：

// 复用 4.2 的泛型包装器，T = map[string]any
var attrs jsontype.JSON[map[string]any]
err = db.QueryRow("SELECT payload FROM events WHERE id = $1", 1).Scan(&attrs)

weight, ok := attrs.Val["weight"].(float64) // 代价：取值要逐个类型断言

好处是灵活、无需预定义 struct；代价是每次取值都要做类型断言，且 JSON 里的数字统一被解成 float64。结构固定就用 struct，结构自由才用 map。

在 Go 1.18 泛型之前，更常见的是定义一个具名 map 类型并直接在其上挂 Value/Scan（coussej 称之为 PropertyMap），一处定义即可被 orders、customers、books 等各种实体复用：

type PropertyMap map[string]any

func (p PropertyMap) Value() (driver.Value, error) { return json.Marshal(p) }
func (p *PropertyMap) Scan(src any) error {
b, ok := src.([]byte)
if !ok {
return errors.New("type assertion to []byte failed")
}
return json.Unmarshal(b, p)
}

泛型 JSON[T] 本质是它的通用化版本——一份代码同时覆盖 struct 与 map，无需为每种形态各写一个类型。

4.5 部分查询：在 SQL 侧抽取字段

有时不想把整个 JSON 拉回 Go 再解析，直接在数据库里取标量字段效率更高：

// PostgreSQL
var user string
db.QueryRow(`SELECT payload ->> 'user' FROM events WHERE id = $1`, 1).Scan(&user)

// MySQL
db.QueryRow(`SELECT payload ->> '$.user' FROM events WHERE id = ?`, 1).Scan(&user)

4.6 各驱动 / ORM 的原生支持

工具	用法要点
lib/pq	直接把 []byte 作为参数写入 jsonb；读回也是 []byte。
jackc/pgx	一等公民支持，可直接 Scan 进 map[string]any 或结构体；pgtype.JSONB 可用。
go-sql-driver/mysql	JSON 列以 []byte 返回，写入接受 []byte/string。
GORM	用 gorm.io/datatypes 的 datatypes.JSON 或 datatypes.JSONType[T]（泛型），自动处理两库差异。
sqlx	结合上面的 JSON[T] 包装器，或用 types.JSONText。
GORM 泛型示例（同一份代码兼容 PG 与 MySQL）：

import "gorm.io/datatypes"

type Event struct {
	ID      uint
	Payload datatypes.JSONType[Payload] // 建表时自动映射为 jsonb / json
}

// 写
db.Create(&Event{Payload: datatypes.NewJSONType(Payload{User: "alice"})})

// 读
var e Event
db.First(&e, 1)
fmt.Println(e.Payload.Data().User)

// 按 JSON 字段查询（跨库表达式）
db.Where(datatypes.JSONQuery("payload").Equals("alice", "user")).Find(&events)

---

五、为什么要用 JSON 类型，而不是拆成多个字段

一句话：字段适合"结构稳定、要被频繁查询和约束"的核心数据；JSON 适合"结构多变、整体存取的边缘数据"。 拆字段不是不行，而是很多场景下拆了更难受。

5.1 什么场景下 JSON 更合理

• 结构因行而异：事件表里 click 事件的 payload 是 {x, y, target}，purchase 事件是 {sku, qty, price}。拆字段意味着要么建一堆稀疏列，要么每来一种新事件就 ALTER TABLE。JSON 天然容纳异构结构。
• 免 DDL 演进：上游加一个属性，数据库什么都不用改。大表 ALTER TABLE 在锁表/重建上有真实成本，老数据回填新列又是一堆问题。
• 避免 EAV 反模式：JSON 流行前的标准解法是属性表 (entity_id, key, value)：查一个对象要几十行自连接/pivot，索引分散、缓存局部性差。jsonb + GIN 一条 @> 查询解决，性能和可维护性都更好。
• 你根本不拥有 schema：第三方 API 回包、webhook、设备上报、审计快照——上游说变就变。拆字段意味着每次上游变动都要改解析代码和表结构；JSON 就是"原样存下、需要时用路径查询取"，把 schema 决策权留在应用侧。
• 稀疏属性：100 个可选属性、每行只填三五个，JSON 只存出现的键；拆列就是一大片 NULL，列定义也膨胀。
• 行数放大：一个 payload 一行，拆进属性表就是几十行，对缓存、备份、复制的开销都不同。

5.2 反过来，什么时候必须拆字段

JSON 的代价在于它是优化器和约束系统的"黑盒"：

需求	为什么 JSON 不行
高频 WHERE / JOIN / ORDER BY / 聚合	内部无统计信息，优化器估行数不准；MySQL 尤其明显
NOT NULL / CHECK / UNIQUE / 外键	JSON 内部字段没有这些约束（CHECK + JSON Schema 能做但很别扭）
单字段频繁 UPDATE	整个 jsonb 文档重写，WAL 放大、行膨胀（MySQL 的部分更新只覆盖部分场景）
存储紧凑	jsonb 每行都重复存键名；列名只存一次
强类型	JSON 内部数字/字符串类型弱，(payload->>'score')::numeric 这种转换就是补课

5.3 实践折中：热字段提拔、冷数据留在 JSON

不是二选一。常见做法是把被频繁查询的字段"提升"出来，其余留在 JSON 里：

-- PG：生成列，把热字段暴露成普通列（可建 B-Tree、有统计信息）
ALTER TABLE events
  ADD COLUMN user_name text
  GENERATED ALWAYS AS (payload ->> 'user') STORED;

-- 或者只在查询侧建表达式索引，不动表结构
CREATE INDEX idx_user ON events ((payload ->> 'user'));

还有一个实用的演进视角：JSON 是起步成本最低的方案。前期不知道哪些字段重要，先整个塞 JSONB；等某个键真的被频繁查询、或需要约束时，再把它"提拔"成生成列/独立列。这个迁移是平滑的，反过来（把拆好的字段合并回 JSON）要痛苦得多。

所以答案不是"JSON 更好"，而是：把 schema 决策从建表那一刻推迟到真正需要约束它的那一刻。核心业务实体（订单、用户）老老实实拆字段；外围的、多变的、只是存着备查的，用 JSON。

---

六、实战建议

1. PG 默认选 jsonb，配 GIN 索引；只有审计留痕才用 json。
2. 高频过滤字段应"提升"为独立列：PG 用表达式索引，MySQL 用生成列 / 多值索引，避免全表扫。
3. Go 侧统一用 JSON[T] 泛型包装器，一处定义处处复用，兼顾类型安全与 NULL 处理。
4. 能在 SQL 里过滤就别拉回内存：@>（PG）和 MEMBER OF（MySQL 8）都能命中索引，远快于把整表捞回 Go 再筛。
5. 跨库项目优先 GORM datatypes.JSONType[T]，屏蔽两库语法差异。
6. 注意大字段的写放大：MySQL 8 的 JSON_SET 支持部分更新（in-place），但字段变长时仍会整体重写；PG 的 jsonb 更新总是整值重写，超大文档要谨慎。

---

七、参考文档

• Using PostgreSQL JSONB with Go — Alex Edwards：Go 中自定义类型实现 driver.Valuer / sql.Scanner 对接 jsonb 的经典范文，本文 4.2/4.4 及若干踩坑点参考自此。
• Handling JSONB in Go Structs — coussej：具名 PropertyMap（map[string]interface{}）可复用类型的出处，本文 4.4 节的具名 map 写法即源于此，并提到用 sqlx 进一步简化。
• JSONB in gorm — gist / yanmhlv：在 GORM 中挂接自定义 Jsonb 类型的示例。注意其年代较早，新版 GORM 应改用 gorm:"type:jsonb" 标签（旧写法 sql:"type:jsonb" 会让值被写成 null），或直接用 pgtype.JSONB。
• JSONB for postgres in Golang — gist / mraaroncruz：一个精简的自定义 Jsonb 类型实现 Value/Scan 的可复制片段。
• Go: Handling JSON in MySQL — Tiago Melo / HackerNoon：MySQL 侧的对应实践（前几篇多偏 PostgreSQL），用 StringInterfaceMap 自定义类型 + go-sql-driver/mysql，Value 对空 map 写入 null、Scan 把 null 还原为空 map，与本文 4.3 的 NULL 处理思路一致。
• Using MySQL JSON data type with Go — GeekGunda：MySQL 8.0 + Go 的实战记录，展示用 json.RawMessage 直接 Scan JSON 列、再 json.Unmarshal 到目标 struct 的轻量写法（对应本文 4.1 思路），附 docker-compose 与完整示例项目。
• PostgreSQL 官方文档：JSON Types 与 JSON Functions and Operators
• MySQL 官方文档：The JSON Data Type 与 JSON Functions
• Working with JSON in MySQL — DigitalOcean：MySQL JSON 列的入门教程，覆盖建表、JSON_EXTRACT/->/->>、增删改函数与生成列索引，是本文第三节 SQL 用法的很好补充读物。
• MySQL 多值索引（Multi-Valued Indexes）
• GORM datatypes 文档 与 gorm.io/datatypes
• jackc/pgx：PostgreSQL 驱动，JSON/JSONB 一等公民支持

---

适用版本：PostgreSQL 12+、MySQL 8.0+、Go 1.18+（泛型）。