Sql query MySql database table name and description table field (column) information

The following is an introduction to using SQL query statements to obtain the table name, table description, field ID, field name, data type, length, precision, whether it can be null, default value, whether it is auto-increment, whether it is a primary key, and column description of the table in the Mysql database.

1. Query table information (table name/table description)

SELECT table_name name,TABLE_COMMENT value FROM INFORMATION_SCHEMA.TABLES WHERE table_type='base table' 
and table_schema = 'database name' order by table_name asc

2. Query field information (field ID/field name/data type/length/precision/whether it can be null/default value/whether it is auto-increment/whether it is primary key/column description)

Method 1:

SHOW FULL COLUMNS FROM 表名

Method 2:

select ORDINAL_POSITION as Colorder, Column_Name as ColumnName, data_type as TypeName, COLUMN_COMMENT as DeText,
(case when data_type = 'float' or data_type = 'double' or data_type = 'decimal' then NUMERIC_PRECISION else CHARACTER_MAXIMUM_LENGTH end ) as length,
NUMERIC_SCALE as Scale,(case when EXTRA='auto_increment' then 1 else 0 end) as IsIdentity,(case when COLUMN_KEY='PRI' then 1 else 0 end) as IsPK,
(case when IS_NULLABLE = 'NO' then 0 else 1 end)as CanNull,COLUMN_DEFAULT as DefaultVal
from information_schema.columns where table_schema = 'Database name' and table_name = 'Table name' order by ORDINAL_POSITION asc

The above are several ways for Sql to obtain table names in the MySql database and column information such as field names and data types in the description table. If this is not what you need, you can also read the following related articles