mysql> show variables like '%storage_engine%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_storage_engine     | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine             | InnoDB |
+----------------------------+--------+
3 rows in set (0.00 sec)

mysql> show engines \G
*************************** 1. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

mysql> show variables like 'have%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| have_compress        | YES      |
| have_crypt           | YES      |
| have_dynamic_loading | YES      |
| have_geometry        | YES      |
| have_openssl         | DISABLED |
| have_profiling       | YES      |
| have_query_cache     | YES      |
| have_rtree_keys      | YES      |
| have_ssl             | DISABLED |
| have_symlink         | YES      |
+----------------------+----------+
10 rows in set (0.00 sec)

MYISAM:

mysql> 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> alter table ai engine=innodb;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table myisam_char(name char(10)) engine=myisam;
Query OK, 0 rows affected (0.03 sec)

-rw-rw---- 1 mysql mysql   8560 7月  16 13:32 myisam_char.frm
-rw-rw---- 1 mysql mysql    124 7月  16 13:33 myisam_char.MYD
-rw-rw---- 1 mysql mysql   1024 7月  16 13:33 myisam_char.MYI

mysql> insert into myisam_char values('abcde'),('abcde  '),('  abcde'),('  abcde  ');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> 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)

INNODB:

mysql> create table autoincre_demo(i smallint not null auto_increment,name varchar(10),primary key(i));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into autoincre_demo values (1,'1'),(0,'2'),(null,'3'),(5,'7'),(1,'7'),(4,'7');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into autoincre_demo values (1,'1'),(0,'2'),(null,'3'),(5,'7'),(4,'7');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from autoincre_demo;
+---+------+
| i | name |
+---+------+
| 1 | 1    |
| 4 | 7    |
| 5 | 7    |
| 8 | 2    |
| 9 | 3    |
+---+------+
5 rows in set (0.00 sec)

mysql> truncate table autoincre_demo;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into autoincre_demo values (1,'1'),(0,'2'),(null,'3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from autoincre_demo;
+---+------+
| i | name |
+---+------+
| 1 | 1    |
| 2 | 2    |
| 3 | 3    |
+---+------+
3 rows in set (0.00 sec)

mysql> alter table autoincre_demo auto_increment=100;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into autoincre_demo values (1,'1'),(0,'2'),(null,'3');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into autoincre_demo values (0,'2'),(null,'3');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from autoincre_demo;
+-----+------+
| i   | name |
+-----+------+
|   1 | 1    |
|   2 | 2    |
|   3 | 3    |
| 100 | 2    |
| 101 | 3    |
+-----+------+
5 rows in set (0.00 sec)

[root@dba test]# service mysql restart
Shutting down MySQL....150716 13:45:43 mysqld_safe mysqld from pid file /var/lib/mysql/dba.fei.com.pid ended
 SUCCESS!
Starting MySQL. SUCCESS!
[1]+  Done                    ./mysqld_safe  (wd: /usr/bin)
(wd now: /var/lib/mysql/test)

mysql> insert into autoincre_demo values (0,'2'),(null,'3');
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from autoincre_demo;
+-----+------+
| i   | name |
+-----+------+
|   1 | 1    |
|   2 | 2    |
|   3 | 3    |
| 100 | 2    |
| 101 | 3    |
| 102 | 2    |
| 103 | 3    |
+-----+------+
7 rows in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|              102 |
+------------------+
1 row in set (0.00 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 (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> 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)

mysql>  create table autoincre_demo2(d1 smallint not null auto_increment,d2 smallint not null,name varchar(10),index(d2,d1)) engine=innodb;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql>  create table autoincre_demo2(d1 smallint not null auto_increment,d2 smallint not null,name varchar(10),index(d1,d2)) engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into autoincre_demo2 (d2,name) values(2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3'),(4,'4');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from autoincre_demo2;
+----+----+------+
| d1 | d2 | name |
+----+----+------+
|  1 |  2 | 2    |
|  2 |  3 | 3    |
|  3 |  4 | 4    |
|  4 |  2 | 2    |
|  5 |  3 | 3    |
|  6 |  4 | 4    |
+----+----+------+
6 rows in set (0.00 sec)

mysql> create table autoincre_demo4(d1 smallint not null auto_increment,d2 smallint not null,name varchar(10),index(d1,d2)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into autoincre_demo4 (d2,name) values(2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3'),(4,'4');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from autoincre_demo4;
+----+----+------+
| d1 | d2 | name |
+----+----+------+
|  1 |  2 | 2    |
|  2 |  3 | 3    |
|  3 |  4 | 4    |
|  4 |  2 | 2    |
|  5 |  3 | 3    |
|  6 |  4 | 4    |
+----+----+------+
6 rows in set (0.00 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));
Query OK, 0 rows affected (0.05 sec)

mysql> show variables like 'FOREIGN_KEY_CHECKS';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> set foreign_key_checks=0
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'FOREIGN_KEY_CHECKS';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> set foreign_key_checks=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'FOREIGN_KEY_CHECKS';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> show table status like 'autoincre_demo4' \G;
*************************** 1. row ***************************
           Name: autoincre_demo4
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: 1
    Create_time: 2015-07-16 14:42:33
    Update_time: 2015-07-16 14:42:33
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show variables like 'innodb_data%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir  |                        |
+-----------------------+------------------------+
2 rows in set (0.00 sec)

mysql> show variables like 'innodb_file%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
| innodb_file_per_table    | ON       |
+--------------------------+----------+
4 rows in set (0.00 sec)

mysql> create table tab_memory(id int) engine=memory;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tab_memory values(1);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tab_memory;
+------+
| id   |
+------+
|    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: 8
    Data_length: 126992
Max_data_length: 16777216
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-07-16 15:15:56
    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(id);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show index from tab_memory \G
*************************** 1. row ***************************
        Table: tab_memory
   Non_unique: 1
     Key_name: mem_hash
 Seq_in_index: 1
  Column_name: id
    Collation: NULL
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: HASH
      Comment:
Index_comment:
1 row in set (0.00 sec)

mysql> drop index mem_hash on tab_memory;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create index mem_hash using btree on tab_memory(id);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show index from tab_memory \G
*************************** 1. row ***************************
        Table: tab_memory
   Non_unique: 1
     Key_name: mem_hash
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

mysql> show variables like 'max_heap_t%';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in set (0.00 sec)

mysql> select 16777216/1024/1024 as M;
+-------------+
| M           |
+-------------+
| 16.00000000 |
+-------------+
1 row in set (0.00 sec)

-rw-rw---- 1 mysql mysql   8556 7月  16 15:18 tab_memory.frm

mysql> 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.04 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.01 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',1500000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO payment_2007 values(1,'2007-02-20',35000),(2,'2007-07-15',220000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from pay_ment2006;
ERROR 1146 (42S02): Table 'test.pay_ment2006' doesn't exist
mysql> 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 | 1500000.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 | 1500000.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.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 | 1500000.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)

mysql> show create table payment_all;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                             |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| payment_all | CREATE TABLE `payment_all` (
  `country_id` smallint(6) DEFAULT NULL,
  `payment_date` datetime DEFAULT NULL,
  `amount` decimal(15,2) DEFAULT NULL,
  KEY `country_id` (`country_id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`payment_2006`,`payment_2007`) |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)