MariaDB中如何为CAST设置自定义/别名数据类型以自动解析JSON

我正在使用 NodeJS / mysql2 / MariaDB 15.1 Distrib 10.6.7，据我了解该版本并未真正支持JSON类型，JSON实际是longBlob的别名，仅在插入或更新时会做JSON_VALID校验。

我不想在所有查询结果里手动解析JSON对象，于是尝试在mysql连接中注入typeCast函数：

typeCast: function (field, next) {
  if ( field.length === "LONG_BLOB" ) {
    try {
       return JSON.parse( field.string() )
    } catch (err){
       return field.string();
    }
  } else {
    return next();
  }
}

但这个方法只在部分场景有效，比如执行SELECT JSON_OBJECT('a', 1)这类语句时，返回的是短blob，对应的字段信息如下：

{
   type: 'BLOB',
   length: 12582912,
   db: '',
   table: '',
   name: 'sctCap',
   string: [Function: string],
   buffer: [Function: buffer],
   geometry: [Function: geometry]
}

请问有没有办法强制把数据类型转为LONG_BLOB，或者通过CAST将字段转为自定义数据类型来解决这个问题？

方案1：扩展typeCast的判断逻辑 #

既然JSON_OBJECT返回的是BLOB类型，你可以把BLOB也加入typeCast的判断范围，同时结合字段内容的JSON校验，不单纯依赖字段类型：

typeCast: function (field, next) {
  // 同时匹配LONG_BLOB和BLOB类型
  if (field.type === 'BLOB' || field.length === 'LONG_BLOB') {
    const value = field.string();
    try {
      return JSON.parse(value);
    } catch (err) {
      // 解析失败返回原始字符串
      return value;
    }
  }
  return next();
}

这样不管是表中定义的JSON（longBlob）还是动态生成的JSON对象（BLOB），都会自动尝试解析。

方案2：查询时显式CAST为JSON类型 #

在SQL查询里，把生成的JSON对象CAST为JSON类型（MariaDB中JSON是longBlob别名，CAST后字段类型会被识别为LONG_BLOB）：

SELECT CAST(JSON_OBJECT('a', 1) AS JSON) AS sctCap;

这样原有typeCast函数就能正常识别并解析该字段。

方案3：自定义字段类型识别 #

如果需要更精准的匹配，可以通过字段名或表名辅助判断，比如只针对特定命名规则的字段做JSON解析：

typeCast: function (field, next) {
  // 匹配字段名含json、_json后缀，或类型为BLOB/LONG_BLOB的情况
  const isJsonField = /(json|_json)$/i.test(field.name) || 
                      field.type === 'BLOB' || 
                      field.length === 'LONG_BLOB';
  if (isJsonField) {
    const value = field.string();
    try {
      return JSON.parse(value);
    } catch (err) {
      return value;
    }
  }
  return next();
}

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