选择所有在MySQL中使用特定外键值的表格。

huangapple go评论74阅读模式
英文:

Selecting all tables that uses a specific foriegn key value in MySQL

问题

在MySQL中,我有一个表Currency(id,name)和多个引用它的表(id),我想知道是否有任何表使用特定的Currency.id值。

Currency

id name
1 USD
2 JD

SomeTable

id currencyId
112 1
345 2

如何获取所有表名,如果currencyId引用特定的Currency表的值(例如:Currency.id = 1,SomeTable.currencyId = 1)?

英文:

In MySQL I have a table Currency (id, name) and multiple tables that refers to its (id) and I want to know if there is any table that uses a specific Currency.id value.

Currency

id name
1 USD
2 JD

SomeTable

id currencyId
112 1
345 2

How can I get all table names if the currencyId referenced to specific value of Currency table (for example: Currency.id = 1, SomeTable.currencyId = 1)?

答案1

得分: 1

你可以查询系统表KEY_COLUMN_USAGE以查找与货币ID的所有引用。然后使用CONCATGROUP_CONCAT来构建一个查询,返回包含特定ID的表名。

select 
  group_concat(
    concat('select ''', table_schema, '.', table_name, ''' where exists (select null from ', table_name, ' where ', column_name, ' = @id)')
    separator '\nunion all\n'
  ) as query
from information_schema.key_column_usage
where upper(referenced_table_schema) = 'FIDDLE' -- 在这里填写你的模式名称
  and upper(referenced_table_name) = 'CURRENCY';

演示链接:https://dbfiddle.uk/Jxi6K1TV

英文:

You can query the system table KEY_COLUMN_USAGE to find all references to the currency ID. Then use CONCAT and GROUP_CONCAT to build a query returning the table names containing a particular ID.

select 
  group_concat(
    concat('select ''', table_schema, '.', table_name, ''' where exists (select null from ', table_name, ' where ', column_name, ' = @id)')
    separator '\nunion all\n'
  ) as query
from information_schema.key_column_usage
where upper(referenced_table_schema) = 'FIDDLE' -- your schema name here
  and upper(referenced_table_name) = 'CURRENCY';

Demo: https://dbfiddle.uk/Jxi6K1TV

huangapple
  • 本文由 发表于 2023年6月22日 15:24:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/76529475.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定