MySQL小记(一)：存储引擎

MySQL存储引擎

MySQL默认支持多种存储引擎，以适用于不同领域的数据库应用。MySQL5.0支持的存储引擎包括MyISAM、InnoDB、MEMORY、MERGE、BDB、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。其中InnoDB和BDB提供事务安全表，其他都是非事务安全表。5.5版本之前默认存储引擎是MyISAM，5.5之后改成了InnoDB.
查看当前的默认存储引擎：

  mysql> show engines;

各种存储引擎的特性

MyISAM

• 不支持事务、不支持外键，优点是访问速度快

• 每个MyISAM在磁盘上存储成3个文件，文件名和表名相同，扩展名分别是：
  .frm(存储表定义)、.MYD(MYData, 存储数据)、.MYI(MYIndex, 存储索引)

• MyISAM的表支持三种存储格式：

  1. 静态表： 字段非变长，存储空间多
  2. 动态表： 变长字段，记录是不固定长度的
  3. 压缩表： 由myisampack工具创建，占据非常小的磁盘空间

InnoDB

• InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全
• InnoDB表的自动增长列可以手工插入，但是插入的值是空或者是0，实际插入的是自动增长后的值。

mysql> CREATE TABLE autoincr_demo
    -> (id smallint not null auto_increment,
    -> name varchar(10) not null,
    -> primary key(id)
    -> )engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
+------------------+
| Tables_in_shannonAJ |
+------------------+
| autoincr_demo    |
| country          |
+------------------+
2 rows in set (0.00 sec)

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

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

可以使用 LAST_INSERT_ID（） 查询当前线程最后插入记录使用的值。如果一次插入多条记录，返回的是最后一条记录使用的自动增长值

mysql> insert into autoincr_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 autoincr_demo(name) values('5'),('6'),('7');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)

• 对于InnoDB表，自增长列必须是索引。如果是组合索引，则必须是组合索引的第一列
• 对于MyISAM表，自增长列可以是组合索引的其他列，插入记录后，自增长列是按照组合索引的前面几列进行排序后递增的

mysql> create table autoincr_test
    -> (id SMALLINT NOT NULL AUTO_INCREMENT, 
    -> num SMALLINT NOT NULL, 
    -> name VARCHAR(10), 
    -> index(num, id)
    -> )engine=myisam;
Query OK, 0 rows affected (0.06 sec)

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

mysql> select * from autoincr_test;
+----+-----+------+
| id | num | name |
+----+-----+------+
|  1 |   2 | 2    |
|  1 |   3 | 3    |
|  1 |   4 | 4    |
|  2 |   2 | 2    |
|  2 |   3 | 3    |
|  2 |   4 | 4    |
+----+-----+------+
6 rows in set (0.01 sec)

参考书籍《深入浅出MySQL-数据库开发、优化与管理维护》