技术分享小站

前言

本文内容来自于我在工作和学习中遇到的问题以及整理和收集一些在开发中会经常用到或者遇到的MYSQL技巧或者解决思路。

另外本文会长期更新.....

如果文中有任何不对的地方，或者有好的建议和提醒。也希望大家多多指导！

本文会参考和引用很多大佬的文章并且放到文章末尾进行标注。

技术类

VARCHAR的长度

在Mysql中Varchar是一个可变长度。

Varchar的长度指定是能存储的字符的最大数量。然而实际能存储的数量要受到字符编码的限制。

对于 VARCHAR 字段，MySQL 会根据使用的字符集来确定每个字符需要多少字节。例如：

如果使用 latin1 字符集，每个字符占用 1 个字节，那么 VARCHAR(30) 可以存储 30 个字符。
如果使用 utf8 字符集，每个字符可能占用 1 到 3 个字节（对于大多数常用字符是 3 个字节），那么 VARCHAR(30) 可以存储的汉字数量可能少于 30 个。
如果使用 utf8mb4 字符集，每个字符可能占用 1 到 4 个字节（对于大多数常用字符和所有 emoji 表情是 4 个字节），那么 VARCHAR(30) 可以存储的汉字数量可能更少。

如果我们要存储一个长度为30的字符串，如果都是汉字的话，需要根据字符集的最大字节数来计算 VARCHAR 的长度。

例如，对于 utf8mb4 字符集，可能需要定义VARCHAR(120)（30个汉字 * 4个字节/汉字）来确保有足够的空间存储 30 个汉字。

LENGTH函数

在 MySQL 中，LENGTH() 函数返回的是字符串的字节长度，而不是字符的数量。这意味着如果字符串包含多字节字符（如 UTF-8 编码的汉字），LENGTH() 函数返回的值将大于实际的字符数。

如果想要获取字符串中字符的数量，不管它们是单字节还是多字节字符，那么应该使用 CHAR_LENGTH() 或 CHARACTER_LENGTH() 函数。这些函数返回的是字符串中的字符数。

VARCHAR的排序

如果使用mysql，并且某一个字段的类型是 varchar ，但是存入的值是数字。当你使用排序的时候，就会发现排序结果不对。

varchar 的排序是按照字符串的排序规则来的。不是按照数字的排序规则，所以如果遇到这种情况可以把排序列转换成数值类型排序。

仔细观察的话会发现它的排序是按第一个字符1-9或者9-1，然后依次按后面的字符进行排序。

TRUNCATE的规则

首先 truncate 是一个DDL命令，不是DML命令，所以在执行期间会自动提交事务，无法回滚。并且执行 truncate 命令会对当前表添加表锁。

当执行TRUNCATE TABLE命令时，MySQL会立即释放表所占用的存储空间，并且不会激活与表相关的触发器。TRUNCATE TABLE执行完成后表的自增主键值会被重置为初始值，而不是保留之前的值。

INNODB加锁规则

所有加锁，都是锁的索引记录
唯一索引（主键）只有锁住多条记录或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加行锁，不会产生间隙锁
普通索引不管是锁住单条，还是多条记录，都会产生间隙锁
间隙锁有个特性就是 不同事务，可以获取 同一个间隙/不同间隙上 的间隙锁。

间隙锁

间隙锁（Gap Lock）是一种锁定机制，用于在事务中防止其他事务在一个范围内插入新的数据。简单来说就是对一个范围内的数据进行加锁。比如：

-- 1.必须在 RR 的隔离级别下生效
-- 2.必须申请锁
select * from test where id > 1 and id < 7 for update;

但是需要注意的是，在进行范围查询的时候

必须申请共享锁或者排他锁才会有间隙锁的发生
间隙锁是在事务隔离级别为可重复读（REPEATABLE READ）时起作用
不同事务可以拥有统一个范围的间隙锁！！！！

当我们已经意识到间隙锁是对一个范围的数据进行加锁的时候，就会发现一个问题：

