mysql如何快速查询

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,CHARACTER_SET_NAME

from information_schema.COLUMNS

where (CHARACTER_SET_NAME is null or CHARACTER_SET_NAME <> ‘utf8‘)

and TABLE_SCHEMA not in (‘information_schema‘,‘performance_schema‘,‘test‘,‘mysql‘,‘sys‘);

show variables like ‘collation_%‘;

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME

from information_schema.COLUMNS

where (COLLATION_NAME is null or COLLATION_NAME <> ‘utf8_general_ci‘)

and TABLE_SCHEMA not in (‘information_schema‘,‘performance_schema‘,‘test‘,‘mysql‘,‘sys‘);

select GRANTEE,PRIVILEGE_TYPE,concat(TABLE_SCHEMA,‘-‘,TABLE_NAME,‘-‘,COLUMN_NAME) from COLUMN_PRIVILEGES where PRIVILEGE_TYPE not in (‘select‘,‘insert‘,‘update‘)

union

select GRANTEE,PRIVILEGE_TYPE,TABLE_SCHEMA from SCHEMA_PRIVILEGES where PRIVILEGE_TYPE not in (‘select‘,‘insert‘,‘update‘)

union

select GRANTEE,PRIVILEGE_TYPE,concat(TABLE_SCHEMA,‘-‘,TABLE_NAME) from TABLE_PRIVILEGES where PRIVILEGE_TYPE not in (‘select‘,‘insert‘,‘update‘)

union

select GRANTEE,PRIVILEGE_TYPE,concat(‘user‘) from USER_PRIVILEGES where PRIVILEGE_TYPE not in (‘select‘,‘insert‘,‘update‘);

select TABLE_NAME,ENGINE

from information_schema.tables

where ENGINE!=‘innodb‘ and TABLE_SCHEMA not in (‘information_schema‘,‘performance_schema‘,‘test‘,‘mysql‘, ‘sys‘);

select a.TABLE_SCHEMA,a.TABLE_NAME,a.CONSTRAINT_TYPE,a.CONSTRAINT_NAME,b.REFERENCED_TABLE_NAME,b.REFERENCED_COLUMN_NAME

from information_schema.TABLE_CONSTRAINTS a LEFT JOIN information_schema.KEY_COLUMN_USAGE b

ON a.CONSTRAINT_NAME=b.CONSTRAINT_NAME where a.CONSTRAINT_TYPE=‘FOREIGN KEY‘;

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,REFERENCED_TABLE_SCHEMA,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME

from information_schema.KEY_COLUMN_USAGE

where REFERENCED_TABLE_SCHEMA is not null

and REFERENCED_TABLE_NAME is not null

and REFERENCED_COLUMN_NAME is not null and table_schema not in (‘information_schema‘,‘performance_schema‘,‘test‘,‘mysql‘, ‘sys‘);

select USER,HOST,DB from processlist where TIME>2;

select TABLE_SCHEMA,TABLE_NAME from information_schema.tables

where TABLE_NAME  not in (select distinct(any_value(TABLE_NAME))  from information_schema.STATISTICS group by INDEX_NAME)

and TABLE_SCHEMA not in (‘sys‘,‘mysql‘,‘information_schema‘,‘performance_schema‘);

select TABLE_SCHEMA,TABLE_NAME,group_concat(INDEX_NAME)

from  information_schema.STATISTICS where TABLE_SCHEMA not in (‘sys‘,‘mysql‘,‘information_schema‘,‘performance_schema‘)  group by TABLE_NAME ;