MySQL storage engine MyISAM and InnoDB configuration

MySQL storage engine MyISAM and InnoDB configure MyISAM and InnoDB the biggest feature: MyISAM: ① does not support transactions. In the form of ② table-level locking, the data locks the entire table when updated. ③ does not support foreign key constraints, only full-text indexing. ④ databases block each other during reads. ⑤ data is written or read separately, which is faster and takes up relatively less resources. InnoDB: ① supports transactions. ② row-level locks, but full table scans will still be table-level locks. ③ supports partitions and tablespaces, similar to Oracle databases. ④ read-write blocking is related to the transaction isolation level. ⑤ tables and primary keys are stored in clusters. ⑥ has a very efficient caching feature, caching indexes as well as data. How to view the storage engine: view the MySQL default storage engine: mysql > show engines +- -+ | Engine | Support | Comment | Transactions | XA | Savepoints | +- -- + | InnoDB | DEFAULT | Supports transactions Row-level locking, and foreign keys | YES | YES | YES to view the engines supported by MySQL and the default storage engine. View the storage engine that the table is using: method 1: show table status from library name where name=' table name'; mysql > show table status from jdy where name='test' +-+ | Name | Engine | Version | Row_format | Rows | +-+ | test | InnoDB | 10 | | Dynamic | 0 | # InnoDB method of table storage engine 2: show create table table name | Mysql > use jdy; # enter the database to view the table Database changedmysql > show create table test +- -+ | Table | Create Table | + -+ | test | CREATE TABLE "test" ("name" varchar (10) DEFAULT NULL) "id" int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 | # Table storage engine how to modify the storage engine: method 1: mysql > use jdy # enter the database Database changedmysql > show create table test to view the table +- -+ | Table | Create Table | + -+ | test | CREATE TABLE "test" ("name" varchar (10) DEFAULT NULL) "id" int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 | # Table storage engine InnoDB# command: alter table table name engine= engine Mysql > alter table test engine=MyISAM; # modify the storage engine of the table is MyISAMQuery OK, 0 rows affected (0.39 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show create table test +- -+ | Table | Create Table | + -+ | test | CREATE TABLE "test" ("name" varchar (10) DEFAULT NULL) "id" int (11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 | # method 2: modify the configuration file my.cnf of MySQL Specify default storage engine vim / etc/ my.cnf [mysqld] default-storage-engine=MyISAM # add specified default storage engine mysql > create table test01 (id int) # create table Query OK, 0 rows affected (0.00 sec) mysql > show create table test01 +-+-+ | Table | Create Table | | +-+- -- + | test01 | CREATE TABLE "test01" ("id" int (11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 | # the new table created defaults to the MyISAM setting default storage engine The new table created will use the MyISAM storage engine. Method 3: specify the storage engine mysql > create table test02 (id int) engine=InnoDB;Query OK, 0 rows affected (0.35 sec) mysql > show create table test02 when creating the table +-+-+ | Table | Create Table | | +-+- -- + | test02 | CREATE TABLE "test02" ("id" int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 | # New table storage engine is InnoDB method 4: use the mysql_convert_table_format command to batch convert [root@localhost] # mysql_convert_table_format-- host=localhost-- user=root-- password=abc123-- socket=/tmp/mysql.scok-- error will occur when engine=InnoDB jdy test01 test02 executes Need to install the operation module yum install perl perl-DBD-MySQL-y can not convert InnoDB engine, mysql_convert_table_forma command bug, need to modify the command script. Vim / usr/local/mysql/bin/mysql_convert_table_format # modify the command script $opt_help=$opt_version=$opt_verbose=$opt_force=0;$opt_user=$opt_database=$opt_password=undef;$opt_host= "localhost"; $opt_socket= ""; $opt_engine= "MYISAM"; $opt_port=0;$exit_status=0 GetOptions ("e | engine | type=s" = >\ $opt_type, # modify type to engine "f | force" = >\ $opt_force, "help |?" = >\ $opt_help, "h | host=s" = >\ $opt_host, "p | password=s" = >\ $opt_password, "u | user=s" = >\ $opt_user, "v | verbose" = >\ $opt_verbose, "V | version" = >\ $opt_version "S | socket=s" = >\ $opt_socket, "P | port=i" = >\ $opt_port) | | usage (0) Ps: the fourth method is only suitable for MySQL version 5.5. MySQL 5.5 default storage engine MyISAM 5.7 default storage engine InnoDB.