CODEWITHSUNDEEP
phpmysql
DaFrog
DaFrog

Reputation: 23

How do I add a custom key to mysql information_schema.COLUMNS

Im using this mysql statement SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '$dbck' AND TABLE_NAME = '$tbck' AND COLUMN_NAME = '$field_param'". How can I add a custom key to the mysql information_schema.columns to look like this?

    {
        "Field": "id",
        "Type": "mediumint(5) unsigned",
        "Null": "NO",
        "Key": "PRI",
        "Default": null,
        "Extra": "auto_increment",
        ***"CUSTOM_KEY": "custom_value"***
    }

Upvotes: 2

Views: 67

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

You can create and fill your own table in your own database like :

CREATE TABLE CUSTOM_COLUMNS_DATA (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `CUSTOM_KEY` varchar(64) NOT NULL DEFAULT ''
);

and retrieve data using JOIN

SELECT * 
FROM information_schema.COLUMNS 
JOIN mydb.CUSTOM_COLUMNS_DATA USING(`TABLE_CATALOG`, `TABLE_SCHEMA`, `TABLE_NAME`, `COLUMN_NAME`)
WHERE TABLE_SCHEMA = '$dbck' AND TABLE_NAME = '$tbck' AND COLUMN_NAME = '$field_param'"

Upvotes: 1

Related Questions