Mysql数据表引擎及事务回滚机制

Sian

1、查看当前支持的数据引擎

[SQL] 纯文本查看 复制代码

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| 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>

2、查看数据表引擎

[SQL] 纯文本查看 复制代码

mysql> show create table books \G
*************************** 1. row ***************************
Table: books
Create Table: CREATE TABLE `books` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bookname` char(50) NOT NULL DEFAULT '',
`publisher` char(80) NOT NULL DEFAULT '',
`author` char(30) NOT NULL DEFAULT '',
`price` double NOT NULL DEFAULT '0',
`ptime` int(11) NOT NULL DEFAULT '0',
`pic` char(40) NOT NULL DEFAULT '',
`detail` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=162 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> 

3、修改数据表引擎

[SQL] 纯文本查看 复制代码

mysql> alter table books engine=innodb;[/align]Query OK, 98 rows affected (0.07 sec)
Records: 98 Duplicates: 0 Warnings: 0

mysql> 

4、关闭数据自动提交

[SQL] 纯文本查看 复制代码

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

5、开启一个事务

[SQL] 纯文本查看 复制代码

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

6、回滚

[SQL] 纯文本查看 复制代码

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

7、提交事务

[SQL] 纯文本查看 复制代码

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