MySQL表类型 存储引擎 的选择_Mysql_

 mysql> show engines \G; *************************** 1. row *************************** Engine: InnoDB Support: YES Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary 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: MyISAM Support: DEFAULT Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO *************************** 9. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL 9 rows in set (0.00 sec) ERROR: No query specified 

 mysql> show variables like 'have%'; +------------------------+----------+ | Variable_name | Value | +------------------------+----------+ | have_compress | YES | | have_crypt | NO | | 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_statement_timeout | YES | | have_symlink | YES | +------------------------+----------+ 11 rows in set, 1 warning (0.00 sec) 

 mysql> create table a( -> i bigint(20) not null auto_increment, -> primary key (i) -> ) engine=myisam default charset=gbk; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 3 Current database: test Query OK, 0 rows affected (1.33 sec) 

 mysql> alter table a engine=innodb; Query OK, 0 rows affected (1.70 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table a \G; *************************** 1. row *************************** Table: a Create Table: CREATE TABLE `a` ( `i` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk 1 row in set (0.14 sec) 

 mysql> create table autoincre_demo( -> i smallint not null auto_increment, -> name varchar(10),primary key(i) -> )engine=innodb; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 5 Current database: test Query OK, 0 rows affected (1.19 sec) mysql> insert into autoincre_demo values(1,"121"),(0,"dddf"),(null,"fdf"); Query OK, 3 rows affected (0.59 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from autoincre_demo; +---+------+ | i | name | +---+------+ | 1 | 121 | | 2 | dddf | | 3 | fdf | +---+------+ 3 rows in set (0.00 sec) 

 mysql> insert into autoincre_demo(name) values('3'); Query OK, 1 row affected (0.36 sec) mysql> select LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 15 | +------------------+ 1 row in set (0.00 sec) mysql> insert into autoincre_demo(name) values('3'),('6'),('323'),('21'); Query OK, 4 rows affected (0.09 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 16 | +------------------+ 1 row 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) -> )engine=innodb default charset=utf8; Query OK, 0 rows affected (0.86 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 (3.22 sec)