查询MySQL information_schema数据库是查找相关表的好方法吗? [英] Is querying the MySQL information_schema database a good way to find related tables?
问题描述
我有一个表，该表在许多其他表上都被外键引用.在我的程序中，如果要删除这些行之一，我需要首先搜索依赖项并将其呈现给用户-此对象取决于表y中的x，表q中的z，依此类推".我还希望具有该表的外键的表的数量会随着时间的推移而显着增长.
I have a table which is referenced by foreign keys on many other tables. In my program if I want to delete one of these rows I need to first search for dependencies and present them to the user - "This object depends on x from table y, z from table q, etc". I also expect the number of tables which have foreign keys to this table to grow considerably over time.
information_schema数据库是搜索所有依赖项的好方法吗?我试图对其进行查询，以检索所有包含对我的表有外键的表的列表，然后遍历结果并从每个表中选择外键值与用户尝试删除的值相匹配的所有条目.我的查询如下:
Is the information_schema database a good way to do a search for all dependencies? I tried to query it to retrieve a list of all tables which have foreign keys to my table, then iterate over the result and select all entries from each table where the foreign key value matches the value the user is trying to delete. The query I have is as follows:
SELECT * FROM `KEY_COLUMN_USAGE` kcu
LEFT JOIN TABLE_CONSTRAINTS tc
ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE='FOREIGN KEY'
AND (kcu.REFERENCED_TABLE_SCHEMA='db')
AND (kcu.REFERENCED_TABLE_NAME = 'testtable')
非常适合确定我需要搜索的表，但是非常很慢.在开发计算机上执行查询最多需要大约1到2秒的时间，在生产服务器上运行该查询会减少很多时间，但仍然很慢.
which works perfectly for determining the tables which I need to search, however it is very slow. The query takes around 1 to 2 seconds at best to execute on my development machine, which will reduce a lot when I run it on my production server, but will still be quite slow.
我需要知道以这种方式使用information_schema是否不好.如果没有，如何从查询中提取更好的性能.我使用的是固态查询还是有更好的方法呢?如果是这样，我应该如何从可维护性的角度来解决这个问题.
I need to know if it's a bad idea to use information_schema in this way. If not, how I can extract better performance from the query. Is the query I'm using solid or is there a better way to do it? If so, how best should I tackle this problem from a maintainability perspective.
推荐答案
Dvorak是正确的，INFORMATION_SCHEMA就是为此而设计的.
Dvorak is right, INFORMATION_SCHEMA is intended for that.
关于性能问题，有几种方法可以改善性能
Concerning your performance concerns, there are several ways you can improve the performance
-
简单的方法，但是并不会带来太大的改进: 将信息存储在静态变量中.每页至少查询一次
Easy way, but not much improvement will come from it: Store the info in a static variable. At least the query will occur only once per page
使用持久性缓存:备用PHP缓存可以为您提供帮助(请参见 http://fr3.php.net/manual/zh/book.apc.php ). 您将从信息架构中获得的信息很适合存储在持久性缓存中.
Use persistent caching : The alternative PHP cache can help you (see http://fr3.php.net/manual/en/book.apc.php). The info you'll get from the information schema is a good candidate to store in a persistent cache.
使用ORM库，例如主义( http://www.doctrine-project. org/) 查看文件lib/Doctrine/Import/Mysql.php会发现它完全满足您的需求，而且还有更多.
Use a ORM library, such as doctrine (http://www.doctrine-project.org/) A look at the file lib/Doctrine/Import/Mysql.php will show that it does exactly what you need, and much more.
这篇关于查询MySQL information_schema数据库是查找相关表的好方法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
