MySQL phpMyAdmin从外键生成联接

Question

我正在使用LAMP，并通过phpMyAdmin维护MySQl。我厌倦了每次要在InnoDB表上构建多表查询时都要键入JOIN。

有没有人知道一些功能，插件，方法或资源来从外键生成连接语句？

Answer 1

我最终编写了一个从所有外键构建JOIN语句的查询：

CREATE VIEW Joins_All AS 
SELECT 
    concat(" JOIN ",TABLE_NAME," ON ",
    TABLE_NAME,".",COLUMN_NAME," = ",
    REFERENCED_TABLE_NAME,".", REFERENCED_COLUMN_NAME) 
    AS myJoin 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE REFERENCED_TABLE_NAME IS NOT NULL 

UNION

SELECT  
    concat(" JOIN ",REFERENCED_TABLE_NAME," ON ",
    TABLE_NAME,".",COLUMN_NAME," = ",
    REFERENCED_TABLE_NAME,".", REFERENCED_COLUMN_NAME) 
    AS myJoin 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE REFERENCED_TABLE_NAME IS NOT NULL 

ORDER BY myJoin  ASC

它会生成类似这样的东西：

Area_Tare.Id_Area = Area.Id_Area上的

连接区域

在Area_Tare.Id_Area = Area.Id_Area上加入Area_Tare

在Insect.Id_Insect_Class = Insect_Class.Id_Insect_Class上加入昆虫

在Insect_Insumo_Formula_Ingredient.Id_Insect = Insect.Id_Insect上加入昆虫

我保存了这个文件，并在需要时复制/粘贴适当的行，或者使用所需表的名称过滤视图

这可以很容易地在php中修改(我现在不会这样做)来创建一个基于外键的GUI查询构建器。任何决定这样做的人，请随时使用/改编此代码。