【練習】使用事務控制語句

1.使用show engines 命令確定系統中是否有任何事務存儲引擎可用以及哪個是默認引擎。

2.使用set autocommit 語句啟用autocommit。

3.為使用world數據庫做準備，確認city表使用事務存儲引擎innodb。

4.使用start transaction 語句顯式啟動新事務。

5.刪除一行。

6.使用rollback語句回滾打開的事務。

?

1.

mysql> show engines\G
*************************** 1. row ***************************Engine: PERFORMANCE_SCHEMASupport: YESComment: Performance Schema
Transactions: NOXA: NOSavepoints: NO
*************************** 2. row ***************************Engine: CSVSupport: YESComment: CSV storage engine
Transactions: NOXA: NOSavepoints: NO
*************************** 3. row ***************************Engine: MRG_MYISAMSupport: YESComment: Collection of identical MyISAM tables
Transactions: NOXA: NOSavepoints: NO
*************************** 4. row ***************************Engine: BLACKHOLESupport: YESComment: /dev/null storage engine (anything you write to it disappears)
Transactions: NOXA: NOSavepoints: NO
*************************** 5. row ***************************Engine: MyISAMSupport: YESComment: MyISAM storage engine
Transactions: NOXA: NOSavepoints: NO
*************************** 6. row ***************************Engine: MEMORYSupport: YESComment: Hash based, stored in memory, useful for temporary tables
Transactions: NOXA: NOSavepoints: NO
*************************** 7. row ***************************Engine: ARCHIVESupport: YESComment: Archive storage engine
Transactions: NOXA: NOSavepoints: NO
*************************** 8. row ***************************Engine: InnoDBSupport: DEFAULTComment: Supports transactions, row-level locking, and foreign keys
Transactions: YESXA: YESSavepoints: YES
*************************** 9. row ***************************Engine: FEDERATEDSupport: NOComment: Federated MySQL storage engine
Transactions: NULLXA: NULLSavepoints: NULL
9 rows in set (0.00 sec)

2.

mysql> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

3.

mysql> use world
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changedmysql> show create table city\G
*************************** 1. row ***************************Table: city
Create Table: CREATE TABLE `city` (`ID` int(11) NOT NULL AUTO_INCREMENT,`Name` char(35) NOT NULL DEFAULT '',`CountryCode` char(3) NOT NULL DEFAULT '',`District` char(20) NOT NULL DEFAULT '',`Population` int(11) NOT NULL DEFAULT '0',PRIMARY KEY (`ID`),KEY `CountryCode` (`CountryCode`),CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

4.

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

5.

mysql> select * from city where name = 'manta';
+-----+-------+-------------+----------+------------+
| ID  | Name  | CountryCode | District | Population |
+-----+-------+-------------+----------+------------+
| 600 | Manta | ECU         | Manab鉚   |     164739 |
+-----+-------+-------------+----------+------------+
1 row in set (0.01 sec)mysql> delete from city where name = 'manta';
Query OK, 1 row affected (0.02 sec)mysql> select * from city where name = 'manta';
Empty set (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.02 sec)mysql> select * from city where name = 'manta';
+-----+-------+-------------+----------+------------+
| ID  | Name  | CountryCode | District | Population |
+-----+-------+-------------+----------+------------+
| 600 | Manta | ECU         | Manab鉚   |     164739 |
+-----+-------+-------------+----------+------------+
1 row in set (0.01 sec)

?