有很多SQL查询条件 [英] Having a lot of SQL query conditions

问题描述

我正在通过逐行比较本地 MSSQL 数据库来更新远程 MySQL 数据库.想法是分 3 个步骤进行更新:

I am updating remote MySQL database by comparing row by row from local MSSQL one. Idea was to do update in 3 steps:

1: 从本地选择所有 ID 并在远程执行以下查询:

1: select all ID's from local and execute following query on remote:

delete from REMOTE_DATABASE.TABLE where ID not in 
( list of LOCAL_DATABASE.TABLE ID's linked by OR condition )

2: 从 LOCAL_DATABASE.TABLE 中选择远程不存在的 ID 并插入

2: select ID's from LOCAL_DATABASE.TABLE that are not exist on remote and insert

3:更新包含两个表的行.

3: update rows that have both tables.

我的问题是关于第一步:我有 50000 多种产品.我在 C# 中工作，我可以将所有 ID 加入字符串中，但我想知道是否可以执行由 OR 条件链接的 50000 个 ID 的查询?这是一个错误的方法吗?

My question is regarding first step: I have 50000+ products. I am working in C# and I could join all ID's in string but I was wondering can query with 50000 ID's linked by OR condition be executed? Is it a wrong approach?

推荐答案

所以我以前做过这个.很久以前，所以我可能会错过一步，但我希望你能明白.

So I have this done this before. A long time ago though so I might miss a step but I hope you will get the idea.

1. 在 MSSQL 数据库上设置一个链接服务器到 MySQL 数据库.请参阅此链接的 MySQL 服务器文章

2. SELECT 把你想要的所有表从链接服务器放到 MSSQL SQL 上的临时表中.类似于 SELECT * INTO #temp FROM linkedserver.tablename 之类的东西，但是最好创建一个适当的临时表并索引您将要加入的列，即

1. Setup a linked server on the MSSQL database to the MySQL database. See this Linked MySQL Server article

2. SELECT all the table you want from the linked server into a temp table on the MSSQL SQL. Something like SELECT * INTO #temp FROM linkedserver.tablename however it would be better to create a proper temp table and index the columns you will be joining on i.e.

CREATE TABLE #Test
(
ID INT PRIMARY KEY NOT NULL
)
INSERT INTO #Test
SELECT * FROM linkedserver.tablename

做一个LEFT/RIGHT JOIN在本地机器上找到新的ID并通过链接服务器将它们插入到远程服务器中.有关在比较两个表时如何使用 LEFT/RIGHT 连接获取新记录的更多信息，请参阅此链接 使用左连接查找新行

使用 UPDATE 语句和 JOIN 更新远程服务器.所以基本上使用 INNER JOIN 使用临时表中的值更新远程服务器.

Update the remote server with a UPDATE statement and JOIN in it. So basically using a INNER JOIN do a update to the remote server with the values in the temp table.

现在您可能会遇到一些语法错误，请在此处发布它们，我可以尝试为您解决这些错误.但是我已经使用这种技术在 MySQL 和 MSSQL 服务器之间进行同步，并且效果很好.由于它基于 SETS 而不是基于 RBAR，因此速度也非常快.

Now there might be some errors you run into with the syntax post them here and I can try and resolve them for you. However I have used this technique to synchronize between MySQL and MSSQL servers and it works pretty well. As it is SETS based and not RBAR based it is very fast as well.

这篇关于有很多SQL查询条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！