mysql

安装

• linux的安装文档在进阶篇里面。

SQL

练习

链接

DDL

• 操作数据库对象(数据库、表、表字段)。

DML

• 增、删、改。

DQL

• 查。

区别

1.where和having区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组
  之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，而having可以。
• 注意：分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
  2.in和find_in_set的区别

3. in和exists的区别

DCL

• 管理数据库用户、控制数据库的访问权限。

sql执行顺序

• from … where … group by …聚合函数 … having … select … order by … limit …

函数

字符串函数

• 以指定字符拆分字符串:regexp_substr
• 拼接字符串concat()、concat_ws()
• 以逗号拼接字符串：wm_concat

数值函数

• 取整、取模、四舍五入。

日期函数

• DATEDIFF(date1,date2)：返回起始时间date1 和 结束时间date2之间的天数。

流程函数

• IF(value , t , f)：如果value为true，则返回t，否则返回f。
• IFNULL(value1 , value2)：如果value1不为空，返回value1，否则返回value2。
• 判断函数:case when、decode、exists、not exists
• 分组排序：row_number() over()
• 去重：distinct()

其它

• in和find_in_set的区别：查询的条件是常量(传入的值)那就使用in，是变量(查询出来的值)则使用find_in_set。

约束

概念

• 定义字段的规则，限制存储在表中的数据。

类型

• PRIMARY KEY：主键约束，用于约束该字段的值具有唯一性，至多有一个，可以没有，并且非空。比如学号、员工编号等。
• UNIQUE：唯一约束，用于约束该字段的值具有唯一性，可以有多个，可以没有，可以为空。比如座位号。
• NOT NULL：非空约束，用于约束该字段的值不能为空。比如姓名、学号等。
• DEFAULT：默认值约束，用于约束该字段有默认值，约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值。比如性别。
• CHECK：检查约束，用来检查数据表中，字段值是否有效。比如年龄、性别。
• 外键约束：两张表的数据之间建立连接，从而保证数据的一致性和完整性。

设计

数据库设计

(1)一对一：一个人只能有一个身份证号码(主键、身份证号码关联)。
(2)一对多：一个部门有多个员工(员工表里面存部门id、包含中间表部门id和员工id，两个相互对应)。
(3)多对多：一个客户可以参与多个优惠活动，一个优惠活动可以被多个客户参加(中间表)。

char 和 varchar 的区别

(1)char(n) ：固定长度类型，比如char(10)，当你输入"abc"三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。
char优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。
(2)varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。
所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

多表查询

• 笛卡尔积：两个集合A集合 和 B集合的所有组合情况。多表查询时如果没有关联条件就会产生笛卡尔积。

分类

• 内连接语法
  

子查询：

• SQL语句中嵌套SELECT语句，又称为嵌套查询。
• 
• 
  
  
• 

联合查询

• 多张表的列数、字段类型必须保持一致。
• union all (效率高)会将全部的数据合并在一起，union 会对合并之后的数据去重。

事务

四大特性(ACID)

• 原子性（Atomicity）：操作数据库数据时，要么全部成功，要么全部失败，其中只要又一个失败，数据就会回滚。
• 一致性（Consistency）：数据在事务前后，业务整体一致(转账：A:1000 B:1000 总:2000 转200 事务成功 A:800 B:1200 总:还是2000)。
• 隔离性（Isolation）：事务之间项目隔离(一个方法里面100个事务同时执行，一个事务失败了数据回滚，不会影响到其它事务)。
• 持久性（Durability）：一旦事务成功，数据一定会保存在数据库。

并发事务问题

• 赃读：一个事务读到另外一个事务还没有提交的数据。
• 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
• 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”。

事务隔离级别

• 事务隔离级别越高，数据越安全，但是性能越低
  
• 未提交读（read uncommitted）
• 提交读（read committed）
• 可重复读（repeatable read）
• 串行化（serializable）

配置

• 通过sql
• 在MySQL. ini的最后添加：transaction-isolation = REPEATABLE-READ，可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

存储引擎

InnoDB(默认)

特点

• 支持事务。
• 支持行级锁，提高并发访问性能。
• 支持外键(FOREIGN KEY)，保证数据的完整性和正确性；

MyISAM

特点

• 不支持事务，不支持外键。
• 支持表锁，不支持行锁。
• 访问速度快。

Memory

特点

• 数据存储在内存中。
• hash索引（默认）。

