MariaDB Comment: Syntax, Table and Column Comments

A MariaDB comment starts with # or a double dash plus a space (-- ) and runs to the end of the line, or sits between /* and */ across lines:

# Hash: comment to the end of the line
-- Double dash: the space after it is required
SELECT 'Hannah Brooks' AS customer;
SELECT 2 /* inline */ + 1 AS total;
/* C-style comments
   can span lines */
SELECT 2 /*M! +1 */ AS mariadb_only;

Three result sets came back:

+---------------+
| customer      |
+---------------+
| Hannah Brooks |
+---------------+
+-------+
| total |
+-------+
|     3 |
+-------+
+--------------+
| mariadb_only |
+--------------+
|            3 |
+--------------+

The last line is an executable comment: MariaDB runs the +1, other databases skip it. Tested on MariaDB 13.0.2 on Windows 11.

Need a note stored on a table or column instead? That’s the COMMENT clause, covered from the sample table down.

MariaDB Comment Syntax: Hash, Double Dash and /* */

Style	Scope	Watch for
# text	to end of line	MariaDB/MySQL only, not standard SQL
-- text	to end of line	needs a space or control character after --
/* text */	inline or many lines	nesting is “not supported or recommended” (docs)
/*! code */	executable	runs on MariaDB and MySQL
/*M! code */	executable	runs on MariaDB only

Gotcha: a MariaDB Double-Dash Comment Needs a Space

Skip the space and -- stops being a comment. The parser reads two minus signs:

SELECT 10--2 AS no_space;
SELECT 10
-- 2
  AS with_space;
SELECT full_name --customer name
FROM customers LIMIT 1;

Two results, then an error:

+----------+
| no_space |
+----------+
|       12 |
+----------+
+------------+
| with_space |
+------------+
|         10 |
+------------+
--------------
SELECT full_name --customer name
FROM customers LIMIT 1
--------------

ERROR 1054 (42S22) at line 5: Unknown column 'customer' in 'SELECT'

10--2 silently returns 12. That’s the scary one, because nothing fails and the number just looks a little high.

The --customer line fails with error 1054 because customer is now an identifier. Other typos land on MariaDB error 1064 instead.

Executable Comments: /*! */ and /*M! */ With Version Numbers

A number after ! sets the minimum server version. /*! takes 5 digits (50101 = 5.1.1); /*M! takes 6 (100500 = 10.5.0):

SELECT 1 /*! +1 */ AS both_servers,
       1 /*!50101 +1 */ AS mysql_5_1_up,
       1 /*!50700 +1 */ AS range_ignored,
       1 /*M!100500 +1 */ AS maria_10_5_up,
       1 /*M!999999 +1 */ AS future_version;

One row:

+--------------+--------------+---------------+---------------+----------------+
| both_servers | mysql_5_1_up | range_ignored | maria_10_5_up | future_version |
+--------------+--------------+---------------+---------------+----------------+
|            2 |            2 |             1 |             2 |              1 |
+--------------+--------------+---------------+---------------+----------------+

• /*!50700 +1 */ did nothing. MariaDB ignores MySQL-style comments numbered 50700 to 99999 (MySQL 5.7+ features), per the docs.
• /*M!999999 names a version that doesn’t exist yet, so it’s skipped.
• Keep the ; outside: /*M!100100 SELECT 1 */; works, a delimiter inside is a syntax error.

You’ll see these all over dump files, like /*!40101 SET NAMES utf8mb4 */. See backing up a MariaDB database for where those dumps come from.

Sample Table With Column and Table Comments

Every COMMENT example below uses this table. Column comments go after the type, the table comment after the closing parenthesis, the same spots CREATE TABLE in MariaDB uses for other options:

CREATE TABLE customers (
  id        INT PRIMARY KEY,
  full_name VARCHAR(30) NOT NULL COMMENT 'First and last name',
  city      VARCHAR(20) NOT NULL,
  state     CHAR(2) NOT NULL DEFAULT 'TX' COMMENT 'USPS code',
  credit    DECIMAL(8,2) COMMENT 'Store credit in USD'
) COMMENT = 'Retail customers, US only';
INSERT INTO customers VALUES
(1,'Hannah Brooks','Austin','TX',25.00),
(2,'Tyler Reed','Fresno','CA',0.00),
(3,'Grace Miller','Spokane','WA',140.50),
(4,'Marcus Bennett','Dallas','TX',NULL),
(5,'Olivia Carter','Albany','NY',12.75);

