Mysql常用命令

#查询字段名和注释  
select COLUMN_NAME,COLUMN_COMMENT from information_schema.COLUMNS where table_name = '表名' and table_schema = '库名' order by ordinal_position 
#查询整个内容
select * from information_schema.COLUMNS where table_name = '表名' and table_schema = '库名' order by ordinal_position 

#拼接  group_concat(field，SEPARATOR '拼接字符')  
select group_concat(COLUMN_NAME SEPARATOR ';'),group_concat(COLUMN_COMMENT SEPARATOR ';') from information_schema.COLUMNS where table_name = '表名' and table_schema = '库名' order by ordinal_position 

#按顺序排字段拼接 group_concat(field order by field,SEPARATOR '字符')
select group_concat(COLUMN_NAME order by ordinal_position SEPARATOR ';'),group_concat(COLUMN_COMMENT ORDER BY ordinal_position SEPARATOR ';') from information_schema.COLUMNS where table_name = '表名' and table_schema = '库名' order by ordinal_position 

#整个库下的所有表
select TABLE_NAME,group_concat(COLUMN_NAME order by ordinal_position SEPARATOR ';'),group_concat(COLUMN_COMMENT ORDER BY ordinal_position SEPARATOR ';') from information_schema.COLUMNS where table_schema = '库名'  GROUP BY TABLE_NAME

#整个服务器下的所有库中表，每个表的字段和注释的拼接
select TABLE_SCHEMA,TABLE_NAME,group_concat(COLUMN_NAME order by ordinal_position SEPARATOR ';'),group_concat(COLUMN_COMMENT ORDER BY ordinal_position SEPARATOR ';') from information_schema.COLUMNS   GROUP BY TABLE_NAME,TABLE_SCHEMA ORDER BY TABLE_SCHEMA

>查询当前锁
select * from information_schema.innodb_trx
>查询当前执行线程
show processlist;
>查询锁的超时时间
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
>设置锁的超时时间
SET GLOBAL innodb_lock_wait_timeout=300;

-- RC
show variables like '%isolation%'

//设置read uncommitted级别：
set session transaction isolation level read uncommitted;

//设置read committed级别：
set session transaction isolation level read committed;

//设置repeatable read级别：
set session transaction isolation level repeatable read;

//设置serializable级别：
set session transaction isolation level serializable;

show PROCESSLIST;

批量生成修改表结构SQL：
select CONCAT('alter table ',TABLE_NAME,' modify ',COLUMN_NAME,' varchar(128) collate utf8_bin;') 
from information_schema.COLUMNS where TABLE_SCHEMA='sms_tb_sellermsg' and COLUMN_NAME in ('short_url');

mysql的排序规则：
utf8_general_ci（case insensitive）：快，不区分大小写
utf8_general_cs（case sensitive）：区分大小写(b):测试时，没有这种排序规则
utf8_bin：二进制存储，区分大小写

查询表结构信息：
SELECT * FROM information_schema.tables WHERE  table_schema = 'sms_tb_sellermsg' AND table_name = 'order_'

select concat('KILL ',id,';') 
from information_schema.processlist p inner join information_schema.INNODB_TRX x on p.id=x.trx_mysql_thread_id 
where db='sms_tb_business';

show PROCESSLIST;

show OPEN TABLES where In_use > 0;

SELECT * FROM information_schema.PROCESSLIST where length(info) >0 ;

SELECT * FROM information_schema.INNODB_LOCKS;

select * from information_schema.innodb_trx;

show open tables where in_use >0 or name_locked >0;

#查看mysql语句大小

show VARIABLES like '%max_allowed_packet%';