MYSQL中的14个神仙功能，惊艳到我了！！！

前语

通过一年的尽力，总算上了今年优异作者榜单了，求票求票，每人能够投8票。顶我一波呀，十分感谢。 2022年度人气创作者打榜中，快来帮我打榜吧～ rank./rank/2022/w…

我最近几年用MYSQL数据库挺多的，发现了一些十分有用的小玩意，今日拿出来分享到大家，期望对你会有所协助。

1.group_concat

在咱们平常的工作中，运用group by进行分组的场景，是十分多的。

比方想统计出用户表中，称号不同的用户的详细称号有哪些？

详细sql如下：

select name from `user`
group by name;

但假如想把name相同的code拼接在一同，放到别的一列中该怎么办呢？

答：运用group_concat函数。

例如：

select name,group_concat(code) from `user`
group by name;

履行成果：

运用group_concat函数，能够轻松的把分组后，name相同的数据拼接到一同，组成一个字符串，用逗号分隔。

2.char_length

有时分咱们需求获取字符的长度，然后依据字符的长度进行排序。

MYSQL给咱们供给了一些有用的函数，比方：char_length。

通过该函数就能获取字符长度。

获取字符长度而且排序的sql如下：

select * from brand where name like '%苏三%'
order by char_length(name) asc limit 5;

履行作用如图所示：

name字段运用关键字含糊查询之后，再运用char_length函数获取name字段的字符长度，然后按长度升序。

3.locate

有时分咱们在查找某个关键字，比方：苏三，需求明确知道它在某个字符串中的位置时，该怎么办呢？

答：运用locate函数。

运用locate函数改造之后sql如下：

select * from brand where name like '%苏三%'
order by char_length(name) asc, locate('苏三',name) asc limit 5,5;

履行成果：

先按长度排序，小的排在前面。假如长度相同，则按关键字从左到右进行排序，越靠左的越排在前面。

除此之外，咱们还能够运用：instr和position函数，它们的功用跟locate函数类似，在这里我就不一一介绍了，感兴趣的小伙伴能够找我私聊。

4.replace

咱们常常会有替换字符串中部分内容的需求，比方：将字符串中的字符A替换成B。

这种状况就能运用replace函数。

例如：

update brand set name=REPLACE(name,'A','B')
where id=1;

这样就能轻松完成字符替换功用。

也能用该函数去掉前后空格：

update brand set name=REPLACE(name,' ','') where name like ' %';
update brand set name=REPLACE(name,' ','') where name like '% ';

运用该函数还能替换json格局的数据内容，真的十分有用。

5.now

时刻是个好东西，用它能够快速缩小数据范围，咱们常常有获取当时时刻的需求。

在MYSQL中获取当时时刻，能够运用now()函数，例如：

select now() from brand limit 1;

回来成果为下面这样的：

它会包含年月日时分秒。

假如你还想回来毫秒，能够运用now(3)，例如：

select now(3) from brand limit 1;

回来成果为下面这样的：

运用起来十分便利好记。

6.insert into … select

在工作中许多时分需求刺进数据。

传统的刺进数据的sql是这样的：

INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`)
VALUES (5, '108', '苏三', '2022-09-02 19:42:21');

它主要是用于刺进少数而且现已确认的数据。但假如有大批量的数据需求刺进，特别是是需求刺进的数据来源于，别的一张表或许多张表的成果集中。

这种状况下，运用传统的刺进数据的方法，就有点束手无策了。

这时分就能运用MYSQL供给的：insert into ... select语法。

例如：

INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`)
select null,code,name,now(3) from `order` where code in ('004','005');

这样就能将order表中的部分数据，十分轻松刺进到brand表中。

7.insert into … ignore

不知道你有没有遇到过这样的场景：在刺进1000个品牌之前，需求先依据name，判别一下是否存在。假如存在，则不刺进数据。假如不存在，才需求刺进数据。

假如直接这样刺进数据：

INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`)
VALUES (123, '108', '苏三', now(3));

肯定不可，由于brand表的name字段创立了仅有索引，同时该表中现已有一条name等于苏三的数据了。

履行之后直接报错了：

这就需求在刺进之前加一下判别。

当然许多人通过在sql句子后面拼接not exists句子，也能达到避免呈现重复数据的意图，比方：

INSERT INTO `brand`(`id`, `code`, `name`, `edit_date`)
select null,'108', '苏三',now(3) 
from dual where  not exists (select * from `brand` where name='苏三');

这条sql确实能够满足要求，可是总觉得有些费事。那么，有没有更简单的做法呢？

答：能够运用insert into ... ignore语法。

例如：

INSERT ignore INTO `brand`(`id`, `code`, `name`, `edit_date`)
VALUES (123, '108', '苏三', now(3));

这样改造之后，假如brand表中没有name为苏三的数据，则能够直接刺进成功。

但假如brand表中现已存在name为苏三的数据了，则该sql句子也能正常履行，并不会报错。由于它会忽略异常，回来的履行成果影响行数为0，它不会重复刺进数据。

8.select … for update

MYSQL数据库自带了失望锁，它是一种排它锁，依据锁的粒度从大到小分为：表锁、空隙锁和行锁。

在咱们的实际业务场景中，有些状况并发量不太高，为了保证数据的正确性，运用失望锁也能够。

比方：用户扣减积分，用户的操作并不集中。但也要考虑系统主动赠送积分的并发状况，所以有必要加失望锁约束一下，避免呈现积分加错的状况发生。

这时分就能够运用MYSQL中的select ... for update语法了。

例如：

begin;
select * from `user` where id=1 
for update;
//业务逻辑处理
update `user` set score=score-1 where id=1;
commit;

这样在一个业务中运用for update锁住一行记载，其他业务就不能在该业务提交之前，去更新那一行的数据。

需求留意的是for update前的id条件，有必要是表的主键或许仅有索引，不然行锁或许会失效，有或许变成表锁。

9.on duplicate key update

通常状况下，咱们在刺进数据之前，一般会先查询一下，该数据是否存在。假如不存在，则刺进数据。假如已存在，则不刺进数据，而直接回来成果。

在没啥并发量的场景中，这种做法是没有什么问题的。但假如刺进数据的恳求，有必定的并发量，这种做法就或许会发生重复的数据。

当然避免重复数据的做法许多，比方：加仅有索引、加分布式锁等。

但这些方案，都无法做到让第2次恳求也更新数据，它们一般会判别现已存在就直接回来了。

这种状况能够运用on duplicate key update语法。

该语法会在刺进数据之前判别，假如主键或仅有索引不存在，则刺进数据。假如主键或仅有索引存在，则履行更新操作。

详细需求更新的字段能够指定，例如：

INSERT  INTO `brand`(`id`, `code`, `name`, `edit_date`)
VALUES (123, '108', '苏三', now(3))
on duplicate key update name='苏三',edit_date=now(3);

这样一条句子就能轻松搞定需求，既不会发生重复数据，也能更新最新的数据。

但需求留意的是，在高并发的场景下运用on duplicate key update语法，或许会存在死锁的问题，所以要依据实际状况酌情运用。

10.show create table

有时分，咱们想快速检查某张表的字段状况，通常会运用desc指令，比方：

desc `order`;

成果如图所示：

确实能够看到order表中的字段称号、字段类型、字段长度、是否答应为空，是否主键、默认值等信息。

但看不到该表的索引信息，假如想看创立了哪些索引，该怎么办呢？

答：运用show index指令。

比方：

show index from `order`;

也能查出该表一切的索引：

但检查字段和索引数据呈现方法，总觉得有点怪怪的，有没有一种更直观的方法？

答：这就需求运用show create table指令了。

例如：

show create table `order`;

履行成果如图所示：

其间Table表示表名，Create Table便是咱们需求看的建表信息，将数据打开：

咱们能够看到十分完整的建表句子，表名、字段名、字段类型、字段长度、字符集、主键、索引、履行引擎等都能看到。

十分直接明晰。

11.create table … select

有时分，咱们需求快速备份表。

通常状况下，能够分两步走：

1. 创立一张暂时表
2. 将数据刺进暂时表

创立暂时表能够运用指令：

create table order_2022121819 like `order`;

创立成功之后，就会生成一张称号叫：order_2022121819，表结构跟order一模一样的新表，仅仅该表的数据为空而已。

接下来运用指令：

insert into order_2022121819 select * from `order`;

履行之后就会将order表的数据刺进到order_2022121819表中，也便是完成数据备份的功用。

但有没有指令，一个指令就能完成上面这两步的功用呢？

答：用create table ... select指令。

例如：

create table order_2022121820
select * from `order`;

履行完之后，就会将order_2022121820表创立好，而且将order表中的数据主动刺进到新创立的order_2022121820中。

一个指令就能轻松搞定表备份。

12.explain

许多时分，咱们优化一条sql句子的性能，需求检查索引履行状况。

答：能够运用explain指令，检查mysql的履行计划，它会显示索引的运用状况。

例如：

explain select * from `order` where code='002';

成果：

通过这几列能够判别索引运用状况，履行计划包含列的含义如下图所示：

假如你想进一步了解explain的详细用法，能够看看我的另一篇文章《explain | 索引优化的这把绝世好剑，你真的会用吗？》

说实话，sql句子没有走索引，排除没有建索引之外，最大的或许性是索引失效了。

下面说说索引失效的常见原因：

假如不是上面的这些原因，则需求再进一步排查一下其他原因。

13.show processlist

有些时分咱们线上sql或许数据库呈现了问题。比方呈现了数据库衔接过多问题，或许发现有一条sql句子的履行时刻特别长。

这时分该怎么办呢？

答：咱们能够运用show processlist指令检查当时线程履行状况。

如图所示：

从履行成果中，咱们能够检查当时的衔接状况，协助识别出有问题的查询句子。

• id 线程id
• User 履行sql的账号
• Host 履行sql的数据库的ip和端号
• db 数据库称号
• Command 履行指令，包含：Daemon、Query、Sleep等。
• Time 履行sql所消耗的时刻
• State 履行状况
• info 履行信息，里边或许包含sql信息。

假如发现了异常的sql句子，能够直接kill掉，保证数据库不会呈现严重的问题。

14.mysqldump

有时分咱们需求导出MYSQL表中的数据。

这种状况就能够运用mysqldump工具，该工具会将数据查出来，转换成insert句子，写入到某个文件中，相当于数据备份。

咱们获取到该文件，然后履行相应的insert句子，就能创立相关的表，而且写入数据了，这就相当于数据还原。

mysqldump指令的语法为： mysqldump -h主机名 -P端口 -u用户名 -p暗码 参数1,参数2.... > 文件称号.sql

备份远程数据库中的数据库：

mysqldump -h 192.22.25.226 -u root -p123456 dbname > backup.sql

通过一年的尽力，总算上了今年优异作者榜单了，求票求票，每人能够投8票。顶我一波呀，十分感谢。

最终说一句(求重视，别白嫖我)

假如这篇文章对您有所协助，或许有所启发的话，帮助扫描下发二维码重视一下，您的支撑是我坚持写作最大的动力。

求一键三连：点赞、转发、在看。

重视大众号：【苏三说技术】，在大众号中回复：面试、代码神器、开发手册、时刻管理有超赞的粉丝福利，别的回复：加群，能够跟许多BAT大厂的长辈沟通和学习。