仅在其他表中不存在记录时才将记录插入表中-MySQL [英] Insert record to the table only if doesn't exist in other tables - MySQL

问题描述

我正在做一个蓝牙项目，其中有一个嗅探MAC地址的设备.我希望将这些嗅探到的地址保存到某个表中(在我的情况下称为访问者").

I am doing some Bluetooth project where I have a device sniffing for MAC addresses. I want those sniffed addresses to be saved to a certain table (In my case called "Visitors").

现在是这样.表I_year_student和II_year_student将具有手动插入的记录.我希望将所有找到的设备保存到访问者"表中.问题是:有时设备将再次嗅探表I_year_student中已经存在的一个MAC地址，当然，它将被再次保存到数据库中，更确切地说，将其保存到表"Visitors"中.现在，我在两个不同的表中有两个相同的记录，并且我不希望发生这种情况.

Now, the thing is. The tables I_year_student and II_year_student will have a manually inserted records. I want all found devices to be saved to the Table "Visitors". The problem is: sometimes one MAC address which is already in the table I_year_student is going to be sniffed by the device again, and of course, it is going to be saved to the database again, more precisely, to the table "Visitors". Now I have two identical records in two different tables and I don't want that to happen.

有没有一种方法，在我的设备找到某个MAC地址之后，可以检查记录是否已存在于其他表中.像这样:

Is there a way, where after my device finds a certain MAC address, to check if the record already exists in other tables. Something like this:

• 我找到了一个新" MAC地址
• 在将该记录插入到"Visitors"表之前，请检查数据库中其他表中是否存在类似的记录.
• 如果有类似的记录，请忽略它.
• 如果记录是唯一的，请将其保存到表"Visitors"中

用于嗅探并输入到表格Visitors的部分代码如下:

A part of the code for sniffing and inputing to the table Visitors is written below:

if len(nearby_devices) > 0:
print(len(nearby_devices))

for addr, name in nearby_devices:
    try:

        cur.execute("""INSERT INTO Visitors
                           (Address, Name)
                       VALUES
                           (%s,%s)
                    """, (addr, name)
                   )
        con.commit()

推荐答案

好，谢谢大家的帮助，但我想我找到了答案并设法解决了这个问题:

OK, thanks everyone for help but I think I found and answer and manage to solve the problem:

我的情况是这样.我有一台始终在蓝牙协议上嗅探MAC地址的设备，我想将找到的所有地址保存到MySQL数据库中，以便以后处理.另外，我想通过基于不同的配置文件将地址分开来保存这些地址，例如:

My situation was this. I have a device that is always sniffing for MAC addresses on Bluetooth protocol and I wanted to save all the addresses that I've found to MySQL database for later processing. Also, I wanted to save those addresses by separating them based on different profiles, for example:

• mac_address1:列:ProfileID( typeA )
• mac_address2:列:ProfileID( typeB )
• mac_address3:列:ProfileID( typeC )，依此类推...

• mac_address1 : Column: ProfileID (typeA)
• mac_address2 : Column: ProfileID (typeB)
• mac_address3 : Column: ProfileID (typeC), and so on...

问题是我的数据库已经有一些手动添加的mac地址，并且我不希望将我的新地址(也可能是旧的，因为某些设备可以再次进入嗅探区域)再次保存在某些ProfileID下，而我已经将它们放在其他ProfileID下.

The thing is that my database already has some manually added mac addresses and I didn't want my new addresses (that could also be old, because some devices can enter the sniffing area again) to be saved again under some ProfileID, while I already have them under some other ProfileID.

因此，经过一番思考，我编写了查询并对其进行了测试，现在它可以正常工作.

So, after some thinking I wrote the query and tested it and now it's working fine.

cur.execute("""INSERT INTO ordenador
                           (Address, Name, ProfileID)
                       VALUES
                           (%s,%s,'public')
                       ON DUPLICATE KEY UPDATE
                       ProfileID = VALUES(ProfileID)
                    """, (addr, name)
                   )

现在，当我的设备捕获到一个新" mac地址时，他将该记录输入到表"ordenador"中，如果新" mac地址的记录已经存在，它会将记录更新为旧记录.在其他情况下，如果该记录不存在，则查询将在ProfileID下输入该记录，在本例中为"public".

Now, when my device catches a "new" mac address, he inputs that record to the table "ordenador" and if the record for "new" mac address already exists there, it just update the record to the old one. In other case, if the record doesn't exist there, the query inputs the record under a ProfileID, in my case "public".

所有建议和想法都受到欢迎.我希望这对已经遇到类似问题的人有所帮助.

All the suggestions and thoughts are welcomed. I hope this will help to someone who already had some similar issues.

这篇关于仅在其他表中不存在记录时才将记录插入表中-MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！