Mysql支持事务的InnoDB

http://lggege.iteye.com/blog/204577

Mysql支持事务的InnoDB

1. 配置My.ini
我的my.ini是从my-small.ini重命名来的，也没有更改里面原有的任何配置。

看英文提示，将下面的原本注释掉的行打开，再修改了一下目录即可。

引用

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = D:\JAVA\mysql-5.0.27-win32\data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = D:\JAVA\mysql-5.0.27-win32\data
innodb_log_arch_dir = D:\JAVA\mysql-5.0.27-win32\data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

配置完毕，启动Mysql，会发现，D:\JAVA\mysql-5.0.27-win32\data【innodb_data_home_dir】
多了ibdata1，ib_logfile0，ib_logfile1三个文件。


2. 创建InnoDB表

引用

mysql> drop database iwoo;
Query OK, 1 row affected (0.08 sec)

mysql> create database iwoo;
Query OK, 1 row affected (0.02 sec)

mysql> use iwoo;
Database changed
mysql> create table article (id bigint(20), title varchar(50)) type=InnoDB;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> show table status from iwoo;
+---------+--------+
| Name    | Engine |
+---------+--------+
| article | InnoDB |
+---------+--------+
1 row in set (0.00 sec)

3. 转换 MyISAM与InnoDB

引用

mysql> drop database iwoo;
Query OK, 1 row affected (0.36 sec)

mysql> create database iwoo;
Query OK, 1 row affected (0.00 sec)

mysql> use iwoo;
Database changed
mysql> create table article (id bigint(20), title varchar(50));
Query OK, 0 rows affected (0.05 sec)

mysql> show table status from iwoo;
+---------+--------+
| Name    | Engine |
+---------+--------+
| article | MyISAM |
+---------+--------+
1 row in set (0.00 sec)

mysql> alter table article type=InnoDB;
Query OK, 0 rows affected, 1 warning (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status from iwoo;
+---------+--------+
| Name    | Engine |
+---------+--------+
| article | InnoDB |
+---------+--------+
1 row in set (0.00 sec)

mysql> alter table article type = MyISAM;
Query OK, 0 rows affected, 1 warning (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status from iwoo;
+---------+--------+
| Name    | Engine |
+---------+--------+
| article | MyISAM |
+---------+--------+
1 row in set (0.00 sec)

4。测试事务

引用

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into article values(1,'z');
Query OK, 1 row affected (0.00 sec)

mysql> select * from article;
+------+-------+
| id   | title |
+------+-------+
|    1 | z     |
+------+-------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from article;
Empty set (0.00 sec)