1.查看引擎mysql> show variables like 'table_type';Empty set (0.00 sec)mysql> show engines \G*************************** 1. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES XA: YES Savepoints: YES*************************** 2. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance SchemaTransactions: NO XA: NO Savepoints: NO*************************** 3. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tablesTransactions: NO XA: NO Savepoints: NO*************************** 4. row *************************** Engine: CSV Support: YES Comment: CSV storage engineTransactions: NO XA: NO Savepoints: NO*************************** 5. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engineTransactions: NO XA: NO Savepoints: NO*************************** 6. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tablesTransactions: NO XA: NO Savepoints: NO6 rows in set (0.00 sec)mysql> show variables like 'have%';+----------------------+-------+| Variable_name | Value |+----------------------+-------+| have_compress | YES || have_crypt | YES || have_csv | YES || have_dynamic_loading | YES || have_geometry | YES || have_innodb | YES || have_ndbcluster | NO || have_openssl | NO || have_partitioning | YES || have_profiling | YES || have_query_cache | YES || have_rtree_keys | YES || have_ssl | NO || have_symlink | YES |+----------------------+-------+14 rows in set (0.00 sec)2.myisam引擎相关mysql> use test1;Database changedmysql> create table ai( -> i bigint(20) not null auto_increment, -> primary key(i) -> ) engine=myisam default charset=gbk;Query OK, 0 rows affected (0.01 sec)mysql> create table country( -> country_id smallint unsigned not null auto_increment, -> country varchar(50) not null, -> last_update timestamp not null default current_timestamp on update current_timestamp, primary key(country_id) -> ) engine = innodb default charset=gbk;Query OK, 0 rows affected (0.02 sec)mysql> alter table ai engine = innodb;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table ai \G;*************************** 1. row *************************** Table: aiCreate Table: CREATE TABLE `ai` ( `i` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`)) ENGINE=InnoDB DEFAULT CHARSET=gbk1 row in set (0.01 sec)ERROR: No query specifiedmysql> create table myisam_char(name char(10)) engine=myisam;Query OK, 0 rows affected (0.01 sec)mysql> insert into myisam_char values('abcde'),('abcde '),(' abcde'),(' abcde ');Query OK, 4 rows affected, 2 warnings (0.00 sec)Records: 4 Duplicates: 0 Warnings: 2mysql> select name,length(name) from myisam_char;+------------+--------------+| name | length(name) |+------------+--------------+| abcde | 5 || abcde | 5 || abc | 10 || abc | 10 |+------------+--------------+4 rows in set (0.00 sec)mysql> truncate myisam_char;Query OK, 0 rows affected (0.01 sec)mysql> select * from myisam_char;Empty set (0.00 sec)mysql> insert into myisam_char values -> ('abcde'), -> ('abcde '), -> (' abcde'), -> (' abcde ');Query OK, 4 rows affected (0.01 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> select * from myisam_char;+---------+| name |+---------+| abcde || abcde || abcde || abcde |+---------+4 rows in set (0.00 sec)mysql> select name,length(name) from myisam_char;+---------+--------------+| name | length(name) |+---------+--------------+| abcde | 5 || abcde | 5 || abcde | 7 || abcde | 7 |+---------+--------------+4 rows in set (0.00 sec)3.自增长mysql> use test1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table autoincre_demo -> ( i smallint not null auto_increment, -> name varchar(10),primary key(i) -> ) engine = innodb;Query OK, 0 rows affected (0.02 sec)mysql> insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3');Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from autoincre_demo;+---+------+| i | name |+---+------+| 1 | 1 || 2 | 2 || 3 | 3 |+---+------+3 rows in set (0.00 sec)mysql> insert into autoincre_demo values(4,'4');Query OK, 1 row affected (0.01 sec)mysql> select last_insert_id();+------------------+| last_insert_id() |+------------------+| 2 |+------------------+1 row in set (0.00 sec)mysql> insert into autoincre_demo(name) values('5'),('6'),('7');Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select last_insert_id();+------------------+| last_insert_id() |+------------------+| 5 |+------------------+1 row in set (0.00 sec)mysql> alter table autoincre_demo rename autoincre_demo_old;Query OK, 0 rows affected (0.02 sec)mysql> create table autoincre_demo (d1 smallint not null auto_increment, d2 smallint not null, name varchar(10), index(d2,d1) ) engine = myisam;Query OK, 0 rows affected (0.02 sec)mysql> insert into autoincre_demo (d2,name) values (2,'2'), (3,'3'), (4,'4'), (2,'2'), (3,'3'), (4,'4');Query OK, 6 rows affected, 1 warning (0.01 sec)Records: 6 Duplicates: 0 Warnings: 1mysql> select * from autoincre_demo;+----+----+------+| d1 | d2 | name |+----+----+------+| 1 | 2 | 2 || 1 | 3 | 3 || 1 | 4 | 4 || 2 | 2 | 2 || 2 | 3 | 3 || 2 | 4 | 4 |+----+----+------+6 rows in set (0.00 sec)4.主外键关联mysql> alter table country rename country_old;Query OK, 0 rows affected (0.02 sec)mysql> create table country( country_id smallint unsigned not null auto_increment, country varchar(50) not null, last_update timestamp not null default current_timestamp on update current_timestamp, primary key( country_id) ) engine = innodb default charset=utf8;Query OK, 0 rows affected (0.03 sec)mysql> create table city( -> city_id smallint unsigned not null auto_increment, -> city varchar(50) not null, country_id smallint unsigned not null, -> last_update timestamp not null default current_timestamp on update current_timestamp, -> primary key(city_id), key idx_fk_country_id(country_id), -> constraint fk_city_country foreign key(country_id) references country(country_id) on delete restrict on update cascade -> ) engine = innodb default charset=utf8;Query OK, 0 rows affected (0.02 sec)mysql> insert into country(country_id,country) values (1,'tom');Query OK, 1 row affected (0.01 sec)mysql> select * from country where country_id =1;+------------+---------+---------------------+| country_id | country | last_update |+------------+---------+---------------------+| 1 | tom | 2015-10-02 20:48:15 |+------------+---------+---------------------+1 row in set (0.00 sec)mysql> insert into city(city_id,city,country_id) values ('251','bill',1);Query OK, 1 row affected (0.01 sec)mysql> select * from city where country_id = 1;+---------+------+------------+---------------------+| city_id | city | country_id | last_update |+---------+------+------------+---------------------+| 251 | bill | 1 | 2015-10-02 20:48:51 |+---------+------+------------+---------------------+1 row in set (0.00 sec)mysql> update country set country_id = 10000 where country_id = 1;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from country where country='tom';+------------+---------+---------------------+| country_id | country | last_update |+------------+---------+---------------------+| 10000 | tom | 2015-10-02 20:49:29 |+------------+---------+---------------------+1 row in set (0.00 sec)mysql> select * from city where city_id = 251;+---------+------+------------+---------------------+| city_id | city | country_id | last_update |+---------+------+------------+---------------------+| 251 | bill | 10000 | 2015-10-02 20:48:51 |+---------+------+------------+---------------------+1 row in set (0.00 sec)mysql> show table status like 'city' \G*************************** 1. row *************************** Name: city Engine: InnoDB Version: 10 Row_format: Compact Rows: 1 Avg_row_length: 16384 Data_length: 16384Max_data_length: 0 Index_length: 16384 Data_free: 0 Auto_increment: 252 Create_time: 2015-10-02 20:47:27 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)mysql> show table status like 'country' \G*************************** 1. row *************************** Name: country Engine: InnoDB Version: 10 Row_format: Compact Rows: 1 Avg_row_length: 16384 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 2 Create_time: 2015-10-02 20:42:25 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)mysql> desc country;+-------------+----------------------+------+-----+-------------------+-----------------------------+| Field | Type | Null | Key | Default | Extra |+-------------+----------------------+------+-----+-------------------+-----------------------------+| country_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || country | varchar(50) | NO | | NULL | || last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |+-------------+----------------------+------+-----+-------------------+-----------------------------+3 rows in set (0.00 sec)mysql> select * from city;+---------+------+------------+---------------------+| city_id | city | country_id | last_update |+---------+------+------------+---------------------+| 251 | bill | 10000 | 2015-10-02 20:48:51 |+---------+------+------------+---------------------+1 row in set (0.00 sec)5.memory引擎mysql> create table tab_memory engine = memory select city_id,city,country_id from city group by city_id;Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select count(*) from tab_memory;+----------+| count(*) |+----------+| 1 |+----------+1 row in set (0.00 sec)mysql> show table status like 'tab_memory' \G*************************** 1. row *************************** Name: tab_memory Engine: MEMORY Version: 10 Row_format: Fixed Rows: 1 Avg_row_length: 155 Data_length: 127040Max_data_length: 32505825 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2015-10-02 20:53:16 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)mysql> create index mem_hash using hash on tab_memory(city_id);Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> show index from tab_memory \G;*************************** 1. row *************************** Table: tab_memory Non_unique: 1 Key_name: mem_hash Seq_in_index: 1 Column_name: city_id Collation: NULL Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: HASH Comment: Index_comment: 1 row in set (0.00 sec)ERROR: No query specifiedmysql> drop index mem_hash on tab_memory;Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> create index mem_hash using btree on tab_memory(city_id);Query OK, 1 row affected (0.02 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> show index from tab_memory \G*************************** 1. row *************************** Table: tab_memory Non_unique: 1 Key_name: mem_hash Seq_in_index: 1 Column_name: city_id Collation: A Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec)6.merge引擎mysql> use test1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table payment_2006( country_id smallint, payment_date datetime, amount decimal(15,2), key idx_fk_country_id(country_id) )engine=myisam;Query OK, 0 rows affected (0.01 sec)mysql> create table payment_2007( country_id smallint, payment_date datetime, amount decimal(15,2), key idx_fk_country_id(country_id) )engine=myisam;Query OK, 0 rows affected (0.02 sec)mysql> create table payment_all( -> country_id smallint, -> payment_date datetime, -> amount decimal(15,2), -> index(country_id) -> )engine=merge union=(payment_2006,payment_2007) insert_method=last;Query OK, 0 rows affected (0.01 sec)mysql> insert into payment_2006 -> values(1,'2006-05-01',100000), -> (2,'2006-08-15',150000);Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> insert into payment_2007 -> values(1,'2007-02-20',35000), -> (2,'2007-07-15',220000);Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from payment_2006;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2006-05-01 00:00:00 | 100000.00 || 2 | 2006-08-15 00:00:00 | 150000.00 |+------------+---------------------+-----------+2 rows in set (0.00 sec)mysql> select * from payment_2007;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2007-02-20 00:00:00 | 35000.00 || 2 | 2007-07-15 00:00:00 | 220000.00 |+------------+---------------------+-----------+2 rows in set (0.00 sec)mysql> select * from payment_all;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2006-05-01 00:00:00 | 100000.00 || 2 | 2006-08-15 00:00:00 | 150000.00 || 1 | 2007-02-20 00:00:00 | 35000.00 || 2 | 2007-07-15 00:00:00 | 220000.00 |+------------+---------------------+-----------+4 rows in set (0.00 sec)mysql> insert into payment_all -> values(3,'2006-03-31',112200);Query OK, 1 row affected (0.01 sec)mysql> select * from payment_all;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2006-05-01 00:00:00 | 100000.00 || 2 | 2006-08-15 00:00:00 | 150000.00 || 1 | 2007-02-20 00:00:00 | 35000.00 || 2 | 2007-07-15 00:00:00 | 220000.00 || 3 | 2006-03-31 00:00:00 | 112200.00 |+------------+---------------------+-----------+5 rows in set (0.00 sec)mysql> select * from payment_2007;+------------+---------------------+-----------+| country_id | payment_date | amount |+------------+---------------------+-----------+| 1 | 2007-02-20 00:00:00 | 35000.00 || 2 | 2007-07-15 00:00:00 | 220000.00 || 3 | 2006-03-31 00:00:00 | 112200.00 |+------------+---------------------+-----------+3 rows in set (0.00 sec)版权声明：本博客原创文章欢迎转载，请转载的朋友最好注明出处，谢谢大家。