Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the operation of the mysql database, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

# # View the storage capacity of each table in the database

SELECT aa.TABLE_NAME,aa.data_size,aa.index_size,aa.data_size+aa.index_size as table_sizefrom (select TABLE_NAME, concat (truncate (data_length/1024/1024,2), 'MB') as data_size,concat (truncate (index_length/1024/1024,2),' MB') as index_sizefrom information_schema.tables where TABLE_SCHEMA = 'xxxx'group by TABLE_NAMEorder by data_length desc) aa

Note: just change the name of the database.

# # View the size of all databases

Select TABLE_SCHEMA, concat (truncate (sum (data_length) / 1024 index_length), 'MB') as data_size,concat (truncate (sum (index_length) / 1024 index_length),' MB') as index_sizefrom information_schema.tablesgroup by TABLE_SCHEMAorder by data_length desc

# query at 0: 00 on the same day in the format of YYYY-MM-DD HH:mm:ss

SELECT DATE_FORMAT (CURDATE (),'% Y-%m-%d% HRV% iRU% s')

# query 0: 00 this week in YYYY-MM-DD HH:mm:ss format

SELECT DATE_FORMAT (DATE_ADD (CURDATE (), INTERVAL-WEEKDAY (CURDATE ()) DAY),'% Y-%m-%d% HRV% iRO% s')

# query 0: 00 this month in YYYY-MM-DD HH:mm:ss format

SELECT DATE_FORMAT (DATE_SUB (CURDATE (), INTERVAL DAY (CURDATE ())-1 DAY),'% Y-%m-%d% HRV% iRO% s')

After reading the above, do you have any further understanding of the operation of the mysql database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.