您可以在 information_schema.tables 的帮助下完成此操作。语法如下 -
SELECT TABLE_NAME, table_rows, data_length, index_length,
round(((data_length + index_length) / 1024 / 1024),2) "MB Size"
FROM information_schema.TABLES WHERE table_schema = "yourDatabaseName"
ORDER BY (data_length + index_length) ASC;
登录后复制
为了理解上述语法，让我们为任何数据库实现它。在这里，我使用数据库 TEST。让我们看看数据库 TEST 的查询。
mysql> SELECT TABLE_NAME, table_rows, data_length, index_length,
-> round(((data_length + index_length) / 1024 / 1024),2) "MB Size"
-> FROM information_schema.TABLES WHERE table_schema = "test"
-> ORDER BY (data_length + index_length) ASC;
登录后复制
以下是显示按大小排序的表格的输出。
+------------------------------------+------------+-------------+--------------+---------+
| TABLE_NAME | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | MB Size |
+------------------------------------+------------+-------------+--------------+---------+
| empinfoview | 0 | 0 | 0 | 0.00 |
| lookuptable | 0 | 0 | 0 | 0.00 |
| view_student | 0 | 0 | 0 | 0.00 |
| empidandempname_view | 0 | 0 | 0 | 0.00 |
| viewemployeeidandemployeename | 0 | 0 | 0 | 0.00 |
| customers | 0 | 0 | 1024 | 0.00 |
| addingcurrencysymboldemo | 4 | 16384 | 0 | 0.02 |
| allrecordswithactive | 6 | 16384 | 0 | 0.02 |
| autostoredatetime | 0 | 16384 | 0 | 0.02 |
| bookdatedemo | 2 | 16384 | 0 | 0.02 |
| changecurrentautoincrementvalue | 6 | 16384 | 0 | 0.02 |
| conditionalinsertdemo | 4 | 16384 | 0 | 0.02 |
| datefromtimestamp | 4 | 16384 | 0 | 0.02 |
| decrementdemo | 6 | 16384 | 0 | 0.02 |
| differenceinhours | 2 | 16384 | 0 | 0.02 |
| fetchrowlasthourdemo | 6 | 16384 | 0 | 0.02 |
| getfirstdayofmonth | 4 | 16384 | 0 | 0.02 |
| increaseanddecreasedemo | 2 | 16384 | 0 | 0.02 |
| insertingnull | 0 | 16384 | 0 | 0.02 |
| isnulldemo | 6 | 16384 | 0 | 0.02 |
| newstable | 6 | 16384 | 0 | 0.02 |
| nthrecorddemo | 6 | 16384 | 0 | 0.02 |
| orderbyrandname | 8 | 16384 | 0 | 0.02 |
| pricedemo | 4 | 16384 | 0 | 0.02 |
| rowpositiondemo | 4 | 16384 | 0 | 0.02 |
| selectdataonyearandmonthdemo | 4 | 16384 | 0 | 0.02 |
| sortcolumnzeroatlastdemo | 6 | 16384 | 0 | 0.02 |
| studentdemo | 4 | 16384 | 0 | 0.02 |
| sumdemooncolumns | 4 | 16384 | 0 | 0.02 |
| tinyintdemo | 0 | 16384 | 0 | 0.02 |
| unixtime | 2 | 16384 | 0 | 0.02 |
| uppertabledemo | 4 | 16384 | 0 | 0.02 |
| wheredemo | 4 | 16384 | 0 | 0.02 |
| addingdaysdemo | 8 | 16384 | 0 | 0.02 |
| allrows | 4 | 16384 | 0 | 0.02 |
| averageontime | 4 | 16384 | 0 | 0.02 |
| booldemo | 0 | 16384 | 0 | 0.02 |
| changeenginetabledemo | 0 | 16384 | 0 | 0.02 |
| databystringlength | 4 | 16384 | 0 | 0.02 |
| dateinsertdemo | 2 | 16384 | 0 | 0.02 |
| defaultdemo | 0 | 16384 | 0 | 0.02 |
| differenceinseconds | 4 | 16384 | 0 | 0.02 |
| employee | 2 | 16384 | 0 | 0.02 |
| findlowercasevalue | 4 | 16384 | 0 | 0.02 |
| gettinglast5characters | 4 | 16384 | 0 | 0.02 |
| increasevarchardemo | 0 | 16384 | 0 | 0.02 |
| insertnulldemo | 3 | 16384 | 0 | 0.02 |
| jsonformatdemo | 4 | 16384 | 0 | 0.02 |
| lowercasedemo | 2 | 16384 | 0 | 0.02 |
| newtableduplicate | 3 | 16384 | 0 | 0.02 |
| nullandemptydemo | 7 | 16384 | 0 | 0.02 |
| orderbyrelevance | 4 | 16384 | 0 | 0.02 |
| primarykey1000demo | 4 | 16384 | 0 | 0.02 |
| rowwithsamevalue | 4 | 16384 | 0 | 0.02 |
| selectdistincttwocolumns | 8 | 16384 | 0 | 0.02 |
| sortingstringdemo | 5 | 16384 | 0 | 0.02 |
| studentinformation | 5 | 16384 | 0 | 0.02 |
| sumwithifcondition | 6 | 16384 | 0 | 0.02 |
| toggledemo | 6 | 16384 | 0 | 0.02 |
| unixtimedemo | 0 | 16384 | 0 | 0.02 |
| userdateformat | 4 | 16384 | 0 | 0.02 |
| wholewordmatchdemo | 2 | 16384 | 0 | 0.02 |
| addoneday | 2 | 16384 | 0 | 0.02 |
| appendingdatademo | 2 | 16384 | 0 | 0.02 |
| base64demo | 2 | 16384 | 0 | 0.02 |
| booleandemo | 0 | 16384 | 0 | 0.02 |
| charsetdemo | 0 | 16384 | 0 | 0.02 |
| convertdatetimetodate | 4 | 16384 | 0 | 0.02 |
| dateandtimetotimestamp | 4 | 16384 | 0 | 0.02 |
| daterange | 8 | 16384 | 0 | 0.02 |
| deleteallfromtable | 0 | 16384 | 0 | 0.02 |
| differencetimestamp | 2 | 16384 | 0 | 0.02 |
| employeedesignation | 2 | 16384 | 0 | 0.02 |
| firsttabledemo | 2 | 16384 | 0 | 0.02 |
| gmailsignin | 4 | 16384 | 0 | 0.02 |
| incrementanddecrementvalue | 2 | 16384 | 0 | 0.02 |
| insertwithmultipleandsigle | 21 | 16384 | 0 | 0.02 |
| keywordsearchdemo | 6 | 16384 | 0 | 0.02 |
| maxlengthfunctiondemo | 4 | 16384 | 0 | 0.02 |
| notempty | 0 | 16384 | 0 | 0.02 |
| nullatbottom | 6 | 16384 | 0 | 0.02 |
| orderindemo | 6 | 16384 | 0 | 0.02 |
| proceduredemotable | 2 | 16384 | 0 | 0.02 |
| safedeletedemo | 4 | 16384 | 0 | 0.02 |
| selectdomainnameonly | 4 | 16384 | 0 | 0.02 |
| startautoincrement | 3 | 16384 | 0 | 0.02 |
| studentmodifytabledemo | 3 | 16384 | 0 | 0.02 |
| tablealiasdemo | 4 | 16384 | 0 | 0.02 |
| toogledemo | 6 | 16384 | 0 | 0.02 |
| updatealldemo | 2 | 16384 | 0 | 0.02 |
| userrole | 4 | 16384 | 0 | 0.02 |
| yesterdaydatedemo | 2 | 16384 | 0 | 0.02 |
| agecalculatesdemo | 4 | 16384 | 0 | 0.02 |
| autoincrementdemo | 6 | 16384 | 0 | 0.02 |
| betweendatedemo | 6 | 16384 | 0 | 0.02 |
| casedemo | 2 | 16384 | 0 | 0.02 |
| columnwithspace | 4 | 16384 | 0 | 0.02 |
| countingdemo | 4 | 16384 | 0 | 0.02 |
| dateasstringdemo | 2 | 16384 | 0 | 0.02 |
| datesofoneweek | 4 | 16384 | 0 | 0.02 |
| deletedemo | 5 | 16384 | 0 | 0.02 |
| differentrows | 4 | 16384 | 0 | 0.02 |
| employeeinformation | 6 | 16384 | 0 | 0.02 |
| firsttablemaxvalue | 6 | 16384 | 0 | 0.02 |
| groupbytwofieldsdemo | 6 | 16384 | 0 | 0.02 |
| incrementby1 | 6 | 16384 | 0 | 0.02 |
| int11demo | 2 | 16384 | 0 | 0.02 |
| lasthourrecords | 4 | 16384 | 0 | 0.02 |
| monthdemo | 12 | 16384 | 0 | 0.02 |
| notequaldemo | 7 | 16384 | 0 | 0.02 |
| onlyhourdemo | 3 | 16384 | 0 | 0.02 |
| originaltable | 4 | 16384 | 0 | 0.02 |
| queryresultdemo | 4 | 16384 | 0 | 0.02 |
| searchdemo | 6 | 16384 | 0 | 0.02 |
| sha256demo | 0 | 16384 | 0 | 0.02 |
| storedproctable | 2 | 16384 | 0 | 0.02 |
| studenttable | 3 | 16384 | 0 | 0.02 |
| tableview | 0 | 16384 | 0 | 0.02 |
| triggerdemo1 | 3 | 16384 | 0 | 0.02 |
| updatedate | 4 | 16384 | 0 | 0.02 |
| uservariable | 2 | 16384 | 0 | 0.02 |
| zipcodepadwithzerodemo | 4 | 16384 | 0 | 0.02 |
| _student_trackerdemo | 0 | 16384 | 0 | 0.02 |
| aliasdemo | 2 | 16384 | 0 | 0.02 |
| autoincrementdemo1 | 0 | 16384 | 0 | 0.02 |
| bigintandintdemo | 0 | 16384 | 0 | 0.02 |
| caseupdatedemo | 4 | 16384 | 0 | 0.02 |
| comparingdate | 4 | 16384 | 0 | 0.02 |
| creatingtable | 7 | 16384 | 0 | 0.02 |
| datecreatedemo | 4 | 16384 | 0 | 0.02 |
| datetimedemo | 6 | 16384 | 0 | 0.02 |
| deletemanyrows | 2 | 16384 | 0 | 0.02 |
| display | 2 | 16384 | 0 | 0.02 |
| employeetable | 2 | 16384 | 0 | 0.02 |
| firstworddemo | 2 | 16384 | 0 | 0.02 |
| groupmonthandyeardemo | 4 | 16384 | 0 | 0.02 |
| incrementcounterdemo | 6 | 16384 | 0 | 0.02 |
| intcurrencydemo | 5 | 16384 | 0 | 0.02 |
| lasttwocharacters | 2 | 16384 | 0 | 0.02 |
| moviecollectiondemo | 0 | 16384 | 0 | 0.02 |
| notequalsdemo | 5 | 16384 | 0 | 0.02 |
| onlymonthandyear | 4 | 16384 | 0 | 0.02 |
| parsedatedemo | 4 | 16384 | 0 | 0.02 |
| renameviewdemo | 0 | 16384 | 0 | 0.02 |
| searchtextdemo | 0 | 16384 | 0 | 0.02 |
| simulatearraydemo | 6 | 16384 | 0 | 0.02 |
| stringreplacedemo | 2 | 16384 | 0 | 0.02 |
| stuedntinformation | 0 | 16384 | 0 | 0.02 |
| tblemployee | 0 | 16384 | 0 | 0.02 |
| triggerdemo2 | 0 | 16384 | 0 | 0.02 |
| updatenumber1to3 | 3 | 16384 | 0 | 0.02 |
| uservariableinlike | 5 | 16384 | 0 | 0.02 |
| _studenttrackerdemo | 0 | 16384 | 0 | 0.02 |
| allcharacterbeforespace | 4 | 16384 | 0 | 0.02 |
| autoincrementedprimary | 4 | 16384 | 0 | 0.02 |
| bigintdemo | 0 | 16384 | 0 | 0.02 |
| changecellsdata | 4 | 16384 | 0 | 0.02 |
| concatenatetwocolumnsdemo | 4 | 16384 | 0 | 0.02 |
| creatingtableusingviewstudent | 0 | 16384 | 0 | 0.02 |
| dateequaltoday | 6 | 16384 | 0 | 0.02 |
| dayofweekdemo | 6 | 16384 | 0 | 0.02 |
| demo | 2 | 16384 | 0 | 0.02 |
| displayint | 5 | 16384 | 0 | 0.02 |
| rowexistdemo | 4 | 16384 | 0 | 0.02 |
| selectconcat | 5 | 16384 | 0 | 0.02 |
| sortbydateandtime | 4 | 16384 | 0 | 0.02 |
| sumcasedemo | 6 | 16384 | 0 | 0.02 |
| timetoseconddemo | 0 | 16384 | 0 | 0.02 |
| union_table2 | 3 | 16384 | 0 | 0.02 |
| updatewithifcondition | 2 | 16384 | 0 | 0.02 |
| constraintdemo | 0 | 16384 | 16384 | 0.03 |
| insertignoredemo | 2 | 16384 | 16384 | 0.03 |
| student | 2 | 16384 | 32768 | 0.05 |
+------------------------------------+------------+-------------+--------------+---------+
240 rows in set (22.56 sec)
登录后复制
以上就是列出按大小排序的 MySQL 表和大小？的详细内容，更多请关注每日运维网(www.mryunwei.com)其它相关文章！
