关于sql:支持多数据源联合查询的SQL运行引擎sycnanySQL类型注解和类型转换

60次阅读

共计 3097 个字符，预计需要花费 8 分钟才能阅读完成。

应用介绍
装置和配置
应用自定义函数

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` 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 条件编写时可能须要解决类型统一能力从关联表中正确查问出数据，而不同数据源可能有不同反对，如 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`;

正文完