How to modify table type in mysql

In mysql, you can use the "alter table" statement and "ENGINE clause" to modify the table type. The syntax is "alter table data table name ENGINE = required 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, and InnoBDB.

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

## Reason for the error:

Due to the MYSQL query statement Writing method

TYPE=MyISAM is too old and has been disabled in MYSQL 5.5 or above

Use sql statement to modify the table type:

alter table product  ENGINE = MyISAM; #（用ENGINE = MyISAM代替TYPE=MyISAM，修改成功了）

Extended knowledge:

• MyISAM The default MySQL plug-in storage engine, it is based on the ISAM type, but it adds many useful extensions, it is used in Web, data warehousing and other One of the most commonly used storage engines in application environments. 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. Binary migration between different platforms is possible. The type of the table is specified when creating the table. .

• 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 in MySQL tables. It saves all data in RAM and provides excellent performance in environments where references and other similar data need to be quickly found. Quick 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. It is merged mainly for performance reasons 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 can 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, text files separated by commas, 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.

[Related recommendations:

mysql video tutorial]

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