Check Table Type in MySQL Database

Posted on

In this article, there will be a simple demonstration on how to check the type of a table within a database running in MySQL Database Server.

First of all, check whether MySQL Database Server is installed and already enabled. To do it, refer to the following article titled ‘Check MySQL Service Status‘ for checking MySQL Database Server status.

Access to MySQL Database Server console by typing the following command on the command line :

mysql -uroot -p

Below is sample of the output of the execution’s command :

user@hostname:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Start selecting database which is going to be checked for their table types, by typing the following command in MySQL command console :

use database_name

The following is an example of how to do it in MySQL Command Console, database ‘test’ is chosen for giving the example :

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 

Successfully executed the command to select certain database, follow it with the command used to check the table type by executing the following query :

show table status from `database_name`;
mysql> show table status from `test`;
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+------
----+----------------+---------+
| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Check
sum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+------
----+----------------+---------+
| account | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2016-08-08 22:51:32 | NULL                | NULL       | latin1_swedish_ci |     N
ULL |                |         |
| user    | InnoDB |      10 | Dynamic    |    4 |           4096 |       16384 |               0 |            0 |         0 |              5 | 2016-11-01 08:50:56 | 2016-11-01 08:51:31 | NULL       | latin1_swedish_ci |     N
ULL |                |         |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+------
----+----------------+---------+
2 rows in set (0,00 sec)

As we can see that the type of the tables both of them are InnoDB.

Another example from another database :

 mysql> use testing;
 mysql>

Show the table type by executing the following query in the MySQL Command Console :

mysql> show table status from `testing`;
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+--
--------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | C
reate_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+--
--------------+---------+
| CUSTOMER | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 | 158334976 |           NULL | 2016-06-24 22:48:16 | NULL        | NULL       | latin1_swedish_ci |     NULL |
|         |
| user     | InnoDB |      10 | Dynamic    |    4 |           4096 |       16384 |               0 |            0 |         0 |              5 | 2016-07-04 21:27:48 | NULL        | NULL       | latin1_swedish_ci |     NULL |
|         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+--
--------------+---------+
2 rows in set (0,00 sec)
mysql>

That is several steps and description on how to check the type of certain tables in MySQL Database Server.

Leave a Reply