How to find all tables use particular storage engine on MySQL

MySQL รองรับ table engine อยู่หลากหลาย ไม่ว่าจะเป็น MyISAM, InnoDB, MEMORY, NDB เป็นต้น ดังนั้นหลายคนอาจคิดว่าเมื่อต้องตรวจสอบว่า table นี้ใช้ engine อะไรคงวุ่นวายไม่ใช่เล่น แต่จริงๆแล้วสามารถทำการค้นหาได้ไม่อยากเย็นอะไร โดยเราจะ query ข้อมูลใน database INFORMATION_SCHEMA ซึ่งภายในเก็บข้อมูลเกี่ยวกับ table ไหนใช้ engine อะไรไว้อยู่แล้ว สำหรับคำสั่งที่จะตรวจสอบก็ใช้ SQL syntax ธรรมดาๆ ไม่มีอะไรพิเศษ หากเราต้องการตรวจสอบว่ามี table อะไรบ้างที่ใช้ InnoDB engine ก็ใช้คำสั่งดังนี้

mysql> SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';

ถ้าหากต้องการตรวจสอบว่ามี table อะไรบ้างที่ใช้ MyISAM engine ก็ให้ใช้คำสั่งดังนี้

mysql> SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'myisam';

จากคำสั่งด้านบน ถ้าเราสังเกตดูจะพบว่าคำสั่งต่างกันตรง engine = ‘xxx’ เท่านั้น ดังนั้นหากเราต้องการตรวจสอบ engine อื่นก็ให้แทนชื่อ engine ที่ต้องการตรวจสอบเข้าไปเท่านั้นเอง ง่ายไหมละ ตัวอย่างผลลัพธ์

+--------------------+-------------------------------+
| table_schema       | table_name                    |
+--------------------+-------------------------------+
| information_schema | COLUMNS                       |
| information_schema | EVENTS                        |
| information_schema | PARAMETERS                    |
| information_schema | PARTITIONS                    |
| information_schema | PLUGINS                       |
| information_schema | PROCESSLIST                   |
| information_schema | ROUTINES                      |
                    ...
| information_schema | TRIGGERS                      |
| information_schema | VIEWS                         |
| mysql              | columns_priv                  |
| mysql              | help_keyword                  |
| mysql              | help_relation                 |
| mysql              | tables_priv                   |
+--------------------+-------------------------------+
25 rows in set (0.01 sec)

*Note table_schema คือ ชื่อของ database table_name คือ ชื่อของ table * คำสั่งที่ดูว่า MySQL ที่ใช้ support storage engine อะไร ใช้คำสั่ง SHOW ENGINES\G วิธีการ convert storage engine จาก MyISAM ไปเป็น InnoDB