mysql> show table status like ‘TABLE007’\G
*************************** 1. row ***************************
Name: TABLE007
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 9
Avg_row_length: 1820
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 10
Create_time: 2009-02-17 11:30:45
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

mysql>

mysql> show create table TABLE007\G
*************************** 1. row ***************************
Table: TABLE007
Create Table: CREATE TABLE `TABLE007` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

mysql> select table_name, Engine from information_schema.tables where
-> table_schema = ‘DB01’ and table_name = ‘TABLE007’;
+————+——–+
| table_name | Engine |
+————+——–+
| TABLE007 | InnoDB |
+————+——–+
1 row in set (0.00 sec)

mysql>

show_table_status

mysql> show engines;
\+————+———+—————————————————————-+————–+—–+————+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+————+———+—————————————————————-+————–+—–+————+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+————+———+—————————————————————-+————–+—–+————+
7 rows in set (0.00 sec)
mysql>

DEFAULT & YES = Storage Engine is compiled and enabled.
DISABLED = Compiled in but disabled.
NO = Not Compiled in when server was build.

engine

※ MYISAM, MERGE,MEMORYは常に利用可能なストレージエンジン
サポートされたストレージエンジン

『テーブルに利用されているストレージエンジンを把握する為の3つの方法。』

━━━━━━ SHOW TABLE STATUS ━━━━━━

mysql> show table status like ‘mt_author’\G
*************************** 1. row ***************************
Name: mt_author
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1
Avg_row_length: 88
Data_length: 88
Max_data_length: 281474976710655
Index_length: 6144
Data_free: 0
Auto_increment: 2
Create_time: 2008-03-10 17:55:37
Update_time: 2008-03-10 17:55:37
Check_time: 2008-03-10 17:55:37
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

━━━━━━ SHOW CREATE ━━━━━━

mysql> show create table mt_author\G
*************************** 1. row ***************************
Table: mt_author
Create Table: CREATE TABLE `mt_author` (
`author_id` int(11) NOT NULL auto_increment,
`author_api_password` varchar(60) collate utf8_bin default NULL,
`author_can_create_blog` tinyint(4) default NULL,
`author_can_view_log` tinyint(4) default NULL,
`author_email` varchar(75) collate utf8_bin default NULL,
`author_entry_prefs` varchar(255) collate utf8_bin default NULL,
`author_hint` varchar(75) collate utf8_bin default NULL,
`author_is_superuser` tinyint(4) default NULL,
`author_name` varchar(50) collate utf8_bin NOT NULL default ”,
`author_nickname` varchar(50) collate utf8_bin default NULL,
`author_password` varchar(60) collate utf8_bin NOT NULL default ”,
`author_preferred_language` varchar(50) collate utf8_bin default NULL,
`author_public_key` mediumtext collate utf8_bin,
`author_remote_auth_token` varchar(50) collate utf8_bin default NULL,
`author_remote_auth_username` varchar(50) collate utf8_bin default NULL,
`author_type` smallint(6) NOT NULL default ‘1’,
`author_url` varchar(255) collate utf8_bin default NULL,
`author_created_on` datetime default NULL,
`author_created_by` int(11) default NULL,
`author_modified_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`author_modified_by` int(11) default NULL,
PRIMARY KEY (`author_id`),
KEY `mt_author_email` (`author_email`),
KEY `mt_author_created_on` (`author_created_on`),
KEY `mt_author_name` (`author_name`),
KEY `mt_author_type` (`author_type`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

━━━━━━ INFORMATION SCHEMA ━━━━━━

mysql> select TABLE_NAME,TABLE_TYPE,ENGINE from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA =’MT’;
+—————–+————+——–+
| TABLE_NAME | TABLE_TYPE | ENGINE |
+—————–+————+——–+
| mt_author | BASE TABLE | MyISAM |
| mt_blog | BASE TABLE | MyISAM |
| mt_category | BASE TABLE | MyISAM |
| mt_comment | BASE TABLE | MyISAM |
| mt_config | BASE TABLE | MyISAM |
| mt_entry | BASE TABLE | MyISAM |
| mt_fileinfo | BASE TABLE | MyISAM |
| mt_ipbanlist | BASE TABLE | MyISAM |
| mt_log | BASE TABLE | MyISAM |
| mt_notification | BASE TABLE | MyISAM |
| mt_objecttag | BASE TABLE | MyISAM |
| mt_permission | BASE TABLE | MyISAM |
| mt_placement | BASE TABLE | MyISAM |
| mt_plugindata | BASE TABLE | MyISAM |
| mt_rfdata | BASE TABLE | MyISAM |
| mt_session | BASE TABLE | MyISAM |
| mt_tag | BASE TABLE | MyISAM |
| mt_tbping | BASE TABLE | MyISAM |
| mt_template | BASE TABLE | MyISAM |
| mt_templatemap | BASE TABLE | MyISAM |
| mt_trackback | BASE TABLE | MyISAM |
+—————–+————+——–+
21 rows in set (0.01 sec)

mysql>

Comments are closed.

Post Navigation