结合两个查询来检查MySQL中的重复项? [英] Combine two queries to check for duplicates in MySQL?

问题描述

我有一个看起来像这样的表:

I have a table that looks like this:

Number  | Name 
--------+--------
123     | Robert

这就是我想要做的:

如果号码已经在数据库中，请不要插入新记录.

If the Number is already in the database, don't insert a new record.

如果数字不在数据库中，但名称在其中，则创建一个新名称并插入它.因此，例如，如果我有一条记录，其中Number包含123，Name包含Bob，则我不想插入它，但是如果我得到的记录包含Number的456 >和Robert表示name，我将插入456和Robert1.我要分别检查重复项，例如:

If the Number is not in the databse, but the name is, create a new name and insert it. So for example, if I have a record that contains 123 for Number and Bob for Name, I don't want to insert it, but if I get a record that contains 456 for Number and Robert for name, I would insert 456 and Robert1. I was going to check for duplicates individually like:

SELECT * FROM Person where Number = 123;

//If number is not found
SELECT * FROM Person where Name = 'Robert';

//If name is found, add a number to it.

有没有办法将两个语句组合在一起?

Is there a way I can combine the two statements?

推荐答案

您的问题中实际上有两个问题.第一个问题是使Number列唯一，第二个问题是通过添加数字(如果已经存在)来增加Name列.

There are actually two problems in your question. The first problem is to make Number column unique and the second one is to increment the column Name by appending a number if it already exists.

第一部分

由于数字为UNIQUE，因此在列上强制使用UNIQUE约束.可以是PRIMARY KEY或UNIQUE KEY.

Since the number is UNIQUE, enforce a UNIQUE constraint on the column. It could be a PRIMARY KEY or a UNIQUE KEY.

如果列中没有KEY，而您想将其设置为PRIMARY，则这是ALTER语句:

If the column has no KEY and you want to make it PRIMARY, here is the ALTER statement:

ALTER TABLE TableName ADD CONSTRAINT tb_pk PRIMARY KEY (Number)

• SQLFiddle演示
• SQLFiddle Demo

但是如果您只希望它是UNIQUE而不是主键，

but if you only want it to be UNIQUE and not a primary key,

ALTER TABLE TableName ADD CONSTRAINT tb_uq UNIQUE (Number)

• SQLFiddle演示
• SQLFiddle Demo

第二部分

您实际上可以在不使用join的情况下进行操作.

You can actually do it without using join.

INSERT INTO TableName(Number, Name)
SELECT  124 AS Number, 
        CONCAT('Robert', COALESCE(MAX(CAST(REPLACE(Name, 'Robert', '0') AS UNSIGNED)) + 1,'')) AS Name
FROM    TableName
WHERE   Name LIKE 'Robert%'

• SQLFiddle演示
• SQLFiddle演示(添加了更多示例)
• SQLFiddle演示(由于唯一性而引发异常)
• SQLFiddle Demo
• SQLFiddle Demo (added more example)
• SQLFiddle Demo (throws exception due to uniqueness)

一些细节:

当列Number上提供的值已经存在时，由于该列是唯一的，因此将引发错误.我从已删除的帖子中读到一条评论，上面写着:"..数字不是唯一的，但如果确实存在，则我不想输入记录." -它不会产生任何影响如果您不想在该列上添加唯一性，则可以理解.您怎么知道这个号码是否已经存在?对Number的存在进行一些检查对我来说似乎有点负担.因此，我最好的建议是强制执行唯一性.

when the value supplied on column Number already exists, it will throw an error since the column is unique. I have read a comment from a deleted posts saying: "..Number is not unique, but if it does exist, I don't want to enter a record." -- it does not make any sense if you don't want to add uniqueness on the column. How will you know if the number already exists or not? Doing a little check for the existence of Number feels like a little overhead for me. So my best recommendation is to enforce uniqueness.

这篇关于结合两个查询来检查MySQL中的重复项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！