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

This article mainly introduces "how mysql modifies the type of table". In daily operation, I believe many people have doubts about how to modify the type of table in mysql. The editor consulted all kinds of data and sorted out a simple and easy-to-use method of operation. I hope it will be helpful to answer the question of "how to modify the type of table in mysql". Next, please follow the editor to study!

In mysql, you can use the "alter table" statement and the "type" keyword to modify the type of the table. The "alter table" statement is used to change the structure of the original table. The syntax format is "alter table table name type = specified type name;".

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

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. After looking for this tool for a long time, I didn't find how to modify it, and I didn't know if I asked the water god students. Fortunately, I found the sql command to modify the sentence on the Internet. It seems that the sql sentence is still powerful.

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.

The types (engines) supported by mysql: MyISAM, InnoDB, BDB, MEMORY, MERGE, EXAMPLE, NDBCluster, ARCHIVE, CSV, BLACKHOLE, FEDERATED, etc., in which InnoDB and BDB provide transaction security tables, and other storage engines are non-transaction security tables.

At this point, the study on "how to modify the type of table by mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.