MySQL:使用外键引用连接字段

Question

我想做什么

我正在创建一个 html 表单，我想在其中自动创建字段(例如 <input type="text"> 、 <textarea> 、 <select> )。为此，我执行 MySQL 查询 "SHOW FIELDS from $table" .

a) 现在我想加入REFERENCED_TABLE_SCHEMA , REFERENCED_TABLE_NAME , REFERENCED_COLUMN_NAME来自information_schema.KEY_COLUMN_USAGE到FIELDS -表。

b)稍后我想查询引用表中每个外键的可能值，以提供 html <select> - 菜单，用户可以在其中选择一种预设。

问题

a) 如何在 MySQL 中将这两者结合起来？

对于 a) 和 b)，我使用 2 个数据对象(一个用于字段和引用，另一个用于引用值)。您认为这是一个好方法还是有更好的方法？

编辑

说明

字段和引用的 JSON

[
    {
        "Field": "id",
        ...
        "Key": "PRI",
        ...
        "foreign_db": "null",
        "foreign_table": "null",
        "foreign_column": "null"
    },
    {
        "Field": "url",
        ...
        "Key": "UNI",
        ...
        "foreign_db": "null",
        "foreign_table": "null",
        "foreign_column": "null"
    },
    {
        "Field": "sitemap__site_id",
        ...
        "Key": "MUL",
        ...
        "foreign_db": "my_db",
        "foreign_table": "sitemap",
        "foreign_column": "site_id"
    }
]

引用列值的 JSON

[
    {
        "Field": "sitemap__site_id",
        "Value": "index" 
    },
    {
        "Field": "sitemap__site_id",
        "Value": "error" 
    },
    {
        "Field": "sitemap__site_id",
        "Value": "about" 
    }
]

放在一起，我可以自动创建一个 html <form>其中每个字段都是根据其字段 Type 创建的(例如， varchar(32) 的文本 <input> 字段或 varchar(512) 的 <textarea> 以及这种情况的 <select> 字段，由于外键的引用，我必须预设可能的条目)。

@rlanvin 希望这可以清楚地说明。

再说一遍:如何在 MySQL 中加入第一个 JSON 对象？或者一般来说:这个问题通常是如何解决的？

Answer 1

致a)

SELECT
    c.COLUMN_NAME AS 'Field',
    c.COLUMN_KEY AS 'Key',
    k.REFERENCED_TABLE_SCHEMA AS 'foreign_db', 
    k.REFERENCED_TABLE_NAME AS 'foreign_table', 
    k.REFERENCED_COLUMN_NAME  AS 'foreign_column'
FROM
    information_schema.COLUMNS AS c
LEFT JOIN
    information_schema.KEY_COLUMN_USAGE AS k
ON 
    c.TABLE_SCHEMA = k.TABLE_SCHEMA
AND
    c.TABLE_NAME = k.TABLE_NAME
AND
    c.COLUMN_NAME = k.COLUMN_NAME
WHERE
    c.TABLE_SCHEMA = '$dbname'
AND
    c.TABLE_NAME = '$tableName'