SQL中能否设置列支持JSONB或TEXT二选一类型？附字典存储疑问

云上老周

2026-5-6

关于SQL列支持JSONB/TEXT双类型及字典存储的问题

一、能不能定义同时接受JSONB或TEXT的列？

直接写 column1 JSONB OR TEXT 这种语法在SQL（包括你提到的支持JSONB的PostgreSQL）里是不支持的——SQL要求列类型必须是单一明确的类型。不过有几种变通方案可以实现类似的效果：

1. 使用TEXT类型+约束校验

把列定义为TEXT，通过CHECK约束确保内容要么是合法的JSON对象（字典），要么是普通字符串。先写一个辅助函数判断文本是否能转成合法JSONB：

CREATE OR REPLACE FUNCTION is_valid_jsonb(input_text TEXT) 
RETURNS BOOLEAN AS $$
BEGIN
    PERFORM input_text::JSONB;
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

然后建表时添加约束：

CREATE TABLE your_table (
    column1 TEXT,
    CONSTRAINT check_column1_valid 
    CHECK (is_valid_jsonb(column1) OR NOT is_valid_jsonb(column1))
);

如果需要严格只接受JSON对象（不是JSON数组/字符串）或普通文本，可以修改函数判断JSON类型：

CREATE OR REPLACE FUNCTION is_json_object(input_text TEXT) 
RETURNS BOOLEAN AS $$
BEGIN
    RETURN jsonb_typeof(input_text::JSONB) = 'object';
EXCEPTION
    WHEN OTHERS THEN
        RETURN FALSE;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 更新约束：
CONSTRAINT check_column1_valid 
CHECK (is_json_object(column1) OR NOT is_valid_jsonb(column1))

2. 使用JSONB类型存储所有内容

JSONB本身支持多种JSON类型，包括对象（字典）和字符串。你可以把普通字符串包装成JSON字符串（比如把hello存为'"hello"'），这样整个列都是JSONB类型，应用层读取时再判断是对象还是字符串：

插入字典：INSERT INTO your_table (column1) VALUES ('{"key": "value"}'::JSONB);
插入普通文本：INSERT INTO your_table (column1) VALUES ('"some plain text"'::JSONB);

这种方式的好处是类型统一，还能利用PostgreSQL对JSONB的索引支持。

3. 使用复合列（不推荐）

定义两个列，一个JSONB类型，一个TEXT类型，加约束确保只有一个非空：

CREATE TABLE your_table (
    column1_json JSONB,
    column1_text TEXT,
    CONSTRAINT check_only_one_non_null 
    CHECK ((column1_json IS NOT NULL AND column1_text IS NULL) OR 
           (column1_json IS NULL AND column1_text IS NOT NULL))
);

但这种方式会增加查询和维护的复杂度，一般不推荐。

二、为什么存储字典（JSON）被视为不良实践？

这个说法是有前提的，不是绝对的“不良”，但在传统关系型数据库设计中，不推荐过度使用JSON存储的原因主要有这些：

缺乏数据一致性约束：JSON内部的字段没有固定类型和非空约束，很容易出现数据格式混乱（比如同一个字段有的是数字，有的是字符串）。
查询效率较低：虽然PostgreSQL支持JSONB的索引，但查询JSON内部字段的性能还是不如普通关系型列，复杂查询会更慢。
不符合数据库范式：把结构化数据塞进JSON里，容易造成数据冗余，也难以利用关系型数据库的联表、事务等特性。
维护成本高：如果JSON结构需要修改（比如新增/删除字段），你需要手动更新所有相关的JSON数据，而普通列可以用ALTER TABLE轻松修改，还能自动处理默认值。

不过，JSONB也有适合的场景：比如存储半结构化数据（比如用户自定义配置、日志内容）、快速迭代的临时数据，或者当数据结构无法提前确定时，用JSONB会比强行设计固定表结构更灵活。

内容的提问来源于stack exchange，提问作者dtb