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