기본적으로 innodb가 탑재가 안되며 configure옵션에 --with-plugins=innobase 또는 --with-plugins=max 옵션을 추가해줘야한다.
innodb부분이 YES로 나와있어야함.
mysql> show variables like 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb | YES |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | NO | Clustered, fault-tolerant tables | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
roundcubemail table 같은경우에는 innodb를 사용함
mysql> show table status;
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------------------------------------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------------------------------------------------------------------+
| cache | InnoDB | 10 | Compact | 4 | 4096 | 16384 | 0 | 32768 | 0 | 10 | 2009-05-07 16:41:57 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 11264 kB; (`user_id`) REFER `roundcubemail/users`(`user_id`) |
| contacts | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | 1 | 2009-05-07 16:41:57 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 11264 kB; (`user_id`) REFER `roundcubemail/users`(`user_id`) |
| identities | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 16384 | 0 | 3 | 2009-05-07 16:41:57 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 11264 kB; (`user_id`) REFER `roundcubemail/users`(`user_id`) |
| messages | InnoDB | 10 | Compact | 49 | 1337 | 65536 | 0 | 32768 | 0 | 273 | 2009-05-07 16:41:57 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 11264 kB; (`user_id`) REFER `roundcubemail/users`(`user_id`) |
| session | InnoDB | 10 | Compact | 11 | 1489 | 16384 | 0 | 16384 | 0 | NULL | 2009-05-07 16:41:57 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 11264 kB |
| users | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 32768 | 0 | 3 | 2009-05-07 16:41:57 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 11264 kB |
+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------------------------------------------------------------------+
6 rows in set (0.00 sec)
myisam에서 innodb로 변경
ALTER TABLE 테이블명 ENGINE=INNODB
ALTER TABLE 테이블명 TYPE=INNODB
innodb테이블생성시
create table a(a int not null, b int) engine=innodb (engine=inodb옵션이 붙어야함)
