|
**************
Engine: ISAM
Support: NO
Comment: Obsolete storage engine
12 rows in set (0.00 sec)
或者使用这个cmd:
mysql> show variables like 'have%';
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | NO |
| have_csv | NO |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | DISABLED |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
+-----------------------+----------+
17 rows in set (0.00 sec)
disabled说明mysql支持该engine，但是启动的时候被禁用.
创建表的时候，可以使用engine关键字指定该表使用哪个engine:
mysql> create table ai(i bigint(20) NOT NULL AUTO_INCREMENT,PRIMARY KEY(I)) ENGI
NE=MyISAM DEFAULT CHARSET=GBK;
Query OK, 0 rows affected (0.03 sec)
也可以修改表的引擎:
mysql> alter table ai engine=innodb;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table ai \G;
*************************** 1. row ***************************
Table: ai
Create Table: CREATE TABLE `ai` (
`i` bigint(20) NOT NULL auto_increment,
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
常见的存储引擎有：
MyISAM,InnoDB,MEMORY,MERGE,NDB
上述引擎中只有InnoDB支持外键。
mysql的默认存储引擎是MyISAM
每个MyISAM在磁盘上存储成3个文件:
.frm(存储表定义)
.MYD(MYData,存储数据)
.MYI(MYIndex,存储索引)
关于InnoDB的一些特性:
a.自动增长字段:
mysql> create table autoincre(i smallint not null auto_increment,name varchar(20
),primary key(i))engine=innodb;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into autoincre values(1,'1'),(2,'2'),(null,'3');
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from autoincre;
+---+------+
| i | name |
+---+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+------+
3 rows in set (0.00 sec)
向自增长字段插入记录后，不影响该字段自己增加值.
对于InnoDB表，自动增长字段必须是索引，如果是组合索引也必须是组合索引的第一个列.
但是对于MyISAM表，自增长字段可以不是组合索引的第一个列，可以作为第二个列出现:
mysql> create table autoincre_demo(d1 smallint not null auto_increment,d2 smalli
nt not null,name varchar(10),index(d2,d1))engine=myisam;
Query OK, 0 rows affected (0.05 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)
自增长字段d1作为组合索引在第二列中出现，自增长字段的记录按照组合索引d2进行排序后递增.
b.mysql的存储引擎中只有InnoDB支持fk:
建表语句:
mysql> create table country(country_id smallint unsigned not null auto_increment
,country varchar(50) not null,primary key(country_id))
-> engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.14 sec)
mysql> create table city(
-> city_id smallint unsigned not null auto_increment,
-> country_id smallint unsigned not null,
-> primary key(city_id),
-> foreign key(country_id) references country(country_id)
-> on delete restrict on update cascade
-> engine=innodb default charset=utf8;
Q |