区别

选择

• InnoDB:对数据的完整性、一致性有较高的要求；除了插入和查询之外，还包含很多的更新、删除操作(行锁)。
• MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作(表锁，更新和删除会锁住整个表)；对数据的完整性、一致性要求不高(日志、足迹)；有mongodb替代。
• MEMORY：数据保存在内存中，访问速度快，通常用于临时表及缓存；对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性；有redis替代。

索引

概述

• 一种查找算法类型的数据结构，让mysql可以高效的查找数据。
• 如果不使用索引，数据库就会从第一条数据开始查，直到查到相关数据，表的条目数越多，花费的时间也就越多。

优劣势

结构

• InnoDB默认是B+Tree(理解：所有的数据都存放在叶子节点)。
• B+Tree优点(为什么选择B+Tree)：查询效率高；支持等值查询、范围查询、排序。
  
  

分类

具体类型：主键索引&唯一索引&常规索引(包括联合索引)&全文索引(用的少)

存储形式： 聚集索引&二级索引

• 聚集索引：关联整行数据。
• 二级索引：关联查询的字段值对应的聚集索引。
  

聚集索引选取规则

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

语法(创建、查看、删除)

sql性能分析

sql执行频率

• 查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次。

show [session|global] status
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';(7个下划线)

慢查询日志

• 开启(默认没有开启)。
• 配置。

profile详情

• 查看每条sql的耗时及每个阶段耗时。

explain

• 关键字：explain / desc
• 重点关注字段：type、possible_key、key、key_len。
• 相关博客

使用

最左前缀法则

• 联合索引中，查询从索引的最左列开始，并且不跳过索引中的列。如果跳过了某一列，后面的字段索引就会失效。索引的列只要存在就行了，和位置无关。
• 联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。加了=就不会失效。在业务允许的情况下，尽可能的使用类似于 >= 或<=。

索引失效的情况

• 索引列上进行运算(包括字符串截取等)。
• 字符串查询不加引号。
• 左模糊查询(右模糊不会)。
• or连接时，左右两侧字段都有索引时，索引才会生效。
• order by排序时，字段排序顺序和创建索引的顺序不一样(不满足顺序的字段及后面的字段会失效)。
• 数据分布影响：MySQL评估如果使用索引比全表更慢，则不使用索引(通常索引不能覆盖查询结果一半以上的数据，就不会走索引)；is null与 is not null操作也是如此，不是固定的。

sql提示

• 建议、强制、忽略使用某个索引。

覆盖索引

• 概念：查询使用了索引，并且需要返回的列在该索引中已经全部能够找到 (如果找不到需要回表查询，拿到主键id，再去扫描聚集索引，再获取额外的数据，这个过程就是回表）。
• 可通过执行计划的extra查看是否有回表查询(每个版本的mysql显示可能不一样)。
• 使用select * 很容易造成回表查询（除非是根据主键查询，此时只会扫描聚集索引）。
  

前缀索引

• 概念：当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
• 前缀长度：根据索引的选择性来决定，而选择性是指不重复的索引值和数据表的记录总数的比值(截取字段的前缀查询在表中的数量/表数据的数量，越接近1越好，实际开发中在选择性和索引的体积之间做一个平衡(截取的前缀越少，索引体积越小))。

select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;

单列索引&联合索引

• and连接的两个字段时，都有单列索引，但是最终mysql只会选择一个索引，如果select查询的值的索引没有被选中就会回表查询。
• 存在多个查询条件时，考虑对查询字段建立索引时，建议建立联合索引。

命名规则

• 全部使用小写。
• 非唯一索引：idx_字段名称,例如idx_name。
• 组合索引：包含所有字段名,过长的字段名可以采用缩写形式。例如idx_age_name。
• 唯一索引：uniq_字段名称。例如uniq_name。

索引设计原则

• 针对数据量较大，且查询比较频繁的表建立索引(几万条数据的表不建索引效率也很高)。
• 针对常作为查询条件（where）、排序（order by）、分组（group by）后面的字段建立索引。
• 尽量选择区分度高的列作为索引(比如身份证号、电话号码，像性别、状态字段、逻辑删除字段建立索引效率也不高)，尽量建立唯一索引，区分度越高，使用索引的效率越高。
• 如果是字符串类型的字段，字段的长度较长，建前缀索引。
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
• 控制索引的数量，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
• 如果索引列不能存储NULL值，创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

sql优化

插入数据

• 批量插入(最好控制在500-1000条)。
• 手动控制事务。
• 主键顺序插入。

大批量插入数据

• load指令。

主键优化

• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
• 尽量降低主键的长度。
• 尽量不要使用UUID做主键或者是其他自然主键(因为长度长、且插入时不是顺序插入)，如身份证号。
• 避免对主键的修改。

order by优化

• 多字段排序时，order by后面的字段要满足最左前缀法则。
• 尽量使用覆盖索引。
• 倒叙排序时，建反向索引(默认索引的叶子节点是从小到大排序的,MySQL8版本中，支持降序索引，其它版本自己创建降序索引)。
• 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

group by优化

• 多字段排序时，group by后面的字段要满足最左前缀法则(在group by后面没有，但是在where后面有也行(order by没试过))。

limit优化

• 说明：在数据量比较大时进行limit分页查询，越往后，分页查询效率越低；如果执行 limit 2000000,10 ，排序前2000010 记
  录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大。
• 优化思路：
  

count优化

• 说明：没有比较好的优化方式。
• 优化思路：自己计数(可以借助于redis每插入一条+1,但是如果是带条件的count又比较麻烦了)。
• count（主键）、count（字段）、count（数字）、count（）
  
  效率：count(字段) < count(主键 id) < count(1) ≈ count()，所以尽量使用 count(*)。

update优化

• 说明：InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。
• 更新数据时，条件字段要加索引，不然会锁住整张表。

视图(View)

介绍

• 一种虚拟存在的表，数据并不在数据库中实际存在。
• 数据来自 定义视图时使用的表，并且在使用视图时是动态生成的。

作用

• 简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
• 安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
• 数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响(比如基表的字段名改变了，但是视图查询还是想叫原来的名字，可以加个别名)。

存储过程(routines)

介绍

• 事先编译好存储在数据库中的一段 SQL 语句集合。

优点

• 封装，复用： 可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。
• 可以接收参数，也可以返回数据：在存储过程中，可以传递参数，也可以接收返回值。
• 减少网络交互，效率提升：如果涉及到多条SQL，每执行一次都是一次网络传输。 而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。

注意

• 在命令行中执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。

触发器

介绍

• 触发器是与表有关的数据库对象，指在insert/update/delete之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。
• 协助应用在数据库端确保数据的完整性, 日志记录 , 数据校验等操作 。

锁

概述

• 锁是计算机协调多个进程或线程并发访问某一资源的机制。

全局锁

介绍

• 对整个数据库实例加锁，加锁后整个数据库处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。
• 应用场景：数据备份。

特点

表级锁

介绍

• 锁住整张表；锁定粒度大，发生锁冲突的概率最高，并发度最低。

表锁

特点

• 读锁不会阻塞其他客户端的读，但是会阻塞写；也会阻塞自己的写。
• 写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写；不会阻塞自己的读写。

元数据锁(meta data lock 简写MDL)

• 元数据，可以简单理解为就是一张表的表结构。 某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。
• MDL加锁过程是系统自动控制。主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行更改操作。为了避免DML与DDL冲突，保证读写的正确性。
• 在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)。

意向锁

• 为了避免DML在执行时，此时其它线程想要加表锁，会检查当前表是否有对应的行锁，会从第一行数据检查到最后一行数据，效率较低。 InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
• 在执行DML操作时，会对涉及的行加行锁，同时也可以对该表加上意向锁。

行级锁

• 每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最低，并发度最高(InnoDB独有)。

行锁(Record Lock)

• InnoDB的行锁是针对于索引加的锁，不通过索引条件更新和删除时，nnoDB将对表中的所有记录加锁，此时 就会升级为表锁。

间隙锁（Gap Lock）& 临键锁（Next-Key Lock）

• 加这两种锁要在sql中额外加语句，正常的查询不回加。
• 间隙锁：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。
• 临键锁：行锁和间隙锁组合，锁住数据，并锁住数据前后的间隙Gap。在RR隔离级别下支持。
  

mysql管理

系统数据库

常用工具

日志

错误日志

二进制日志

查询日志

慢查询日志

主从复制

主库配置

从库配置

分库分表

mycat

安装

配置

垂直分表

• 业务逻辑实现。

垂直分库

• 原理：把需要跨库查询的表弄成全局表。

分片规则

范围分片

取模分片

一致性hash算法

