mysql数据库 操作常用命令

查看数据库文件大小

SELECT
  file_name,
  concat(TOTAL_EXTENTS, 'M') as 'FIle_size'
FROM
  INFORMATION_SCHEMA.FILES
order by
  TOTAL_EXTENTS DESC;

查看所有数据库容量大小

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
order by data_length desc, index_length desc;

查看指定数据库容量大小

例：查看库容量大小

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
where table_schema='db_name';

查看指定数据库各表容量大小

例：查看库各表容量大小

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='db_name'
order by data_length desc, index_length desc;

表数量对比

SELECT
  TABLE_SCHEMA,
  count(*)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')
GROUP BY TABLE_SCHEMA
ORDER BY 2 DESC;

触发器对比

SELECT
  TRIGGER_SCHEMA,
  count(*)
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')
GROUP BY TRIGGER_SCHEMA
ORDER BY 2 DESC;

字符集对比

SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

表中数据量对比

查询对比2917-07-08日之前表的数据量看两库是否一致。

select 'mobile_card.wepay_wallet_record' `name`, count(*) CNT from mobile_card.wepay_wallet_record where create_time <'2017-07-08' union all
select 'mobile_card.mcc_wescore_record' `name`, count(*) CNT from mobile_card.mcc_wescore_record where id <100712118 union all
select 'mobile_card.mcc_customer_grant' `name`, count(*) CNT from mobile_card.mcc_customer_grant where id <39691660 union all
select 'mobile_card.mcc_credit_score' `name`, count(*) CNT from mobile_card.mcc_credit_score where id <35485079 union all
select 'mobile_card.mcc_consume_repayment' `name`, count(*) CNT from mobile_card.mcc_consume_repayment where id <29256981 union all
select 'mobile_card.mcc_customer' `name`, count(*) CNT from mobile_card.mcc_customer where id <23204782 union all
select 'mobile_card.mcc_repayment_record' `name`, count(*) CNT from mobile_card.mcc_repayment_record where id <26479142 union all
select 'mobile_card.mcc_credit_card' `name`, count(*) CNT from mobile_card.mcc_credit_card where id <15473828 union all
select 'mobile_card.mcc_customer_card' `name`, count(*) CNT from mobile_card.mcc_customer_card where id <3509303 ;

创建数据库命令

CREATE DATABASE IF NOT EXISTS db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

GRANT ALL PRIVILEGES ON *.* TO 'app_rw'@'%' IDENTIFIED BY PASSWORD ""