SQL从查询中的数据中选择数据库中尚未存在的数据? [英] SQL select from data in query where this data is not already in the database?

问题描述

我想在进行Web服务调用之前检查数据库中是否已记录的记录.

I want to check my database for records that I already have recorded before making a web service call.

这是我想像的查询形式，我似乎无法弄清楚语法.

Here is what I imagine the query to look like, I just can't seem to figure out the syntax.

SELECT * 
  FROM (1,2,3,4) as temp_table 
 WHERE temp_table.id 
LEFT JOIN table ON id IS NULL 

有没有办法做到这一点?这样的查询是什么?

Is there a way to do this? What is a query like this called?

我想将ID列表传递给mysql，并希望它吐出数据库中尚未存在的ID?

I want to pass in a list of id's to mysql and i want it to spit out the id's that are not already in the database?

推荐答案

为确认我已正确理解，您想传递一个数字列表，看看哪些数字不是 存在于现有表格中?实际上:

To confirm I've understood correctly, you want to pass in a list of numbers and see which of those numbers isn't present in the existing table? In effect:

SELECT Item
FROM IDList I
    LEFT JOIN TABLE T ON I.Item=T.ID
WHERE T.ID IS NULL

您似乎可以立即建立此查询，在这种情况下，您可以通过将数字更改为/tally表来实现此目的，方法是将上面的内容更改为

You look like you're OK with building this query on the fly, in which case you can do this with a numbers / tally table by changing the above into

SELECT Number
FROM (SELECT Number FROM Numbers WHERE Number IN (1,2,3,4)) I
    LEFT JOIN TABLE T ON I.Number=T.ID
WHERE T.ID IS NULL

尽管由于查询的构建方式，这相对容易受到SQL Injection攻击.如果可以将"1、2、3、4"作为字符串传递，并将其拆分为多个部分以生成数字列表以更安全的方式加入，则更好-有关如何执行此操作的示例，请参见 http://www.sqlteam.com/article/parsing- csv值成多行

This is relatively prone to SQL Injection attacks though because of the way the query is being built. It'd be better if you could pass in '1,2,3,4' as a string and split it into sections to generate your numbers list to join against in a safer way - for an example of how to do that, see http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

所有这些假设都假设您的数据库中有一个数字/理算表，但是它们通常非常有用，因此我强烈建议您这样做.

All of this presumes you've got a numbers / tally table in your database, but they're sufficiently useful in general that I'd strongly recommend you do.

这篇关于SQL从查询中的数据中选择数据库中尚未存在的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！