mysql表引擎更改_mysql表引擎修改

1、修改建表引擎

mysql> CREATE TABLE test_2(

-> name varchar(10),

-> year int(10))

-> ENGINE=InnoDB; -->创建表时指定默认引擎

Query OK, 0 rows affected (0.10 sec)

mysql> show create table test_2\G

*************************** 1. row ***************************

Table: test_2

Create Table: CREATE TABLE `test_2` (

`name` varchar(10) DEFAULT NULL,

`year` int(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

mysql>

mysql> show tables;

+------------------+

| Tables_in_excise |

+------------------+

| test1 |

| test_1 |

+------------------+

2 rows in set (0.00 sec)

mysql> show create table test_1\G

*************************** 1. row ***************************

Table: test_1

Create Table: CREATE TABLE `test_1` (

`name` varchar(20) DEFAULT NULL,

`year` int(5) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 --> 默认的表引擎为MyISAM;

1 row in set (0.00 sec)

mysql> alter table test_1 type=InnoDB; --> 修改表的数据引擎;

Query OK, 0 rows affected, 1 warning (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test_1\G

*************************** 1. row ***************************

Table: test_1

Create Table: CREATE TABLE `test_1` (

`name` varchar(20) DEFAULT NULL,

`year` int(5) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 --> 显示表的数据引擎已经变为InnoDB;

1 row in set (0.00 sec)

mysql>

2、

mysql> show engines; --> 查看MySQL支持引擎

+------------+---------+------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+------------+---------+------------------------------------------------------------+--------------+------+------------+

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |

| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

+------------+---------+------------------------------------------------------------+--------------+------+------------+

5 rows in set (0.00 sec)

mysql>