本文围绕MySQL展开，涵盖常用CURD操作，如增删改查、建表；介绍修改表结构、字符串转换方法；阐述执行计划、查询语句执行顺序；提及SQL性能测试、索引失效与调优等优化内容；还探讨自增主键优劣、隔离级别，以及DBeaver导出CSV编码格式问题。
前言
1.常用CURD
1.增
1.单行插入
insert into tableName (colunm1,colunm2,...) value(value1,value2,...);
2.多行插入
insert into tableName(colunm1,colunm2,..) values(value1,value2...),(value1,value2...);
3.搭配set插入
insert into tableName set colunm1=value1,colunm2=value2....;
4.搭配select插入
-- 从当前表
insert into tableName(colunm1,colunm2,..) select colunm1,colunm2,..;
-- 从其他表
insert into tableName(colunm1,colunm2,..) select colunm1,colunm2,.. from tableName1;
2.删
delete from table_name where id='123'
3.改
1.将表a的字段更新到表b
-- x表有a,b,c字段，y表有d,e,f字段，当x表的a字段等于y表的d字段时，将x表的b字段更新到y表的e字段，将x表的c字段更新到y表的f字段
UPDATE y
JOIN x ON y.d = x.a
SET y.e = x.b, y.f = x.c;
2.批量更新
UPDATE
engineer_info
SET
updated_by = CASE
WHEN ID = '111' THEN 'systemSynchronize'
WHEN ID = '222' THEN 'systemSynchronize'
WHEN ID = '333' THEN 'systemSynchronize'
WHEN ID = '444' THEN 'systemSynchronize'
END,
VERSION = CASE
WHEN ID = '111' THEN 1
WHEN ID = '222' THEN 1
WHEN ID = '333' THEN 3
WHEN ID = '444' THEN 1
END,
updated_date = CASE
WHEN ID = '111' THEN '2024-12-03 18:25:43'
WHEN ID = '222' THEN '2024-12-03 18:25:43'
WHEN ID = '333' THEN '2024-12-03 18:25:43'
WHEN ID = '444' THEN '2024-12-03 18:25:43'
END,
name = CASE
WHEN ID = '111' THEN '工程师A'
WHEN ID = '222' THEN '第三方'
WHEN ID = '333' THEN 'Regi'
WHEN ID = '444' THEN '秦卫'
END
WHERE
ID IN ( '111', '222', '333', '444' )
4.查
1.查表的字段及其注释
select
a.ordinal_position '序号',
concat(COLUMN_name, ",") '字段名',
-- a.COLUMN_type '字段类型',
-- (case a.is_nullable when 'NO' then '是' else '否' end) '是否非空',
-- (case a.column_key when 'PRI' then '是' else '否' end) '是否主键',
concat("-- ", COLumn_comment) '注释'
from
information_schema.COLUMNS a
where
TABLE_schema = 'your_database_name'
and TABLE_name = 'your_table_name';
2.注释作为别名
-- 注释作为别名
select
a.ordinal_position '序号',
a.COLUMN_name '字段名',
" as ",
case a.COLumn_comment
when '' then concat("'",a.COLUMN_name,"',")
else concat("'",a.COLumn_comment,"',")
end '-注释-'
from
information_schema.COLUMNS a
where
TABLE_schema = 'your_database_name'
and TABLE_name = 'your_table_name';
3.查找某字段值包含中文的数据
SELECT *
FROM student
WHERE
name REGEXP '[\\x{4e00}-\\x{9fa5}]'; -- 包含中文
4.窗口函数查询示例
WITH RankedW AS (
SELECT
w.*,
ROW_NUMBER() OVER(PARTITION BY serv_no ORDER BY CREATED_DATE asc) AS rn
FROM
work w
where w.is_active = 'Y'
-- and w.serv_no in ("FW00002187","FW00002189","FW00002190","FW00002190","FW00002191")
)
select
o.WO_NO ,
o.CREATED_DATE ,
s.SECHEDULE_DATE,
s.SERV_NO
FROM
work o
join service s on o.SERV_ID = s.id
JOIN RankedW r on o.id = r.id AND r.rn = 1
where 1=1
and s.SECHEDULE_DATE > '2024-09-25 00:00:00'
注意 ：
1.开窗row_number() over(partition by sth order by sth)，先分组后排序。
2.排序方式：假设有4行需要排列，rank 是跳次排序 1,1,1,4, dense_rank是不跳次排序1,1,1,2, row_number是顺序1,2,3,4
5.建表
5.1 备份表数据
CREATE TABLE user_20241010 AS SELECT * FROM user;
这条语句会创建一个新的表user2，表结构与user一样，并且将表user中的所有数据复制到user2中
如果只想复制结构而不复制数据，可以使用WHERE子句来确保不选择任何行：
CREATE TABLE user_20241010 AS SELECT * FROM user WHERE 1=0;
5.2 建表自增主键
CREATE TABLE your_table_name (
id INT NOT NULL AUTO_INCREMENT,
-- 其他字段...
PRIMARY KEY (id)
) AUTO_INCREMENT = 1;
在MySQL中，自增值默认从1开始，如果你需要重置自增值，可以使用ALTER TABLE语句：
ALTER TABLE your_table_name AUTO_INCREMENT = 1;
这将重置自增值为1。不过，请注意，重置自增值可能会影响已有数据的主键值。
2.修改表结构
1.修改字段
1.增删字段
-- 增加字段 ---------------------------------------------------------------------------------------
-- 增加字段
ALTER TABLE your_table ADD reward varchar(10) default null comment '奖惩单位' AFTER pro_res;
-- 回滚
ALTER TABLE table_name DROP COLUMN column_name;
-- 增加字段 ---------------------------------------------------------------------------------------
2.修改字段长度
ALTER TABLE table_name MODIFY COLUMN column_name datatype(new_length);
3.修改字段类型
ALTER TABLE your_table_name MODIFY COLUMN id BIGINT;
2.修改索引
1.增删索引
-- 单个字段非唯一索引
CREATE INDEX idx_lastname ON users(last_name);
-- 多字段联合索引
CREATE INDEX idx_name ON users(last_name, first_name);
-- 删除索引
ALTER TABLE `database_name`.table_name DROP INDEX idx_name;
-- 删除索引
select database();
2.基于索引优化MIN/MAX操作
3.查表信息
1.查表的字段名和注释
SELECT
column_name as columnName,
column_comment as columnComment
FROM
information_schema.COLUMNS
WHERE
TABLE_NAME = 'user'
3.字符串转换
1.varchar转Date
SELECT STR_TO_DATE('2021-05-20 12:30:45','%Y-%m-%d %H:%i:%s') AS datetime;
2.Date的大小比较
SELECT * FROM your_table WHERE date_column1 > '2023-01-01';
3.Date类型的加减
1.date_add()
DATE_ADD() 函数是用于在日期时间值上添加指定的时间间隔的函数。它可以根据你的需求，在日期或日期时间字段中增加年、月、日、小时、分钟或秒钟等时间单位。
参考博客：SQL_时间函数-DATE_ADD()函数
DATE_ADD() 函数接受三个参数：日期时间值、时间间隔和时间单位。
日期时间值可以是日期类型、日期时间类型或时间戳类型。
时间间隔是一个整数，表示要添加的数量。
时间单位可以是年份 (YEAR)、月份 (MONTH)、日期 (DAY)、小时 (HOUR)、分钟 (MINUTE)、秒钟 (SECOND) 等等。
返回计算后的日期时间值。
-- 筛选24小时前到现在
where 1=1
and o.ARRIVAL_TIME > date_add(sysdate(),interval -24 hour)
-- 7天后的时间，2023-09-14
SELECT DATE_ADD('2023-09-07', INTERVAL 7 DAY);
2.datediff()日期的加减
注意：
datediff()函数，只进行日期层级的加减计算，时分秒部分不参与计算
DATEDIFF( ‘2024-01-09 12:00:30’, ‘2024-01-08 11:04:00’) 与
DATEDIFF( ‘2024-01-09 12:00:30’, ‘2024-01-08 14:00:10’) 的计算结果是一样的
DATEDIFF(end_date, start_date)
end_date：表示要计算的时间段的结束日期。
start_date：表示要计算的时间段的开始日期。
3.unix_timestamp()
-- 小于24小时
(unix_timestamp(now())-unix_timestamp(STR_TO_DATE(s.crtd_tm, '%Y%m%d%H%i%s')))/(3600*24) < 1
-- 大于等于24小时，小于48小时
(unix_timestamp(now())-unix_timestamp(STR_TO_DATE(s.crtd_tm, '%Y%m%d%H%i%s')))/(3600*24) >=1 and (unix_timestamp(now())-unix_timestamp(STR_TO_DATE(s.crtd_tm, '%Y%m%d%H%i%s')))/(3600*24) < 2
4.timestampdif()
注意：
第一个参数为HOUR时，计算粒度为小时（分秒不参与计算）
第一个参数为minute时，计算粒度为分钟（秒不参与计算），
TIMESTAMPDIFF(minute, ‘2024-01-09 12:00:30’, ‘2024-01-09 12:00:40’) 与 TIMESTAMPDIFF(minute, ‘2024-01-09 12:00:30’, ‘2024-01-09 12:00:20’) 的计算结果是一样的
SELECT TIMESTAMPDIFF(HOUR, '2024-01-04 00:00:05', '2024-01-06 00:01:05') AS diff
4.mysql执行计划
1.MySQL执行查询过程
2.查询语句执行顺序
mysql执行顺序如下：
-
from 阶段
-
where 阶段
-
group by 阶段
-
having 阶段
-
select 阶段
-
order by 阶段
-
limit 阶段
按照以上书写顺序，完整的执行顺序应该是这样：
-
from子句识别查询表的数据；
-
where子句基于指定的条件对记录进行筛选；
-
group by 子句将数据划分成多个组别，如按性别男、女分组； 有聚合函数时，要使用聚集函数进行数据计算；
-
Having子句筛选满足第二条件的数据；
-
执行select语句进行字段筛选。筛选重复数据；
-
对数据进行排序；
-
执行limit进行结果限定
3.explain 执行计划
1.Explain 执行计划中各字段的信息
参考博客1：面试官：不会看 Explain执行计划，简历敢写 SQL 优化？
参考博客2：Mysql优化之Explain详解、索引最佳实践
5.mysql调优
1.SQL性能测试
1.sql查询缓存
- 清除整个查询缓存
MySQL 不提供直接的方式来清除特定查询语句的缓存，因为查询缓存是基于查询结果集的。如果你想要清除查询缓存，你可以通过清除整个查询缓存来间接实现。清除查询缓存的方法是，在当前MySQL会话中执行以下命令：RESET QUERY CACHE;
这个命令会清除当前MySQL实例的查询缓存。 - 避免单词查询缓存
如果你想要避免将来执行某个特定查询时使用缓存，你可以考虑不对那个查询使用查询缓存，或者在查询中使用一些特定的提示来避免缓存，例如：SELECT SQL_NO_CACHE * FROM your_table;在这个例子中，SQL_NO_CACHE 提示告诉MySQL不要缓存这个查询的结果。
请注意，查询缓存在MySQL中并不总是默认启用的，它可能需要在my.cnf或my.ini配置文件中启用。如果没有启用查询缓存，上述命令不会产生任何效果。
2.索引失效
1.聚合函数
1.在使用sum()、avg()等聚合函数时导致索引失效
2.max()、min()等函数与where同时使用时，例如在下列语句中，即时id市住建，mysql也会在扫描在过滤sex = '2’后得到的结果集里的所有数据
select max(id) as max_id from student where sex = '2' -- 2:女性
2.关键字in + 子查询
在 where column_name in (select xx from ...)中，例如下列语句中，即时子查询中的结果集很小且id是主键，也会导致外层查询的索引失效
3.索引调优
1.索引覆盖
2
4.sql调优案例
1.找分组组内最大的记录
基本背景：表内共有约7千个员工，每个员工负责若干个订单，每个订单有几千~几万条记录，每天记录会根据用upload_date字段存该条记录上传的时间。
查询需求：查出每个员工最新的一条记录。
数据表基本信息：
- 表m_engineer_temp的数据量为6千2百万 x 24
- 主键为id，bigint类型，自增唯一主键
- srv_bp字段（工号）上建有非唯一索引。表中共有7千个不同的工号。
- upload_date, srv_bp上建有联合索引
版本1：只查单个工号
执行时间：srv_bp无索引2m22s 有索引4s
select
SQL_NO_CACHE
id,srv_bp,updated_date,order_id
from
m_engineer_temp -- srv_bp无索引2m22s 有索引4s
where 1 = 1
and srv_bp = "123456"
and updated_date = (select max(updated_date) maxDate from m_engineer_temp where srv_bp = "123456" )
limit 1
优化后–版本2 ：只查单个工号
执行时间：0.3~1s
select
SQL_NO_CACHE
id,srv_bp,updated_date ,order_id
from
m_engineer_temp
where 1 = 1
and srv_bp = "123456"
and upload_date = (select max(upload_date) maxDate from m_engineer_temp where srv_bp = "123456" )
limit 1
优化后–版本3 ：只查单个工号
执行时间：0.3~1s
select
SQL_NO_CACHE
id,srv_bp,longitude,latitude,updated_date
from
m_engineer_temp -- srv_bp无索引1m22s 有索引快的0.5s，慢的2s
where 1 = 1
and id = (select id from m_engineer_temp where srv_bp = "123465" order by updated_date desc limit 1)
优化后–版本4 ：可查多个工号
下列SQL执行时间约30ms，查询性能高的详细原因尚未完全理解。
select SQL_NO_CACHE r1.id as "id", r1.updated_date as "updatedDate", r1.upload_date as "uploadDate", r1.upload_status as "uploadStatus", r1.srv_bp as srvBp, r1.order_id as orderId
from m_engineer_temp as r1
inner join (
select
max(id) max_id
from
m_engineer_temp
where
srv_bp in ( '123456' , '456789')
group by srv_bp
) as r2
on r1.id = r2.max_id
order by r1.upload_date;
explain的执行计划如下：
5.exsit与not exist
exist 参考博客：EXISTS的用法
exist 参考博客：EXISTS的用法
not exist 参考博客：https://blog.youkuaiyun.com/qq_38816305/article/details/143506727
6.有趣的问题
1.自增主键的优劣
1.自增主键类型和取值范围
| 主键类型 | 范围 | 范围 | 位数 |
|---|---|---|---|
| int | -2^31 ~ 2^31 - 1 | (-2,147,483,648) ~ (2,147,483,647) | 10位数 |
| bigint | -2^63 ~ 2^63-1 | (-9223372036854775808) ~ (9223372036854775807) | 19位数 |
2.自增主键ID问题
参考博客：Mysql 主键设计
1.可靠性不高
存在主键ID回溯，这个bug在mysql 8.0才修复。 主键ID回溯就是: 创建一个表，主键ID且整形且AUTO_INCREMENT，然后插入三条数据，然后show create table 查看，此时的AUTO_INCREMENT为4，删除ID=3的数据，此时AUTO_INCREMENT仍为4，重启mysql-server，此时AUTO_INCREMENT就为3了。
2.安全性不好
对外接口很容易暴露业务信息，如/user/1接口，那么别人就知道用户ID是多少，新注册一个用户就知道用户数量是多少，轻易的使用爬虫工具进行数据的获取。
3.性能差
自增ID由服务器生成。在高并发插入数据时，存在AUTO_INCREMENT锁竞争问题（详见InnodBD内幕p262）。
AUTO_INCREMENT锁竞争问题初步理解：
增长在数据库中是非常常见的一种属性，也是很多DBA或开发人员首选的主键方式。在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器.插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称为AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。
例如：使用了insert into a select … from b … 因为无法预知会插入多少行,所以会对表a直接生成insert操作的表锁，直到insert执行完毕，对b表是逐条加读锁，直到select执行完毕。如果该SQL脚本的执行过程较长，会产生一个长时间的表锁，导致性能大幅下降。
参考博客1
参考博客2
参考博客3
4.交互多
业务还需要一次last_insert_id才能知道刚才插入数据的ID，这需要多一次的网络交互。这在高并发性能吃紧的场景下是及其浪费的。
5.局部唯一性
只能在当前数据库中唯一，而非全局唯一，对于核心业务表，这在分布式环境下是绝对不允许的。
3.优势
1.很小的数据存储空间
2.性能最好
3.容易记忆
2.mysql的隔离级别
1.查看数据库的隔离级别
SELECT @@transaction_isolation;
7.DBeaver使用
1.DBeaver导出cvs的编码格式
GB2312格式导出后中文不会乱码，其他格式会导致中文乱码
扫一扫
763
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
