Displays the column information of a table. You can use the LIKE clause to search the column names matching it. If you use the WHERE clause, you can search column names with more general terms like, "General Considerations for All SHOW Statements." If you use the FULL keyword, the additional information of a column will be displayed as follows:
SHOW FIELDS is the same command as SHOW COLUMNS.
The DESCRIBE(abbreviated DESC) statement and the EXPLAIN statement provide similar information to SHOW COLUMNS.
SHOW COLUMNS {FROM | IN} tbl_name [LIKE 'pattern' | WHERE expr]
The following is the result of a query for the demodb.
SHOW COLUMNS FROM athlete;
Field Type Null Key Default Extra
====================================================================================================================================
'code' 'INTEGER' 'NO' 'PRI' NULL 'auto_increment'
'name' 'STRING(40)' 'NO' '' NULL ''
'gender' 'CHAR(1)' 'YES' '' NULL ''
'nation_code' 'CHAR(3)' 'YES' '' NULL ''
'event' 'STRING(30)' 'YES' '' NULL ''
SHOW COLUMNS FROM athlete LIKE '%c%';
Field Type Null Key Default Extra
====================================================================================================================================
'code' 'INTEGER' 'NO' 'PRI' NULL 'auto_increment'
'nation_code' 'CHAR(3)' 'YES' '' NULL ''
SHOW COLUMNS FROM athlete WHERE "type" = 'INTEGER' and "key"='PRI' AND extra='auto_increment';
Field Type Null Key Default Extra
====================================================================================================================================
'code' 'INTEGER' 'NO' 'PRI' NULL 'auto_increment'