JSON 转 SQL 类型推断：MySQL 与 PostgreSQL 的边界

一、问题概述：样本值不是完整 schema

12 可能是数量，也可能是超过安全范围的标识符；2026-07-29 可能是日期，也可能只是订单编号。自动推断最多生成候选 schema，不能替业务决定精度、索引和约束。尤其是只看到 null 时，无法知道非空值将来是什么类型。

二、最小复现：同一个 JSON 在两种数据库的候选类型不同

对象和数组可以保留为原生 JSON 类型，而标量类型需要结合方言和业务契约。下面只展示候选，不代表可以直接用于生产建表。

const sample = {
  count: 12,
  label: "2026-07-29",
  enabled: true,
  meta: { source: "import" },
  values: [1, 2],
  unknown: null,
};

// MySQL candidates: DOUBLE, TEXT, BOOLEAN/TINYINT(1), JSON, JSON, nullable unknown
// PostgreSQL candidates: double precision, text, boolean, jsonb, jsonb, nullable unknown

三、根因：MySQL 与 PostgreSQL 的类型语义不相同

MySQL 提供 JSON 类型，常见布尔约定会落到 TINYINT(1)；PostgreSQL 区分 json 与 jsonb，并有原生 boolean。两者的数值类型、索引方式和运算也不同。字符串是否是日期、金额或 ID，不能仅凭正则或长度可靠决定。

四、推荐方案：先输出候选，再由契约确认

推断器应返回方言、候选 SQL 类型、是否可空以及不确定原因。对数字优先提示范围和精度确认；对字符串保守使用文本；对数组和对象保留 JSON。最终 DDL 应来自明确 schema、迁移评审和真实约束，而不是单个样本。

五、完整代码：按方言生成保守建议

以下 TypeScript 只做候选推断。null 返回 unknown 并标记 nullable；它不会把日期字符串变成 DATE，也不会把数字样本直接声明为整数。

type Dialect = "mysql" | "postgresql";
type Candidate = { sqlType: string; nullable: boolean; reason: string };
function inferCandidate(value: unknown, dialect: Dialect): Candidate {
  if (value === null) return { sqlType: "unknown", nullable: true, reason: "null carries no non-null type" };
  if (typeof value === "boolean") return { sqlType: dialect === "mysql" ? "TINYINT(1) convention" : "boolean", nullable: false, reason: "boolean observed" };
  if (typeof value === "number") return { sqlType: dialect === "mysql" ? "DOUBLE (confirm precision)" : "double precision (confirm precision)", nullable: false, reason: "range and scale are unknown" };
  if (typeof value === "string") return { sqlType: "TEXT", nullable: false, reason: "string meaning is not inferable" };
  return { sqlType: dialect === "mysql" ? "JSON" : "jsonb", nullable: false, reason: Array.isArray(value) ? "array preserved as JSON" : "object preserved as JSON" };
}
console.log(inferCandidate(12, "mysql"));
console.log(inferCandidate({ source: "import" }, "postgresql"));

六、常见错误方案

看到数字就生成 INT 会溢出大 ID，也会丢失小数；看到日期格式就生成 DATE 会把普通字符串误转。把所有字段生成 TEXT 虽然少报错，却丢失约束和查询语义。把 MySQL 的 TINYINT(1) 直接当成所有数据库的 boolean，也会让跨方言迁移产生错误。

七、边界条件：NULL、数组、精度与时间

全是 null 的字段需要人工补充类型；混合 null 与数值时应把 null 作为可空性，而非独立类型。数组元素可能混合类型，不能只看第一项。金额要明确小数位和舍入规则，大整数 ID 要确认是否应为字符串。日期必须由契约和时区决定，不由字符串外观决定。

八、如何验证生成的 DDL

为每个字段准备多个样本，包括 null、负数、小数、超大整数、空字符串、Unicode、对象和空数组。分别生成 MySQL 与 PostgreSQL 候选，人工确认类型后在临时数据库执行 DDL，再插入边界数据并检查读取类型和精度。

九、FAQ

问：为什么不直接把数字分成 INT 和 FLOAT？答：样本不提供完整范围、精度和业务单位，自动选择可能造成溢出或舍入。

问：JSON 对象该拆成列还是 JSON 列？答：取决于查询、更新和约束需求；推断器只能给候选。

问：null 能推断出类型吗？答：不能；它只能说明当前值为空以及字段可能可空。

十、总结

JSON 到 SQL 的类型推断应是候选生成，不是 schema 真相。MySQL 与 PostgreSQL 必须分别处理 JSON、布尔和数值；字符串日期、金额和 ID 需要业务契约。保守输出候选、标记不确定性，再用边界数据和临时数据库验证 DDL。