MySQL supports several storage engines that act as handlers for different table types. MySQL storage engines include both those that handle transaction-safe tables and those that handle non-transaction-safe tables:

This chapter describes each of the MySQL storage engines except for NDBCLUSTER, which is covered in Chapter 15, MySQL Cluster.

When you create a new table, you can specify which storage engine to use by adding an ENGINE or TYPE table option to the CREATE TABLE statement:

Do you need more help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related MySQL Links Downloads common doc downloads guilds img programs sources support tech-resources user-groups usingmysql workbench Request more information about Pantek

CREATE TABLE t (i INT) ENGINE = INNODB;
CREATE TABLE t (i INT) TYPE = MEMORY;

ENGINE is the preferred term, but cannot be used before MySQL 4.0.18. TYPE is available beginning with MySQL 3.23.0, the first version of MySQL for which multiple storage engines were available. TYPE is supported for backward compatibility but is deprecated.

If you omit the ENGINE or TYPE option, the default storage engine is used. Normally, this is MyISAM, but you can change it by using the --default-storage-engine or --default-table-type server startup option, or by setting the default-storage-engine or default-table-type option in the my.cnf configuration file.

You can set the default storage engine to be used during the current session by setting the storage_engine or table_type variable:

SET storage_engine=MYISAM;
SET table_type=BDB;

When MySQL is installed on Windows using the MySQL Configuration Wizard, the InnoDB storage engine can be selected as the default instead of MyISAM. See Section 2.3.4.6, “The Database Usage Dialog”.

To convert a table from one storage engine to another, use an ALTER TABLE statement that indicates the new engine:

ALTER TABLE t ENGINE = MYISAM;
ALTER TABLE t TYPE = BDB;

See Section 12.1.5, “CREATE TABLE Syntax”, and Section 12.1.2, “ALTER TABLE Syntax”.

If you try to use a storage engine that is not compiled in or that is compiled in but deactivated, MySQL instead creates a table using the default storage engine, usually MyISAM). (Before MySQL, MyISAM is always used for unavailable storage engines.) type MyISAM. This behavior is convenient when you want to copy tables between MySQL servers that support different storage engines. (For example, in a replication setup, perhaps your master server supports transactional storage engines for increased safety, but the slave servers use only non-transactional storage engines for greater speed.)

This automatic substitution of the default storage engine for unavailable engines can be confusing for new MySQL users. In MySQL 4.1, a warning is generated when a storage engine is automatically changed.

Can we help you?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related MySQL Links Downloads common doc downloads guilds img programs sources support tech-resources user-groups usingmysql workbench Request more information about Pantek

For new tables, MySQL always creates an .frm file to hold the table and column definitions. The table's index and data may be stored in one or more other files, depending on the storage engine. The server creates the .frm file above the storage engine level. Individual storage engines create any additional files required for the tables that they manage.

A database may contain tables of different types. That is, tables need not all be created with the same storage engine.

Transaction-safe tables (TSTs) have several advantages over non-transaction-safe tables (NTSTs):

You can combine transaction-safe and non-transaction-safe tables in the same statements to get the best of both worlds. However, although MySQL supports several transaction-safe storage engines, for best results, you should not mix different storage engines within a transaction with autocommit disabled. For example, if you do this, changes to non-transaction-safe tables still are committed immediately and cannot be rolled back. For information about this and other problems that can occur in transactions that use mixed storage engines, see Section 12.4.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.

Note that to use the InnoDB storage engine in MySQL 3.23, you must configure at least the innodb_data_file_path startup option. In 4.0 and up, InnoDB uses default configuration values if you specify none. See Section 13.2.4, “InnoDB Configuration”.

Non-transaction-safe tables have several advantages of their own, all of which occur because there is no transaction overhead: