CREATE TABLEをトランザクション内で実行した比較 (SQLite, MySQL)

gistfile1.txt

upsilon(screen)@udon-dev:~/$ sqlite3 hoge.sqlite
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> begin immediate;
sqlite> create table hoge (id integer, name string, value integer);
sqlite> .schema hoge
CREATE TABLE hoge (id integer, name string, value integer);
sqlite> rollback;
sqlite> .schema hoge
sqlite> .quit

upsilon(screen)@udon-dev:~/$ mysql -u root -p hoge
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 537
Server version: 5.5.24-5 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table hoge (id integer, name varchar(10), value integer);
Query OK, 0 rows affected (0.04 sec)

mysql> describe hoge;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| value | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

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

mysql> describe hoge;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| value | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> quit
Bye


まとめ

SQL DDL（CREATE TABLEとか）をトランザクション内で実行してロールバックすると、SQLiteでは処理が取り消されるがMySQLでは取り消されない。

ここにも書いてある: http://dev.mysql.com/doc/refman/5.1/ja/cannot-roll-back.html