你的位置:首页 > 数据库

[数据库]mysql修改表的存储引擎(myisam=innodb)


查看当前数据库的所支持的数据库引擎以及默认数据库引擎

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

 查看表的存储引擎

mysql> show tables;+----------------------------------------------+| Tables_in_performance_schema         |+----------------------------------------------+| cond_instances                || events_waits_current             || events_waits_history             || events_waits_history_long          || events_waits_summary_by_instance       || events_waits_summary_by_thread_by_event_name || events_waits_summary_global_by_event_name  || file_instances                || file_summary_by_event_name          || file_summary_by_instance           || mutex_instances               || performance_timers              || rwlock_instances               || setup_consumers               || setup_instruments              || setup_timers                 || threads                   |+----------------------------------------------+17 rows in set (0.00 sec)mysql> show create table threads;+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table  | Create Table                                                                                   |+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| threads | CREATE TABLE `threads` ( `THREAD_ID` int(11) NOT NULL, `PROCESSLIST_ID` int(11) DEFAULT NULL, `NAME` varchar(128) NOT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

 直接更改存储引擎

mysql> show create table wholesale;+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table       | Create Table                                                                                                                                                                                                   |+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| jindong_wholesale | CREATE TABLE `wholesale` ( `act_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `goods_id` mediumint(8) unsigned NOT NULL, `goods_name` varchar(255) NOT NULL, `rank_ids` varchar(255) NOT NULL, `prices` text NOT NULL, `enabled` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`act_id`), KEY `goods_id` (`goods_id`) USING BTREE) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

 如下命令:

mysql> alter table wholesale engine=innodb;Query OK, 1 row affected (0.32 sec)Records: 1 Duplicates: 0 Warnings: 0

 

mysql> show create table wholesale;+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table       | Create Table                                                                                                                                                                                                   |+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| jindong_wholesale | CREATE TABLE `wholesale` ( `act_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `goods_id` mediumint(8) unsigned NOT NULL, `goods_name` varchar(255) NOT NULL, `rank_ids` varchar(255) NOT NULL, `prices` text NOT NULL, `enabled` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`act_id`), KEY `goods_id` (`goods_id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

 多表改变引擎:

# mysqldump -uroot -p123456 sx >sx_bak.sqlsed -i "s/MyISAM/InnoDB/g" sx_bak.sql mysql -uroot -p123456 sx <sx_bak.sql