MySQL如何设置自动更新行号列？附Express查询问题求助

一、修复当前查询的undefined问题 #

你的SQL执行了两个独立查询，返回了两个结果数组：第一个是全表带排名的数据集，第二个是匹配name的数据集。前端直接遍历整个返回结果，导致每个遍历项是数组而非单个数据对象，所以解构id, name等字段时全部为undefined。

修正方法：合并查询逻辑 #

把两个查询合并成一个，直接返回匹配name且带排名的结果：

async searchByName(name) {
    try{
        const response = await new Promise((resolve, reject) => {
            // 合并查询：只返回匹配name的行，同时计算排名
            const query = `
                SELECT t.*, ROW_NUMBER() OVER (ORDER BY time) rn 
                FROM medley_relay t
                WHERE t.name = ?;
            `;

            connection.query(query, [name], (err, results) => {
                if (err) reject(new Error(err.message));
                resolve(results);
            })
        });
        console.log(response);
        return response;
    } catch (error) {
        console.log(error);
    }
}

如果需要同时保留全表排名和匹配结果，前端需明确处理两个数组：

function loadHTMLTable(data) {
    const table = document.querySelector('table tbody');
    // 取第二个数组（匹配name的结果）
    const targetData = data[1];
    console.log(targetData);

    if (targetData.length === 0) {
        table.innerHTML = "<tr><td class='no-data'>No Data</td><td class = 'no-data'colspan='5'>No Data</td></tr>";
        return;
    }

    let tableHtml = "";

    targetData.forEach(function ({id, name, time, date}) {
        tableHtml += "<tr>";
        tableHtml += `<td>N/A</td>`;
        tableHtml += `<td>${name}</td>`;
        tableHtml += `<td>${time}</td>`;
        tableHtml += `<td>${new Date(date).toLocaleDateString()}</td>`;
        tableHtml += `<td><button class="delete-row-btn" data-id=${id}>Delete</td>`;
        tableHtml += `<td><button class="edit-row-btn" data-id=${id}>Edit</td>`;
        tableHtml += "</tr>";
    });

    table.innerHTML = tableHtml;
}

二、实现自动更新的永久rank列 #

要创建随数据增删自动刷新的永久rank列，MySQL中可以通过触发器实现：

1. 给表添加rank列 #

ALTER TABLE medley_relay ADD COLUMN `rank` INT DEFAULT 0;

2. 创建计算排名的存储过程 #

DELIMITER //
CREATE PROCEDURE update_ranks()
BEGIN
    -- 临时表存储新排名
    CREATE TEMPORARY TABLE temp_ranks AS
    SELECT id, ROW_NUMBER() OVER (ORDER BY time) AS new_rank
    FROM medley_relay;
    
    -- 更新原表的rank列
    UPDATE medley_relay m
    JOIN temp_ranks tr ON m.id = tr.id
    SET m.`rank` = tr.new_rank;
    
    DROP TEMPORARY TABLE temp_ranks;
END //
DELIMITER ;

3. 创建触发排名更新的触发器 #

-- 插入数据后更新排名
DELIMITER //
CREATE TRIGGER after_medley_insert
AFTER INSERT ON medley_relay
FOR EACH ROW
BEGIN
    CALL update_ranks();
END //
DELIMITER ;

-- 修改time字段后更新排名
DELIMITER //
CREATE TRIGGER after_medley_update
AFTER UPDATE ON medley_relay
FOR EACH ROW
BEGIN
    IF OLD.time != NEW.time THEN
        CALL update_ranks();
    END IF;
END //
DELIMITER ;

-- 删除数据后更新排名
DELIMITER //
CREATE TRIGGER after_medley_delete
AFTER DELETE ON medley_relay
FOR EACH ROW
BEGIN
    CALL update_ranks();
END //
DELIMITER ;

4. 初始化现有数据的rank值 #

首次执行存储过程，给已有数据设置排名：

CALL update_ranks();

之后查询时直接使用rank列即可，无需每次调用ROW_NUMBER：

const query = "SELECT * FROM medley_relay WHERE name = ?;";

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