mysql transaction id_mysql中transaction的实现

2.配置

安装后，可以对innodb做一些配置，在my.cnf或my.ini中的[mysqld]段。

#存储目录，如果不指定默认为安装的data目录，为空时以innodb_data_file_path指定路径为准

innodb_data_home_dir =

#数据文件名及大小，默认为ibdata1，10m大小。autoextend可以自增，max:2000M文件最大2g，因为有的硬盘有2g文件大小限制。

innodb_data_file_path = ibdata1:2000M;ibdata2:2000M:autoextend:max:2000M

# 设置缓冲池大小

set-variable = innodb_buffer_pool_size=70M

set-variable = innodb_additional_mem_pool_size=10M

#设置日志文件路径，默认在date目录下，名称为ib_logfile...

innodb_log_group_home_dir =

#设置日志文件数目，默认为3

set-variable = innodb_log_files_in_group=3

# 设置日志文件大小

set-variable = innodb_log_file_size=10M

# 设置日志缓冲大小

set-variable = innodb_log_buffer_size=8M

# 任何事务提交前写入日志，方便故障诊断，请设为1。如果丢失最近的几个事务影响不大的话，设置为0(默认值)。

innodb_flush_log_at_trx_commit=1

#设置超时时间

set-variable = innodb_lock_wait_timeout=50

注意：1.innodb不会自动生成目录，上面所有指定目录要手工生成，默认不用。

2.在/etc下面默认没有my.cnf文件，需要copy，如下：

[root@test-server root]# find / -name 'my*.cnf'

/etc/my.cnf

/usr/share/doc/packages/MySQL-server/my-huge.cnf

/usr/share/doc/packages/MySQL-server/my-innodb-heavy-4G.cnf

/usr/share/doc/packages/MySQL-server/my-large.cnf

/usr/share/doc/packages/MySQL-server/my-medium.cnf

/usr/share/doc/packages/MySQL-server/my-small.cnf

/usr/share/mysql/my-large.cnf

/usr/share/mysql/my-huge.cnf

/usr/share/mysql/my-innodb-heavy-4G.cnf

/usr/share/mysql/my-medium.cnf

/usr/share/mysql/my-small.cnf

在/usr/share/mysql目录下有5个my*.cnf文件，打开查看根据自己配置情况选择一个，执行#cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

这时候，就可以进行上面的配置啦！

3.添加表

mysql> create table testerinfo(ID int primary key,  name char(30), address char(30), sex char(10)) type=innodb;

Query OK, 0 rows affected, 1 warning (0.02 sec)     //设置表的engine

这里出现一个warning，我们查看warning内容：

mysql> show warnings;

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

| Level   | Code | Message                                                                  |

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

| Warning | 1287 | 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead |

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

1 row in set (0.00 sec)

意思是应设置engin=innodb，而不是type。没有关系，下面我们修改：

mysql> alter table testerinfo engine='InnoDB';

Query OK, 0 rows affected (0.06 sec)

Records: 0  Duplicates: 0  Warnings: 0

没有出现警告提示，搞定！

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              |

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

| dept       | MyISAM |      10 | Fixed      |    3 |             59 |         177 | 16607023625928703 |         2048 |         0 |           NULL | 2009-06-06 10:23:30 | 2009-06-06 12:18:02 | NULL       | latin1_swedish_ci |     NULL |                |                      |

| tem_tb     | MyISAM |      10 | Fixed      |    3 |             73 |         219 | 20547673299877887 |         2048 |         0 |           NULL | 2009-06-05 16:18:49 | 2009-06-05 16:47:01 | NULL       | latin1_swedish_ci |     NULL |                |                      |

| temp       | MyISAM |      10 | Fixed      |    4 |             68 |         272 | 19140298416324607 |         2048 |         0 |           NULL | 2009-06-05 16:18:49 | 2009-06-05 16:18:49 | NULL       | latin1_swedish_ci |     NULL |                |                      |

| tester     | MyISAM |      10 | Fixed      |    6 |             73 |         438 | 20547673299877887 |         5120 |         0 |           NULL | 2009-06-06 14:50:47 | 2009-06-06 14:52:12 | NULL       | latin1_swedish_ci |     NULL |                |                      |

| testerinfo | InnoDB |      10 | Compact    |    0 |              0 |       16384 |                 0 |            0 |         0 |           NULL | 2009-06-06 15:23:23 | NULL                | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 4096 kB |

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

5 rows in set (0.06 sec)

4.试验

mysql> select * from testerinfo;

Empty set (0.00 sec)

mysql> desc testerinfo;

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

| Field   | Type     | Null | Key | Default | Extra |

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

| ID      | int(11)  | NO   | PRI |         |       |

| name    | char(30) | YES  |     | NULL    |       |

| address | char(30) | YES  |     | NULL    |       |

| sex     | char(10) | YES  |     | NULL    |       |

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

4 rows in set (0.01 sec)

mysql> insert into testerinfo values(1,'jason','shenzhen','male');

Query OK, 1 row affected (0.01 sec)

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into testerinfo values(2,'tina','zhuhai','female');

Query OK, 1 row affected (0.00 sec)

mysql> select * from testerinfo;

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

| ID | name  | address  | sex    |

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

|  1 | jason | shenzhen | male   |

|  2 | tina  | zhuhai   | female |

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

2 rows in set (0.00 sec)

mysql> insert into testerinfo values(5,'john','zhuhai','male');

Query OK, 1 row affected (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected (0.01 sec)

mysql> select * from testerinfo;

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

| ID | name  | address  | sex  |

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

|  1 | jason | shenzhen | male |

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

1 row in set (0.00 sec)

很高兴，设置成功！接下来大家就可以进行事务处理啦！