淮南哪里做网站,中国建设网官网下载,浅谈电子商务网站建设与管理,泰州市建设局网站简介#xff1a;个人学习分享#xff0c;如有错误#xff0c;欢迎批评指正。 一、MySQL的触发器
1.概述
介绍 触发器#xff0c;就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段#xff0c;但是触发器无需调用个人学习分享如有错误欢迎批评指正。 一、MySQL的触发器
1.概述
介绍 触发器就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段但是触发器无需调用当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行无需手动调用。
在MySQL中只有执行insert,delete,update操作时才能触发触发器的执行
触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作。
使用别名 OLD 和NEW 来引用触发器中发生变化的记录内容这与其他的数据库是相似的。现在触发器还只支持行级触发不支持语句级触发。 触发器的特性 1、什么条件会触发I、D、U 2、什么时候触发在增删改前或者后 3、触发频率针对每一行执行 4、触发器定义在表上附着在表上
格式
a.创建只有一个执行语句的触发器 create trigger 触发器名 before|after 触发事件 on 表名 for each row 执行语句; b.创建有多个执行语句的触发器 create trigger 触发器名 before|after 触发事件 on 表名 for each row begin 执行语句列表 end; 代码
create database if not exists mydb10_trigger;
use mydb10_trigger;-- 用户表
create table user(uid int primary key ,username varchar(50) not null,password varchar(50) not null
);
-- 用户信息操作日志表
create table user_logs(id int primary key auto_increment,time timestamp,log_text varchar(255)
);-- 如果触发器存在则先删除
drop trigger if exists trigger_test4;-- 创建触发器trigger_test1
create trigger trigger_test1
after insert on user -- 触发时机当添加user表数据时触发
for each row
insert into user_logs values(NULL,now(), 有新用户注册);-- 添加数据触发器自动执行并添加日志代码
insert into user values(1,张三,123456);-- 如果触发器trigger_test2存在则先删除
drop trigger if exists trigger_test2;-- 创建触发器trigger_test2
delimiter $$
create trigger trigger_test2
after update on user -- 触发时机当修改user表数据时触发
for each row -- 每一行
begin
insert into user_logs values(NULL,now(), 用户修改发生了修改);
insert into user_logs values(NULL,now(), 用户修改发生了修改);
end $$delimiter ;
-- 添加数据触发器自动执行并添加日志代码
update user set password 888888 where uid 1;
2.操作-NEW与OLD
格式
MySQL 中定义了 NEW 和 OLD用来表示触发器的所在表中触发了触发器的那一行数据来引用触发器中发生变化的记录内容具体地 使用方法NEW.columnName columnName为相应数据表某一列名
代码
-- NEW和OLD-- insert 类型的触发器-- NEW
-- 定义触发器trigger_test3create trigger trigger_test3 after insert
on user for each row
insert into user_logs values(NULL,now(),concat(有新用户添加信息为:,NEW.uid,NEW.username,NEW.password));-- 测试
insert into user values(4,赵六,123456);-- update 类型的触发器
-- NEW
create trigger trigger_test5 after update
on user for each row
insert into user_logs values(NULL,now(),concat_ws(,,有新用户信息修改为信息修改之后为:,NEW.uid,NEW.username,NEW.password));update user set password 000000 where uid 1;-- OLD
create trigger trigger_test4 after update
on user for each row
insert into user_logs values(NULL,now(),concat(有新用户信息修改为信息修改之前为:,OLD.uid,OLD.username,OLD.password));update user set password 999999 where uid 4;-- delete 类型的触发器
-- OLDcreate trigger trigger_test6 after delete
on user for each row
insert into user_logs values(NULL,now(),concat(,,有用户被删除被删除用户信息为:,old.uid,old.username,old.password));
操作-查看触发器
show triggers;操作-删除触发器
-- drop trigger [if exists] trigger_name
drop trigger if exists trigger_test1;注意事项 1.MYSQL中触发器中不能对本表进行 insert ,update ,delete 操作以免递归循环触发 2.尽量少使用触发器假设触发器触发每次执行1sinsert table 500条数据那么就需要触发500次触发器光是触发器执行的时间就花费了500s而insert 500条数据一共是1s那么这个insert的效率就非常低了。 3. 触发器是针对每一行的对增删改非常频繁的表上切记不要使用触发器因为它会非常消耗资源。
二、MySQL的存储引擎
概念
数据库存储引擎是数据库底层软件组织数据库管理系统DBMS使用数据引擎进行创建、查询、更新和删除数据。 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。 用户可以根据不同的需求为数据表选择不同的存储引擎
可以使用SHOW ENGINES 命令 可以查看Mysql的所有执行引擎我们 可以到 默认的执行引擎是innoDB 支持事务行级锁定和外键。
分类
MyISAMMysql 5.5之前的默认数据库引擎最为常用。拥有较高的插入查询速度但不支持事务 InnoDB事务型速记的首选引擎支持ACID事务支持行级锁定MySQL5.5成为默认数据库引擎 Memory 所有数据置于内存的存储引擎拥有极高的插入更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在MYSQL重新启动是会丢失。 Archive 非常适合存储大量的独立的作为历史记录的数据。因为它们不经常被读取。Archive 拥有高效的插入速度但其对查询的支持相对较差 Federated 将不同的 MySQL 服务器联合起来逻辑上组成一个完整的数据库。非常适合分布式应用 CSV 逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .csv 文件。这是一种普通文本文件每个数据行占用一个文本行。CSV 存储引擎不支持索引。 BlackHole 黑洞引擎写入的任何数据都会消失一般用于记录 binlog 做复制的中继 ERFORMANCE_SCHEMA存储引擎该引擎主要用于收集数据库服务器性能参数。 Mrg_Myisam Merge存储引擎是一组MyIsam的组合也就是说他将MyIsam引擎的多个表聚合起来但是他的内部没有数据真正的数据依然是MyIsam引擎的表中但是可以直接进行查询、删除更新等操作。 代码 -- 查询当前数据库支持的存储引擎
show engines;-- 查看当前的默认存储引擎
show variables like %storage_engine%;-- 查看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
show create table student; -- 创建新表时指定存储引擎
create table stu2(id int, name varchar(20)) engineMyISAM;
show create table stu2;
-- 修改数据库引擎
alter table student engine INNODB;
alter table stu2 engine MyISAM;**-- 修改MySQL默认存储引擎方法 关闭mysql服务找到mysql安装目录下的my.ini文件找到default-storage-engineINNODB 改为目标引擎 如default-storage-engineMYISAM 4.启动mysql服务 ** 三、MySQL的事务
概述 在MySQL中的事务Transaction是由存储引擎实现的在MySQL中只有InnoDB存储引擎才支持事务。
事务处理可以用来维护数据库的完整性保证成批的 SQL 语句要么全部执行要么全部不执行。
事务用来管理 DDL、DML、DCL 操作比如 insert,update,delete 语句默认是自动提交的。 理解事务 在银行转账时必须保证转账绝对安全这时需要事务参与:
update account set money money - 200 where id 1;
update account set money money 200 where id 2;假如在第一次update之后出现了意外、异常没有执行第二次update这时转账是否会出现异常
事务操作 MySQL的事务操作主要有以下三种
1、开启事务Start Transaction
任何一条DML语句(insert、update、delete)执行标志事务的开启 命令BEGIN 或 START TRANSACTION
2、提交事务Commit Transaction 成功的结束将所有的DML语句操作历史记录和底层硬盘数据来一次同步 命令COMMIT
3、回滚事务Rollback Transaction 失败的结束将所有的DML语句操作历史记录全部清空 命令ROLLBACK
之前的所有SQL操作其实也有事务只是MySQL自动帮我们完成的每执行一条SQL时MySQL就帮我们自动提交事务因此如果想要手动控制事务则必须关闭MySQL的事务自动提交。
在MySQL中直接用 SET 来改变 MySQL 的自动提交模式:
set autocommit0 禁止自动提交
set autocommit1 开启自动提交 代码
create database if not exists mydb12_transcation;
use mydb12_transcation;
-- 创建账户表
create table account(id int primary key, -- 账户idname varchar(20), -- 账户名money double -- 金额
);-- 插入数据
insert into account values(1,zhangsan,1000);
insert into account values(2,lisi,1000);-- 设置MySQL的事务为手动提交(关闭自动提交)
select autocommit;
set autocommit 0;-- 模拟账户转账
-- 开启事务
begin;
update account set money money - 200 where name zhangsan;
update account set money money 200 where name lisi;
-- 提交事务不可逆
commit;-- 如果转账中的任何一条出现问题则回滚事务
rollback;
事务的特性 事务的隔离级别
Isolate顾名思义就是将事务与另一个事务隔离开为什么要隔离呢如果一个事务正在操作的数据被另一个事务修改或删除了最后的执行结果可能无法达到预期。如果没有隔离性还会导致其他问题。 读未提交(Read uncommitted) 一个事务可以读取另一个未提交事务的数据最低级别任何情况都无法保证,会造成脏读。 读已提交(Read committed) 一个事务要等另一个事务提交后才能读取数据可避免脏读的发生会造成不可重复读。
可重复读(Repeatable read) 就是在开始读取数据事务开启时不再允许修改操作可避免脏读、不可重复读的发生但是会造成幻读。
串行(Serializable) 是最高的事务隔离级别在该级别下事务串行化顺序执行可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下比较耗数据库性能一般不使用。
Mysql的默认隔离级别是Repeatable read。 代码
-- 查看隔离级别
show variables like %isolation%; -- 设置隔离级别
/*
set session transaction isolation level 级别字符串
级别字符串read uncommitted、read committed、repeatable read、serializable
*/
-- 设置read uncommitted
set session transaction isolation level read uncommitted;-- 设置read committed
set session transaction isolation level read committed;-- 设置repeatable read
set session transaction isolation level repeatable read;-- 设置serializable
set session transaction isolation level serializable;四、MySQL的锁机制
锁是计算机协调多个进程或线程并发访问某一资源的机制避免争抢。
在数据库中除传统的计算资源如 CPU、RAM、I/O 等的争用以外数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说锁对数据库而言显得尤其重要也更加复杂。 概述 从对数据操作的粒度分
1 表锁操作时会锁定整个表。
2行锁操作时会锁定当前操作行。
从对数据操作的类型分
1 读锁共享锁针对同一份数据多个读操作可以同时进行而不会互相影响。
2 写锁排它锁当前操作没有完成之前它会阻断其他写锁和读锁。
相对其他数据库而言MySQL的锁机制比较简单其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况 MySQL锁的特性可大致归纳如下 从上述特点可见很难笼统地说哪种锁更好只能就具体应用的特点来说哪种锁更合适仅从锁的角度来说表级锁更适合于以查询为主只有少量按索引条件更新数据的应用如Web 应用
而行级锁则更适合于有大量按索引条件并发更新少量不同数据同时又有并查询的应用如一些在线事务处理OLTP系统。
MyISAM 表锁 MyISAM 在执行查询语句SELECT前会自动给涉及的所有表加读锁在执行更新操作UPDATE、DELETE、INSERT 等前会自动给涉及的表加写锁这个过程并不需要用户干预因此用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。
加读锁 lock table table_name read; 加写锁 lock table table_name write表锁特点
1 对MyISAM 表的读操作不会阻塞其他用户对同一表的读请求但会阻塞对同一表的写请求
2 对MyISAM 表的写操作则会阻塞其他用户对同一表的读和写操作
简而言之就是读锁会阻塞写但是不会阻塞读。而写锁则既会阻塞读又会阻塞写。
此外MyISAM 的读写锁调度是写优先这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后其他线程不能做任何操作大量的更新会使查询很难得到锁从而造成永远阻塞。
create table tb_book (id int(11) auto_increment,name varchar(50) default null,publish_time date default null,status char(1) default null,primary key (id)
) enginemyisam default charsetutf8 ;insert into tb_book (id, name, publish_time, status) values(null,java编程思想,2088-08-01,1);
insert into tb_book (id, name, publish_time, status) values(null,solr编程思想,2088-08-08,0);
create table tb_user (id int(11) auto_increment,name varchar(50) default null,primary key (id)
) enginemyisam default charsetutf8 ;insert into tb_user (id, name) values(null,令狐冲);
insert into tb_user (id, name) values(null,田伯光);InnoDB行锁
行锁特点
行锁特点 偏向InnoDB 存储引擎开销大加锁慢会出现死锁锁定粒度最小发生锁冲突的概率最低,并发度也最高。 InnoDB 与 MyISAM 的最大不同有两点一是支持事务二是 采用了行级锁。 行锁模式
InnoDB 实现了以下两种类型的行锁。
共享锁S又称为读锁简称S锁共享锁就是多个事务对于同一数据可以共享一把锁都能访问到数据但是只能读不能修改。 排他锁X又称为写锁简称X锁排他锁就是不能与其他锁并存如一个事务获取了一个数据行的排他锁其他事务就不能再获取该行的其他锁包括共享锁和排他锁但是获取排他锁的事务是可以对数据就行读取和修改。
对于UPDATE、DELETE和INSERT语句InnoDB会自动给涉及数据集加排他锁X)
对于普通SELECT语句InnoDB不会加任何锁
可以通过以下语句显示给记录集加共享锁或排他锁 。
共享锁sselect * from table_name where ... lock in share mode
排他锁x) select * from table_name where ... for update操作代码
-- 行锁
drop table if exists test_innodb_lock;
create table test_innodb_lock(id int(11),name varchar(16),sex varchar(1)
)engine innodb ;insert into test_innodb_lock values(1,100,1);
insert into test_innodb_lock values(3,3,1);
insert into test_innodb_lock values(4,400,0);
insert into test_innodb_lock values(5,500,1);
insert into test_innodb_lock values(6,600,0);
insert into test_innodb_lock values(7,700,0);
insert into test_innodb_lock values(8,800,1);
insert into test_innodb_lock values(9,900,1);
insert into test_innodb_lock values(1,200,0);create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);
五、MySQL的日志
介绍 在任何一种数据库中都会有各种各样的日志记录着数据库工作的方方面面以帮助数据库管理员追踪数据库曾经发生过的各种事件。MySQL 也不例外。
日志分类错误日志二进制日志查询日志慢查询日志。
错误日志
错误日志是 MySQL 中最重要的日志之一它记录了当 mysqld 启动和停止时以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时可以首先查看此日志。
该日志是默认开启的 默认存放目录为 mysql 的数据目录, 默认的日志文件名为 hostname.errhostname是主机名。
查看日志位置指令
show variables like log_error%;二进制日志-binlog
二进制日志BINLOG记录了所有的 DDL数据定义语言语句和 DML数据操纵语言语句但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用MySQL的主从复制 就是通过该binlog实现的。
二进制日志MySQl8.0默认已经开启低版本的MySQL的需要通过配置文件开启并配置MySQL日志的格式。 Windows系统my.ini Linux系统:my.cnf
#配置开启binlog日志 日志的文件前缀为 mysqlbin ----- 生成的文件名如 : mysqlbin.000001,mysqlbin.000002
log_binmysqlbin#配置二进制日志的格式
binlog_formatSTATEMENT日志格式
STATEMENT 该日志格式在日志文件中记录的都是SQL语句statement每一条对数据进行修改的SQL都会记录在日志文件中通过Mysql提供的mysqlbinlog工具可以清晰的查看到每条语句的文本。主从复制的时候从库slave会将日志解析为原文本并在从库重新执行一次。
ROW 该日志格式在日志文件中记录的是每一行的数据变更而不是记录SQL语句。比如执行SQL语句 update tb_book set status‘1’ , 如果是STATEMENT 日志格式在日志中会记录一行SQL文件 如果是ROW由于是对全表进行更新也就是每一行记录都会发生变更ROW 格式的日志中会记录每一行的数据变更。
MIXED 混合了STATEMENT 和 ROW两种格式。
-- 查看MySQL是否开启了binlog日志
show variables like log_bin;-- 查看binlog日志的格式
show variables like binlog_format;-- 查看所有日志
show binlog events;-- 查看最新的日志
show master status;-- 查询指定的binlog日志
show binlog events in binlog.000010;
select * from mydb1.emp2;
select count(*) from mydb1.emp2;
update mydb1.emp2 set salary 8000;-- 从指定的位置开始,查看指定的Binlog日志
show binlog events in binlog.000010 from 156;-- 从指定的位置开始,查看指定的Binlog日志,限制查询的条数
show binlog events in binlog.000010 from 156 limit 2;
--从指定的位置开始带有偏移查看指定的Binlog日志,限制查询的条数
show binlog events in binlog.000010 from 666 limit 1, 2;-- 清空所有的 binlog 日志文件
reset master
查询日志
查询日志中记录了客户端的所有操作语句而二进制日志不包含查询数据的SQL语句。 默认情况下 查询日志是未开启的。如果需要开启查询日志可以设置以下配置
#该选项用来开启查询日志 可选值 0 或者 1 0 代表关闭 1 代表开启
general_log1
#设置日志的文件名 如果没有指定 默认的文件名为 host_name.log
general_log_filefile_name-- 查看MySQL是否开启了查询日志
show variables like general_log;-- 开启查询日志
set global general_log1;select * from mydb1.emp2;
select * from mydb6_view.emp;select count(*) from mydb1.emp2;
select count(*) from mydb6_view.emp;
update mydb1.emp2 set salary 9000;慢查询日志 慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志。long_query_time 默认为 10 秒最小为 0 精度可以到微秒。
# 该参数用来控制慢查询日志是否开启 可取值 1 和 0 1 代表开启 0 代表关闭
slow_query_log1# 该参数用来指定慢查询日志的文件名
slow_query_log_fileslow_query.log# 该选项用来配置查询的时间限制 超过这个时间将认为值慢查询 将需要进行日志记录 默认10slong_query_time10该内容主要是本人对哔站up黑马程序员的mysql课程的学习总结
结~~~
