The difference introduction of Engine=innodb and Engine=myisam in Mysql _mysql

When you first build a database with MySQL administrator, the table defaults to the InnoDB type, and you don't care. Later, when using Access2mysql to guide the data found can only lead to myisam type of table, do not know what the difference between the two types, went to check. The MyISAM type does not support advanced processing, such as transaction processing, but InnoDB type support. Tables of the MyISAM type emphasize performance, which executes more than INNODB types faster, but does not provide transactional support, while InnoDB provides transactional support for advanced database features such as foreign keys. This makes it possible to use different storage types for different uses of the datasheet.

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

Modify:

ALTER TABLE tablename ENGINE = MyISAM;

MyISAM: This is the default type, which is based on the traditional ISAM type, and ISAM is the 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 something is rolled back it will cause an incomplete rollback and not be atomic. It is a better choice if you perform a lot of select,myisam.

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

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 transactions, causing each insert to automatically commit, seriously affecting the speed. You can call begin before executing SQL, and multiple SQL forms one thing (even if the autocommit is open), which can 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. When you create a table, the default is MyISAM if you do not specify a type, and transactions are not supported.
You can look at the type of the table with the Show CREATE TABLE tablename command.

2.1 does not have any effect on the start/commit of tables that do not support transactions, submitted before the execution of a commit, testing:
Execute a MSYQL:
Use test;
drop table if exists TN;
Create TABLE TN (a varchar (a)) Type=myisam;
drop table if exists Ty;
Create table Ty (a varchar (a)) Type=innodb;

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

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

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

3.1 InnoDB table cannot be used with repair table commands and MYISAMCHK-R table_name
But you can use check table, and Mysqlcheck [OPTIONS] database [tables]

4. The following parameters are added to the command line that launches the MySQL database to make the newly published MySQL data table default to the use transaction (
Affects only the CREATE statement. ）
--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 change 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