枚举分片

应用指定算法

固定hash算法

字符串hash解析

按天分片

按自然月分片

mycat监控及管理

原理

监控

读写分离

• 用mycat实现

一主一从

• 弊端：因为mycat配置了读写节点，所以Master宕机之后，业务系统就只能够读、不能写数据。

双主双从

配置文件

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\\working_software\\mysql
# 设置mysql数据库的数据的存放目录
datadir=D:\\working_software\\mysql\\data   
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

数据找回

☆、参考地址(自己的虚拟机中无法开启binglog，还未亲自实践)

步骤

☆、登录后输入命令，查看 binlog 是否开启，binlog 二进制日志文件，数据库的 insert、delete、update、create、alter、drop 等写入操作都会被 binlog 记录。

SHOW VARIABLES LIKE 'LOG_BIN%';

☆：
1、log_bin是处于ON的状态，说明 binlog 是开启的。
2、log_bin_basename是/var/lib/mysql/bin-log，说明 binlog 是存放在 mysql 所在的服务器的/var/lib/mysql目录下，文件是以bin-log开头，比如：bin-log.000001。
☆、登录 mysql 所在的服务器，进入到 binlog 所在的目录。

cd /var/lib/mysql

查看 binlog 日志文件。

☆、binlog 日志文件是滚动生成的，从图中看到现在已经有 4 个文件了，通常情况下，生产环境的 binlog 会有成百上千个，这时候就需要确认我们需要的数据是在第几个 binlog 中了，因为我们删库是刚刚发生的事情，所以我们需要的数据大概率是在第 4 个文件中，直接去查看第 4 个 binlog 文件，看到的全都是乱码，就像下面这样，这是因为 binlog 文件是二进制的。

☆、需要借助 mysql 官方提供的mysqlbinlog命令去才能正确解析 binlog 文件。用 mysqlbinlog 命令可以打开 binlog 文件，但是一个 binlog 文件的大小可能有几百兆，要从几百兆日志中找到我们需要的日志，还是比较麻烦的。mysqlbinlog 命令提供一些参数选项可以让我们对 binlog 文件进行筛选，最常用的参数就是时间参数。经过和删库的研发人员确定，删库的时间大概是「10:40」，那我们就以这个时间点为参考，找前后 5 分钟的日志。

mysqlbinlog -v --start-datetime='2021-06-10 10:35:00' --stop-datetime='2021-06-10 10:45:00' bin-log.000004 | grep t_user

从图中可以看到，这个时间点的日志确实包含我们删除数据的日志。接下来需要把这些日志整理一下，然后想办法恢复到数据库就可以了。
☆、把需要的日志单独保存到 tmp.log 文件中，方便下载到本地。

mysqlbinlog -v --start-datetime='2021-06-10 10:35:00' --stop-datetime='2021-06-10 10:45:00' bin-log.000004 > tmp.log

☆、把 tmp.log 下载到本地，用文本编辑工具打开看一下，可以看到一堆伪 sql。

在上图的伪 sql 中
@1 表示第一个字段
@2 表示第二个字段
其他的以此类推
日志中包含的 sql 是一些伪 sql，并不能直接在数据库执行，需要把这些伪 sql 处理成可在数据库执行的真正的 sql。使用的文本编辑工具的批量替换功能，就像下面这样：

最终处理好的 sql 就像是这样：

☆、把处理好的 sql 在测试数据库验证一下没问题后直接在生产库执行。sql 执行完以后，被误删除的数据就恢复回来了。

binglog 日志开启

binlog 即 Binary Log，它是二进制文件，用来记录数据库写操作的日志。
数据库的 insert、delete、update、create、alter、drop 等写入操作都会被 binlog 记录。
因此，数据库的主从数据同步通常也是基于 binlog 完成的，本文只对 binlog 做一些简单介绍，后期会单独写一篇文章讲基于 binlog 的主从数据同步。
binlog 日志需要配置开启，可以通过脚本查看 binlog 是否开启。

SHOW VARIABLES LIKE 'LOG_BIN%';

如果log_bin参数显示的是OFF说明 binlog 是关闭状态，需要手动开启。
开启 binlog 需要修改数据库的my.cnf配置文件，my.cnf文件通常在服务器的/etc目录下。
打开/etc/my.cnf文件，配置 binlog 的相关参数，下文配置 binlog 的常用参数。

