Differences between engine = innodb and engine = myisam in mysql _ MySQL

Difference between engineinnodb and enginemyisam in mysql bitsCN.com

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

When I first used MySQL Administrator to create a database, the table is of the InnoDB type by default, so I did not care about it. 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, AUTOCOMMIT is enabled by default, and the program does not explicitly call BEGIN to start the transaction. as a result, each inserted entry is automatically Commit, the speed is seriously affected. 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. MySQL 4.0 and above support 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 on a table that does not support transactions has no effect. 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 a record.

Execute another mysql:

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

Only tn can see one record

Then on the other side

Commit;

You can see the record.

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

However, you can use check table and mysqlcheck [OPTIONS] database [tables]

4. the following parameters are added to the Command Line for starting 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

BitsCN.com