How to modify table type in mysql

In mysql, you can use the alter command to modify the table type. This command can be used to modify the data table name or modify the data table fields. When the parameter is set to the table type, the table with the specified table name will be modified. Table type, the syntax is "alter table table name type=table type".

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

How does mysql modify the table type

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

Recently encountered a problem of modifying the mysql table type. In the past, when phpmyadmin managed the mysql database, the table created was of MyISAM type by default, and it was more convenient to modify the table type.

However, there is something wrong with configuring phpmyadmin now, and I am too lazy to fix 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.

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, which is based on the traditional ISAM type, ISAM is Indexed Abbreviation for 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.

Recommended learning: 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!