How to modify the type of table in mysql

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 encountered a problem of modifying the MySQL table type. When I used phpmyadmin to manage the mysql database, the table created was MyISAM type by default, and it was more convenient to modify the table type. However, there is a problem configuring phpmyadmin now, and I am too lazy to do it, so I installed another mysql management tool, which is more convenient. However, the new table I created is of InnoDB type by default, and when I migrated in the past, this type was prone to problems. Although I couldn't tell which type was better, I felt that I liked MyISAM better, so I tried to convert it to MyISAM type. I have been looking for this tool for a long time but I can't find how to modify it. I asked Shuishen and I didn't know. Fortunately, I found the sql command on the Internet to modify the sentence. It seems that the sql statement is still powerful.

Sql statement to modify the mysql table type:

alter table 表名 type = MyISAM;
alter table 表名 type = InnoDB;

Attached mysql table type description

MyISAM: This is the default type , it is based on the traditional ISAM type, ISAM is the abbreviation of Indexed Sequential Access Method (indexed sequential access method), which is a standard method for storing records and files. Compared to other storage engines, MyISAM has most of the tools to check and repair tables. MyISAM tables can be compressed, and they support full-text search. They are not transaction safe and do not support foreign keys. If things are rolled back, it will cause an incomplete rollback and is not atomic. If you perform a large number of SELECTs, MyISAM is a better choice.

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

For InnoDB type labels that support things, the main reason that affects 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 inserted item, which seriously affects speed. You can call begin before executing the SQL. Multiple SQLs form one transaction (even if autocommit is turned on), which will greatly improve performance.

mysql supported types (engines): MyISAM, InnoDB, BDB, MEMORY, MERGE, EXAMPLE, NDBCluster, ARCHIVE, CSV, BLACKHOLE, FEDERATED, etc., among which InnoDB and BDB provide transaction safety tables, others Storage engines are non-transaction safe tables.

[Related recommendations: mysql video tutorial]

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