mysql三種提交類型

有一段時間沒寫部落格，剛看到mysql的提交類型的知識點，寫個小demon記錄下。

 

mysql提交分顯式提交、隱式提交及自動提交。

 

(1) 顯式提交
用COMMIT命令直接完成的提交為顯式提交。其格式為：
SQL>COMMIT；

(2) 隱式提交
用SQL命令間接完成的提交為隱式提交。這些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自動提交
若把AUTOCOMMIT設置為ON，則在插入、修改、刪除語句執行後，
系統將自動進行提交，這就是自動提交。其格式為：
SQL>SET AUTOCOMMIT ON；

 

隱式提交應用舉例如下：

#表結構
mysql> show create table planet_user_blacklist\G;
*************************** 1. row ***************************
       Table: planet_user_blacklist
Create Table: CREATE TABLE `planet_user_blacklist` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用戶id',
  `created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '創建時間',
  `updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新時間',
  `add_type` tinyint(1) unsigned DEFAULT '0' COMMENT '0系統添加,1手動錄入',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='用戶黑名單'
1 row in set (0.00 sec)

#自動提交已被關閉
mysql> show variables like 'autocommit';;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

ERROR:
No query specified




#測試開始
mysql> select * from planet_user_blacklist;
+----+----------+------------+------------+------------+
| id | uid      | created_at | updated_at | add_type |
+----+----------+------------+------------+-----------+
|  1 | 29904034 |          1 |          2 |        0  |
|  2 |  2839939 |          2 |          2 |        0  |
+----+----------+------------+------------+----------+
2 rows in set (0.00 sec)

#開啟事務
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

#新增欄位
mysql> alter table planet_user_blacklist add column address varchar(100) default '';
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update planet_user_blacklist set address = '廣州市番禺區番禺廣場' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from planet_user_blacklist;
+----+----------+------------+------------+----------+--------------------------------+
| id | uid      | created_at | updated_at | add_type | address                        |
+----+----------+------------+------------+----------+--------------------------------+
|  1 | 29904034 |          1 |          2 |        0 | 廣州市番禺區番禺廣場           |
|  2 |  2839939 |          2 |          2 |        0 |                                |
+----+----------+------------+------------+----------+--------------------------------+
2 rows in set (0.00 sec)

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

#可見事物回滾新增欄位仍然被保留
mysql> select * from planet_user_blacklist;
+----+----------+------------+------------+----------+---------+
| id | uid      | created_at | updated_at | add_type | address |
+----+----------+------------+------------+----------+---------+
|  1 | 29904034 |          1 |          2 |        0 |         |
|  2 |  2839939 |          2 |          2 |        0 |         |
+----+----------+------------+------------+----------+---------+
2 rows in set (0.00 sec)

mysql>

 

Tags:

VirMach 便宜 VPS

QNews

QNews