MySQL事务、索引、数据恢复和备份
MySQL的事务处理
- 事务就是将一组SQL语句放在同一批次内去执行
- 如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行
注意：MySQL事务处理只支持InnoDB和BDB数据表类型
- 事务必须具备以下四个属性，简称 ACID 属性
- 原子性（Atomicity）
- 一致性（Consistency）
- 隔离性（Isolation）
- 持久性（Durability）
MySQL的事务实现方法
- SET AUTOCOMMIT
- 使用SET语句来改变自动提交模式
SET AUTOCOMMIT = 0; -- 关闭自动提交模式
SET AUTOCOMMIT = 1; -- 开启自动提交模式
- 注意：
- MySQL中默认是自动提交
- 使用事务时应先关闭自动提交
- START TRANSACTION
- 开始一个事务,标记事务的起始点
- COMMIT
- 提交一个事务给数据库
- ROLLBACK
- 将事务回滚，数据回到本次事务的初始状态
- SET AUTOCOMMIT = 1;
- 开启MySQL数据库的自动提交
MySQL事务处理步骤
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-buMC8PiD-1662540988774)(G:\笔记文档\mdp\数据库\SharedScreenshot.jpg)]
示例
-- 事务脏读现象
-- 关闭自动提交
set autocommit=0;
-- 开启事务
START TRANSACTION;
-- 过程体
update yhzz SET money = money + 100 WHERE id = 1;
UPDATE yhzz SET money = money - 100 + 'aaa' WHERE id = 2;
-- 回滚
ROLLBACK;
-- 重新开启自动提交
SET autocommit = 1;
COMMIT;
数据库索引
- 作用
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接，实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化
- 分类
- 主键索引（PRIMARY KEY）
- 唯一索引（UNIQUE）
- 常规索引（INDEX）
- 全文索引（FULLTEXT）
主键索引
- 某一个属性或属性的组合能唯一标识一条记录
- 如：学生表（学号，姓名，班级，性别等） ，学号就是唯一标识的，可作为主键
- 特点
- 最常见的索引类型
- 确保数据记录的唯一性
- 确定特定数据记录在数据库中的位置
CREATE TABLE `Grade` (
`GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY,
-- 省略代码……
-- 主键索引也可在字段字义之后，如
-- PRIMARY KEY(`GradeID`)
)
唯一索引（UNIQUE）
- 作用
- 避免同一个表中某数据列中的值重复（null可以重复）
- 与主键索引的区别
- 主键索引只能有一个
- 唯一索引可有多个
CREATE TABLE `Grade` (
`GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
-- 或 UNIQUE KEY `GradeID` (`GradeID`)
)
常规索引（INDEX）
- 作用：快速定位特定数据
- 注意
- index和key关键字都可设置常规索引
- 应加在查找条件的字段
- 不宜添加太多常规索引，影响数据的插入、删除和修改操作
CREATE TABLE `result` (
//省略一些代码
INDEX/KEY `ind` (`studentNo`, `subjectNo`) -- 创建表时添加
)
ALTER TABLE `result` ADD INDEX `ind` (`studentNo`, `subjectNo`); -- 创建后追加
全文索引（FULLTEXT）
- 作用：快速定位特定数据
- 注意
- 只能用于MyISAM类型的数据表
- 只能用于 CHAR 、 VARCHAR、TEXT数据列类型
- 适合大型数据集
CREATE TABLE `student` (
#省略一些SQL语句
FULLTEXT (`StudentName`) -- 创建表时添加
)ENGINE=MYISAM;
ALTER TABLE employee ADD FULLTEXT (`first_name`); -- 创建后追加
管理索引
- 创建索引
- 创建表时添加
- 建表后追加
ALERT TABLE 表名 ADD 索引类型（数据列名）
- 删除索引
DROP INDEX 索引名 ON 表名
ALTER TABLE 表名 DROP INDEX 索引名
ALTER TABLE 表名 DROP PRIMARY KEY
- 查看索引
SHOW INDEX(或KEYS) FROM 表名
索引准则
- 在WHERE、ORDER BY 子句中经常使用的字段
- 字段的值是多个（例如性别字段则不适合）
- 索引不是越多越好
- 不宜过多添加索引
- 每添加一条索引都会占用磁盘空间
- 不要对经常变动的数据加索引
- 经常变化的字段，添加索引反而降低性能
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段
MySQL的备份
- 数据库备份必要性
- 保证重要数据不丢失
- 数据转移
- MySQL数据库备份方法
- mysqldump备份工具
- 数据库管理工具，如SQLyog
- 直接拷贝数据库文件和相关配置文件
mysqldump数据库备份
- 作用
- 转储数据库
- 搜集数据库进行备份
- 将数据转移到另一个SQL服务器(不一定是MySQL服务器)
-- 语法
mysqldump -h 主机名 –u 用户名 –p [options] 数据库名
[ table1 table2 table3 ]
> path/filename.sql -- 预存文件目录，须有该目录读写权限
-- 示例
-- 备份myschool数据库如：
> mysqldump -u root -p myschool
> d:/myschool.sql
EnterPassword: *****
mysqldump常用选项
| 符号名称 | 描述 |
|---|---|
| –add-drop-table | 导出sql脚本会加上 DROP TABLE IF EXISTS 语句 默认是打开的，可以用 --skip-add-drop-table 来取消 |
| –add-locks | 该选项会在INSERT 语句中捆绑一个LOCK TABLE 和UNLOCK TABLE 语句 好处：防止记录被再次导入时，其他用户对表进行的操作，默认是打开的 |
| -t 或 --no-create-info | 忽略不写重新创建每个转储表的CREATE TABLE语句 |
| -c 或 --complete-insert | 在每个INERT语句的列上加上字段名 在数据库导入另一个数据库时非常有用 |
| -d 或 --no-data | 不写表的任何行信息。对于只想转储表的结构很有用 |
| –where “where-condition”, -w “where-condition” | 只转储给定的WHERE条件选择的记录 |
| –opt | 该选项是速记；等同于指定 --add-drop-tables --add-locking --create-option --disable-keys–extended-insert --lock-tables --quick --set-charset |
- 可通过mysqldump --help 查看该命名的选项
MySQL数据库的恢复
- 方法一
- 用 SOURCE 语法
- /path/是一个绝对路径，并且必须是mysql 运行用户有权限读取的文件
- SOURCE 在MySQL命令行里执行
SOURCE /path/db_name.sql; -- 都要先创建或选择数据库
- 方法二
- 用 mysql 客户端
mysql –u root –p dbname
< /path/db_name.sql;
利用SQL语句导出、导入数据
SELECT * INTO OUTFILE 'file_name'
FROM tbl_name -- 导出数据
-- 输出的文件不能先存在，否则报错
LOAD DATA INFILE 'file_name '
INTO TABLE tbl_name[FIELDS] -- 导入数据
bname
< /path/db_name.sql;
### 利用SQL语句导出、导入数据
```sql
SELECT * INTO OUTFILE 'file_name'
FROM tbl_name -- 导出数据
-- 输出的文件不能先存在，否则报错
LOAD DATA INFILE 'file_name '
INTO TABLE tbl_name[FIELDS] -- 导入数据
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
