如何判断SQL列的数据类型？属性表多类型值存储优化咨询

不同数据库系统有对应的系统表/视图可以查询列的数据类型，以下是几种常见数据库的实现方式：

• MySQL：通过information_schema.columns视图查询，指定目标库和表名即可：

  SELECT column_name, data_type 
  FROM information_schema.columns 
  WHERE table_schema = '你的数据库名' 
    AND table_name = '你的表名';
  

• PostgreSQL：可使用information_schema.columns，或者结合底层系统表pg_attribute与pg_type：

  -- 通用方式
  SELECT column_name, data_type 
  FROM information_schema.columns 
  WHERE table_schema = 'public' 
    AND table_name = '你的表名';
  
  -- 系统表关联方式
  SELECT a.attname AS column_name, t.typname AS data_type
  FROM pg_attribute a
  JOIN pg_type t ON a.atttypid = t.oid
  WHERE a.attrelid = '你的表名'::regclass
    AND a.attnum > 0
    AND NOT a.attisdropped;
  

• SQL Server：关联sys.columns和sys.types系统表查询：

  SELECT c.name AS column_name, t.name AS data_type
  FROM sys.columns c
  JOIN sys.types t ON c.system_type_id = t.system_type_id
  WHERE c.object_id = OBJECT_ID('你的表名');
  

你当前用text列统一存储再在客户端转换的方式虽灵活，但存在类型校验缺失、查询效率低、客户端逻辑复杂的问题，以下是几个更优方案：

1. 拆分多列存储对应类型 #

新增多个类型专属列，比如int_value、date_value、bool_value、text_value，每个属性仅存入对应类型的列，其余列留空。例如身高存入int_value，眼睛颜色存入text_value，是否成年存入bool_value。

优势：

• 数据库层面约束数据类型，避免非法值
• 查询无需类型转换，性能更优
• 逻辑清晰，后续维护简单

示例表结构：

CREATE TABLE attribute (
  id INT PRIMARY KEY AUTO_INCREMENT,
  person_id INT REFERENCES person(id),
  name VARCHAR(50) NOT NULL,
  int_value INT,
  date_value DATE,
  bool_value BOOLEAN,
  text_value TEXT
);

2. 保留text列，新增类型标记列 #

给attribute表添加data_type列（建议用枚举类型，比如ENUM('int', 'date', 'bool', 'text')），标记当前value列存储的数据类型。查询时可在SQL层面直接完成类型转换，减轻客户端处理压力。

示例查询（以PostgreSQL为例）：

SELECT 
  name,
  CASE data_type
    WHEN 'int' THEN value::INTEGER
    WHEN 'date' THEN value::DATE
    WHEN 'bool' THEN value::BOOLEAN
    ELSE value
  END AS typed_value
FROM attribute
WHERE person_id = 1;

这种方案兼顾灵活性与类型可控性，适合不想大幅改动表结构的场景。

3. 使用JSON/JSONB类型存储（适合支持的数据库） #

如果你的数据库支持JSON/JSONB（如PostgreSQL、MySQL 5.7+），可将属性整体存为JSON对象，保留原生数据类型。既可以把每个person的属性存在一个JSON列中，也可以让attribute表的value列使用JSON类型。

示例（PostgreSQL的JSONB）：

-- 表结构
CREATE TABLE attribute (
  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  person_id INT REFERENCES person(id),
  attrs JSONB NOT NULL
);

-- 插入数据
INSERT INTO attribute (person_id, attrs)
VALUES (1, '{"height": 180, "eye_color": "blue", "is_adult": true}'::JSONB);

-- 查询并获取对应类型的值
SELECT 
  (attrs->>'height')::INTEGER AS height,
  attrs->>'eye_color' AS eye_color,
  (attrs->>'is_adult')::BOOLEAN AS is_adult
FROM attribute
WHERE person_id = 1;

JSON/JSONB的优势是灵活性极强，可轻松扩展新属性类型，同时保留数据原生类型，查询时可直接转换使用。

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