Help Center/ Relational Database Service/ Troubleshooting/ RDS for MySQL/ SQL Issues/ AUTO_INCREMENT Not Displayed in the Table Structure
Updated on 2023-03-06 GMT+08:00

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 table_name 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.