How Do I Show Comments in MariaDB?

You’ve got three ways to read them back. information_schema gives clean output you can filter, join and export:

SELECT COLUMN_NAME, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customers';

SELECT TABLE_NAME, TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE();

Two result sets:

+-------------+---------------------+
| COLUMN_NAME | COLUMN_COMMENT      |
+-------------+---------------------+
| id          |                     |
| full_name   | First and last name |
| city        |                     |
| state       | USPS code           |
| credit      | Store credit in USD |
+-------------+---------------------+
+------------+---------------------------+
| TABLE_NAME | TABLE_COMMENT             |
+------------+---------------------------+
| customers  | Retail customers, US only |
+------------+---------------------------+

Columns without a comment return an empty string, not NULL. For one table, SHOW FULL COLUMNS adds a Comment column:

SHOW FULL COLUMNS FROM customers WHERE Field IN ('state', 'credit');

+--------+--------------+-----------------------+------+-----+---------+-------+---------------------------------+---------------------+
| Field  | Type         | Collation             | Null | Key | Default | Extra | Privileges                      | Comment             |
+--------+--------------+-----------------------+------+-----+---------+-------+---------------------------------+---------------------+
| state  | char(2)      | utf8mb4_uca1400_ai_ci | NO   |     | TX      |       | select,insert,update,references | USPS code           |
| credit | decimal(8,2) | NULL                  | YES  |     | NULL    |       | select,insert,update,references | Store credit in USD |
+--------+--------------+-----------------------+------+-----+---------+-------+---------------------------------+---------------------+

SHOW CREATE TABLE prints every comment inside the DDL. I ran it with -E (vertical output):

SHOW CREATE TABLE customers;

*************************** 1. row ***************************
       Table: customers
