What is the difference between engine = innodb and engine = myisam in mysql?

The difference between engine = innodb and engine = myisam in mysql is that when MySQL Administrator is used to create a database, the table is of the InnoDB type by default. Later, when using Access2MySQL to export data, I found that only tables of the MyISAM type can be imported. I checked the differences between the two types. Originally, the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB, but transactions are not supported. InnoDB provides advanced database functions such as external keys for transactions. In this way, Different Storage types can be used for different data tables. In addition, binary data files of the MyISAM type can be migrated in different operating systems. That is, it can be copied directly from Windows to linux. Modify:

ALTER TABLE tablename ENGINE = MyISAM ；

 

MyISAM: This is the default type. It is based on the traditional ISAM type, and ISAM is the abbreviation of Indexed Sequential Access Method (Sequential Access Method with indexes, it is a standard method for storing records and files. compared with other storage engines, MyISAM has most tools for checking and repairing tables. myISAM tables can be compressed and support full-text search. they are not transaction-safe and do not support foreign keys. If a transaction is rolled back, incomplete rollback is not atomic. If you execute a large number of SELECT statements, MyISAM is a better choice. InnoDB: This type is transaction-safe. it has the same features as the BDB type and supports foreign keys. the InnoDB table is fast. it has more features than BDB. Therefore, if you need a transaction-safe storage engine, we recommend that you use it. if your data executes a large number of INSERT or UPDATE operations, InnoDB tables should be used for performance considerations. For the InnoDB type labels that support transactions, the main cause of the impact on the speed is that AUTOCOMMIT is enabled by default, and the program does not explicitly call BEGIN to start the transaction, resulting in automatic Commit for each inserted entry, seriously affecting the speed. You can call begin before executing the SQL statement. Multiple SQL statements form a transaction (even if you open the autocommit statement), which greatly improves the performance. ========================================================== ======================================= 1. more than 4.0 mysqld supports transactions, including non-max versions. 3.23 requires the max version mysqld to support transactions. 2. If no type is specified during table creation, the default value is myisam. transactions are not supported. You can run the show create table tablename command to view the table type. 2.1 The start/commit operation has no effect on tables that do not support transactions. It has been submitted before the execution of the commit operation. Test: Execute an msyql:

use test;drop table if exists tn;create table tn (a varchar(10)) type=myisam;drop table if exists ty;create table ty (a varchar(10)) type=innodb;begin;insert into tn values('a');insert into ty values('a');select * from tn;select * from ty;

 

You can see that one record executes another mysql:

use test;select * from tn;select * from ty;

 

Only one record can be viewed by tn and then commit on the other side. 3. You can run the following command to switch the non-transaction table to the transaction (data will not be lost). The innodb table is safer than the myisam table:

alter table tablename type=innodb;

 

3.1 innodb tables cannot use the repair table command and myisamchk-r table_name, but can use check table and mysqlcheck [OPTIONS] database [tables] 4. the following parameters are added to the command line that starts the mysql database to enable the newly released mysql data tables to use transactions by default (only the create statement is affected .)

--default-table-type=InnoDB

 

Test command:

use test;drop table if exists tn;create table tn (a varchar(10));show create table tn;

 

5. You can temporarily change the default table Type:

Set table_type = InnoDB; show variables like 'table _ type'; or: c:/mysql/bin/mysqld-max-nt -- standalone -- default-table-type = InnoDB