MySQL如何创建从关联表取值的INSERT存储过程实现用户插入

正确存储过程实现 #

原有写法核心问题 #

• 手动给自增id字段赋值，违背自动递增的设计逻辑
• 错误使用先插入后更新的冗余逻辑，UPDATE语句未关联两张表且未限定行范围，会导致全表数据被错误修改
• INSERT语句本身支持结合SELECT查询读取其他表的字段值作为插入值，无需拆分操作

正确代码 #

DELIMITER //
CREATE PROCEDURE insertUser(
    IN p_type VARCHAR(50),
    IN p_email VARCHAR(50)
)
BEGIN
    -- 直接通过INSERT ... SELECT语法从userTypes表查询对应type的id完成插入
    INSERT INTO users (email, userTypeId)
    SELECT p_email, id 
    FROM userTypes 
    WHERE type = p_type;
END //
DELIMITER ;

说明 #

• 参数名加p_前缀是为了避免和userTypes表的type字段重名，防止条件匹配出现逻辑错误
• 插入时省略自增id字段，MySQL会自动生成连续的自增id值
• 直接通过SELECT查询匹配到的userTypes.id作为userTypeId的值，一步完成插入操作，效率更高也不会出现数据错误

调用你给出的测试语句：

CALL insertUser('free', 'name_1@mail.com');
CALL insertUser('paid', 'name_2@mail.com');

执行SELECT * FROM users;即可得到你预期的结果。

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