MySQL View types of tables

MySQL data tables support six types, namely: BDB, HEAP, ISAM, MERGE, MYISAM, Innobdb.

These six types are divided into two categories: "Transaction-Safe" (Transaction-safe), including BDB and InnoDB, while the rest fall into the second category, known as "non-Transactional Security" (Non-transaction-safe).

The BDB full name is "Brekeley DB", which is the first type of MySQL table with transactional capabilities, developed by Sleepycat Software (http://www.sleepycat.com). It provides transaction control capabilities that ensure that a set of commands are executed successfully, or that the results of all commands are rolled back when there is an error in any command, and it is important to imagine the transaction control capability in an electronic bank. Supports commit, rollback, and other transactional features. The latest version of MySQL is already planning to remove support for BDB and to develop InnoDB in full.

InnoDB is a newer, transaction-safe storage engine for transactional applications that supports almost all of the features of BDB and has many new features, including ACID transaction support.

Characteristics:

• Transaction processing mechanism

• Support for external chain

• Can recover immediately after a crash

• Support for foreign key function, cascade Delete

• Support concurrency Capabilities

• How to store on your hard disk: innobdb frm

The latest version of MySQL is already planning to remove support for BDB and to develop InnoDB in full. InnoDB has better feature support for MySQL, and the development community is active.

MyISAM is the default MySQL plug-in storage engine, which is based on the ISAM type, but it adds a number of useful extensions that are one of the most commonly used storage engines in the Web, data warehousing, and other application environments. Note that it is easy to change the default storage engine of the MySQL server by changing the storage_engine configuration variable.
Advantages:

• 1. Smaller than the ISAM table, with less resources

• 2. The types of binary migration tables that can be made between different platforms are specified when the table is created.

ISAM is the default type used by MySQL tables before the MyISAM type appears, and is now replaced by MyISAM.

Memory (HEAP) is the fastest-accessing table in the MySQL table, storing all the data in RAM, providing extremely fast access in environments where you need to quickly find references and other similar data. Note that this type of data is non-persistent, it is generally adapted to temporary tables, and if MySQL or server crashes, all data in the table is lost.

Merge is an interesting new type of table, which is composed of a set of MyISAM tables, which are combined primarily for performance reasons, because it can improve the search speed, improve the efficiency of repair, save disk space. Allows a MySQL DBA or developer to logically group together a series of equivalent MyISAM tables and reference them as 1 objects. Ideal for VLDB environments such as data warehousing. Sometimes it appears as a mrg_myisam name.

Archive provides the perfect solution for storing and retrieving a large number of rarely cited historical, archival, or security audit information.

Federated can link multiple separate MySQL servers to create a logical database from multiple physical servers. Ideal for distributed environments or data mart environments.

ndbcluster/cluster/ndb MySQL's clustered database engine, especially for applications with high performance lookup requirements, also requires the highest uptime and availability.

CSV using standard CSV format, comma-delimited text file, adapted to external data exchange

blackhole It accepts data but does not save it, and returns an empty set for any retrieval request. It is generally adapted to the distributed database design where data is automatically copied and not stored locally. (indeterminate) may also be used to temporarily disallow/ignore application input to the database.

Example This is a test engine, you can create a table like this, but you cannot write data or retrieve data. It seems to be a sample engine provided for MySQL developers.

The command syntax for manipulating and viewing table types is as follows

123456789101112131415161718192021222324

---- 创建表时指定表类型的方法CREATETABLE`message` (`id` int(11) NOT NULLauto_increment,`sender` varchar(255) defaultNULL,`receiver` varchar(255) defaultNULL,`content` text,`is_read` char(1) default‘N‘,`send_time` datetime defaultNULL,`del_flag` tinyint(4) default‘0‘,PRIMARYKEY(`id`)) ENGINE=MYISAM; -- 也可以创建后更改：ALTERTABLE`message` ENGINE = INNODB; -- 查看表类型（存储引擎）SHOW TABLESTATUS FROM`test`;-- -- 结果为数据库test中所有表的信息，其中第二项type即是表的类型。  -- 查看表类型（存储引擎）2SHOW CREATETABLE`message`;-- -- 结果为表message创建时的信息，其中有TYPE或ENGINE一项，指定了表的类型（存储引擎）。-- show tables不能得到表类型的信息。

Note: The old version of MySQL uses type instead of engine (for example, type = MYISAM). MySQL 5.1 supports this syntax for backwards compatibility, but the type is now despised, and the engine is the first usage. In general, the engine option is not necessary; MyISAM is the default storage engine unless it has been changed by default.

Portability of data tables

Common data Migration Method: Export the contents of the data table to a text file, then copy to the hard disk, and then import into the target database.

For file-level operations, some data tables can be moved directly by copying the table files individually, as shown in the following table

ISAM No

myiasm Yes

BDB No

innobdb Yes

MySQL View types of tables