英文:
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的所有引用。然后使用CONCAT
和GROUP_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';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论