例子

在一个事务对一个范围内的数据加了间隙锁后，其它事务是没法在这个范围进行 insert 操作的，会导致死锁的发生。比如：

-- 如果是在并发的情况下，这里的操作就会导致死锁。（其实就是我们在服务器会遇到的情况，毕竟用户请求不会等待别人请求完成）

-- tx1 进行范围查询并加锁
-- t1想插入一条id为3的数据，这时由于tx2对这部分数据加了间隙锁，只能等待锁释放
select * from test where id > 1 and id < 7 for update;
insert into test (id,name) values(3,'test1');

-- tx2 进行范围查询并加锁
-- tx2 tx2想插入一条id为4的数据，这时由于tx1对这部分数据加了间隙锁，所以tx2是没法执行的，只能是等待innodb最后判定死锁并撤销tx2.
select * from test where id > 1 and id < 7 for update;
insert into test (id,name) values(4,'test2');

-- 可以看到上面的列子，tx1 和 tx2 都申请了同一个范围内的间隙锁，因为不同事务可以拥有同一个范围的间隙锁
-- 但是在tx1插入数据时，则需要等待tx2释放锁了，tx2则需要等待tx1释放锁。所以会造成死锁

间隙锁发生的通常情况

间隙锁除了我们在上面的进行范围查询并加锁的情况以外，还有一种情况是我们经常会遇见的。

那就是索引没有命中！要知道在innodb中加锁操作实际上是针对索引加的锁，而不是锁住数据行。

那么就会有一种现象，比如：

我们想先删除一个条数据后再添加一条数据，当我们通过一个参数去删除一条记录的时，如果参数在数据库中存在，那么这个时候产生的是普通行锁，锁住这个记录，然后删除，然后释放锁。

如果这条记录不存在，就有问题了，数据库会扫描索引，发现这个记录不存在，这个时候的delete语句获取到的就是一个间隙锁，然后数据库会向左扫描扫到第一个比给定参数小的值，向右扫描扫描到第一个比给定参数大的值，然后以此为界，构建一个区间，锁住整个区间内的数据，一个特别容易出现死锁的间隙锁诞生了。间隙锁加锁是非互斥的，AB都删除同一个不同在的数据，产生了两个一样的互斥锁，然后都去插入数据，这时候他们都只能拿到自己的间隙锁，拿不到另外一个间隙锁，就进入了互相等待的死锁环节。

解决死锁

第一种：

查询是否锁表 show OPEN TABLES where In_use > 0;
查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）
show processlist
杀死进程id（就是上面命令的id列）
kill id

第二种：

查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
查看当前锁定的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
杀死进程
kill 进程ID

引用

简书：间隙锁 gap lock

(强烈推荐一看！！！！！) 稀土掘金：记一次线上间隙锁引发的死锁问题

实用类

获取七天连续日期列表

# 核心sql解析：
# select 查询的字段内容中，其实就是查询处当前时间，然后使用 INTERVAL 指定当前时间减去 一个整数(INTERVAL sntable.sn DAY)这里 DAY表示减去是天还是YEAR年，MONTH月。然后 FROM 中 UNION了几个数字是自己希望减去的整数

SELECT
	DATE_FORMAT( SUBDATE( NOW( ), INTERVAL sntable.sn DAY ), '%Y-%m-%d' ) AS statDate 
FROM
	( SELECT 1 AS sn UNION SELECT 2 AS sn UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 ) sntable 
	
	
####搭配使用示例
SELECT
	* 
FROM
	(SELECT
		DATE_FORMAT( SUBDATE( NOW( ), INTERVAL sntable.sn DAY ), '%Y-%m-%d' ) AS statDate 
	FROM
		( SELECT 1 AS sn UNION SELECT 2 AS sn UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 ) sntable 
	) t1
	LEFT JOIN risk_quantification_result rqr ON t1.statDate = rqr.create_time

备份表、执行表、回退表、删除表流程

