Specify A Storage Engine

• When creating a new table, one can specify which storage engine to use by adding an ENGINE or TYPE table option to the CREATE TABLE statement.

  CREATE TABLE table ENGINE = INNODB;
  

  CREATE TABLE table TYPE = MEMORY;

  • The older term TYPE is supported as a synonym for ENGINE for backward compatibility.
  • ENGINE is the preferred term and TYPE is deprecated.

• If the ENGINE or TYPE option is omitted, the default storage engine is used.
  • Normally, this is MyISAM.

• To change the default storage engine at start up use the --default-storage-engine or --default-table-type server startup option.
  • Setting the default-storage-engine or default-table-type option in the my.cnf configuration file will also change the default storage engine.

• 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;

• Use an ALTER TABLE statement to convert a table from one storage engine to another.

  ALTER TABLE table ENGINE = MYISAM;
  

  ALTER TABLE table TYPE = BDB;

Should I use transaction-safe tables or non-transaction-safe tables?

---

---

© 2007 John Michael Pierobon

Notes