Mysql viewing table type_mysql

Mysql viewing table types Mysql viewing table types InnoDB

Problem description:

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

These six types are divided into two categories: transaction-safe, including BDB and InnoDB, and the other are in the second category, it is called non-transaction security (non-transaction-safe ).

Solution:

The storage engine explains how to store data, create indexes for the stored data, and update and query data. Because data in relational databases is stored as tables, the storage engine can also be called the table type (that is, the type of storing and operating the table ).

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

Features:

Transaction processing mechanism
Support for external links
Immediate recovery after crash
Supports the foreign key function and cascading deletion
Support concurrency
Storage method on hard disk: InnoBDB frm

The latest version of Mysql has planned to remove support for BDB and develop InnoDB. InnoDB provides better support for Mysql features and is active in the development community.

MyISAM's default MySQL plug-in storage engine, which 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: by changing the STORAGE_ENGINE configuration variables, you can easily change the default storage engine of the MySQL server.
Advantages:

1. smaller than the ISAM table, with less resources
2. you can specify the type of binary porting table between different platforms when creating the table.

(1) view storage engines supported by the database

Command: show engines;

Note: shows the storage engine currently supported by mysql.

We can see that mysql 5.5 is used here, and InnoDB database engine is used by default, which is the security transaction engine.

(2) view the table structure

Command: desc table_name;

(3) display the table creation statement

Note:

The above shows the default table creation statement. as shown above, mysql5.5 uses the InnoDB engine by default and uses UTF-8 encoding.

(4) display the current status value of the table

(5) engine for modifying database tables

Note: By default, the data table created by mysql5.5 is InnoDB. The structure information of the table is as follows:

Modify the database engine of a table

After modification, re-view the mysql database engine