MySQL-常用的一些查询及拼接语句

1.查询所有表的字符集/校对规则

select table_schema,table_name,TABLE_COLLATION 
from information_schema.tables 
where table_schema not in ('sys','mysql','information_schema','performance_schema'); 

2.所有字段的字符集

select table_schema,table_name,column_name,CHARACTER_SET_NAME 
from information_schema.columns 
where table_schema not in ('sys','mysql','information_schema','performance_schema') ;

3.没有主键的表

SELECT
	t1.table_schema,
	t1.table_name
FROM
	information_schema. TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
	t2.table_name IS NULL
AND t1.TABLE_SCHEMA NOT IN (
	'information_schema',
	'performance_schema',
	'mysql',
	'sys'
) ;

4.表字段的默认值

select table_schema,table_name,COLUMN_NAME,COLUMN_DEFAULT 
from information_schema.columns 
where table_schema not in ('sys','mysql','information_schema','performance_schema') and table_schema='zabbix';

5.有外键的表

select * 
from information_schema.key_column_usage 
where referenced_table_name <> '' and table_schema not in ('sys','mysql','information_schema','performance_schema');

6.查询当前大事务

select * from information_schema.processlist where command <>'Sleep' order by time ;

select * 
from information_schema.processlist 
where time > 10 and command <>'Sleep' and command <> 'Binlog Dump GTID' order by time ;

7.查询 row format

select table_schema,table_name,row_format 
from information_schema.tables 
where table_schema not in ('sys','mysql','information_schema','performance_schema');

8.查询文件格式(针对5.7以前版本)

select distinct t.TABLE_SCHEMA,t.table_name,i.FILE_FORMAT,t.ROW_FORMAT 
from INNODB_SYS_TABLESPACES i 
join tables t on i.ROW_FORMAT=t.ROW_FORMAT 
where t.table_schema not in ('sys','mysql','information_schema','performance_schema');

9.表创建时间

select table_schema,table_name,CREATE_TIME 
from information_schema.tables 
where table_schema not in ('sys','mysql','information_schema','performance_schema');

10.约束信息

select * 
from information_schema.TABLE_CONSTRAINTS 
where table_SCHEMA not in ('sys','mysql','information_schema','performance_schema');

11. 查看binlog或relaylog大事务

mysqlbinlog db-binlog.000002 | grep "GTID$(printf '\t')last_committed" -B 1  | egrep -E '^# at|^#22' | awk '{print $1,$2,$3}' | sed 's/server//' | sed 'N;s/\n/ /' | awk 'NR==1 {tmp=$1} NR>1 {print $4,$NF,($3-tmp);tmp=$3}' | sort -k 3 -n -r | head -n 20

12.没有索引的表

select table_schema,table_name  
from information_schema.tables 
WHERE TABLE_SCHEMA not in ('information_schema','mysql','performance_schema','sys')  and index_length=0;

注：以下语句适用于5.7版本 低版本可能稍许不同。

13. 拼接查询所有用户

SELECT DISTINCT CONCAT('User: \'',USER,'\'@\'',HOST,'\';') AS QUERY_User FROM mysql.user;
/*当拼接字符串中出现''时 需使用\转义符*/

14. 拼接DROP table

SELECT CONCAT('DROP table ',TABLE_NAME,';') 
FROM information_schema.tables 
WHERE TABLE_SCHEMA = 'test';

15. 拼接创建数据库语句(创建和原实例相同的数据库时使用)

SELECT CONCAT('create database ','`',SCHEMA_NAME,'`',' DEFAULT CHARACTER SET ',DEFAULT_CHARACTER_SET_NAME,';') AS CreateDatabaseQuery
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME NOT IN ('information_schema','performance_schema','mysql','sys');

16. 拼接创建用户的语句

SELECT CONCAT('create user \'',user,'\'@\'',Host,'\'' ' IDENTIFIED BY PASSWORD \'',authentication_string,'\';') AS CreateUserQuery 
FROM mysql.`user` 
WHERE `User` NOT IN ('root','mysql.session','mysql.sys');
/*有密码字符串哦 在其他实例执行 可直接创建出与本实例相同密码的用户*/

17. 导出权限shell脚本

#!/bin/bash  
#Function export user privileges  

pwd=123456 
expgrants()  
{  
  mysql -B -u'root' -p${pwd} -h 124.222.117.108 -N $@ -e "SELECT CONCAT(  'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u'root' -p${pwd} -h 124.222.117.108 $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'  
}  
 
expgrants > /tmp/grants.sql
echo "flush privileges;" >> /tmp/grants.sql