【mysql】常用命令

一、查看是否有死锁情况及解决

1.show processlist;

查看是否有Waiting for table metadata lock | ALTER TAB等；

2.kill id；

其他查询语句：

show status like '%lock%';

show status like 'Table%';

二、查看表详情

desc tablename；

show full columns from tablename;

三、查看数据库、表大小

--数据库大小

SELECT table_schema                                        "DB Name",     Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"  FROM   information_schema.tables  GROUP  BY table_schema;

--表大小

SELECT table_schema as `Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES where table_schema = 'purge_qms' ORDER BY (data_length + index_length) DESC ;

四、查看数据库中的存储过程和触发器

show PROCEDURE status;

show triggers;

五、生成8位16进制编码及两位随机数

select CONCAT(lpad(concat(conv(left(number, 4), 10, 16),""),8,0),lpad(floor(rand()*100),2,0))from dual;

number替换为序列号，如输入1，即得到00000001+随机数两位

六、查看数据中含有字段名为customer_id的数据库实例名及表名

SELECT TABLE_SCHEMA,TABLE_NAME from information_schema.columns where COLUMN_NAME='customer_id';