MySQL中这10个小玩意，让人眼前一亮

清疚 2024-03-30 15:04 73阅读 0赞

我最近几年用`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;

执行结果：

![d4dba5b8397e5ba4d4decd39ca426334.png][]

使用`group_concat`函数，可以轻松的把分组后，name相同的数据拼接到一起，组成一个字符串，用`逗号`分隔。

### 2.char\_length ###

有时候我们需要获取字符的`长度`，然后根据字符的长度进行`排序`。

MYSQL给我们提供了一些有用的函数，比如：`char_length`。

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

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

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

执行效果如图所示：

![baeb5fba660e3f3cde7792d678fc0c25.png][]

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;

执行结果：

![ca7f193ca67d1615ce3f9c868aaaf828.png][]

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

除此之外，我们还可以使用：`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;

返回结果为下面这样的：

![055a54b673f1dd7f1a48c37c0e3de7fd.png][]

它会包含`年月日时分秒`。

如果你还想返回`毫秒`，可以使用`now(3)`，例如：

select now(3) from brand limit 1;

返回结果为下面这样的：

![ce1e15ee8371ff0630a76710f385c717.png][]

使用起来非常方便好记。

### 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等于苏三的数据了。

执行之后直接报错了：

![e6a63533c10979e723d0c36cfd384c03.png][]

这就需要在插入之前加一下判断。

当然很多人通过在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 ###

通常情况下，我们在插入数据之前，一般会先查询一下，该数据是否存在。如果不存在，则插入数据。如果已存在，则不插入数据，而直接返回结果。

在没啥并发量的场景中，这种做法是没有什么问题的。但如果插入数据的请求，有一定的并发量，这种做法就可能会产生重复的数据。

当然防止重复数据的做法很多，比如：`加唯一索引`、`加分布式锁`等。

但这些方案，都没法做到让第二次请求也更新数据，它们一般会判断已经存在就直接返回了。

这种情况可以使用`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\`;

结果如图所示：

![0771d5c9934b16e44718790bfa9bd740.png][]

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

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

答：使用`show index`命令。

比如：

show index from \`order\`;

也能查出该表所有的索引：

![9bf448f205f48f3ff88b577bba81f626.png][]

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

答：这就需要使用`show create table`命令了。

例如：

show create table \`order\`;

执行结果如图所示：

![c73a09215950fc531d9ee9601e97692f.png][]

其中`Table`表示`表名`，`Create Table`就是我们需要看的`建表信息`，将数据展开：

![e1e4c026632a0bf9c51b22a4640d223e.png][]

我们能够看到非常完整的建表语句，表名、字段名、字段类型、字段长度、字符集、主键、索引、执行引擎等都能看到。

非常直接明了。

[d4dba5b8397e5ba4d4decd39ca426334.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/c9a70ab4790949419206082ac0679a7c.png
[baeb5fba660e3f3cde7792d678fc0c25.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/fcfdb1ab09d64f62a422015df0c81792.png
[ca7f193ca67d1615ce3f9c868aaaf828.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/da4548e22ec441df96655a03181994a2.png
[055a54b673f1dd7f1a48c37c0e3de7fd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/02a5ffe6a0c2419e8abc7ea4ee0c5271.png
[ce1e15ee8371ff0630a76710f385c717.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/d3dc96954b6c4c58af08a1a33ace1063.png
[e6a63533c10979e723d0c36cfd384c03.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/825fec0787244ed3ad4dc93d0eb1c42d.png
[0771d5c9934b16e44718790bfa9bd740.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/c8b11d4d9bd44860be0e8659dd95cd8b.png
[9bf448f205f48f3ff88b577bba81f626.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/b1f9f236e5ea4fecb26fe5dedbb29781.png
[c73a09215950fc531d9ee9601e97692f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/ee23265ef4194677a2c14fabaee443b2.png
[e1e4c026632a0bf9c51b22a4640d223e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/fb0ae86c3df0499dbba28868370afa0d.png