MariaDB/MySQL: Comments on database objects

MariaDB and MySQL support a non-standard syntax to read and write comments for database objects.

Reading comments

Tables, columns, indexes and many other database objects may have comments. They can be read from the information_schema database, in a column called COMMENT.

For example:

SELECT TABLE_COMMENT FROM information_schema.TABLES;
SELECT COLUMN_COMMENT FROM information_schema.COLUMNS;

An easier way to read comments is to use the SHOW CREATE statements:

SHOW CREATE TABLE db.tab;
SHOW CREATE PROCEDURE db.proc;

Human users generally prefer the SHOW CREATE statements, while applications may need to read the information_schema.

Writing comments

In MariaDB and MySQL, one can add comments with the CREATE statements or change them with the ALTER statements.

For example:

CREATE TABLE user (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    UNIQUE (username)
)
    COMMENT 'Users who subscribed using the main website';
ALTER TABLE user
    COMMENT 'Users who subscribed using the main website or downloaded the app';

Reference

More articles: