如何显示 MySQL 中所有使用 InnoDB 存储引擎的表?
mysqlmysqli database
要显示所有 ENGINE 为 InnoDB 的表名，请执行以下查询
mysql> SELECT TABLE_SCHEMA as DATABASENAME ,TABLE_NAME as AllTABLENAME ,ENGINE as ENGINETYPE FROM information_schema.TABLES
-> WHERE ENGINE = 'innoDB'
-> AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');
以下是输出。
+--------------+------------------------------------------------------------------+------------+ | DATABASENAME | AllTABLENAME | ENGINETYPE | +--------------+------------------------------------------------------------------+------------+ | sys | sys_config | InnoDB | | business | mytable | InnoDB | | business | primarytable | InnoDB | | business | tblstudent | InnoDB | | business | modifydatatype | InnoDB | | business | nulldemo | InnoDB | | business | groupdemo | InnoDB | | sample | mytable | InnoDB | | sample | yourtable | InnoDB | | business | texturl | InnoDB | | business | varcharurl | InnoDB | | business | addcolumntable | InnoDB | | business | demoschema | InnoDB | | business | demoint | InnoDB | | business | nthrecorddemo | InnoDB | | business | int1demo | InnoDB | | business | intdemo | InnoDB | | business | textdemo | InnoDB | | business | chardemo | InnoDB | | business | varchardemo | InnoDB | | business | demo | InnoDB | | business | latandlangdemo | InnoDB | | business | smallintdemo | InnoDB | | business | student | InnoDB | | business | moviecollection | InnoDB | | business | bookindexes | InnoDB | | business | duplicatebookindexes | InnoDB | | business | tbluni | InnoDB | | business | existsrowdemo | InnoDB | | business | demoascii | InnoDB | | business | modifycolumnnamedemo | InnoDB | | business | demobcrypt | InnoDB | | business | primarytable1 | InnoDB | | business | foreigntable | InnoDB | | business | autoincrementtable | InnoDB | | business | demoauto | InnoDB | | business | demoonreplace | InnoDB | | business | employeetable | InnoDB | | business | whereconditon | InnoDB | | business | moneydemo | InnoDB | | business | varchardemo1 | InnoDB | | business | varchardemo2 | InnoDB | | business | presenthistory | InnoDB | | business | pasthistory | InnoDB | | business | columnvaluenulldemo | InnoDB | | business | demoemptyandnull | InnoDB | | business | nonasciidemo | InnoDB | | business | nullwithselect | InnoDB | | business | autoincrement | InnoDB | | business | unsigneddemo | InnoDB | | education | university | InnoDB | | education | student | InnoDB | | business | dateadddemo | InnoDB | | business | distinctdemo | InnoDB | | business | groupdemo1 | InnoDB | | business | incasesensdemo | InnoDB | | business | demo1 | InnoDB | | business | spacecolumn | InnoDB | | business | milliseconddemo | InnoDB | | business | primarytabledemo | InnoDB | | business | foreigntabledemo | InnoDB | | business | trailingandleadingdemo | InnoDB | | business | duplicatefound | InnoDB | | business | sequencedemo | InnoDB | | business | findandreplacedemo | InnoDB | | business | limitoffsetdemo | InnoDB | | business | deletedemo | InnoDB | | business | deleterecord | InnoDB | | business | clonestudent | InnoDB | | business | firsttable | InnoDB | | business | secondtable | InnoDB | | business | rowstranspose | InnoDB | | business | rowstransposedemo | InnoDB | | business | rownumberdemo | InnoDB | | business | tblfirst | InnoDB | | business | updtable | InnoDB | | business | transcationdemo | InnoDB | | business | mytable1 | InnoDB | | business | columnexistdemo | InnoDB | | business | numbercolumndemo | InnoDB | | business | timestampdemo | InnoDB | | business | escapedeom | InnoDB | | business | rowcountdemo | InnoDB | | business | addconstraintdemo | InnoDB | | business | addcheckconstraintdemo | InnoDB | | business | checkdemo | InnoDB | | business | alphademo | InnoDB | | business | imagedemo | InnoDB | | business | tblifdemo | InnoDB | | business | tblupdatelimit | InnoDB | | business | sortingvarchardemo | InnoDB | | business | backticksymbol | InnoDB | | business | qutesdemo | InnoDB | | business | ondemo | InnoDB | | business | xmldemo | InnoDB | | business | ifelsedemo | InnoDB | | business | demowhere | InnoDB | | business | indexingdemo | InnoDB | | business | functiontriggersdemo | InnoDB | | business | tblfunctiontrigger | InnoDB | | business | triggedemo | InnoDB | | business | usernameandpassworddemo | InnoDB | | business | tbldemotrail | InnoDB | | business | tblp | InnoDB | | business | tblf | InnoDB | | business | tblselectdemo | InnoDB | | business | commaseperatedemo | InnoDB | | business | nextpreviousdemo | InnoDB | | business | multiplerecordwithvalues | InnoDB | | business | multipleindexdemo | InnoDB | | business | trigger1 | InnoDB | | business | trigger2demo | InnoDB | | business | distinctdemo1 | InnoDB | | business | currentdatetime | InnoDB | | business | addnotnulldemo | InnoDB | | business | uniquedemo | InnoDB | | business | uniqueconstdemo | InnoDB | | business | distcountdemo | InnoDB | | business | trimdemo | InnoDB | | business | trimdemo2 | InnoDB | | business | newstudent | InnoDB | | business | keydemo | InnoDB | | business | singlequotesdemo | InnoDB | | business | functionindexdemo | InnoDB | | business | saveoutputintext | InnoDB | | business | groupt_concatdemo | InnoDB | | business | groupconcatenatedemo | InnoDB | | business | employeerecords | InnoDB | | business | myisamtoinnodbdemo | InnoDB | | test | myisamtoinnodbdemo | InnoDB | | business | table1 | InnoDB | | business | table2 | InnoDB | | business | sourcetable | InnoDB | | test | destination | InnoDB | | business | originaltable | InnoDB | | test | originaltable | InnoDB | | business | saveintotextfile | InnoDB | | business | tabledemo | InnoDB | | business | studenttable | InnoDB | | business | truefalsetable | InnoDB | | business | uniondemo1 | InnoDB | | business | uniondemo2 | InnoDB | | business | commentdemo | InnoDB | | business | commentdemo2 | InnoDB | | business | commentdemo3 | InnoDB | | business | tblnull | InnoDB | | business | proctabledemo | InnoDB | | business | enumdemo | InnoDB | | business | enumvalues | InnoDB | | business | nextiddemo | InnoDB | | business | firsttabledemo | InnoDB | | business | secondtabledemo | InnoDB | | business | duplicatedeletedemo | InnoDB | | business | variablenametable | InnoDB | | business | variableastablename | InnoDB | | business | tableforeign | InnoDB | | business | tablepri | InnoDB | | business | tabledemo2 | InnoDB | | business | tabledemo3 | InnoDB | | business | numberofcolumns | InnoDB | | business | texttabledemo | InnoDB | | business | blobtabledemo | InnoDB | | business | tttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttttt | InnoDB | | business | college | InnoDB | | business | studentenrollment | InnoDB | | business | randoptimizedemo | InnoDB | | business | randomoptimizationdemo | InnoDB | | business | timestamptodatedemo | InnoDB | | business | tablename1tablename1tablename1tablename1tablename1tablename1demo | InnoDB | | business | last10recordsdemo | InnoDB | | business | bitdemo | InnoDB | | business | tinyintdemo | InnoDB | | business | booleandemo | InnoDB | | business | tinyint1demo | InnoDB | | business | uniquedemo1 | InnoDB | | business | lowcardinality | InnoDB | | business | ipv4addressdemo | InnoDB | | business | stringtodatedemo | InnoDB | | business | wordcountdemo | InnoDB | | business | columnnameasnumberdemo | InnoDB | | business | columnslist | InnoDB | | business | jasonasmysqldemo | InnoDB | | business | insubquerydemo | InnoDB | | business | orderdemo | InnoDB | | business | rowintocolumn | InnoDB | | business | colortable | InnoDB | | business | finddemo | InnoDB | | business | uniqueautoid | InnoDB | | business | countrycitydemo | InnoDB | | business | schemadatabasemethoddemo | InnoDB | | business | employeeinformation | InnoDB | | business | addingunique | InnoDB | | business | ipv6demo | InnoDB | | business | add1daydemo | InnoDB | +--------------+------------------------------------------------------------------+------------+ 194 rows in set (0.01 sec)
