使用命令 mysql -h ip -u username -p 回车后输入密码进入到mysql中
如果提示mysql不是内部命令,则说明mysql的环境变量没有配置
使用 select @@basedir as basePath from dual查询出mysql 的安装路径,进入到bin目录下,复制路径添加到环境变量中
1、查询所有数据库中所有表的数据量大小
SELECT
table_schema AS '数据库名',
table_name AS '表名',
table_rows AS '数据条数',
CONCAT( ROUND( data_length / 1024 / 1024, 2 ), 'M' ) AS '数据大小(M)',
CONCAT( ROUND( index_length / 1024 / 1024, 2 ), 'M' ) AS '索引大小(M)'
FROM
information_schema.TABLES
2、查询指定数据库中所有表的数据量大小
SELECT
table_schema AS '数据库名',
table_name AS '表名',
table_rows AS '数据条数',
CONCAT( ROUND( data_length / 1024 / 1024, 2 ), 'M' ) AS '数据大小(M)',
CONCAT( ROUND( index_length / 1024 / 1024, 2 ), 'M' ) AS '索引大小(M)'
FROM
information_schema.TABLES
WHERE
table_schema = '数据库名';
3、查询指定数据库指定表的数据大小
SELECT
table_schema AS '数据库名',
table_name AS '表名',
table_rows AS '数据条数',
CONCAT( ROUND( data_length / 1024 / 1024, 2 ), 'M' ) AS '数据大小(M)',
CONCAT( ROUND( index_length / 1024 / 1024, 2 ), 'M' ) AS '索引大小(M)'
FROM
information_schema.TABLES
WHERE
table_schema = '数据库名'
AND table_name = '表名';