快速检查主表中是否有匹配的ID？

Question

我有一个SQL数据库，它有一个“主表”，只包含数据库中其余表的ID(重复项已被处理。）我希望遍历数据库中的其余每个表，在“主表”中添加一列，然后如果“主表”中的每个ID都存在于小列表中，则在该列中添加一个“1”，否则添加一个“0”

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

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

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，并对照“主表”进行检查，但我正在寻找一种更快的方法

Answer 1

由于您处理的是元数据，我更喜欢使用信息模式，因此您将有一个查询来获取数据

例如：

#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

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

_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)