SQL:选择一个表中不存在的键 [英] SQL: Select Keys that doesn't exist in one table
问题描述
我得到了一个带有自动公司常规设置的桌子. ID.一些行已被删除，因此ID列表可能看起来像这样:
I got a table with a normal setup of auto inc. ids. Some of the rows have been deleted so the ID list could look something like this:
(1、2、3、5、8 ...)
(1, 2, 3, 5, 8, ...)
然后，从另一个来源(另一个来源=数据库中不存在)，我有这个数组:
Then, from another source ( Another source = NOT in a database) I have this array:
(1、3、4、5、7、8)
(1, 3, 4, 5, 7, 8)
我正在寻找可以在数据库上使用的查询，以从我拥有的数组中获取表中未包含的ID:s的列表.将会是:
I'm looking for a query I can use on the database to get the list of ID:s NOT in the table from the array I have. Which would be:
(4，7)
(4, 7)
是否存在?我现在的解决方案是创建一个临时表，以便命令"WHERE table.id IS NULL"起作用，或者更糟糕的是，使用PHP函数array_diff来查看从表中检索所有ID后丢失的内容.
Does such exist? My solution right now is either creating a temporary table so the command "WHERE table.id IS NULL" works, or probably worse, using the PHP function array_diff to see what's missing after having retrieved all the ids from table.
由于ID列表即将关闭，因此我渴望找到最佳解决方案.
Since the list of ids are closing in on millions or rows I'm eager to find the best solution.
谢谢！ /托马斯
我的主要应用程序是一个相当简单的表，其中填充了很多行.此应用程序是使用浏览器进行管理的，而我使用的是PHP作为代码的解释器.
My main application is a rather easy table which is populated by a lot of rows. This application is administrated using a browser and I'm using PHP as the intepreter for the code.
此表中的所有内容都将导出到另一个系统(这是第三方产品)，除在该程序中手动使用导入功能外，尚无其他方法.尽管商定的路由选择永远不要这样做，但也可以在其他系统中插入新行.
Everything in this table is to be exported to another system (which is 3rd party product) and there's yet no way of doing this besides manually using the import function in that program. There's also possible to insert new rows in the other system, although the agreed routing is to never ever do this.
然后的问题是，我的系统无法100％确保用户在按下导出"键后一切都正确.或者，在其他系统中从来没有创建过行.
The problem is then that my system cannot be 100 % sure that the user did everything correct from when he/she pressed the "export" key. Or, that no rows has ever been created in the other system.
从另一个系统中，我可以得到一个CSV文件，该文件位于系统所有行所在的位置.因此，通过比较CSV文件和我的表格，我可以看到是否: *在其他系统中应该导入的任何行都丢失 *如果有人在另一个系统中创建了行
From the other system I can get a CSV-file out where all the rows that system has. So, by comparing the CSV file and my table I can see if: * There are any rows missing in the other system that should have been imported * If someone has created rows in the other system
问题不在于解决".最好的解决方案是，因为行中的数据太多.
The problem isn't "solving it". It's making the best solution to is since there are so much data in the rows.
再次感谢！
/托马斯
推荐答案
我们可以选择不使用MYSQL.
We can use MYSQL not in option.
SELECT id
FROM table_one
WHERE id NOT IN ( SELECT id FROM table_two )
已编辑
如果要从 csv文件获取源代码，则只需将这些值直接放入即可，例如:
If you are getting the source from a csv file then you can simply have to put these values directly like:
我假设CSV类似于1,2,3，...，n
I am assuming that the CSV are like 1,2,3,...,n
SELECT id
FROM table_one
WHERE id NOT IN ( 1,2,3,...,n );
编辑2
或者，如果要选择其他方法，则可以使用mysqlimport将数据导入MySQL数据库的临时表中，然后检索结果并删除该表.
Or If you want to select the other way around then you can use mysqlimport to import data in temporary table in MySQL Database and retrieve the result and delete the table.
赞:
创建表格
CREATE TABLE my_temp_table(
ids INT,
);
加载.csv文件
LOAD DATA LOCAL INFILE 'yourIDs.csv' INTO TABLE my_temp_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(ids);
选择记录
SELECT ids FROM my_temp_table
WHERE ids NOT IN ( SELECT id FROM table_one )
删除表
DROP TABLE IF EXISTS my_temp_table
这篇关于SQL:选择一个表中不存在的键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
