mysql 给字段加前缀

-- 创建表时添加前缀
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

-- 插入数据时添加前缀
INSERT INTO users (username, email) VALUES ('usr_john_doe', 'john@example.com');

-- 查询数据时去除前缀
SELECT CONCAT('usr_', username) AS prefixed_username, email FROM users;

SELECT CONCAT('usr_', username) AS prefixed_username, email FROM users;

DELIMITER $$
CREATE TRIGGER add_prefix_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.username = CONCAT('usr_', NEW.username);
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE insert_user(IN p_username VARCHAR(255), IN p_email VARCHAR(255))
BEGIN
    INSERT INTO users (username, email) VALUES (CONCAT('usr_', p_username), p_email);
END$$
DELIMITER ;

CALL insert_user('john_doe', 'john@example.com');