MySQL 常用脚本

information_schema
CHARACTER_SETS	可用字符集
COLLATIONS	字符集的排序规则
COLLATION_CHARACTER_SET_APPLICABILITY	字符集和排序规则的可设置信息	SHOW COLLATION;
COLUMNS	数据库中所有列及属性
COLUMN_PRIVILEGES	数据库中所有列DML权限
ENGINES	存储引擎描述
EVENTS	调度事件
FILES
GLOBAL_STATUS	服务器状态变量	SHOW GLOBAL STATUS;
GLOBAL_VARIABLES	服务器状态变量	SHOW GLOBAL VARIABLES;
KEY_COLUMN_USAGE	有约束的键列
PARAMETERS	存储过程和函数的参数或返回值
PARTITIONS	分区表分区信息
PLUGINS	服务器插件
PROCESSLIST	当前正在运行的线程
PROFILING	跟踪信息	SHOW PROFILES; SHOW PROFILE;
REFERENTIAL_CONSTRAINTS	外键约束
ROUTINES	存储过程或函数信息
SCHEMATA	数据库部分信息
SCHEMA_PRIVILEGES	方案（数据库）权限
SESSION_STATUS	会话状态变量	SHOW SESSION STATUS;
SESSION_VARIABLES	会话状态变量	SHOW SESSION VARIABLES;
STATISTICS	索引信息	SHOW INDEX FROM mysql.db; SHOW INDEX FROM mysql;
TABLES	表信息	SHOW TABLES FROM mysql; SHOW TABLES LIKE 'user';
TABLESPACES	表空间信息（非 InnoDB）
TABLE_CONSTRAINTS	表约束信息
TABLE_PRIVILEGES	表权限信息
TRIGGERS	触发器信息
USER_PRIVILEGES	用户权限
VIEWS	视图信息
INNODB_CMP	INNODB 压缩信息
INNODB_CMP_RESET	INNODB 压缩信息
INNODB_TRX	INNODB 当前未提交的事务
INNODB_CMPMEM	INNODB 缓冲池中压缩信息
INNODB_CMPMEM_RESET	INNODB 缓冲池中压缩信息
INNODB_LOCK_WAITS	INNODB 锁等待信息
INNODB_LOCKS	INNODB 锁等待信息

查看配置变量及运行状态变量：

#变量(全局)
mysqladmin -uroot -pmysql variables
mysql -uroot -pmysql -e "show global variables;"
select * from information_schema.global_variables;

#运行状态(全局)
mysqladmin -uroot -pmysql extended-status
mysql -uroot -pmysql -e "show global status;"
select * from information_schema.global_status;

#变量(会话)
mysql -uroot -pmysql -e "show session variables;"
select * from information_schema.session_variables;

#运行状态(会话)
mysql -uroot -pmysql -e "show session status;"
select * from information_schema.session_status;

#重置状态(会话等)
flush status;

 

-- 查看当前连接  
select * from information_schema.processlist;  

-- 查看当前正在被锁的事务（锁请求超时后则查不到）  
select * from information_schema.innodb_locks;  

-- 查看当前等待锁的事务（锁请求超时后则查不到）  
select * from information_schema.innodb_lock_waits; 

-- 查看当前未提交的事务（如果死锁等待超时,事务可能还没有关闭）Kill trx_mysql_thread_id
select * from information_schema.innodb_trx; 

-- 查看正在被访问的表  
show open tables where in_use > 0;

 

--查看数据库中所有外键  
select referenced_table_name,referenced_column_name,constraint_name,table_name,column_name
from information_schema.key_column_usage
where constraint_name <> 'PRIMARY';

SELECT CONSTRAINT_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,UPDATE_RULE,DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS 
WHERE CONSTRAINT_SCHEMA='MYSQL';


 --查看数据库中表大小及行数
select table_name,table_rows
,round((data_length / 1024 / 1024), 3) "data_length_MB" 
,round((index_length / 1024 / 1024), 3) "index_length_MB" 
,create_time,engine,table_collation 
from information_schema.tables
where table_schema = 'MYSQL'
order by table_rows desc;

--碎片查询
select table_name,table_rows
,concat(round(DATA_FREE/1024/1024, 2), ' MB') as free_size
,concat(round(DATA_LENGTH/1024/1024, 2), ' MB') as size
,concat(DATA_FREE * 100/DATA_LENGTH,' %') as frag_percent
from information_schema.TABLES
where table_schema='MYSQL' and TABLE_TYPE='BASE TABLE'
order by DATA_LENGTH desc ;

 

