Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to query table types in mysql. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Query method: 1. Use the "SHOW TABLE STATUS FROM `database name`;" statement to list the information of all tables in the specified database, including the table type; 2, use the "SHOW CREATE TABLE `data table name `;" statement to list the information of the specified data table, including table type.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

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

These six categories are divided into two categories, one is "transaction security" (transaction-safe), including BDB and InnoDB;, the rest belong to the second category, called "non-transaction security" (non-transaction-safe).

BDB, whose full name is "Brekeley DB", is the earliest type of transactional table in Mysql, developed by Sleepycat Software (http://www.sleepycat.com). It provides the function of transaction control ability, which ensures that a set of commands are executed successfully, or the results of all commands are backed back when any command goes wrong. It is conceivable that transaction control ability is very important in electronic banking. Support for COMMIT, ROLLBACK, and other transaction features. The latest version of Mysql has planned to remove support for BDB and focus on InnoDB instead.

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

Properties:

Transaction processing mechanism

Support outer chain

Can recover immediately after a crash

Support for foreign key function, cascading deletion

Support for concurrency

Storage method on hard disk: InnoBDB frm

The latest version of Mysql has planned to remove support for BDB and focus on InnoDB instead. InnoDB has better feature support for Mysql, and the development community is active.

MyISAM's default MySQL plug-in storage engine is based on the ISAM type, but it adds many useful extensions. It is one of the most commonly used storage engines in Web, data warehousing, and other application environments. Note that you can easily change the default storage engine of the MySQL server by changing the STORAGE_ENGINE configuration variable.

Advantages:

1. Smaller and takes up less resources than ISAM tables

two。 The type of binary migration table between different platforms can be specified when the table is created.

ISAM is the default type used by the MySQL table before the advent of the MyISAM type, and it has now been replaced by MyISAM.

Memory (HEAP) is the fastest access table in the MySQL table, saving all data in RAM, providing extremely fast access in environments that need to quickly find references and other similar data. Note that data in this type is designed for non-persistence and is generally suitable for temporary tables where all data is lost if the MySQL or server crashes.

Merge is a new table worthy of attention, which is composed of a set of MyISAM tables, which are merged mainly for performance considerations, because it can improve search speed, repair efficiency, and save disk space. Allows MySQL DBA or developers to logically group a series of equivalent MyISAM tables together and reference them as one object. Ideal for VLDB environments such as data warehousing. Sometimes it appears under the name MRG_MYISAM.

Archive provides the perfect solution for the storage and retrieval of a large number of rarely referenced historical, archiving, or security audit information.

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

NDBCluster/Cluster/NDB MySQL's clustered database engine is especially suitable for applications with high-performance lookup requirements, which also require the highest uptime and availability.

CSV uses the standard CSV format, comma-separated text files, suitable for external data exchange

Blackhole it accepts data but does not save it, and returns an empty set for any retrieval request. It is generally suitable for distributed database design where data is replicated automatically and not saved locally. It seems that (uncertain) can also be used to temporarily disable / ignore application input to the database.

Example this is a test engine, you can create such a table, but you can neither write data nor retrieve data. It seems to be a sample engine for MySql developers.

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

-CREATE TABLE `message` (`id` int (11) NOT NULL auto_increment, `sender` varchar (255) default NULL, `receiver` varchar (255) default NULL, `content` text, `send_ read`char (1) default 'Number`, `send_ time` datetime default NULL, `del_ time` tinyint (4) default' 0simplified KEY (`id`) ENGINE=MYISAM; when creating a table-- can also be changed after creation: ALTER TABLE `message` ENGINE= INNODB

View table types

Method 1:

-check the table type (storage engine) SHOW TABLE STATUS FROM `test`. The result is the information of all tables in the database test, where the second item type is the table type.

Method 2:

-- View the table type (storage engine) 2SHOW CREATE TABLE `message`

The result is the information when the table message was created, with either TYPE or ENGINE, which specifies the type of table (storage engine).

Note: older versions of MySQL use TYPE instead of ENGINE (for example, TYPE = MYISAM). MySQL 5.1 supports this syntax for backward compatibility, but TYPE is now despised, and ENGINE is the first usage. In general, the ENGINE option is unnecessary; unless the default has been changed, MyISAM is the default storage engine.

This is the end of this article on "how to query table types in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.