mysql获取上一条记录

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com');

DELIMITER //

CREATE PROCEDURE GetPreviousRecord(IN userId INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE prevName VARCHAR(50);
    DECLARE cur CURSOR FOR SELECT name FROM users ORDER BY id DESC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO prevName;
        IF done THEN
            LEAVE read_loop;
        END IF;
        IF (SELECT id FROM users WHERE id = userId) > (SELECT id FROM users WHERE name = prevName) THEN
            SELECT prevName;
            LEAVE read_loop;
        END IF;
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;

CALL GetPreviousRecord(3); -- 获取id为3的上一条记录

SET @prevId := (SELECT id FROM users ORDER BY id DESC LIMIT 1 OFFSET 1);

SELECT * FROM users WHERE id = @prevId;