--各表索引字段
SELECT TABLE_NAME,INDEX_NAME,NON_UNIQUE,NULLABLE,INDEX_TYPE,GROUP_CONCAT(COLUMN_NAME)
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'mysql' and TABLE_NAME='user'
GROUP BY TABLE_NAME,INDEX_NAME,NON_UNIQUE,NULLABLE,INDEX_TYPE;

 

--存储引擎
show engines;
show variables like '%storage_engine%';
show create table <table_name>;
show table status from <db_name> where name='<table_name>';

--数据库字符集&排序规则
select * from information_schema.schemata;

--查看表存储引擎&字符集
select TABLE_SCHEMA,ENGINE,VERSION,ROW_FORMAT,TABLE_COLLATION,CREATE_TIME 
from information_schema.tables where table_schema='mysql';

 

--查看存储过程/函数/触发器/视图 定义 
select name from mysql.proc where type='PROCEDURE';
select name from mysql.proc where type='FUNCTION';
select * from information_schema.triggers where trigger_name='';
select * from information_schema.views where table_name='';
select * from information_schema.tables where table_name='';

show create procedure proc_name;
show create function func_name;
show create view view_name;

 

-- 导出 MySQL 授权脚本（pt-show-grants）
mysql -B -N -uroot -pmysql -e \
"SELECT CONCAT('\'',user,'\'@\'',host,'\'') FROM mysql.user"  2>&1 \
| grep -v "Using a password" \
| while read line; do mysql -B -N -uroot -pmysql -e "SHOW GRANTS FOR $line" 2>&1 \
| grep -v "Using a password" ; done \
| awk '{ print $0 ";" }'

 

================================================================================

分组排序编号：

--分组排序编号
SELECT @rownum := @rownum + 1 AS rank,t.*
FROM mysql.user t, (SELECT @rownum := 0) r;

--分组排序编号
SELECT
 @row_num := IF(@prev_value=concat_ws('',t.table_schema),@row_num+1,1) AS RowNumber
,@prev_value := concat_ws('',t.table_schema )
,t.table_schema,t.table_name
FROM information_schema.`TABLES` t,(SELECT @row_num := 1) x,(SELECT @prev_value := '') y
ORDER BY t.table_schema;

"Merge into" 方法： 表中必须有主键或唯一键(默认以主键/唯一键关联两个表)

-- 表中必须有主键或唯一键(默认以tablea 的主键/唯一键关联两个表判断)
insert into tablea(id,name,addr,createtime) -- 如：id 为主键，默认A、B表以id关联判断
select uid,name,addr,createtime 
from tableb b -- B表给个别名方便
on duplicate key update -- 若插入重复则不插入，而是更新A表，更新字段如下
 name = b.name
,addr = b.addr
,code = b.usercode
,status = 1;

mysql 同列字符相加：

create table test(id int,name varchar(10));

insert into test value(1,'aa'),(1,'bb'),(1,'aa'),(2,'bb'),(2,'dd'),(3,'gg'),(3,null);

select * from test

SELECT id, GROUP_CONCAT(name SEPARATOR ', ') usernames FROM test GROUP BY id;

SELECT DISTINCT T0.id, (SELECT GROUP_CONCAT(DISTINCT name SEPARATOR ', ') FROM test T1 WHERE T1.id = T0.id) AS 'nameAll' 
FROM test T0;

游标：

DROP PROCEDURE  IF EXISTS proc_test;
delimiter //
CREATE PROCEDURE proc_test()
BEGIN
	declare flag boolean default true;
	declare v_id int;
	declare cur cursor for select id from tablename ;
	declare continue handler for not found set flag=false;
	open cur;
	fetch cur into v_id;
	while flag do
		select v_id;
		fetch cur into v_id;
	        -- commit;
	end while;
	close cur;
END //
delimiter ;

动态SQL执行：单独一个存储过程更方便

CREATE PROCEDURE sp_execsql( p_sql varchar(5000))
BEGIN
  SET @tquery = p_sql;
  PREPARE stmt FROM @tquery;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

存储过程事务控制：

CREATE PROCEDURE sp_test()
BEGIN
	declare p_mk int default 0;
	declare continue handler for sqlexception set p_mk=1;

	START TRANSACTION;
  -- sql statement

	IF p_mk = 1 THEN
		ROLLBACK; 
	ELSE 
		COMMIT; 
	END IF;
END

逗号分隔的列转为多行

# 逗号分隔的列转为多行
-- drop temporary table tmp; 
create temporary table tmp(id int,cols varchar(30));
insert into tmp values(1,'a,b,c'),(2,'e,j'),(3,'a,e,d'),(4,'b,b,c');

select distinct id,cols,substring_index(substring_index(a.`cols`,',',b.help_topic_id+1),',',-1) as new_cols
from tmp a join mysql.help_topic b on b.help_topic_id < (length(a.`cols`) - length(replace(a.`cols`,',',''))+1);