Adding metadata attributes to MySQL table

Question

I would like to add custom attributes to a MySQL table which I can read via php.

These attributes are not to interfere with the table itself - they are primarily accessed by php code during code generation time and these attributes HAVE to reside in the DB itself.

Something similar in concept to .NET reflection.

Does MySQL support anything like this?

Thanks.

Answer 1

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.

Answer 2

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?