Create Table: CREATE TABLE `customers` (
  `id` int(11) NOT NULL,
  `full_name` varchar(30) NOT NULL COMMENT 'First and last name',
  `city` varchar(20) NOT NULL,
  `state` char(2) NOT NULL DEFAULT 'TX' COMMENT 'USPS code',
  `credit` decimal(8,2) DEFAULT NULL COMMENT 'Store credit in USD',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci COMMENT='Retail customers, US only'

Plain SHOW COLUMNS and DESCRIBE leave comments out, as in checking a MariaDB column data type.

MariaDB ALTER TABLE COMMENT: Add or Change a Table Comment

A table comment is a table option, so you can set it on its own:

ALTER TABLE customers
  COMMENT = 'Retail and online customers, US only',
  ALGORITHM = INSTANT;

SELECT TABLE_COMMENT FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customers';

Check your work:

+--------------------------------------+
| TABLE_COMMENT                        |
+--------------------------------------+
| Retail and online customers, US only |
+--------------------------------------+

MariaDB 13.0.2 accepted ALGORITHM = INSTANT, so no rows were copied. Set COMMENT = '' to remove it.

MariaDB Comment on Column: MODIFY Drops What You Don’t Repeat

There’s no comment-only statement for a column. You redefine it with MODIFY or CHANGE COLUMN. Write only the type and comment, and you lose the rest:

ALTER TABLE customers
  MODIFY state CHAR(2) COMMENT 'Two-letter USPS code';

SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customers'
  AND COLUMN_NAME = 'state';

The state column after the ALTER:

+-------------+-------------+----------------+----------------------+
| COLUMN_NAME | IS_NULLABLE | COLUMN_DEFAULT | COLUMN_COMMENT       |
+-------------+-------------+----------------+----------------------+
| state       | YES         | NULL           | Two-letter USPS code |
+-------------+-------------+----------------+----------------------+

state is now nullable and its 'TX' default is gone. Copy the column line from SHOW CREATE TABLE and change only the comment:

ALTER TABLE customers
  MODIFY state CHAR(2) NOT NULL DEFAULT 'TX'
    COMMENT 'Two-letter USPS code',
  ALGORITHM = INSTANT;

SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'customers'
  AND COLUMN_NAME = 'state';

Everything kept:

+-------------+-------------+----------------+----------------------+
| COLUMN_NAME | IS_NULLABLE | COLUMN_DEFAULT | COLUMN_COMMENT       |
+-------------+-------------+----------------+----------------------+
| state       | NO          | 'TX'           | Two-letter USPS code |
+-------------+-------------+----------------+----------------------+

Same result as before, plus the comment, and it also ran with ALGORITHM = INSTANT. Done.

Comment Length Limits: 2048 and 1024

The docs give 2,048 characters for a table comment and 1,024 for a column or index comment. I built one of each, one character too long:

SET @t = CONCAT('ALTER TABLE customers COMMENT ''', REPEAT('a', 2049), '''');
PREPARE s FROM @t; EXECUTE s;
SET @c = CONCAT('ALTER TABLE customers MODIFY credit DECIMAL(8,2) COMMENT ''',
                REPEAT('a', 1025), '''');
PREPARE s FROM @c; EXECUTE s;

Two errors:

--------------
EXECUTE s
--------------

ERROR 1628 (HY000) at line 2: Comment for table 'customers' is too long (max = 2048)
--------------
EXECUTE s
--------------

ERROR 1629 (HY000) at line 5: Comment for field 'credit' is too long (max = 1024)

Both are hard errors. My server runs the default sql_mode, which includes STRICT_TRANS_TABLES, and you’ll find the table still has its old comments afterward.

MariaDB Database Comment (10.5.0+)

CREATE DATABASE and ALTER DATABASE take a COMMENT from MariaDB 10.5.0. It’s stored in the database’s db.opt file and shown in SCHEMATA:

ALTER DATABASE df_comment_demo COMMENT = 'Demo data for the comment examples';

SELECT SCHEMA_NAME, SCHEMA_COMMENT
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'df_comment_demo';

+-----------------+------------------------------------+
| SCHEMA_NAME     | SCHEMA_COMMENT                     |
+-----------------+------------------------------------+
| df_comment_demo | Demo data for the comment examples |
+-----------------+------------------------------------+

New database? Add COMMENT 'text' to MariaDB CREATE DATABASE. The limit is 1,024 bytes.

Can a MariaDB View Have a Comment?

No. CREATE VIEW has no COMMENT option. I tried it anyway, so you can see what you’d get:

CREATE VIEW tx_customers AS
  SELECT id, full_name FROM customers WHERE state = 'TX';

SELECT TABLE_NAME, TABLE_TYPE, TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE();

CREATE OR REPLACE VIEW tx_customers COMMENT 'Texas only' AS
  SELECT id, full_name FROM customers WHERE state = 'TX';

+--------------+------------+---------------------------+
| TABLE_NAME   | TABLE_TYPE | TABLE_COMMENT             |
+--------------+------------+---------------------------+
| customers    | BASE TABLE | Retail customers, US only |
| tx_customers | VIEW       | VIEW                      |
+--------------+------------+---------------------------+
--------------
CREATE OR REPLACE VIEW tx_customers COMMENT 'Texas only' AS
  SELECT id, full_name FROM customers WHERE state = 'TX'
--------------

ERROR 1064 (42000) at line 8: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COMMENT 'Texas only' AS
  SELECT id, full_name FROM customers WHERE state = 'TX'' at line 1

TABLE_COMMENT just says VIEW. Keep the note in your own docs table instead. More on views in creating views in MariaDB.

MariaDB COMMENT Gotchas From These Runs

• The double dash needs the space. Without it you get math or error 1054.
• MODIFY resets the NULL and DEFAULT you don’t repeat.
• A comment in an app’s query is just text. It protects nothing; parameterized queries stop SQL injection.
• Long comments fail with 1628 (table) or 1629 (column).

Copy-ready version of the safe column change:

ALTER TABLE customers
  MODIFY state CHAR(2) NOT NULL DEFAULT 'TX' COMMENT 'Two-letter USPS code',
  ALGORITHM = INSTANT;

Next Step: Rename a Column Without Losing Its Comment

Renames hit the same trap, so read renaming a MariaDB column next. Related:

• Add a column (with its COMMENT) in MariaDB
• MariaDB reserved words that break column names
• Column descriptions in SQL Server

Frequently Asked Questions