地址验证系统逻辑优化：实现部分匹配与SQL查询优化咨询

问题 #

我正在搭建一个通过SQL查询预定义数据库的地址验证系统，需要实现三级逻辑：

• 查找精确地址匹配，返回包含地址的成功JSON；
• 若无精确匹配，则查找近似匹配（基于Address1中的街道名称部分匹配），若存在一个或多个近似匹配，返回包含地址数组的成功JSON；
• 若无近似匹配，返回失败JSON。

当前代码可处理精确匹配和无匹配场景，但不知道如何添加else if语句实现部分匹配逻辑，现有代码如下：

app.get("/addresses/api/find/", async (req, res) => {
    try {
        const address1 = req.query.Address1;
        const address2 = req.query.Address2;
        const city = req.query.City;
        const state = req.query.State;
        const zip = req.query.ZipCode;
        
        console.log(req.body, "Get request reached.");
        const [address] = await pool.query ("SELECT * FROM addresses WHERE (Address1, City, 
        State, ZipCode) = (?, ?, ?, ?)",
        [
            address1,
            city,
            state,
            zip
        ],
        );
            
            if(address!=''){
            res.json({
                status: "Success: 200",
                message: "There was a match to your address.",
                address
                });
            }

       //NEED TO ADD ELSE IF HERE FOR PARTIAL MATCH, PROBABLY JUST BASED ON STREET 
       //NAME IN "Address1"

            else {
            res.json({
                status: "Failure: 400",
                message: "No match found. Would you like to add your address to the 
                database?",
                address1,
                address2,
                city,
                state,
                zip
                });
            }

      } catch (err) {
        console.error(err.message)
               
            }    
        })

现有响应示例：

精确匹配响应 #

{
"status": "Success: 200",
"message": "There was a match to your address.",
"address": [
    {
        "id": 112,
        "Address1": "16 Blue Sage Circle",
        "Address2": "",
        "City": "Atlanta",
        "State": "GA",
        "ZipCode": "30318-1030"
    }
]
}

无匹配响应 #

{
"status": "Failure: 400",
"message": "No match found. Would you like to add your address to the database?",
"address1": "16 Blue Sage",
"address2": "",
"city": "Atlanta",
"state": "GA",
"zip": "30318-1030"
}

需要解决：如何添加触发else if的逻辑，以及最优的SQL查询方式。

解决方案 #

1. 修正精确匹配的判断逻辑 #

原代码中if(address!='')的判断存在问题：pool.query返回的是数组，无匹配时是**空数组[]**而非空字符串。正确判断应为检查数组长度：if (address.length > 0)。

2. 近似匹配的SQL查询方案 #

针对Address1的部分匹配，推荐用LIKE操作符结合通配符，同时绑定城市、州、邮编缩小范围（保证匹配相关性），SQL语句如下：

SELECT * FROM addresses 
WHERE City = ? AND State = ? AND ZipCode = ? 
AND Address1 LIKE CONCAT('%', ?, '%')

• 用CONCAT拼接通配符%，避免直接字符串拼接带来的SQL注入风险；
• 限定同城市/州/邮编范围，避免返回无关地址。

3. 完整逻辑实现（添加else if） #

在精确匹配失败后执行近似匹配查询，根据结果长度分支处理：

• 若近似匹配结果长度>0，返回包含地址数组的成功响应；
• 否则返回失败响应。

修改后的完整代码 #

app.get("/addresses/api/find/", async (req, res) => {
    try {
        const address1 = req.query.Address1;
        const address2 = req.query.Address2;
        const city = req.query.City;
        const state = req.query.State;
        const zip = req.query.ZipCode;
        
        console.log(req.body, "Get request reached.");
        
        // 1. 执行精确匹配查询
        const [exactMatches] = await pool.query(
            "SELECT * FROM addresses WHERE (Address1, City, State, ZipCode) = (?, ?, ?, ?)",
            [address1, city, state, zip]
        );
            
        if (exactMatches.length > 0) {
            return res.json({
                status: "Success: 200",
                message: "There was an exact match to your address.",
                address: exactMatches
            });
        }

        // 2. 执行近似匹配查询
        const [partialMatches] = await pool.query(
            "SELECT * FROM addresses WHERE City = ? AND State = ? AND ZipCode = ? AND Address1 LIKE CONCAT('%', ?, '%')",
            [city, state, zip, address1]
        );

        if (partialMatches.length > 0) {
            return res.json({
                status: "Success: 200",
                message: "Found partial matches for your address.",
                addresses: partialMatches
            });
        }

        // 3. 无匹配场景
        res.json({
            status: "Failure: 400",
            message: "No match found. Would you like to add your address to the database?",
            address1,
            address2,
            city,
            state,
            zip
        });

    } catch (err) {
        console.error(err.message);
        // 补充服务器错误响应
        res.status(500).json({
            status: "Error: 500",
            message: "Server error occurred."
        });
    }    
});

近似匹配响应示例 #

{
"status": "Success: 200",
"message": "Found partial matches for your address.",
"addresses": [
    {
        "id": 112,
        "Address1": "16 Blue Sage Circle",
        "Address2": "",
        "City": "Atlanta",
        "State": "GA",
        "ZipCode": "30318-1030"
    },
    {
        "id": 113,
        "Address1": "22 Blue Sage Drive",
        "Address2": "",
        "City": "Atlanta",
        "State": "GA",
        "ZipCode": "30318-1030"
    }
]
}

优化建议 #

• 为City、State、ZipCode、Address1建立联合索引，提升近似匹配查询效率；
• 若需更精准的地址匹配（如处理街道缩写、同义词），可引入地址标准化库（如addressparser）预处理输入地址后再查询；
• 始终使用参数化查询，杜绝SQL注入风险。

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