mysql - 在许多具有相同结构的mySql表中搜索数据

Question

我划分了一个有大量行的表，现在我有 16 个具有相同结构的表。当我尝试在其中一个表中查找某些内容时，它效果很好，但是当我不知道它到底应该在哪里时，我必须在所有表中搜索它。

我这样做:

SELECT id, name, surname, age, sex, telephon, fax, email
FROM
    `out_sim_valuesmatrix_ep_ma13_1_16`,
    `out_sim_valuesmatrix_ep_ma13_2_16`,
    `out_sim_valuesmatrix_ep_ma13_3_16`,
etc
`    out_sim_valuesmatrix_ep_ma13_16_16`
WHERE
    `appln_idNum1` IN (24842422,24918676,24771783,24908804,24960166,25041955,25017764,25104985,25039175,25089121,24897200,25038905,24907508,24856413,24856413,25001485,24873551,24873551,24747119,25055354,25068104,24969433,24736299,24908799,24947820,24861202,24861202,25101573,24811406,24811406,24939062,25072049,24899024,24908795,25045775,24741197,25033220,24862936,24862936,24926838,24872619,24872619,25011020,24791353,24791353,24983796,24975187,25087564,25044301,24969428,24941046,24944919,24835727,24835727,25015010,25001957,24976200,25057258,25064419,25040849,25063554,25061255,24863241,24863241,24749445,24885880,24751246,24882026,25095717) // Here there are actually 1000 values!!!

但随后我收到以下消息:

#1052 - Column 'appln_idNum1' in where clause is ambiguous

我应该如何编写查询来找到正确的值？

当我这样做时https://stackoverflow.com/a/409720/3499881在我收到答复之前，速度非常慢。

这个https://stackoverflow.com/a/21260560/3499881这不是一个好的解决方案，因为我的选择应该有 16 * 8 行 [16 个表 * 8 列]。我觉得这太过分了，不是吗？

还有其他可能性吗？

非常感谢!

Answer 1

如果表具有相同的结构，那么您需要在 from 子句中使用 union all，而不是逗号:

SELECT id, name, surname, age, sex, telephon, fax, email
FROM (select * from `out_sim_valuesmatrix_ep_ma13_1_16` union all
      select * from `out_sim_valuesmatrix_ep_ma13_2_16` union all
      select * from `out_sim_valuesmatrix_ep_ma13_3_16` union all
      . . .
      select * from `out_sim_valuesmatrix_ep_ma13_16_16`
     ) t
WHERE
    `appln_idNum1` IN (24842422,24918676,24771783,24908804,24960166,25041955,25017764,25104985,25039175,25089121,24897200,25038905,24907508,24856413,24856413,25001485,24873551,24873551,24747119,25055354,25068104,24969433,24736299,24908799,24947820,24861202,24861202,25101573,24811406,24811406,24939062,25072049,24899024,24908795,25045775,24741197,25033220,24862936,24862936,24926838,24872619,24872619,25011020,24791353,24791353,24983796,24975187,25087564,25044301,24969428,24941046,24944919,24835727,24835727,25015010,25001957,24976200,25057258,25064419,25040849,25063554,25061255,24863241,24863241,24749445,24885880,24751246,24882026,25095717) 

, 对所有表进行笛卡尔积，这是浪费大量精力。您只需要所有行，因此请使用union all。