在主表中检查匹配 ID 的快速方法? [英] Fast way to check for matching ID's in master table?

问题描述

我有一个带有主表"的 SQL 数据库，其中仅包含数据库中其余表的 ID.(已经处理了重复项.)我想遍历数据库中其余的每个表，向主表"添加一列，然后向主表"中的每个 ID 添加1"table' 存在于小列表中，否则添加 '0'.

I have a SQL database with a 'master table' containing only the IDs from the rest of the tables in the database. (Duplicates have already been handled.) I want to iterate through each of the rest of the tables in my database, adding a column to the 'master table' and then adding a '1' to the column if each ID from the 'master table' exists in the small list, and adding a '0' otherwise.

到目前为止我已经尝试了一些查询，但它们似乎很慢.我使用的表将包含几千个 ID，所以我想找到一种快速的方法.

I've tried a few queries so far but they seem pretty slow. The tables I am using will contain a few thousand IDs each so I'd like to find a fast method.

到目前为止，我的 Python 代码如下所示:

My Python code looks like this so far:

def main():
    table_list = init() #Gets a list of others tables in the database.
    for ltab in table_list:
        id_list = getids(ltab) #Gets the ids for each smaller table.
        cursor.execute("ALTER TABLE " + table + " ADD " + ltab + " BIT;")
        cnx.commit()
        for ID in id_list:
            (...)

我接下来(作为初学者)要做的是遍历每个 ID 并对照主表"进行检查，但我正在寻找一种更快的方法来做到这一点.

What I would do next (as a beginner) would be to iterate through each ID and check it against the 'master table,' but I'm looking for a faster way to do this.

推荐答案

由于您正在处理元数据，我更喜欢使用 information_schema，因此您将有一个查询来获取数据.

Since you are dealing with meta data, i prefer to use information_schema, so you will have a single query to fetch the data.

例如:

#create table Test1(id_1 integer, title varchar(100));
#create table Test2(id_2 integer, title varchar(100));
#insert into Test1(id_1, title) values(1, "Hello");
#insert into Test2(id_2, title) values(1, "Hello");
#insert into Test1(id_1, title) values(2, "Hello");
#insert into Test2(id_2, title) values(2, "Hello");
select column_name,
       sum( if( TABLE_NAME = 'Test1', 1, 0 ) ) as Test1, 
       sum( if( TABLE_NAME = 'Test2', 1, 0 ) ) as Test2 
   from information_schema.columns
   where TABLE_SCHEMA = 'your_schema'
   and column_name like '%id%'
   group by column_name;

会给你类似的东西:

    column_name Test1   Test2
1   accepterid    0       0
2   acl_id        0       0
3   id_1          1       0
4   id_2          0       1

因此在您上面的查询中，您可以将其调整为

So in your above query you can tweak it as

_tables = ','.join([("sum( if( TABLE_NAME = '%s', 1, 0 ) ) as %s" % (i,i)) for i in table_list ])

query = """
        create view master as(
        select column_name, %s
         from information_schema.columns
         where TABLE_SCHEMA = 'your_schema'
         and column_name like '%id%'
         group by column_name;)
       """ % (_table,)
cursor.execute(query)

这篇关于在主表中检查匹配 ID 的快速方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！