mysql常用命令

查询库大小

select TABLE_SCHEMA,
concat(truncate(sum(data_length) / 1024 / 1024 / 1024, 2), ' GB')  as data_size,
concat(truncate(sum(index_length) / 1024 / 1024 / 1024, 2), ' GB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_size desc;
select concat(truncate(sum(data_length) / 1024 / 1024 / 1024, 2), ' GB')  as data_size,
concat(truncate(sum(index_length) / 1024 / 1024 / 1024, 2), ' GB') as index_size
from information_schema.tables;
select table_schema                                        as '数据库',
sum(table_rows)                                     as '记录数',
sum(truncate(data_length / 1024 / 1024 / 1024, 2))  as '数据容量(GB)',
sum(truncate(index_length / 1024 / 1024 / 1024, 2)) as '索引容量(GB)'
from information_schema.tables;

mysql查询某个字段去重之后的条数和总条数

SELECT COUNT(1) AS 某个字段去重后的条数, SUM(num) AS 总条数
FROM (SELECT COUNT(1) AS num FROM 表名 GROUP BY 字段名) T;
mysql> SELECT COUNT(1) AS 某个字段去重后的条数, SUM(num) AS 总条数
    -> FROM (SELECT COUNT(1) AS num FROM out_subscribe_relation GROUP BY uid) T;
+--------------------------------+-----------+
| 某个字段去重后的条数           | 总条数    |
+--------------------------------+-----------+
|                         618426 |    842989 |
+--------------------------------+-----------+
1 row in set (0.88 sec)