在MySQL和MariaDB中设置set autocommit=0与start transaction有什么区别

最新推荐文章于 2022-05-04 14:18:52 发布

zhengzizhi

最新推荐文章于 2022-05-04 14:18:52 发布

阅读量1.4k

点赞数

分类专栏： MySQL 文章标签：数据库事务

本文链接：https://blog.csdn.net/zhengzizhi/article/details/78700655

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

set autocommit=0指事务非自动提交，自此句执行以后，

每个SQL语句或者语句块所在的事务都需要显示"commit"才能提交事务。

1、不管autocommit 是1还是0
START TRANSACTION 后，只有当commit数据才会生效，ROLLBACK后就会回滚。

2、当autocommit 为 0 时
不管有没有START TRANSACTION。
只有当commit数据才会生效，ROLLBACK后就会回滚。

3、如果autocommit 为1 ，并且没有START TRANSACTION 。
调用ROLLBACK是没有用的。即便设置了SAVEPOINT。

使用客户端工具 SQLyog Ultimate - MySQL GUI 创建数据库、数据表、存储过程

创建测试数据表：

create database `testdb`;

CREATE TABLE `t_user` (
  `user_id` varchar(30) NOT NULL,
  `user_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

当我们设置autocommit =1时，创建测试存储过程：

set autocommit =1;

DELIMITER $$
DROP PROCEDURE IF EXISTS proc_test_trans $$  
CREATE PROCEDURE `proc_test_trans`()
BEGIN

    DECLARE t_error INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND  SET t_error=1;

    START TRANSACTION;
    SAVEPOINT p1;

            update t_user set user_id = '00101';
            update t_user set user_id2 = '00102';  -- 故意使用一个不存在的字段更新其值

            IF t_error = 1 THEN 
                ROLLBACK to p1;
            ELSE 
                COMMIT; 
            END IF;

END$$
DELIMITER ;

提交创建存储过程成功后打印输出以下信息：

3 queries executed, 3 success, 0 errors, 1 warnings

查询：set autocommit =1

共 0 行受到影响

执行耗时   : 0.001 sec
传送时间   : 0.003 sec
总耗时      : 0.004 sec
--------------------------------------------------

查询：DROP PROCEDURE IF EXISTS proc_test_trans

共 0 行受到影响， 1 个警告

执行耗时   : 0.004 sec
传送时间   : 1.046 sec
总耗时      : 1.050 sec

注意：要查看所有警告的完整列表，请启用 工具 -> 首选项 -> 常规 -> 在信息选项卡下显示警告
--------------------------------------------------

查询：CREATE PROCEDURE `proc_test_trans`() BEGIN DECLARE t_error INTEGER DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNI...

共 0 行受到影响

执行耗时   : 0.023 sec
传送时间   : 1.046 sec
总耗时      : 1.070 sec

CALL `proc_test_trans`();  -- 执行存储过程

打印输出以下信息：

1 queries executed, 1 success, 0 errors, 0 warnings

查询：CALL `proc_test_trans`()

共 0 行受到影响

执行耗时   : 0.002 sec
传送时间   : 0.001 sec
总耗时      : 0.004 sec

执行存储过程后输出共 0 行受到影响的信息表明存储过程回滚成功

当我们设置autocommit =0时，创建测试存储过程：

set autocommit =0;

DELIMITER $$
DROP PROCEDURE IF EXISTS proc_test_trans $$  
CREATE PROCEDURE `proc_test_trans`()
BEGIN

    DECLARE t_error INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND  SET t_error=1;

    START TRANSACTION;
    SAVEPOINT p1;

            update t_user set user_id = '00101';
            update t_user set user_id2 = '00102';  -- 故意使用一个不存在的字段更新其值

            IF t_error = 1 THEN 
                ROLLBACK to p1;
            ELSE 
                COMMIT; 
            END IF;

END$$
DELIMITER ;

提交创建存储过程成功后打印输出以下信息：

3 queries executed, 3 success, 0 errors, 0 warnings

查询：set autocommit =0

共 0 行受到影响

执行耗时   : 0.001 sec
传送时间   : 0.003 sec
总耗时      : 0.005 sec
--------------------------------------------------

查询：DROP PROCEDURE IF EXISTS proc_test_trans

共 0 行受到影响

执行耗时   : 0.007 sec
传送时间   : 1.048 sec
总耗时      : 1.055 sec
--------------------------------------------------

查询：CREATE PROCEDURE `proc_test_trans`() BEGIN DECLARE t_error INTEGER DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNI...

共 0 行受到影响

执行耗时   : 0.006 sec
传送时间   : 1.044 sec
总耗时      : 1.051 sec

CALL `proc_test_trans`();  -- 执行存储过程

打印输出以下信息：

1 queries executed, 1 success, 0 errors, 0 warnings

查询：CALL `proc_test_trans`()

共 0 行受到影响

执行耗时   : 0.002 sec
传送时间   : 0.001 sec
总耗时      : 0.004 sec

执行存储过程后输出共 0 行受到影响的信息表明存储过程回滚成功

当我们设置autocommit =0时，创建测试存储过程：

set autocommit =0;

DELIMITER $$
DROP PROCEDURE IF EXISTS proc_test_trans $$  
CREATE PROCEDURE `proc_test_trans`()
BEGIN

    DECLARE t_error INTEGER DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND  SET t_error=1;

    SAVEPOINT p1;

            update t_user set user_id = '00101';
            update t_user set user_id2 = '00102';  -- 故意使用一个不存在的字段更新其值

            IF t_error = 1 THEN 
                ROLLBACK to p1;
            ELSE 
                COMMIT; 
            END IF;

END$$
DELIMITER ;

提交创建存储过程成功后打印输出以下信息：

3 queries executed, 3 success, 0 errors, 0 warnings

查询：set autocommit =0

共 0 行受到影响

执行耗时   : 0.001 sec
传送时间   : 0.003 sec
总耗时      : 0.004 sec
--------------------------------------------------

查询：DROP PROCEDURE IF EXISTS proc_test_trans

共 0 行受到影响

执行耗时   : 0.005 sec
传送时间   : 1.048 sec
总耗时      : 1.054 sec
--------------------------------------------------

查询：CREATE PROCEDURE `proc_test_trans`() BEGIN DECLARE t_error INTEGER DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNI...

共 0 行受到影响

执行耗时   : 0.008 sec
传送时间   : 1.045 sec
总耗时      : 1.054 sec

CALL `proc_test_trans`();  -- 执行存储过程

打印输出以下信息：

1 queries executed, 1 success, 0 errors, 0 warnings

查询：CALL `proc_test_trans`()

共 0 行受到影响

执行耗时   : 0.002 sec
传送时间   : 0.001 sec
总耗时      : 0.004 sec

执行存储过程后输出共 0 行受到影响的信息表明存储过程回滚成功

Install MariaDB10.4.8 On CentOS8


官网推荐安装包下载地址（软件包下载速度很慢，不推荐你使用官网的MariaDB.repo文件定义方式）

cat > /etc/yum.repos.d/MariaDB.repo
# MariaDB 10.4 CentOS repository list - created 2019-11-07 12:26 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos8-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

自定义清华大学安装包下载地址（软件包下载速度非常快，这种定义MariaDB.repo文件的方式是最高效安装方式）

cat > /etc/yum.repos.d/MariaDB.repo
# MariaDB 10.4 CentOS repository list - created 2019-11-07 12:26 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-10.4.8/yum/centos8-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1


安装MariaDB的服务器端和客户端

yum install -y boost-program-options
yum install -y MariaDB-server MariaDB-client --disablerepo=AppStream


第3条指令创建查询日志，错误日志，慢查询日志，二进日志保存位置以及设置它们的权限

mkdir -p /var/log/mariadb && touch /var/log/mariadb/queries.log && touch /var/log/mariadb/mariadb-error.log && touch /var/log/mariadb/mariadb-slow.log && touch /var/log/mariadb/mariadb-log-bin && touch /var/log/mariadb/mariadb-log-bin.index && chown -R mysql:mysql /var/log/mariadb && chmod 755 /var/log/mariadb && chmod 666 /var/log/mariadb/*


第4条指令配置数据库服务存储数据的文件编码，设置表名称必须小写，以及开启多种日志存储的位置

[root@contoso ~]# cat > /etc/my.cnf.d/server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]
character-set-server=utf8
lower-case-table-names=1

log-bin=/var/log/mariadb/mariadb-log-bin
log-bin-index=/var/log/mariadb/mariadb-log-bin.index
log-error=/var/log/mariadb/mariadb-error.log

general-log=ON
general-log-file=/var/log/mariadb/queries.log
log-output=file

slow-query-log=ON
slow-query-log-file=/var/log/mariadb/mariadb-slow.log
long_query_time=1


#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.4 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.4]



运维指令清空日志，实时跟踪日志文件

ll /var/log/mariadb
cat /etc/my.cnf.d/server.cnf
cat /dev/null > /var/log/mariadb/queries.log && cat /dev/null > /var/log/mariadb/mariadb-slow.log && cat /dev/null > /var/log/mariadb/mariadb-error.log
tail -f /var/log/mariadb/queries.log
tail -f /var/log/mariadb/mariadb-slow.log
tail -f /var/log/mariadb/mariadb-error.log
rm -f /var/log/mariadb/*
mysql -uroot -p123456 -h127.0.0.1 -e "reset master"    # 清空bin_log日志



[root@contoso ~]# service mysql start

[root@contoso ~]# mysql -uroot -h127.0.0.1 -e"show variables like 'character%'"			##安装时密码为空

[root@contoso ~]# mysql -uroot -pdbpass -h127.0.0.1 -e"show variables like 'character%'"	##安装时已设密码为dbpass

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
[root@contoso ~]# service mysql status
[root@contoso ~]# service mysql stop
[root@contoso ~]# /usr/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): 回车
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password: dbpass
Re-enter new password: dbpass
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] n
 ... skipping.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@contoso ~]# 
[root@contoso ~]# mysql -uroot -pdbpass -h127.0.0.1
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.4.8-MariaDB-log MariaDB Server

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

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

MariaDB [(none)]> USE mysql;
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 [mysql]> SELECT host,user FROM user;
+-----------+-------+
| Host      | User  |
+-----------+-------+
| localhost | mysql |
| localhost | root  |
+-----------+-------+
2 rows in set (0.002 sec)

MariaDB [mysql]> GRANT ALL ON *.* TO 'root'@'192.168.10.10' IDENTIFIED BY 'dbpass';
Query OK, 0 rows affected (0.002 sec)

MariaDB [mysql]> GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'dbpass';
Query OK, 0 rows affected (0.003 sec)

MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [mysql]> SELECT host,user FROM user;
+---------------+-------+
| Host          | User  |
+---------------+-------+
| %             | root  |
| 192.168.10.10 | root  |
| localhost     | mysql |
| localhost     | root  |
+---------------+-------+
4 rows in set (0.001 sec)

MariaDB [mysql]> quit
Bye
[root@contoso ~]# service mysql stop

[root@contoso ~]# systemctl enable mariadb && systemctl restart mariadb && systemctl status mariadb

zhengzizhi

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
在MySQL和MariaDB中设置set autocommit=0与start transaction有什么区别

set autocommit=0指事务非自动提交，自此句执行以后，每个SQL语句或者语句块所在的事务都需要显示"commit"才能提交事务。1、不管autocommit 是1还是0 START TRANSACTION 后，只有当commit数据才会生效，ROLLBACK后就会回滚。2、当autocommit 为 0 时不管有没有START TRANSACTION。...
复制链接

扫一扫