mysql面试必备

最新推荐文章于 2023-04-10 20:22:47 发布

给我一瓶冰阔洛

最新推荐文章于 2023-04-10 20:22:47 发布

阅读量200

点赞数

分类专栏： mysql 文章标签： mysql函数 mysql锁机制 mysql隔离级别 mysql语句优化 mysql面试题

本文链接：https://blog.csdn.net/qq_43035350/article/details/107357350

版权

mysql 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

1、MySQL中常用的字符串函数

MySQL软件支持的字符串函数表如下：

函数	功能
CONCAT(str1,str2,...,strn)	将str1,str2,...,strn连接为一个完整的字符串
INSERT(str,x,y,instr)	将字符串str从第x开始，y个字符串长度的子串替换为字符串instr
LOWER(str)	将字符串str中的所有字母变成小写
UPPER(str)	将字符串str中的所有字母变成大写
LEFT(str,x)	返回字符串最左边的x个字符
RIGHT(str,x)	返回字符串最右边的x个字符
LPAD(str,n,pad)	使用字符串pad对字符串str最左边进行填充，直到长度为n个字符长度
RPAD(str,n,pad)	使用字符串pad对字符串str最右边进行填充，直到长度为n个字符长度
LTRIM(str)	去掉str左边的空格
RTRIM(str)	去掉str右边的空格
REPEAT(str,x)	返回字符串str重复x次的结果
REPLACE(str,a,b)	使用字符串b替换字符串str中所有出现的字符串a
STRCMP(str1,str2)	比较字符串str1和str2
TRIM(str)	去掉字符串行头和行尾的空格
SUBSTRING(str,x,y)	返回字符串str中从x位置起y个字符串长度的字符串

2.数据库隔离级别

READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

脏读 -> 事务A中没有commit；事务B就能读取事务A中添加或其它操作的数据；

一个事务读取另外一个事务还没有提交的数据叫脏读

不可重复读

不可重复读是指在同一个事务内，两次相同的查询返回了不同的结果。

例如：事务T1会读取两次数据，在第一次读取某一条数据后，事务T2修改了该数据并提交了事务，T1此时再次读取该数据，两次读取便得到了不同的结果。

解决办法：把数据库的事务隔离级别调整到REPEATABLE_READ（可重复读）

根据数据库对隔离性(Isolation)的定义：事务中的操作对其它事务而言是隔离的，事务的执行不受其它其它事务的干扰。从上面的三个时刻可以看出，事务1的删除操作影响了事务2的读操作(事务2第二次没能读到第一次读到的martin用户)。所以不可重复读违背了数据库的隔离性。

幻读

幻读也是指当事务不独立执行时，插入或者删除另一个事务当前影响的数据而发生的一种类似幻觉的现象。

例如：系统事务A将数据库中所有数据都删除的时候，但是事务B就在这个时候新插入了一条记录，当事务A删除结束后发现还有一条数据，就好像发生了幻觉一样。这就叫幻读。

解决办法：把数据库的事务隔离级别调整到SERIALIZABLE_READ（序列化执行），或者数据库使用者自己进行加锁来保证

不可重复读出现多是因为修改；幻读重点是新增、删除。mysql中的REPEATABLE_READ模式引入了间隙锁（GAP），解决了幻读的问题。不论是什么方式解决幻读，都会付出一定代价的性能让步。所以说在业务需求和技术方案之间权衡也是技术人员最需要掌握得技能之一。

隔离级别与锁的关系

在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突

在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。

SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

=====================mysql锁机制========================

共享锁: 又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。

排他锁: 又叫做写锁。当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥

索引对表锁的影响

BEGIN;
update test set money = 550 where name="小红";
COMMIT;

如果在筛选列中（name）没有索引，则会锁定整个表，

如果有索引，则只会锁定当前这行数据，

事务中范围查询，会使范围内的数据全部加锁（update test set a =1 where id <10）;id<10 的数据都会加锁；

只有当事务commit后锁才会释放；

悲观锁

BEGIN;
select * from test where id = 5 FOR UPDATE;

在没有commit之前，其它的（select * from test where id = 5 FOR UPDATE;）会阻塞。

由于悲观锁在开始读取时即开始锁定，因此在并发访问较大的情况下性能会变差

乐观锁

begin;

select * from test where id = 1; // 每次修改前先查version 假设version = 0；

update test set total = total-1,version = version +1 where id = 1 and version = 0;

在没有commit之前，其它的事物也会查询到version = 0;

但是当事务1commit后，事务二中的version 与之前查询的version 不一致，则sql失败，乐观锁获取失败，回滚并重试

<?php   
mysqli_query($conn, 'BEGIN');  
$rs = mysqli_query($conn, 'SELECT num, version FROM counter WHERE id = 1');  
mysqli_free_result($rs);  
$row = mysqli_fetch_array($rs);  
$num = $row[0];  
$version = $row[1];//此例：新增version字段标记  
mysqli_query($conn, 'UPDATE counter SET num = ' . $num . ' +1, version = version + 1 WHERE id = 1 AND version = ' . $version);  
$affectRow = mysqli_affected_rows($conn);  
if ($affectRow == 0 || mysqli_errno($conn)) {  
    // 回滚事务重新提交  
    mysqli_query($conn, 'ROLLBACK');  
} else {  
    mysqli_query($conn, 'COMMIT');  
}

也可以用时间代替version

首先我们为counter表增加一列update_time字段,当进行操作时，将先前读取的update_time与当时表中的update_time进行一次对比，如果一致，那么允许操作，然后生成一个新的update_time值替换表中原有的update_time。