mysql innodb엔진은 4.x부터는 mysql설치시 기본적으로 탑재되어있었으나 5.1.3x부터는 기존 configure옵션되로 하면
기본적으로 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옵션이 붙어야함)
From. http://www.ysy2080.com
(
0)
(
0)
