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? [Copy to Clipboard] View Code SQL

123456789101112131415161718192021222324

----method of specifying table type when creating table create TABLE ' message ' (' id ' int (one) not null auto_increment, ' sender ' varchar (255) Default NULL, ' Receive R ' varchar (255) default NULL, ' content ' text, ' Is_read ' char (1) Default ' N ', ' send_time ' datetime default NULL, ' Del_flag ' Ti Nyint (4) Default ' 0 ', PRIMARY KEY (' id ')) Engine=myisam; --can also be created after change: Alter TABLE ' message ' ENGINE = INNODB; --View table type (storage engine) Show Table STATUS from ' test ';----result is the information for all tables in the database test, where the second item type is the kind of table. --View table type (storage engine) 2SHOW CREATE table ' message ';----result is the information at the time the table message was created, with type or engine an item specifying the type of table (storage engine). --Show tables cannot get information about the table type.

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 type of table (GO)