How to query table type in mysql

Query method: 1. Use the "SHOW TABLE STATUS FROM `database name`;" statement to list information about all tables in the specified database, including table types; 2. Use "SHOW CREATE TABLE `data table The `;" statement can list the information of the specified data table, including the 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, and InnoBDB.

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

BDB The full name is "Brekeley DB", which is Mysql's earliest transaction-capable table type, developed by Sleepycat Software (http://www.sleepycat.com). It provides the transaction control capability function, which ensures that a set of commands are all executed successfully, or the results of all commands are rolled back when an error occurs in any command. It can be imagined that transaction control capability is very important in electronic banking. Supports COMMIT, ROLLBACK and other transaction features. The latest version of Mysql has planned to remove support for BDB and focus on developing InnoDB.

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

Features:

• Transaction processing mechanism

• Support external links

• Can be restored immediately after a crash

• Supports foreign key function, cascade deletion

• Supports concurrency capability

• Storage method on the hard disk: InnoBDB frm

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

MyISAM The default MySQL plug-in storage engine, which is based on the ISAM type, but it adds many useful extensions. It is most commonly used in Web, data warehousing and other application environments. One of the storage engines used. Note that the default storage engine of the MySQL server can be easily changed by changing the STORAGE_ENGINE configuration variable.
Advantages:

• 1. Smaller than ISAM tables and occupying fewer resources

• 2. Can be used as binary files between different platforms The type of migration table is specified when the table is created.

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

Memory (HEAP) is the fastest accessed table among MySQL tables. It stores all data in RAM and can be used to quickly find references and other similar data. environment, providing extremely fast access. Note that the data under this type is not designed to be persistent. It is generally suitable for temporary tables. If MySQL or the server crashes, all data in the table will be lost.

Merge is a new type of table worthy of attention. It is composed of a set of MyISAM tables. The merge is mainly for performance considerations because it can improve search speed and repair. Efficiency, saving disk space. Allows a MySQL DBA or developer to logically group together a series of equivalent MyISAM tables and reference them as 1 object. It is very suitable 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 large amounts of rarely referenced historical, archival, or security audit information.

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

NDBCluster/Cluster/NDB MySQL’s clustered database engine is particularly suitable for applications with high-performance search requirements that also require the highest uptime and availability. .

CSV Uses standard CSV format, comma-delimited text files, adapted to external data exchange

Blackhole It will accept data but Not saved, and an empty set is returned for any retrieval request. It is generally suitable for distributed database designs where data is automatically copied and not saved locally. (Not sure) It seems that it can also be used to temporarily suppress/ignore application input to the database.

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

The command syntax for operating 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,
`is_read` char(1) default 'N',
`send_time` datetime default NULL,
`del_flag` tinyint(4) default '0',
PRIMARY KEY (`id`)
) ENGINE=MYISAM;
  
-- 也可以创建后更改：
ALTER TABLE `message` ENGINE = INNODB;

Viewing table types

• Method 1:
  

-- 查看表类型（存储引擎）
SHOW TABLE STATUS FROM `test`;

The result is the information of all tables in the database test, and the second item type is the table's type.

• 方法2：

  -- 查看表类型（存储引擎）2
SHOW CREATE TABLE `message`;

结果为表message创建时的信息，其中有TYPE或ENGINE一项，指定了表的类型（存储引擎）。

注意：老版本的MySQL使用 TYPE而不是ENGINE（例如，TYPE = MYISAM）。MySQL 5.1为向下兼容而支持这个语法，但TYPE现在被轻视，而ENGINE 是首先的用法。 一般地，ENGINE选项是不必要的；除非默认已经被改变了，MyISAM是默认存储引擎。

【相关推荐：mysql视频教程】

The above is the detailed content of How to query table type in mysql. For more information, please follow other related articles on the PHP Chinese website!