mysql 带参数_带参数的MySQL条件插入

我正在创建一个存储过程以将值插入到四个表中。重点在于从SQL中取出SQL以防止SQL注入并更好地定义用户拥有的权限，即不允许无限制地访问INSERT,SELECT,ALTER和DELETE语句，它们仅被允许运行该过程他们需要插入传递的变量。但是，如果玩家已经存在，那么就没有必要再次添加它(并且考虑到我已经使其独特)，所以不必再添加它(并且不可能)。问题的产生是因为玩家表和坐标表之间存在一对多的关系。带参数的MySQL条件插入

所以我想要的是一个条件INSERT来测试值是否已经存在，如果它确实移动到下一个INSERT声明。

这里的存储过程：

CREATE PROCEDURE `acdb_extended`.`addAlliedMember` (IN accountNumber VARCHAR(255),

IN userName VARCHAR(255), IN serverInitial CHAR(1), IN galaxy TINYINT(2),

IN region TINYINT(2), IN system TINYINT(2), IN astro TINYINT(2), IN level TINYINT(2),

IN allianceName VARCHAR(255))

BEGIN

INSERT INTO player (account_number, username)

VALUES (accountNumber, userName);

INSERT INTO coordinates (player_ID, server_initial, galaxy, region, system, astro)

VALUES ((SELECT player_ID FROM player WHERE username = userName), serverInitial,

galaxy, region, system, astro);

INSERT INTO jumpgate (player_ID, coordinates_ID, level, usable)

VALUES ((SELECT player_ID FROM player WHERE username = userName),

(SELECT c.coordinates_ID FROM coordinates c WHERE c.server_initial = serverInitial

AND c.galaxy = galaxy AND c.region = region AND c.system = system AND c.astro = astro),

level, FALSE);

INSERT INTO relationship (player_ID, ally, alliance_name)

VALUES ((SELECT player_ID FROM player WHERE username = userName),

TRUE, allianceName);

END

我想我需要ON DUPLICATE KEY但我不能完全弄清楚它的使用。

在此先感谢您的帮助。

2012-04-06

Arcadian