如果不存在则插入，否则只需在mysql中选择 [英] insert if not exists else just select in mysql

问题描述

我有一个mysql表'alfa'，它将在一个列中包含另一个表'beta'的主键.但是，如果找不到"beta"中的条目，我想在"beta"中插入值，并在"alfa"中使用新密钥.我可以以某种方式在一个查询中执行此操作吗?

I have one mysql table 'alfa' that will contain the primary key of another table 'beta' in one column. But if the entry in 'beta' can not be found I want to insert the value in 'beta' and use the new key in 'alfa'. Can I do this in one query somehow ?

我目前有:

INSERT INTO alfa SET c1=(SELECT id FROM beta WHERE name = 'john');

当表中存在"john"时可以正常工作，但否则失败.因此，如果尚不存在新名称，我可以对其进行改进以使其插入并选择该名称吗? id是自动递增的.

which works fine when 'john' exists in the table, but fails otherwise. So could I improve it to let the new name be inserted and selected if it is not already there ? id is auto_incremented.

我尝试查看IF，但尚未找到如何在SELECT之外使用IF的方法，这可能吗?

I have tried to looking at IF but have not yet found out how to use IF outside the SELECT, is that possible ?

我知道我可以在几个查询中做到这一点，但我正在与一个远程数据库进行交流，因此很高兴一次完成所有工作.

I know I can do it in several queries but I am talking with a remote database so could be nice to do it all at once.

例如，表可以这样创建:

For example the tables could have been created like this:

CREATE TABLE alfa (
  c1 int,
  PRIMARY KEY (c1)
)

CREATE TABLE beta (
  id int auto_increment,
  name varchar(255),
  PRIMARY KEY (id)
)

因此alfa.c1应该引用beta.id值.

so alfa.c1 should refer to the beta.id values.

简而言之，我想这样做:

In short I want to do:

将beta表中的john的ID插入Alfa的c1中，如果beta中不存在john，则将john插入beta并将新的john的自动递增ID插入alfa的c1中.

insert the id for john from the beta table into c1 in alfa, if john does not exist in beta then insert john into beta and insert the new auto incremented id for john into c1 in alfa.

推荐答案

我可以试一试，但是请记住，它来自Microsoft SQL背景，并且我不熟悉表的确切结构，因此，某些SQL可能有些不足.

I'll have a go, but bear in mind that coming from a Microsoft SQL background, and I'm not familiar with the exact structure of your tables, so some of the the SQL is probably a bit ropey.

IF (SELECT COUNT(*) FROM beta WHERE name = 'John' > 0)
  UPDATE alfa SET c1=(SELECT id FROM beta WHERE name = 'John')
ELSE
BEGIN
  INSERT INTO beta (name) VALUES ('John')
  INSERT INTO alfa (c1) VALUES (LAST_INSERT_ID())
END

希望这会有所帮助.

这篇关于如果不存在则插入，否则只需在mysql中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！