행 수를 얻으려면 information_schema.tables를 사용해야 합니다. 구문은 다음과 같습니다.
SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘yourDatabaseName’;
'test'라는 이름의 데이터베이스에 대해 위의 구문을 구현해 보겠습니다. 쿼리는 테이블의 행 개수와 함께 테이블 이름을 표시하는 다음과 같습니다.
mysql> SELECT table_name, table_rows -> FROM INFORMATION_SCHEMA.TABLES -> WHERE TABLE_SCHEMA = 'test';
다음은 출력입니다.
+------------------------------------+------------+ | TABLE_NAME | TABLE_ROWS | +------------------------------------+------------+ | _student_trackerdemo | 0 | | _studenttrackerdemo | 0 | | add30minutesdemo | 0 | | addcolumn | 0 | | addingcurrencysymboldemo | 4 | | addingdaysdemo | 8 | | addoneday | 2 | | agecalculatesdemo | 4 | | agedemo | 4 | | aliasdemo | 2 | | allcharacterbeforespace | 4 | | allownulldemo | 2 | | allrecordsfromadate | 5 | | allrecordswithactive | 6 | | allrows | 4 | | appenddatademo | 3 | | appendingdatademo | 2 | | autoincrementdemo | 6 | | autoincrementdemo1 | 0 | | autoincrementedprimary | 4 | | autoincrementspecificpoint | 6 | | autoincrementwithmaxvaluefromtable | 2 | | autostoredatetime | 0 | | averageontime | 4 | | base64demo | 2 | | betweendatedemo | 6 | | bigintandintdemo | 0 | | bigintdemo | 0 | | bigintdemo8 | 6 | | bitdemo | 2 | | bookdatedemo | 2 | | booldemo | 0 | | booleandemo | 0 | | casedemo | 2 | | caseupdatedemo | 4 | | changecellsdata | 4 | | changecolumnposition | 0 | | changecolumnpositiondemo | 4 | | changecurrentautoincrementvalue | 6 | | changeenginetabledemo | 0 | | charsetdemo | 0 | | columnwithspace | 4 | | comparingdate | 4 | | concatenatetwocolumnsdemo | 4 | | concatenation_operatordemo | 4 | | concatenationdemo | 4 | | conditionalinsertdemo | 4 | | constraintdemo | 0 | | convertdatetimetodate | 4 | | countingdemo | 4 | | creatingtable | 7 | | creatingtableusingviewstudent | 0 | | cumulativesumdemo | 4 | | currentdatetimedemo | 0 | | customers | 0 | | databystringlength | 4 | | dateandtimetotimestamp | 4 | | dateasstringdemo | 2 | | datecreatedemo | 4 | | dateequaltoday | 6 | | dateformatdemo | 4 | | dateformatwithselect | 6 | | datefromtimestamp | 4 | | dateinsertdemo | 2 | | daterange | 8 | | datesofoneweek | 4 | | datetimedemo | 6 | | dayofweekdemo | 6 | | daysinagivenmonth | 6 | | decimaldemo | 4 | | decimaltointdemo | 2 | | decrementdemo | 6 | | defaultdemo | 0 | | deleteallfromtable | 0 | | deletedemo | 5 | | deletemanyrows | 2 | | demo | 2 | | demo1 | 2 | | destination | 0 | | differenceinhours | 2 | | differenceinseconds | 4 | | differencetimestamp | 2 | | differentrows | 4 | | display | 2 | | displayint | 5 | | distinctdemo | 7 | | empidandempname_view | 0 | | empinfoview | 0 | | employee | 2 | | employeedesignation | 2 | | employeeinformation | 6 | | employeetable | 2 | | emptycellsatend | 9 | | emptyin | 4 | | enumdemo | 0 | | fetchrowlasthourdemo | 6 | | findlowercasevalue | 4 | | firsttabledemo | 2 | | firsttablemaxvalue | 6 | | firstworddemo | 2 | | formatnumbertwodecimalplace | 6 | | generatingnumbersdemo | 10 | | getdatefromtimestamp | 4 | | getfirstdayofmonth | 4 | | gettinglast5characters | 4 | | gmailsignin | 4 | | groupbytwofieldsdemo | 6 | | groupmonthandyeardemo | 4 | | highestidorderby | 6 | | highestnumberdemo | 4 | | ifnulldemo | 4 | | increaseanddecreasedemo | 2 | | increasevarchardemo | 0 | | incrementanddecrementvalue | 2 | | incrementby1 | 6 | | incrementcounterdemo | 6 | | inparameter | 5 | | insert | 0 | | insertignoredemo | 2 | | insertingnull | 0 | | insertnulldemo | 3 | | insertwithmultipleandsigle | 21 | | int11demo | 2 | | intcurrencydemo | 5 | | intrangedemo | 2 | | inttodatedemo | 4 | | intvsintanythingdemo | 4 | | isnulldemo | 6 | | jsonformatdemo | 4 | | keywordsearchdemo | 6 | | lasthourrecords | 4 | | lasttwocharacters | 2 | | likebinarydemo | 0 | | likedemo | 4 | | limitafterorderby | 4 | | lookuptable | 0 | | lowercasedemo | 2 | | maxlengthfunctiondemo | 4 | | monthdemo | 12 | | moviecollectiondemo | 0 | | multiplecountdemo | 8 | | myisamtoinnodbdemo | 0 | | nearestdatedemo | 4 | | newstable | 6 | | newtableduplicate | 3 | | notempty | 0 | | notequaldemo | 7 | | notequalsdemo | 5 | | notinsertemptystring | 0 | | nowandcurdatedemo | 2 | | nowdemo | 2 | | nthrecorddemo | 6 | | nullandemptydemo | 7 | | nullatbottom | 6 | | onlyhourdemo | 3 | | onlymonthandyear | 4 | | orderbycharacterlength | 4 | | orderbyif | 6 | | orderbynullfirstdemo | 4 | | orderbyrandname | 8 | | orderbyrelevance | 4 | | orderindemo | 6 | | originaltable | 4 | | parsedatedemo | 4 | | passinganarraydemo | 8 | | persons | 0 | | prependstringoncolumnname | 2 | | pricedemo | 4 | | primarykey1000demo | 4 | | proceduredemotable | 2 | | queryresultdemo | 4 | | renameviewdemo | 0 | | reordercolumn | 0 | | replacedemo | 4 | | rowexistdemo | 4 | | rowpositiondemo | 4 | | rowwithsamevalue | 4 | | safedeletedemo | 4 | | searchdemo | 6 | | searchtextdemo | 0 | | secondtabledemo | 2 | | secondtablerows | 4 | | selectconcat | 5 | | selectdataonyearandmonthdemo | 4 | | selectdistincttwocolumns | 8 | | selectdomainnameonly | 4 | | sha256demo | 0 | | simulatearraydemo | 6 | | skiplasttenrecords | 0 | | smallintdemo | 5 | | sortbydateandtime | 4 | | sortcolumnzeroatlastdemo | 6 | | sortingstringdemo | 5 | | startautoincrement | 3 | | storedproctable | 2 | | stringreplacedemo | 2 | | stringtodate | 2 | | stringwithspecificlength | 4 | | student | 2 | | studentdemo | 4 | | studentinformation | 5 | | studentmodifytabledemo | 3 | | studenttable | 3 | | stuedntinformation | 0 | | substringindexdemo | 4 | | subtract3hours | 2 | | sumcasedemo | 6 | | sumdemooncolumns | 4 | | sumwithifcondition | 6 | | tablealiasdemo | 4 | | tableview | 0 | | tblemployee | 0 | | temporarycolumnwithvaluedemo | 4 | | timetosecond | 2 | | timetoseconddemo | 0 | | tinyintdemo | 0 | | toggledemo | 6 | | toogledemo | 6 | | triggerdemo1 | 3 | | triggerdemo2 | 0 | | truncatetabledemo | 0 | | union_table1 | 3 | | union_table2 | 3 | | unixtime | 2 | | unixtimedemo | 0 | | updatealldemo | 2 | | updatedate | 4 | | updatenumber1to3 | 3 | | updaterowwith1to3 | 6 | | updatevalueincrementally | 5 | | updatewithifcondition | 2 | | uppertabledemo | 4 | | userdateformat | 4 | | userrole | 4 | | uservariable | 2 | | uservariableinlike | 5 | | view_student | 0 | | viewdemo | 2 | | viewemployeeidandemployeename | 0 | | wheredemo | 4 | | wholewordmatchdemo | 2 | | yesterdaydatedemo | 2 | | zipcodepadwithzerodemo | 4 | +------------------------------------+------------+ 243 rows in set (0.86 sec)