The difference between Engine=innodb and Engine=myisam in MySQL

When we first built the database with MySQL administrator, the table defaults to the InnoDB type, and I don't care. Later use Access2mysql Guide data when found can only lead to myisam type of table, do not know what the difference between the two types, went to check. It turns out that MyISAM types do not support advanced processing such as transaction processing, and InnoDB type support. The MyISAM type of table emphasizes performance, which is performed more quickly than the InnoDB type, but does not provide transactional support, while InnoDB provides transactional support for advanced database functions such as external keys. This makes it possible to use different storage types depending on the data table.

In addition, binary data files of the MyISAM type can be migrated in different operating systems. That is, it can be copied directly from the Windows system to a Linux system.

Modify:

ALTER TABLE tablename ENGINE = MyISAM;

MyISAM: This is the default type, which is based on the traditional ISAM type, ISAM is an abbreviation for indexed sequential access method (indexed sequential access methods), which is the standard way to store records and files. Compared to 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 transaction-safe and do not support foreign keys. If the rollback of a thing causes incomplete rollback, it does not have atomicity. If executing a lot of select,myisam is a better choice.

InnoDB: This type is transaction-safe. It has the same characteristics as the BDB type, and they also support foreign keys. InnoDB tables are fast. Has a richer feature than BDB, so it is recommended if a transaction-safe storage engine is required. If your data performs a large number of inserts or update, for performance reasons, you should use the InnoDB table.

For INNODB types that support things, the main reason for the speed is that the AUTOCOMMIT default setting is open, and the program does not explicitly call begin to start a transaction, resulting in an automatic commit for each insert, which seriously affects the speed. You can call begin before you execute SQL, and multiple SQL forms a thing (even if the autocommit is open), which will greatly improve performance.

===============================================================
1. More than 4.0 mysqld support transactions, including non-max versions. 3.23 requires Max version mysqld to support transactions.

2. If you do not specify a type when creating a table, the default is MyISAM, and transactions are not supported.
You can use the Show CREATE TABLE tablename command to see the type of the table.

2.1 Doing a start/commit operation on a table that does not support transactions has no effect and has been committed before committing a commit, testing:
Perform a msyql:
Use test;
drop table if exists TN;
Create TABLE TN (a varchar (ten)) Type=myisam;
drop table if exists Ty;
Create table Ty (a varchar (ten)) Type=innodb;

Begin
Insert into TN values (' a ');
Insert into Ty values (' a ');
SELECT * from TN;
select * from Ty;
Can see a record.

To perform another MySQL:
Use test;
SELECT * from TN;
select * from Ty;
Only TN can see a record
And then on the other side
Commit
Can only see the record.

3. You can execute the following command to toggle non-transactional tables to transactions (data is not lost), and the InnoDB table is more secure than the MyISAM table:
ALTER TABLE TableName TYPE=INNODB;

3.1 InnoDB table cannot use Repair Table command and MYISAMCHK-R table_name
But you can use check table, and Mysqlcheck [OPTIONS] database [tables]

4. The following parameters have been added to the command line starting the MySQL database to make the newly published MySQL data table default to use transactions (
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. Temporarily changing the default table type can be used:
Set Table_type=innodb;
Show variables like ' table_type ';
Or:
C:/mysql/bin/mysqld-max-nt--standalone--default-table-type=innodb

The difference between Engine=innodb and Engine=myisam in MySQL