MySQL
 Computer >> コンピューター >  >> プログラミング >> MySQL

InnoDBストレージエンジンを使用してMySQLのすべてのテーブルを表示するにはどうすればよいですか?


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)

  1. MySQLにinnoDBをインストールまたは有効にするにはどうすればよいですか?

    MySQでinnoDBを有効にするには、 my.iniを回避する必要があります ファイル。ただし、MySQLバージョン8では、デフォルトのストレージエンジンはinnoDBです。 my.iniから同じことを確認してください ファイル- テーブル作成時に設定することもできます- mysql> create table DemoTable    (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentFirstName varchar(100), &

  2. MySQLで特定の列名を持つテーブルを見つける方法は?

    列名を見つけるには、information_schema.columnsを使用します。以下は構文です- select distinct table_name from information_schema.columns where column_name like '%yourSearchValue%' and table_schema=database(); さまざまなテーブルで列名を見つけるために、上記の構文を実装しましょう。ここでは、特定の列名が「クライアント」という単語を持つテーブル名のみが必要です- mysql> select distinct table_