Adding metadata attributes to MySQL table
What kind of attributes? Since you mention reflection, I assume you're trying to find something out about the table structure? Did you realize that the commands that tell you about the table structure are SQL and return their results as any other query? This lets you progammatically process the results of, for example, DESCRIBE TABLE. Is this what you're looking for?
When you CREATE
or ALTER
a table, you can add COMMENTS
- one for the table as a whole and one for each column. To retrieve these comments, you can query the database INFORMATION_SCHEMA
, specifically the columns INFORMATION_SCHEMA.COLUMNS.COLUMNS_COMMENT
and INFORMATION_SCHEMA.TABLE.TABLE_COMMENTS
. INFORMATION_SCHEMA
provides a lot of metadata about your databases, including the data provided by DESCRIBE
statements. Any user who has read access to a certain table or column can read the respective metadata from INFORMATION_SCHEMA
, but cannot read metadata about tables they do not have read access for.
It looks natural to store your custom metadata in INFORMATION_SCHEMA
, but it is not as flexible as you might need it because you can store only one COMMENT
per column. If this is to restrictive for your purpose or you need to update the data regularly, you should follow @Dark Falcon and create an additional table.