支持多数据源联合查询的SQL运行引擎sycnany-SQL使用类型注解和类型转换

使用介绍

安装和配置

使用自定义函数

sycnany-SQL作为SQL运行引擎并不需要提前定义Schema信息，而且很多数据源本身就是无Schema信息的，例如NoSQL数据库MongoDB，所以从数据源查询数据和运行计算默认直接使用输入数据的类型完成查询和计算，此时查询数据或执行计算可能因数据类型不匹配产生查询失败或计算出现异常，因此我们可以在编写SQL时需要时可以手动添加数据类型注解或使用数据类型转换函数完成数据类型转换，以保证得到正确的查询或计算结果。

例如：

MongoDB的ObjectId保存在MySQL中一般转换为字符串使用varchar类型存储，后续在MongoDB和MySQL进行Join关联查询时，就需把MySQL存储的varchar类型转换为MongoDB的ObjectId才能正确的从MongoDB中加载出关联数据。

sycnany-SQL支持两种方式完成数据类型转换：

• 使用类型注解
• 使用类型转换函数

如无特殊说明，后续示例中我们假设有如下数据结构：

# MySQL中mysql_test库users表结构如下
CREATE TABLE `mysql_test`.`users` (
  `user_id` bigint(20) NOT NULL,
  `name` varchar(64),
  `birthday` varchar(20),
  `vip_id` varchar(24),
  PRIMARY KEY (`user_id`) USING BTREE
);

# MongoDB中mongo_test库vips集合有如下数据
[{
    "_id": ObjectId("640a9786bb450457c544f759"),
    "vip_name" : "超级VIP",
    "create_time" : ISODate("2023-03-10T02:35:50.298Z")
}]

类型注解使用

在查询字段上添加该字段的类型注解信息，从数据源加载数据后会自动转换为该类型再参与后续查询或计算。

使用语法：`字段名[类型]`

例如：

# 编写Join查询SQL
SELECT 
    a.`user_id`,
    a.`name`,
    a.`birthday[date]`,
    b.`_id[text]` AS vip_id,
    b.`vip_name`
FROM
    `mysql_test`.`users` a
        LEFT JOIN
    `mongo_test`.`vips` b ON a.`vip_id[objectid]` = b.`_id`;

在该SQL中我们为users表的birthday字段添加了date类型注解，表示该字段需由MySQL表中保存的字符串日期类型转换为date数据类型。

而MySQL表中保存vip_id字段为MongoDB中vips集合的主键_id字段的值，在users表和vips集合join查询时，为users表的vip_id字段添加了objectid类型注解，在从MongoDB查询关联数据前转换为ObjectId，从而正常查询读取出关联数据。

从vips集合加载数据成功后，我们又使用类型注解把注解_id字段从ObjectId转换为了字符串类型，以便能输出查询结果。

注意：类型注解只能添加在查询字段中并且字段一定要用``包裹，不能用于as后alias名称和函数返回值。

支持的注解类型：

• 整型数字：int、tinyint、smallint、mediumint、bigint
• 浮点型数字：float、double
• 字符串：str、tinytext、mediumtext、text、char、varchar、nchar
• 字节数组：bytes、binary、varbinary、blob
• 布尔型：bool、boolean
• Decimal：decimal
• BSON ObjectId： objectid
• UUID：uuid
• 日期时间：datetime、timestamp
• 日期：date
• 时间：time
• 数组：array
• 集合：set
• HashMap： map

类型转换函数使用

类型注解只能添加在查询字段上，但类型转换函数可在查询字段或函数返回值中使用，使用更方便，同时针对内置函数不能满足类型转换的需求，也可以自行编写加载自定义函数来完成类型转换，以便正确的完成查询或计算。

例如：

# 编写Join查询SQL
SELECT 
    a.`user_id`,
    a.`name`,
    CONVERT_DATE(a.`birthday`) AS birthday,
    CONVERT_STRING(b.`_id`) AS vip_id,
    b.`vip_name`
FROM
    `mysql_test`.`users` a
        LEFT JOIN
    `mongo_test`.`vips` b ON CONVERT_OBJECTID(a.`vip_id`) = b.`_id`;

使用类型转换函数也可完成相同操作。

内置支持的类型转换函数：

• 转为整型数字：convert_int(expr)
• 转为浮点型数字：convert_float(expr)
• 转为字符串：convert_string(expr)
• 转为字节数组：convert_bytes(expr)
• 转为布尔型：convert_bool(expr)
• 转为Decimal：convert_decimal(expr)
• 转为BSON ObjectId：convert_objectid(expr)
• 转为UUID：convert_uuid(expr)
• 转为日期时间：convert_datetime(expr)
• 转为日期：convert_date(expr)
• 转为时间：convert_time(expr)
• 转为数组：convert_array(expr)
• 转为集合：convert_set(expr)
• 转为HashMap：convert_map(expr)

针对Join查询On关联条件的特别优化提示

在进行Join关联查询时，On条件编写时可能需要处理类型一致才能从关联表中正确查询出数据，而不同数据源可能有不同支持，如MySQL等SQL类型数据库会自定进行类型转换，MongoDB则必须手动转换保存查询条件类型一致，所以类型转换的字段不应该在需读取关联数据的表上。

例如，对于上面的示例SQL，加入我们改写为以下SQL：

# 编写Join查询SQL
SELECT 
    a.`user_id`,
    a.`name`,
    CONVERT_DATE(a.`birthday`) AS birthday,
    CONVERT_STRING(b.`_id`) AS vip_id,
    b.`vip_name`
FROM
    `mysql_test`.`users` a
        LEFT JOIN
    `mongo_test`.`vips` b ON a.`vip_id` = CONVERT_STRING(b.`_id`);

虽然以上SQL也能正常执行，但因为Join条件中关联表vips的_id字段存在类型手动类型转换，所以需要加载整个表的数据后在内存中完成计算匹配，不能直接使用MongoDB查询语句直接从数据库中直接读取出需要数据，不能使用数据库的索引优化、效率很低且可能因消耗过多资源而出现异常，所以我们应该把类型转换放在users表的vip_id字段上，即：

# 编写Join查询SQL
SELECT 
    a.`user_id`,
    a.`name`,
    CONVERT_DATE(a.`birthday`) AS birthday,
    CONVERT_STRING(b.`_id`) AS vip_id,
    b.`vip_name`
FROM
    `mysql_test`.`users` a
        LEFT JOIN
    `mongo_test`.`vips` b ON CONVERT_OBJECTID(a.`vip_id`) = b.`_id`;

本文由博客一文多发平台 OpenWrite 发布！