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

This article mainly introduces how to modify the table type in the mysql database, the article is very detailed, has a certain reference value, interested friends must read it!

In mysql, you can use the "alter table" statement and the "ENGINE clause" to modify the table type, with the syntax "alter table data table name ENGINE = desired table type value;".

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.

I wanted to change the table type to MyISAM, but I reported an error, as shown below:

Cause of error:

Because the MYSQL query TYPE=MyISAM is too old, it has been disabled on MYSQL 5.5 or above.

Use the SQL statement to modify the table type

Alter table product ENGINE = MyISAM; # (ENGINE = MyISAM instead of TYPE=MyISAM, modified successfully)

Expand knowledge:

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 above is all the contents of the article "how to change table types in mysql database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.