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

This article mainly introduces mysql how to modify the table type, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

In mysql, you can use the alter command to modify the table type, which can be used to modify the data table name or data table field. When the parameter is set to the table type, the table that specifies the table name changes the table type with the syntax of "alter table table name type= table type".

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

When we need to change the data table name or modify the data table field, we need to use the MySQL ALTER command.

Recently, we encountered a problem of changing the mysql table type. In the past, when phpmyadmin managed the mysql database, the established table defaulted to MyISAM type, and it was convenient to change the table type.

However, there is something wrong with the configuration of phpmyadmin now, and I don't bother to do it, so I install another mysql management tool, which is more convenient. But my new table is InnoDB by default, and this type is prone to problems when I migrated in the past. Although I can't tell which type is good, I feel like MyISAM better, so I try to change it to MyISAM type.

Modify the sql statement of the mysql table type:

Alter table table name type = MyISAM;alter table table name type = InnoDB

Attached mysql table type description

MyISAM: this is the default type, which is based on the traditional ISAM type. ISAM, an abbreviation for Indexed Sequential Access Method (indexed sequential access method), is the standard way to store records and files. Compared with other storage engines, MyISAM has most of the tools for checking and repairing tables. MyISAM tables can be compressed, and they support full-text search. They are not transactional safe and do not support foreign keys. If things are rolled back, it will result in incomplete rollback, which is not atomic. It is better to perform a large amount of SELECT,MyISAM.

InnoDB: this type is transaction safe. It has the same features as the BDB type, and they also support foreign keys. InnoDB tables are fast and have richer features than BDB, so if you need a transaction-safe storage engine, it is recommended. If your data does a lot of INSERT or UPDATE, you should use the InnoDB table for performance reasons.

For the InnoDB type label that supports things, the main reason for affecting the speed is AUTOCOMMI, the default setting is on, and the program does not explicitly call BEGIN to start the transaction, resulting in automatic Commit for each insert, seriously affecting the speed. You can call begin before executing sql, and multiple sql forms a single thing (even if the autocommit is opened), which will greatly improve performance.

Thank you for reading this article carefully. I hope the article "how to modify the table type of mysql" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.