# 启用binlog并设置binlog日志的存储目录
log_bin = /var/lib/mysql/bin-log
# 设置binlog索引存储目录
log_bin_index = /var/lib/mysql/mysql-bin.index
# 30天之前的日志自动删除
expire_logs_days = 30
# 设置binlog日志模式，共有3种模式：STATMENT、ROW、MIXED
 binlog_format = row

binlog 的三种格式

☆、ROW 格式
ROW 格式下，binlog 记录的是每一条数据被修改的详细细节。
比如，执行 delete 语句，删除的数据有多少条，binlog 中就记录有多少条伪 sql。

delete from t_user where age>18;

那么 row 格式的日志的缺点就很明显，在发生批量操作时，日志文件中会记录大量的伪 sql，占用较多的磁盘空间。
尤其是当进行 alter 操作时，每条数据都发生变化，日志文件中就会有每一条的数据的日志。此时，如果表中的数据量很大的话，日志文件也会非常大。
在 mysql5.6 版本之后，针对 ROW 格式的日志，新增了binlog_row_image参数。
当binlog_row_image设置为minimal时，日志中只会记录发生改变的列，而不是全部的列，这在一定程度上能减少 binlog 日志的大小。
虽然记录每行数据的变化会造成日志文件过大，但这也是它的优点所在。
因为它记录了每条数据修改细节，所以在一些极端情况下也不会出现数据错乱的问题。在做数据恢复或主从同步时能很好的保证数据的真实性和一致性
☆、STATEMENT 格式
STATEMENT 格式下，日志中记录的是真正的 sql 语句，就像是这样。

日志中的 sql 是直接可以拿到数据库运行的。
STATEMENT 格式的日志的优缺点和 ROW 格式的正好相反，它记录的是 sql 语句和执行语句时的上下文环境，而不是每一条数据。所以它的日志文件会比 ROW 格式的日志文件小一些。
由于记录的只是 sql 语句和上下文的环境，STATEMENT 格式的日志在进行主从数据同步时会有一些不可预估的情况出现，导致数据错乱。比如 sleep()、last_insert_id() 等函数会出现问题。
☆、MIXED 格式
MIXED 格式是 STATEMENT 和 ROW 的结合，mysql 会根据具体执行的 sql 语句，来选择合适的日志格式进行记录。
MIXED 格式下，在执行普通的 sql 语句时会选 STATEMENT 来记录日志，在遇到复杂的语句或函数操作时会选择 ROW 来记录日志。

mysqlbinlog 命令

mysql 数据库的 binlog 文件是二进制的，基本看不懂，使用数据库自带的mysqlbinlog命令可以把二进制文件转换成能看懂的十进制文件。
由于数据库的 binlog 文件可能会很大，查看起来会很麻烦，所以mysqlbinlog命令也提供了一些参数可以用来筛选日志。
mysqlbinlog 语法:

mysqlbinlog [options] log-files
options：可选参数
log-files：文件名称

options 的常用值:

-d: 根据数据库的名称筛选日志
-o：跳过前N行日志
-r, --result-fil: 把日志输出到指定文件
--start-datetime: 读取指定时间之后的日志，时间格式：yyyy-MM-dd HH:mm:ss
--stop-datetime: 读取指定时间之前的日志，时间格式：yyyy-MM-dd HH:mm:ss
--start-position: 从指定位置开始读取日志
--stop-position: 读取到指定位置停止
--base64-output：在 row 格式下，显示伪 sql 语句
-v, --verbose：显示伪 sql 语句，-vv 可以为 sql 语句添加备注

常用写法:
查看 fusion 数据库的日志

mysqlbinlog -d=fusion bin-log.000001

查看某个时间段内的日志:

mysqlbinlog  --start-datetime='2021-06-09 19:30:00' --stop-datetime='2021-06-09 19:50:00' bin-log.000001

恢复数据，事件的开始位置是 4300，结束位置是 10345:

mysqlbinlog --start-position 4300 --stop-position 10345 bin-log.000001 | mysql -uroot -p123456 fusion

mybatis

#{}和 ${}的区别

• #{}方式能够很大程度防止sql注入(安全)，${}方式无法防止Sql注入。
• #{}是一个预处理的占位符，传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。
• $ {}将传入的数据直接生成在sql中，一般sql中有动态参数(order by 动态参数)或者动态sql时使用。

防止sql注入

