Advanced Querying - Learning MySQL - page 269

... | 2006-06-12 07:17:06 | 2006-06-12 07:17:06 | | latin1_swedish_ci |...
... | 2006-06-12 07:17:06 | 2006-06-12 07:17:06 | | latin1_swedish_ci |...
... | 2006-06-12 07:17:06 | 2006-06-12 07:17:06 | | latin1_swedish_ci |...
... | 2006-06-12 07:17:06 | 2006-06-12 07:17:06 | | latin1_swedish_ci |...
... +---------------------+---------------------+------------+-------------------+...
... +---------------------+------------+-------------------+----------+...
... | Update_time | Check_time | Collation | Checksum |...
... +---------------------+------------+-------------------+----------+...
... | 2006-06-12 07:17:06 | | latin1_swedish_ci | NULL |...
... | 2006-06-12 07:17:06 | | latin1_swedish_ci | NULL |...
... | 2006-06-12 07:17:06 | | latin1_swedish_ci | NULL |...
... | 2006-06-12 07:17:06 | | latin1_swedish_ci | NULL |...
... +---------------------+------------+-------------------+----------+...
... +----------------+---------+
... | Create_options | Comment |
... +----------------+---------+
... | | |
... | | |
... | | |
... | | |
... +----------------+---------+
4 rows in set (0.00 sec)
The SHOW ENGINES command displays a list of all table types and indicates whether
they're available for use on your MySQL installation:
mysql> SHOW ENGINES;
+------------+---------+------------------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+------------------------------------------------------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | NO | Supports transactions and page-level locking |
| BLACKHOLE | NO | /dev/null storage engine (anything you write disappears) |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | NO | Archive storage engine |
| CSV | NO | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | NO | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| ISAM | NO | Obsolete storage engine |
+------------+---------+------------------------------------------------------------+
12 rows in set (0.00 sec)
For example, if we need a transaction-safe table on this server, we can use the InnoDB
table type.
If you decide you want to use a different table type, there are two ways to exercise your
choice. One way to do it is in the CREATE TABLE statement. For example, you can create
a new Memory table mytable as follows:
mysql> CREATE TABLE mytable (field INT(2)) type=Memory;
Query OK, 0 rows affected, 1 warning (0.08 sec)