是一种机制、一个操作序列,包含了一组数据库操作命令,并且把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么都执行,要么都不执行 是一个不可分割的工作逻辑单元,在数据库系统上执行并发操作时,事务是最小的控制单元 适用于多用户同时操作的数据库系统的场景,如银行、保险公司及证券交易系统等等 通过事务的整体性以保证数据的一致性 BEGIN或START TRANSACTION 这一种方式目前不是很懂,只是听说能用,希望了解的大佬能指点一下;
MySQL事务管理
MySQL事务概念
事务的ACID特点
事务控制语句
COMMIT
ROLLBACK
SAVEPOINT identifier
RELEASE SAVEPOINT identifier
ROLLBACK TO identifier
SET TRANSACTIONmysql> create table user( -> id int(10) not null, -> name char(16) default '无名小卒', -> age int(3) default'0', -> primary key (id)); Query OK, 0 rows affected (0.01 sec) mysql> desc user; +-------+----------+------+-----+--------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+--------------+-------+ | id | int(10) | NO | PRI | NULL | | | name | char(16) | YES | | 无名小卒 | | | age | int(3) | YES | | 0 | | +-------+----------+------+-----+--------------+-------+ 3 rows in set (0.00 sec) mysql> insert into user values (1,'zhangshan',25); //插入数据 Query OK, 1 row affected (0.00 sec) mysql> select * from user; //查看数据 +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | zhangshan | 25 | +----+-----------+------+ 1 row in set (0.00 sec) mysql> begin; //开始一个事务 Query OK, 0 rows affected (0.00 sec) mysql> update user set id=2; //更改数据,将id改为2 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user; //查看更改后的数据 +----+-----------+------+ | id | name | age | +----+-----------+------+ | 2 | zhangshan | 25 | +----+-----------+------+ 1 row in set (0.00 sec) mysql> q //不提交事务,直接退出 Bye [root@localhost ~]# mysql -u root -p //再次登入 Enter password: Welcome to the MySQL monitor. Commands end with ; or g. mysql> use bbs 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 mysql> select * from user; //查询数据,发现数据有恢复原样,没有保存修改 +----+-----------+------+ | id | name | age | +----+-----------+------+ | 1 | zhangshan | 25 | +----+-----------+------+ 1 row in set (0.00 sec) ###################### 不提交的事务是不会保存的 mysql> begin; //开始一个事务 Query OK, 0 rows affected (0.00 sec) mysql> update user set id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select id from user; //查看id信息,id为2 +----+ | id | +----+ | 2 | +----+ 1 row in set (0.00 sec) mysql> savepoint a; //设置一个回滚点 Query OK, 0 rows affected (0.00 sec) mysql> update user set id=3; //将id改为3 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select id from user; +----+ | id | +----+ | 3 | +----+ 1 row in set (0.00 sec) mysql> rollback to a; //回到回滚点a之前 Query OK, 0 rows affected (0.00 sec) mysql> select id from user; 查看id信息,id变回了2,a之前的信息 +----+ | id | +----+ | 2 | +----+ 1 row in set (0.00 sec) mysql> update user set id=4; //修改id=4 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select id from user; +----+ | id | +----+ | 4 | +----+ 1 row in set (0.00 sec) mysql> savepoint b; //设置回滚点b Query OK, 0 rows affected (0.00 sec) mysql> rollback to a; //回到回滚点a之前 Query OK, 0 rows affected (0.00 sec) mysql> rollback to b; //再回滚到b,发现回滚点吧不存在,完完全全回到a之前,包括a之后设置的回滚点也会消失 ERROR 1305 (42000): SAVEPOINT b does not exist mysql> commit; //提交事务 Query OK, 0 rows affected (0.00 sec) mysql> select id from user; +----+ | id | +----+ | 2 | +----+ 1 row in set (0.00 sec)
MySQL数据库存储引擎
概述
MyISAM
lnnoDB关于MyISAM存储引擎
MyISAM概述
.frm文件存储表定义
数据文件的扩展名为.MYD (MYData)
索引文件的扩展名是.MYI (MYIndex)
会在数据写入的过程阻塞用户数据的读取
也会在数据读取的过程中阻塞用户的数据写入
静态表
动态表
压缩表MyISAM的适用场景
InnoDB存储引擎
InnoDB的概述
InnoDB的适用场景
企业选择存储引擎的依据
所有引擎都支持通用的数据类型
但不是所有的引擎都支持其它的字段类型,如二进制对象
表锁定
行锁定
建立索引在搜索和恢复数据库中的数据时能显著提高性能
不同的存储引擎提供不同的制作索引的技术
有些存储引擎根本不支持索引
提高在向表中更新和插入信息期间的可靠性
可根据企业业务是否要支持事务选择存储引擎相关操作
查看引擎
相关语法
show engine //查看数据库引擎 show table status from 表名 where name='表名' //查看表引擎 show create table 表名 //同样可以用于查看表引擎
操作
mysql> show engines; //查看引擎 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) mysql> show create table user G; //可以查看表的引擎 G代表竖向排列 *************************** 1. row *************************** Table: user Create Table: CREATE TABLE "user" ( "id" int(10) NOT NULL, "name" char(16) DEFAULT '无名小卒', "age" int(3) DEFAULT '0', PRIMARY KEY ("id") ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
修改表的存储引擎
常用语句
create table 表名 engine=引擎类型 //创建表时指定引擎类型 alter table 表名 engine=引擎类型 //修改表的引擎类型
操作
mysql> alter table user engine=MyISAM; //修改表引擎为MyISAM Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show create table user G; //查看,修改成功 *************************** 1. row *************************** Table: user Create Table: CREATE TABLE "user" ( "id" int(10) NOT NULL, "name" char(16) DEFAULT '无名小卒', "age" int(3) DEFAULT '0', PRIMARY KEY ("id") ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> CREATE TABLE "user1" ( //创建表时指定存储引擎 -> "id" int(10) NOT NULL, -> "name" char(16) DEFAULT '无名小卒', -> "age" int(3) DEFAULT '0', -> PRIMARY KEY ("id") -> ) ENGINE=innodb DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) mysql> show create table user1 G; //查看,指定成功 *************************** 1. row *************************** Table: user1 Create Table: CREATE TABLE "user1" ( "id" int(10) NOT NULL, "name" char(16) DEFAULT '无名小卒', "age" int(3) DEFAULT '0', PRIMARY KEY ("id") ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> create table name(id int)engine=MyISAM; //创建表时指定引擎 Query OK, 0 rows affected (0.01 sec) mysql> show create table name G; //查看验证 *************************** 1. row *************************** Table: name Create Table: CREATE TABLE "name" ( "id" int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> alter table name engine=innodb; //使用alter语句修改引擎 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table name G; //查看验证,修改成功 *************************** 1. row *************************** Table: name Create Table: CREATE TABLE "name" ( "id" int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified
修改默认引擎
修改/etc/my.cof 配置文件中配置
[mysqld]区域下配置 default-storage-engine=引擎类型 //插入这条配置 例如:default-storage-engine=InnoDB
操作
[root@localhost ~]# vi /etc/my.cnf [mysqld] ……省略部分 default-storage-engine=myisam //插入这条语句,配置默认引擎为myisam [root@localhost ~]# systemctl restart mysqld //重启生效 mysql> CREATE TABLE "user2" ( //创建新表测试 -> "id" int(10) NOT NULL, -> "name" char(16) DEFAULT '无名小卒', -> "age" int(3) DEFAULT '0', -> PRIMARY KEY ("id")); Query OK, 0 rows affected (0.01 sec) mysql> show table status where name='user2'G; //查看新表,默认存储引擎变为myisam类型 *************************** 1. row *************************** Name: user2 Engine: MyISAM Version: 10 Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 16044073672507391 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2020-09-12 06:07:58 Update_time: 2020-09-12 06:07:58 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> show variables like '%storage_engine%'; //使用查看变量的方式查看默认引擎 +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | default_storage_engine | MyISAM | | default_tmp_storage_engine | InnoDB | | disabled_storage_engines | | | internal_tmp_disk_storage_engine | InnoDB | +----------------------------------+--------+ 4 rows in set (0.00 sec)
转化存储引擎
格式: Mysql_convert_table_format-user=root--password=密码--sock=/tmp/mysql.sock-engine=引擎 库名 表名
[root@localhost ~]# yum -y install perl-DBI perl-DBD-MySQL [root@localhost ~]# /usr/local/mysql/bin/mysql_convert_table_format --user=root --password='123456' --sock=/tmp/mysql.sock auth
本网页所有视频内容由 imoviebox边看边下-网页视频下载, iurlBox网页地址收藏管理器 下载并得到。
ImovieBox网页视频下载器 下载地址: ImovieBox网页视频下载器-最新版本下载
本文章由: imapbox邮箱云存储,邮箱网盘,ImageBox 图片批量下载器,网页图片批量下载专家,网页图片批量下载器,获取到文章图片,imoviebox网页视频批量下载器,下载视频内容,为您提供.
阅读和此文章类似的: 全球云计算