• xml文件中使用#{}接收参数。
• 分页插件拿到参数后过滤一下，防止有恶意脚本。

多级缓存

• 一级缓存的作用域是SqlSession，是默认开启的。首次查询id为1的用户，mybatis会将该用户的对象存储在一级缓存中，再次查 id 为 1 的用户时，mybatis就会去缓存中查找，不会再从数据库中去查询。如果在此中间增删改)，则mybatis会清空一级缓存的数据。关闭：
• 二级缓存是 mapper 级别的缓存。多个SqlSession去操作同一个namespace下的Mapper的sql语句，多个SqlSession可以共用二级缓存,如果两个mapper的namespace相同，（即使是两个mapper，那么这两个mapper中执行sql查询到的数据也将存在相同的二级缓存区域中，但是最后是每个Mapper单独的名称空间），基于PerpetualCache 的 HashMap本地缓存，可自定义存储源，如 Ehcache/Redis等，默认是没有开启二级缓存。二级缓存因为是跨sqlSession的，会存在严重的脏读问题，弊大于利，一般不使用。
• 开启二级缓存
  <1>配置文件中开启二级缓存。
  <2>mapper.xml文件中配置缓存对象。

mysql和oracle的区别

日期与字符串之间的转换

字符串转时间：mysql : str_to_date oracle ： to_date
时间转字符串：mysql : date_format oracle ： to_char

分页

☆、mysql–limit(m,n)
select* from 表名 limit 起始位置,每页条数
起始位置=(当前页数-1)*每页条数
☆、oracle–rownum
select * from 表名 where rownum < 101

可变长度字符串和定长字符串

mysql:varchar–可变长度字符串 char–定长字符串
oracle: varchar2–可变长度字符串 varchar–定长字符串

日期

☆、区别
mysql日期字段分DATE和TIME两种。
oracle日期字段只有DATE，包含年月日时分秒信息。
mysql中取当前系统时间为now()函数,精确到秒。
oracle中取当前数据库的系统时间为SYSDATE, 精确到秒。
☆、日期前一天
mysql : date_sub(curdate(),interval 1 day)
oracle ：sysdate - 1

方言

☆、表的别名
oracle查询时from 表名后面不能加上as 不然会报错的，select t.username from test as t而在mysql下是可以的。
☆、单双引号
mysql：都可以用。
oracle：只能用单引号。

写法收集

固定字符拆分字段

select
 m.*, wm_concat(to_char(c.name))--将查询出来的数据以逗号拼接
  from t_s_category c, t_crm_maintenance m
 where  c.code in
       (select regexp_substr(m.question_type, '[^,]+', 1, level, 'i')---以逗号拆分字符串
          from dual
        connect by level <=
                   length(m.question_type) -
                   length(regexp_replace(m.question_type, ',', '')) + 1) 
 group by m.id,
          m.create_name,
          m.create_by,
          m.create_date,
          m.update_name,
          m.update_by,
          m.update_date,
          m.sys_org_code,
          m.sys_company_code,
          m.bpm_status,
          m.file_path,
          m.image_path,
          m.need_id,
          m.maintenance_number,
          m.type,
          m.tel,
          m.name,
          m.maintenance_date,
          m.address,
          m.question,
          m.customer_type,
          m.complaint_type,
          m.question_type,
          m.remarks,
          m.deadline,
          m.status,
          m.duty

Oracle row_number() 相同值排序

语法格式：row_number() over(partition by 分组列 order by 排序列 desc)

例子1：
  select oec.name, u.realname, t.*
         from (select sa1.*
                 from (select row_number() over(partition by sa.user_id, sa.course_id order by sa.create_date desc) rn,
                              sa.*
                         from (select oes.create_date,
                                      oes.course_id,
                                      oer.user_id,
                                      oes.score,
                                      oes.result
                                 from t_oa_education_score  oes,
                                      t_oa_education_result oer
                                where oer.id = oes.result_id) sa) sa1
                where sa1.rn = 1) t
        inner join t_oa_education_course oec
           on oec.id = t.course_id
        inner join t_s_base_user u
           on u.id = t.user_id;
例子2：
left join (select *
               from (select t.need_id,
                            t.follow_date,
                            t.follow_desc,
                            t.NEXT_PLAN_DATE,
                            t.USER_ID,
                            row_number() over(partition by need_id order by follow_date desc) as rn
                       from t_crm_customer_follow t) t
              where rn = 1) t10