如何编写函数通过传入customer id查询获取对应merchant id

首先先做前置确认，避免写完跑不通：

• 先核对CUSTOMER BASIC INFO表里customer id、merchant id两个字段的实际数据类型，函数入参、返回值的类型必须和字段类型完全匹配，不然会出现隐式转换、索引失效甚至报错
• 先确认业务规则：是否存在一个customer_id绑定多个merchant_id的情况，如果存在，要提前定好返回规则（取最新绑定的、取第一个匹配的还是返回全量列表），避免返回不符合预期的结果
• 因为你的表名、字段名都带空格，写SQL时标识符必须加对应转义符，不然会直接报语法错误：MySQL/ClickHouse用反引号，PostgreSQL/Oracle用双引号，SQL Server/Access用方括号

通用实现逻辑 #

所有数据库的实现逻辑都是统一的，核心步骤如下：

1. 声明函数，定义和customer id类型一致的入参，注意入参名不要和表字段重名，避免查询时出现字段歧义
2. 声明和merchant id类型一致的返回值变量
3. 编写查询语句：从CUSTOMER BASIC INFO表中筛选customer id等于入参的记录，把匹配到的merchant id赋值给返回值变量，单值返回场景加行数限制，避免多结果报错
4. 处理边界场景：如果没有匹配到对应customer的记录，定义好返回规则（默认返回NULL、返回固定提示值还是抛错都可以，根据业务需求选择）
5. 给调用函数的账号开放对应表的查询权限，避免权限不足报错

主流数据库可直接复用的代码示例 #

MySQL 版本 #

-- 如果之前有同名函数先删掉，避免创建冲突
DROP FUNCTION IF EXISTS get_merchant_by_cid;
DELIMITER //
CREATE FUNCTION get_merchant_by_cid(in_cid VARCHAR(32))
RETURNS VARCHAR(32) -- 这里把类型替换成你表中merchant id的实际类型
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE out_mid VARCHAR(32);
    SELECT `merchant id` INTO out_mid
    FROM `CUSTOMER BASIC INFO`
    WHERE `customer id` = in_cid
    LIMIT 1; -- 单值返回必须加，多匹配场景下不会报错
    -- 查不到默认返回NULL，需要自定义返回值的话在这里加判断赋值即可
    RETURN out_mid;
END //
DELIMITER ;

-- 调用示例
SELECT get_merchant_by_cid('要查询的customer_id值');

PostgreSQL 版本 #

CREATE OR REPLACE FUNCTION get_merchant_by_cid(in_cid VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
    out_mid VARCHAR;
BEGIN
    SELECT "merchant id" INTO out_mid
    FROM "CUSTOMER BASIC INFO"
    WHERE "customer id" = in_cid
    LIMIT 1;
    RETURN out_mid;
END;
$$ LANGUAGE plpgsql STABLE;

-- 调用示例
SELECT get_merchant_by_cid('要查询的customer_id值');

SQL Server 版本 #

CREATE OR ALTER FUNCTION get_merchant_by_cid(@in_cid VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
    DECLARE @out_mid VARCHAR(32);
    SELECT TOP 1 @out_mid = [merchant id]
    FROM [CUSTOMER BASIC INFO]
    WHERE [customer id] = @in_cid;
    RETURN @out_mid;
END;
GO

-- 调用示例
SELECT dbo.get_merchant_by_cid('要查询的customer_id值');

踩坑提醒 #

• 绝对不要把入参名设成和表中customer id同名，不然WHERE条件会变成恒真判断，直接触发全表扫描，返回错误结果还会拖垮数据库性能
• 如果业务上确实存在一个customer对应多个merchant的场景，不要用上面的单值返回函数，改成表值函数返回所有匹配的merchant集合，避免漏数
• 表数据量超过10万行的话，一定要给CUSTOMER BASIC INFO表的customer id字段建索引，不然每次调用函数都会全表扫描，性能会很差

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