一些特殊查询SQL语句

# 监控授权
grant select,process,replication client on *.* to monitor@'%' identified by 'xxxxxxxxx';

# 查看processlist
select * from information_schema.PROCESSLIST where info is not null

# 查看锁等待
select * from sys.innodb_lock_waits;
show engine innodb status;

# 通过show processlist看不到TableA上有任何操作，但实际上存在有未提交的事务，可以在 information_schema.innodb_trx中查看到,可以kill sid：
select * from information_schema.innodb_trx;

'通过这个可以一次性显示多个kill sid语句'
select concat('kill ',trx_mysql_thread_id,';') as kill_id from information_schema.INNODB_TRX where trx_lock_structs=0 and trx_weight=0 and trx_rows_locked=0 and trx_rows_modified=0 and trx_state='RUNNING';

# 通过show processlist看不到TableA上有任何操作，在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中，对TableA进行了一个失败的操作（比如查询了一个不存在的字段），这时事务没有开始，但是失败语句获取到的锁依然有效，没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。可以kill sid：
select * from performance_schema.events_statements_current;

# 查看各个数据库大小：
select table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

# 查看指定数据库中所有表大小：
select table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='test_temp1'


# 建立一个相同结构的表
CREATE TABLE members_tmp LIKE members;

# 两个表互换名称
RENAME TABLE members TO members_bak,members_tmp TO members; 


-------
# 将1个大表,分成100个表,并导入数据
<?php 
for(i=0;i< 100; i++ ){ 
//echo "CREATE TABLE db2.members{i} LIKE db1.members<br>"; 
echo "INSERT INTO members{i} SELECT * FROM members WHERE mid%100={i}<br>"; 
} 
?>

------
# 查询相同数据
select [字段名]或[*] from [表名称1] 
intersect
select [字段名]或[*] from [表名称2] 

# 查询不同数据
select [字段名]或[*] from [表名称1]
except
select [字段名2]或[*] from [表名称]