-- 备份
create table batch_logs_20230204 like batch_logs;  
insert into batch_logs_20230204 select * from batch_logs;

create table model_factor_formula_20230204 like model_factor_formula;  
insert into model_factor_formula_20230204 select * from model_factor_formula;

-- 执行
delete from model_factor_formula where fct_id in (370,371,372,373,374,375);
delete from batch_logs where is_inside = 1 and create_time != '2017-12-31 00:00:00' and type = 0;

-- 回退
insert into batch_logs select * from batch_logs_20230204;
insert into model_factor_formula select * from model_factor_formula_20230204;

-- 清空备份表
drop table batch_logs_20230204;
drop table model_factor_formula_20230204;

字符集、排序规则

-- 查看当前数据库的字符集
show variables where Variable_name like 'collation%';

-- 修改表的字符集和排序规则
alter table sjs_fin_data_abnormal_events default character set utf8mb4 collate=utf8mb4_general_ci;

-- 修改表中所有列的字符集和排序规则
alter table sjs_fin_data_abnormal_events CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

创建用户与授权

create user 'zxzx'@'%' identified by 'zxzx123';
grant all on *.* TO 'zxzx'@'%';

锁表相关

# 1.该sql可以查询当前正在被使用的表的信息。包含了当前正在被其他会话锁定或使用的表的列表。
show OPEN TABLES where In_use > 0;
# 2.查看当前的死锁信息。
select * from information_schema.innodb_lock_waits;
# 3.查询当前事务相关的锁表情况
SELECT 
    r.trx_id AS '事务ID',
    r.trx_mysql_thread_id AS '线程ID',
    r.trx_query AS '查询语句',
    l.lock_table AS '锁定表',
    l.lock_index AS '锁定索引',
    l.lock_mode AS '锁定模式',
    r.trx_started AS '事务开始时间',
    r.trx_wait_started AS '等待开始时间',
    r.trx_mysql_thread_id AS '等待线程ID',
    r.trx_query AS '等待查询语句'
FROM
    information_schema.innodb_lock_waits w 
	INNER JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
    INNER JOIN information_schema.innodb_locks l ON w.requested_lock_id = l.lock_id;
# 4.查看当前数据库服务器上所有活动的客户端连接和它们的相关信息。包含了每个活动连接的详细信息，包括连接ID、用户、主机、数据库、当前执行的语句、连接状态等
select * from information_schema.processlist;
# 5.结束掉某个连接的进程
kill <连接ID>;

竖表转横表

# 1.先指定Mysql的group_concat的最大长度，因为后面要使用group_concat来拼接 case when

SET SESSION group_concat_max_len = 10240000;

# 2.使用group_concat拼接 case when
select
	group_concat(
		concat('MAX(case when code= "', b.code, '" then level end) as "', b.code, '"')
	)
from
	(
	select 
		code, 
		name
	from
		字典表) b

# 3.上面的sql执行完成后会返回一串
# MAX(case when factor_code = "xx" then level end) as "xx",MAX(case when factor_code = "xx1" then level end) as "xx1",MAX(case when factor_code = "zz2" then level end) as "zz2"



# 4.执行查询
select 
	b.id,
	b.name,
	b.year,
	MAX(case when factor_code = "xx" then level end) as "xx",MAX(case when factor_code = "xx1" then level end) as "xx1",MAX(case when factor_code = "zz2" then level end) as "zz2"
from (
	select
		fe.id,
		fe.name,
		fal.year,
		fal.factor_code,
		fal.`level`
	from
		表A fal
		left join 表B fe on 表A.entity_id = 表B.entity_id 
	where 
		表A.`year` = 2022
		and 表B.entity_id is not null
) b
group by b.id

如果觉得文章对你有用，请随意赞赏

常规随笔

Mysql必知必会

http://zhsssme.com.cn/archives/1703730031550

作者

月亮是我啃缺的

发布于

2023-12-28

更新于

2024-05-20

许可协议

CC BY 4.0