ITKeyword,专注技术干货聚合推荐

注册 | 登录

解决sql - MySQL SELECT or INSERT if row not exist yet

itPublisher 分享于

2020腾讯云双十一活动,全年最低!!!(领取3500元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1073

【阿里云】双十一活动,全年抄底价,限时3天!(老用户也有),
入口地址https://www.aliyun.com/1111/home

推荐:MySQL 语句级避免重复插入—— Insert Select Not Exist

想要插入一条数据,要避免重复插入,又不想折腾两回数据库连接操作,可以参考如下办法。 INSERT INTO table(column1,column2,column3 ...columnN)SELECT val

I have 3 simple tables:
players:
CREATE TABLE IF NOT EXISTS players (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), UNIQUE KEY (name));

arenas:
CREATE TABLE IF NOT EXISTS arenas (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(40), UNIQUE KEY (name));

and points:
CREATE TABLE IF NOT EXISTS points (playerID INTEGER, arenaID INTEGER, points INTEGER, PRIMARY KEY (playerID, arenaID));

I want insert new row to points table, and automatically insert rows to players and arenas if needed, currently I have just simple INSERT:

INSERT INTO points (playerID, arenaID, points) 
VALUES 
(
    (SELECT players.id FROM players WHERE players.name LIKE ?),
    (SELECT arenas.id FROM arenas WHERE arenas.name LIKE ?),
    ?
) ON DUPLICATE KEY UPDATE points.points = LEAST(points.points, VALUES(points.points));

? will be replaced with value.
Hot to edit this SQL, so if there is no player (SELECT players.id FROM players WHERE players.name LIKE ?) then it will INSERT new player to this table, this same with arenas.
Like: (my pseudo-code)

INSERT INTO points (playerID, arenaID, points) 
VALUES 
(
    (SELECT players.id FROM players WHERE players.name LIKE "Mary" ON KEY NOT EXIST INSERT INTO players (name) VALUES ("Mary")),
    (SELECT arenas.id FROM arenas WHERE arenas.name LIKE "BigBoy" ON KEY NOT EXIST INSERT INTO arenas (name) VALUES ("BigBoy")),
    200
) ON DUPLICATE KEY UPDATE points.points = LEAST(points.points, VALUES(points.points));

And still insert that new points row.

mysql sql database insert
|
  this question
asked Feb 3 '15 at 13:59 GotoFinal 52 8      You need to use triggers for this -- an insert trigger on points. –  Gordon Linoff Feb 3 '15 at 14:01      Or stored procedures. –  jarlh Feb 3 '15 at 14:02

 | 

2 Answers
2

解决方法

Why don't do it in three steps, you can use a stored procedure

IF NOT EXISTS arenas (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(40), UNIQUE KEY (name))
begin
    INSERT INTO arenas (name) VALUES ("BigBoy")
end
IF NOT EXISTS players (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), UNIQUE KEY (name))
begin
    INSERT INTO players (name) VALUES ("Mary")
end

INSERT INTO points (playerID, arenaID, points) 
VALUES 
(
    (SELECT players.id FROM players WHERE players.name LIKE "Mary"),
    (SELECT arenas.id FROM arenas WHERE arenas.name LIKE "BigBoy"),
    200
) ON DUPLICATE KEY UPDATE points.points = LEAST(points.points, VALUES(points.points));

|
  this answer
answered Feb 3 '15 at 14:08 Mahmoud Fawzy 26 4      is IF NOT EXISTS different than simple INSERT IGNORE INTO here? –  GotoFinal Feb 3 '15 at 15:12

 | 

You can use INSERT IGNORE statement.

INSERT IGNORE INTO players (name) VALUES (?);

INSERT IGNORE INTO arenas (name) VALUES (?);

INSERT INTO points (playerID, arenaID, points) 
VALUES 
(
    (SELECT players.id FROM players WHERE players.name LIKE ?),
    (SELECT arenas.id FROM arenas WHERE arenas.name LIKE ?),
    ?
) ON DUPLICATE KEY UPDATE points.points = LEAST(points.points, VALUES(points.points));

|
  this answer
answered Feb 3 '15 at 14:12 Nailgun 1,872 1 14 34

 | 

推荐:Mysql:ROW_COUNT()返回前一个SQL进行UPDATE,DELETE,INSERT操作所影响的行数

在Mysql中ROW_COUNT()返回前一个SQL进行UPDATE,DELETE,INSERT操作所影响的行数。 下面通过一个更新的例子来说明:  MySQL上的测试(数据库版本为:5.1.22): 1.


相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。