MySQL table type MyISAM and InnoDB differences

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:mysqlbinmysqld-max-nt–standalone–default-table-type=innodb