Mysql-sql-mode Database Size Query

Mysql---View database and table size

There are four ways to see the size of the MySQL database in four ways, including the following:

The first type: Go in to specify the schema database (information that holds other databases)

Use Information_schema

The second type: Querying data files and index file sizes for all data

Select Concat (Round (sum (data_length/1024/1024), 2), ' MB ') as Data_size from INFORMATION_SCHEMA. TABLES;

Select Concat (Round (sum (data_length/1024/1024), 2), ' MB ') as Data_size,

CONCAT (TRUNCATE (SUM (index_length)/1024/1024,2), ' MB ') as Index_size

From INFORMATION_SCHEMA. TABLES;

Third: View the size of the data file and index file for the specified database, for example: Database test

Select Concat (Round (sum (data_length/1024/1024), 2), ' MB ') as Data_size,

CONCAT (TRUNCATE (SUM (index_length)/1024/1024,2), ' MB ') as Index_size

From INFORMATION_SCHEMA. TABLES where table_schema= ' test ';

The fourth type: View the data file and the file size of the table of the specified database, for example: AM21 table in database test

Select Concat (Round (sum (data_length/1024/1024), 2), ' MB ') as Data_size,

CONCAT (TRUNCATE (SUM (index_length)/1024/1024,2), ' MB ') as Index_size

From information_schema. TABLES where table_schema= ' test ' and table_name= ' am21 ';

Synthetic queries in 5.7 do not apply because Sql_mode is not set

SELECT Table_schema, CONCAT (TRUNCATE (SUM (data_length)/1024/1024,2), ' MB ') as Data_size,

CONCAT (TRUNCATE (SUM (index_length)/1024/1024,2), ' MB ') as Index_size

From Information_schema.tables;

GROUP by Table_schema

ORDER by Data_length DESC;

The error is as follows

Error 1064 (42000): You have a error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' GROUP by Table_schema

ORDER by Data_length DESC ' at line 1

Workaround---echo "sql_mode=" No_auto_create_user,no_engine_substitution "" >>/etc/my.cnf restart MySQL and execute again

View Sql_mode

Mysql-uroot-psunfuzhou-e "SHOW VARIABLES like ' Sql_mode ';"

Set Sql_mode

Temporary Settings Sql_mode

Mysql-uroot-psunfuzhou-e "SET sql_mode= ' no_auto_create_user,no_engine_substitution '"

Global Settings Sql_mode

Mysql-uroot-psunfuzhou-e "SET global sql_mode= ' no_auto_create_user,no_engine_substitution '"

SET @ @GLOBAL. sql_mode= "No_auto_create_user,no_engine_substitution";

SELECT Table_schema, CONCAT (TRUNCATE (SUM (data_length)/1024/1024,2), ' MB ') as Data_size,

CONCAT (TRUNCATE (SUM (index_length)/1024/1024,2), ' MB ') as Index_size

From Information_schema.tables;

GROUP by Table_schema

ORDER by Data_length DESC;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

This article is from the Linux OPS blog, so be sure to keep this source http://sunfuzhou.blog.51cto.com/8603214/1969687

Mysql-sql-mode Database Size Query