Database Reference
In-Depth Information
In Ruby DBI, do this:
tbl_name
=
"tmp_tbl_"
+
dbh
.
func
(
:thread_id
)
.
to_s
4.5. Checking or Changing a Table Storage Engine
Problem
You want to check which storage engine a table uses so that you can determine what
engine capabilities are applicable. Or you need to change a table's storage engine because
you realize that the capabilities of another engine are more suitable for the way you use
the table.
Solution
To determine a table's storage engine, you can use any of several statements. To change
the table's engine, use
ALTER
TABLE
with an
ENGINE
clause.
Discussion
MySQL supports multiple storage engines, which have differing characteristics. For
example, the InnoDB engine supports transactions, whereas MyISAM does not. If you
need to know whether a table supports transactions, check which storage engine it uses.
If the table's engine does not support transactions, you can convert the table to use a
transaction-capable engine.
To determine the current engine for a table, check
INFORMATION_SCHEMA
or use the
SHOW
TABLE
STATUS
or
SHOW
CREATE
TABLE
statement. For the
mail
table, obtain engine in‐
formation as follows:
mysql>
SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES
->
WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'mail';
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
mysql>
SHOW TABLE STATUS LIKE 'mail'\G
*************************** 1. row ***************************
Name: mail
Engine: InnoDB
…
mysql>
SHOW CREATE TABLE mail\G
*************************** 1. row ***************************
Table: mail
Create Table: CREATE TABLE `mail` (
