AUTO_INCREMENT Not Displayed in the Table Structure

Scenario

When a table was created, AUTO_INCREMENT was set to 1. After show create table was executed, AUTO_INCREMENT was not displayed in the table structure.

A table was created:

After show create table xxx was executed, AUTO_INCREMENT was not displayed in the table structure:

Possible Causes

sql_mode was set to NO_FIELD_OPTIONS.

Valid values for sql_mode are as follows:

• NO_FIELD_OPTIONS: Do not print MySQL-specific column options in the output of SHOW CREATE TABLE.
• NO_KEY_OPTIONS: Do not print MySQL-specific index options in the output of SHOW CREATE TABLE.
• NO_TABLE_OPTIONS: Do not print MySQL-specific table options (such as ENGINE) in the output of SHOW CREATE TABLE.

Solution

Change the value of sql_mode.