在表中创建或更新行的 MySQL 过程 [英] MySQL procedure to create or update a row in a table
问题描述
我想编写一个 MySQL proc,它接受 userId
和 userName
并根据行是否插入或更新到表 users
存在 users.userId = userId
I want to write a MySQL proc which takes userId
and userName
and does an insert or update into table users
dependent on if a row exists where users.userId = userId
我在这里看到了一个类似的问题:MySql create or update row with ip?
I saw a similar question here: MySql create or update row with ip?
但我想在存储过程中执行此操作并想知道这是否提供任何其他工具?
But I want to do this in a stored procedure and wondered if that gives any additional tools?
推荐答案
你不需要存储过程,你可以使用INSERT ... ON DUPLICATE KEY UPDATE ...
.假设 userid 是 PRIMARY
或 UNIQUE
键,这将起作用:
You don't need a stored procedure, you can use INSERT ... ON DUPLICATE KEY UPDATE ...
. Assuming that userid is the PRIMARY
or a UNIQUE
key, this will work:
INSERT INTO user
SET
userid = ?,
userName = ?
ON DUPLICATE KEY UPDATE
userName = VALUES(userName)
当然,如果需要,您可以将此查询包装在存储过程中:
Of course, you could wrap this query in a stored procedure if you want:
DROP PROCEDURE IF EXISTS set_user;
DELIMITER //
CREATE PROCEDURE set_user(
IN i_userid INT UNSIGNED,
IN v_userName VARCHAR(50)
)
DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
INSERT INTO user
SET
userid = i_userid,
userName = v_userName
ON DUPLICATE KEY UPDATE
userName = VALUES(userName);
END;
//
DELIMITER ;
这篇关于在表中创建或更新行的 MySQL 过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!