MySQL表类型 存储引擎 的选择,
目录
- 1、查看当前数据库支出的存储引擎
- 方法1:
- 方法2:
- 2、ENGINE={存储引起类型} 创建表的时候,设置存储引擎
- 3、alter able tablename engine={存储引起类型} 修改表为其他存储引擎
- 3.1 常用存储引擎的对比
- 3.2 常用存储引擎学习(MyISAM、InnoDB、MEMORY 和 MERGE)
1、查看当前数据库支出的存储引擎
方法1:
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
方法2:
(Value 显示为“DISABLED
”的记录表示支持该存储引擎,但是数据库启动的时候被禁用。)
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)
2、ENGINE={存储引起类型} 创建表的时候,设置存储引擎
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)
3、alter able tablename engine={存储引起类型} 修改表为其他存储引擎
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)
3.1 常用存储引擎的对比
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。