< Day Day Up > |
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 InnoDB , which is covered in Chapter 9, "The InnoDB Storage Engine." When you create a new table, you can tell MySQL what type of table to create by adding an ENGINE or TYPE table option to the CREATE TABLE statement: 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. If you omit the ENGINE or TYPE option, the default table type is usually MyISAM . This can be changed by setting the table_type system variable. To convert a table from one type to another, use an ALTER TABLE statement that indicates the new type: ALTER TABLE t ENGINE = MYISAM; ALTER TABLE t TYPE = BDB; 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 of 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 MyISAM table type when an unavailable type is specified can be confusing for new MySQL users. In MySQL 4.1 and up, a warning is generated when a table type is automatically changed. 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 table type. 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. Transaction-safe tables (TSTs) have several advantages over non-transaction-safe tables (NTSTs):
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 9.4, "InnoDB Configuration." Non-transaction-safe tables have several advantages of their own, all of which occur because there is no transaction overhead:
You can combine transaction-safe and non-transaction-safe tables in the same statements to get the best of both worlds . However, within a transaction with autocommit disabled, changes to non-transaction-safe tables still are committed immediately and cannot be rolled back. |
< Day Day Up > |