SQL queries the Table Name and description table field (column) Information in the MySql database, sqlmysql

SQL queries the Table Name and description table field (column) Information in the MySql database, sqlmysql

The following describes how to use an SQL query statement to obtain the name of a table in a Mysql database, table description, field ID, field name, data type, length, precision, whether it can be null, default value, auto-increment, primary key, column description

I. 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/auto-increment/whether it is a primary key/column description)

Method 1:

Show full columns from Table Name

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 DefaultValfrom information_schema.columns where table_schema = 'database name' and table_name = 'table name' order by ORDINAL_POSITION asc

The preceding methods are used to obtain the column information, such as the table name and field name in the description table, in the MySql database. If you do not need them, read the following articles.