2019.10.15 MySQL 事务介绍及操作演示

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 37
Server version: 5.5.41-MariaDB-log MariaDB Server

Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth |
| client |
| crushlinux |
| lty |
| mysql |
| performance_schema |
| test |
+--------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]> use auth
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [auth]> show tables;
+----------------+
| Tables_in_auth |
+----------------+
| user |
+----------------+
1 row in set (0.00 sec)

MariaDB [auth]> drop table user;
Query OK, 0 rows affected (0.01 sec)

MariaDB [auth]> create table users(user_name char(18) not null,user_passwd char(50) default‘‘,primary key (user_name));
Query OK, 0 rows affected (0.02 sec)

MariaDB [auth]> alter table auth.users engine=innodb;

Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

begin实例：

MariaDB [auth]> begin ;                          //事物开始

Query OK, 0 rows affected (0.00 sec)

MariaDB [auth]> insert into users values(‘lisi‘,password(‘123‘));
Query OK, 1 row affected (0.01 sec)

MariaDB [auth]> insert into users values(‘wangwu‘,password(‘321‘));
Query OK, 1 row affected (0. 00 sec)

MariaDB [auth]> commit;                           //事物提交
Query OK, 0 rows affected (0.00 sec)

MariaDB [auth]> select * from users;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| lisi | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| wangwu | *7297C3E22DEB91303FC493303A8158AD4231F486 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

里面存储的数据是永久的事物

rollback实例：

MariaDB [auth]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [auth]> update users set user_passwd=password(‘‘) where user_name=‘lisi‘;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [auth]> select * from users;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| lisi | |
| wangwu | *7297C3E22DEB91303FC493303A8158AD4231F486 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [auth]> rollback;                            //事物回滚（撤销）从begin开始的所有的命令都将被撤销
Query OK, 0 rows affected (0.01 sec)

MariaDB [auth]> select * from users;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| lisi | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| wangwu | *7297C3E22DEB91303FC493303A8158AD4231F486 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

2、使用set设置事务处理方式

set autocommit=0：禁止自动提交

set autocommit=1：开启自动提交

MariaDB [auth]> set autocommit=0;                               //禁止自动提交
Query OK, 0 rows affected (0.00 sec)

MariaDB [auth]> insert into users values(‘lty‘,password(‘123‘));
Query OK, 1 row affected (0.00 sec)

MariaDB [auth]> insert into users values(‘jhc‘,password(‘456‘));
Query OK, 1 row affected (0.00 sec)

MariaDB [auth]> commit;
Query OK, 0 rows affected (0.01 sec)

MariaDB [auth]> set autocommit=1;                             //开启自动提交
Query OK, 0 rows affected (0.00 sec)

不能进行回滚，一条SQL语句就会提交一次

 

2019.10.15 MySQL 事务介绍及操作演示

标签：一起   请求   iso   交易   机制   duplicate   value   mes   sch