Sql queries the table name in the MySql database and describes the field of column information in the table

The following is an introduction to using SQL query statement 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 self-increasing, whether it is primary key and column description of the table in 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 self-increasing/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 is Sql to get MySql database table name and description of the table in the field name data type column information of several methods, if not what you need, you can also see the following related articles