MySQL queries the attributes of all fields in the database

In large projects, there are hundreds of tables in the database , Want the same field name in the statistics database , Different lengths or types , All field properties . Use simple SQL Help us make statistics .

1. Query the properties of all fields in a database （ Specify database name ）, If you want to query all where The conditions are good

select * from information_schema.columns 
where table_schema= ' Database database name '

2. Query the attributes of all fields in the specified table in the database （ Specify database name and table name ）

select * from information_schema.columns 
where table_schema= ' Database database name ' and table_name = ' Table name '

3. Query the attribute whose specific column condition is a field name in the database

select table_schema,table_name,column_name,column_type,column_comment 
from information_schema.columns 
where TABLE_SCHEMA=' Database name ' and column_name = ' Field name ';

4. Query specific columns in the database （ Such as ： Field name 、 Field type , Length size 、 Field comments, etc ）

select table_schema,table_name,column_name,column_type,column_comment from information_schema.columns 
where table_schema= ' Database database name '

• table_schema： Database database name
• table_name： Table name
• column_name： Field name
• column_type： Field properties （ Contains the field type and length ）
• column_comment ： Annotation fields （ Field description ）
• data_type： Field type
• column_key： The primary key of the field （PRI Primary key ）
• is_nullable： Is the field empty

If there is any mistake, please correct it ~~
Learning record

=======
Query a table in the database that does not contain a field

SELECT
	table_name,
	column_name 
FROM
	information_schema.COLUMNS 
WHERE
	table_schema = 'database' 
	AND table_name NOT IN ( SELECT table_name FROM information_schema.COLUMNS WHERE table_schema = 'database' AND column_name = ' Field name ' GROUP BY table_name )