MySQL常用SQL

 

-- 查看表结构
SELECT
	c.table_schema 模式,
	c.TABLE_NAME 表名,	
	REPLACE(REPLACE(t.TABLE_COMMENT, CHAR(10), ''), CHAR(13), '') 表说明,
	t.table_rows `数据量(预估)`,
	c.COLUMN_NAME 字段名,
	c.IS_NULLABLE 允许空值,
	c.DATA_TYPE 数据类型,
	ifnull( c.CHARACTER_MAXIMUM_LENGTH, concat( c.NUMERIC_PRECISION, ',', c.NUMERIC_SCALE ) ) 长度,
	c.COLUMN_DEFAULT 默认值,
	c.ORDINAL_POSITION 顺序,
	REPLACE(REPLACE(c.COLUMN_COMMENT, CHAR(10), ''), CHAR(13), '') 说明注释
FROM
	information_schema.COLUMNS c
	LEFT JOIN information_schema.`TABLES` t ON t.TABLE_NAME = c.TABLE_NAME and t.table_schema = c.table_schema
WHERE
	c.table_schema = 'ods_rx'
	order by c.TABLE_NAME, c.ORDINAL_POSITION;

-- 查看表数据量
SELECT TABLE_NAME,TABLE_ROWS,now() FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mysql' and table_type = 'BASE TABLE' ORDER BY TABLE_ROWS desc;

-- 生成索引创建语句
SET @table_schema := 'ods_bw';
SET @table_name := 'match_info';
 
select concat('ALTER TABLE `',@table_schema,'`.`',@table_name,'` ADD INDEX `',@table_name,'_index_',column_name,'`(`',column_name,'`);') 
		  ,concat('-- ',data_type) data_type
			,column_comment
from information_schema.columns where table_schema = @table_schema and table_name = @table_name and column_key = '';

-- 锁表处理
show OPEN TABLES where In_use > 0;

show processlist;

kill 1331;

-- 生成删除重复数据语句
select concat('delete from ',t.table_schema,'.',t.TABLE_NAME,' where id in (
select id from (
select id from ',t.table_schema,'.',t.TABLE_NAME,' 
where id not in (
  select max(id)
from ',t.table_schema,'.',t.TABLE_NAME,' 
group by ',group_concat(t.column_name),'		
)
) as temp
);'),
t.TABLE_SCHEMA,t.TABLE_NAME,group_concat(t.column_name) from information_schema.columns t 
where t.table_schema = 'ods_rx' 
and t.column_name not in ('id','update_time_etl','del_flag_etl')
and t.TABLE_NAME not in ('tasksms')
group by t.table_schema,t.TABLE_NAME;