MySQL 테이블 스토리지 엔진 확인

How to find out MySQL engine type for a specific table

MySQL 테이블 스토리지 엔진 확인

==방법 1: information_schema==222

SELECT engine FROM information_schema.TABLES where table_name='테이블명' AND table_schema='디비명';

mysql> SELECT engine FROM information_schema.TABLES where table_name='wp_users' AND table_schema='zetawiki';
+--------+
| engine |
+--------+
| MyISAM |
+--------+
1 row in set (0.00 sec)

1 방법 2: SHOW TABLES STATUS

SHOW TABLE STATUS WHERE name='테이블명';

mysql> SHOW TABLE STATUS WHERE name='wp_users';
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| 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 | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| wp_users | MyISAM |      10 | Dynamic    |    1 |             96 |          96 | 281474976710655 |         4096 |         0 |              2 | 2014-12-13 03:51:28 | 2014-12-13 03:51:28 | 2014-12-13 03:51:28 | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

2 방법 3: SHOW CREATE TABLE

SHOW CREATE TABLE 테이블명;

mysql> SHOW CREATE TABLE wp_users;
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wp_users | CREATE TABLE `wp_users` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(60) NOT NULL DEFAULT '',
  `user_pass` varchar(64) NOT NULL DEFAULT '',
  `user_nicename` varchar(50) NOT NULL DEFAULT '',
  `user_email` varchar(100) NOT NULL DEFAULT '',
  `user_url` varchar(100) NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

3 같이 보기

• MySQL 테이블 스토리지 엔진 변경
• MySQL information_schema
• MySQL SHOW TABLE STATUS
• MySQL SHOW CREATE TABLE

4 참고

• http://stackoverflow.com/questions/213543/how-can-i-check-mysql-engine-type-for-a-specific-table