MySQL中实用的知识点有哪些

共计 5761 个字符，预计需要花费 15 分钟才能阅读完成。

这篇文章主要介绍了 MySQL 中实用的知识点有哪些的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇 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 user_id,user_name FROM `sys_user` WHERE user_name LIKE  %JM% 
ORDER BY CHAR_LENGTH(user_name) ASC LIMIT 5;

执行效果如图所示：

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

3.locate

有时候我们在查找某个关键字，比如：JM，需要明确知道它在某个字符串中的位置时，该怎么办呢？

答：使用 locate 函数。

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

SELECT user_id,user_name FROM `sys_user` WHERE user_name LIKE  %JM% 
ORDER BY CHAR_LENGTH(user_name) ASC, LOCATE(JM ,user_name) LIMIT 2,2;

执行结果：

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

除此之外，我们还可以使用：instr 和 position 函数，它们的功能跟 locate 函数类似，在这里我就不一一介绍了。

4.replace

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

这种情况就能使用 replace 函数。

例如：

UPDATE sys_user set user_name=REPLACE(user_name, A , B) WHERE user_id=1;

这样就能轻松实现字符替换功能。

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

UPDATE sys_user set user_name=REPLACE(user_name,   ,) WHERE user_name LIKE   % 
UPDATE sys_user set user_name=REPLACE(user_name,   ,) WHERE user_name LIKE  % 

使用该函数还能替换 json 格式的数据内容，真的非常有用。

5.now

时间是个好东西，用它可以快速缩小数据范围，我们经常有获取当前时间的需求。

在 MYSQL 中获取当前时间，可以使用 now() 函数，例如：

SELECT now() FROM sys_user LIMIT 1;

返回结果为下面这样的：

它会包含年月日时分秒。

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

SELECT now(3) FROM sys_user LIMIT 1;

返回结果为下面这样的：

使用起来非常方便好记。

6.insert into … select

在工作中很多时候需要插入数据。

传统的插入数据的 sql 是这样的：

INSERT INTO `sys_user`(`user_id`, `dept_id`, `user_name`, `create_time`) 
VALUES (6,  103 ,  JM , now());

它主要是用于插入少量并且已经确定的数据。但如果有大批量的数据需要插入，特别是是需要插入的数据来源于，另外一张表或者多张表的结果集中。

这种情况下，使用传统的插入数据的方式，就有点束手无策了。

这时候就能使用 MYSQL 提供的：insert into … select 语法。

例如：

INSERT INTO `sys_user`(`user_id`, `dept_id`, `user_name`, `create_time`) 
SELECT null,dept_id,user_name,now(3) FROM `sys_user_backup` WHERE dept_id in (103 , 105

这样就能将用户备份表中的部分数据，非常轻松插入到用户表中。

7.insert into … ignore

不知道你有没有遇到过这样的场景：在插入 1000 个用户之前，需要先根据 user_name，判断一下是否存在。如果存在，则不插入数据。如果不存在，才需要插入数据。

如果直接这样插入数据：

INSERT INTO `sys_user`(`id`, `dept_id`, `user_name`, `create_time`) 
VALUES (6,  103 ,  JM , now());

肯定不行，因为 sys_user 表的 user_name 字段创建了唯一索引，同时该表中已经有一条 user_name 等于 JM 的数据了。

执行之后直接报错了：

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

当然很多人通过在 sql 语句后面拼接 not exists 语句，也能达到防止出现重复数据的目的，比如：

INSERT INTO `sys_user`(`user_id`, `dept_id`, `user_name`, `create_time`) 
SELECT null,dept_id,user_name,now(3) FROM sys_user_backup
WHERE not exists (SELECT * FROM `sys_user` WHERE user_name =  JM)

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

答：可以使用 insert into … ignore 语法。

例如：

INSERT ignore INTO `sys_user`(`user_id`, `dept_id`, `user_name`, `create_time`) 
VALUES (123,  105 ,  JM , now(3));

这样改造之后，如果 sys_user 表中没有 user_name 为 JM 的数据，则可以直接插入成功。

但如果 sys_user 表中已经存在 user_name 为 JM 的数据了，则该 sql 语句也能正常执行，并不会报错。因为它会忽略异常，返回的执行结果影响行数为 0，它不会重复插入数据。

8.select … for update

MYSQL 数据库自带了悲观锁，它是一种排它锁，根据锁的粒度从大到小分为：表锁、间隙锁和行锁。

在我们的实际业务场景中，有些情况并发量不太高，为了保证数据的正确性，使用悲观锁也可以。

比如：用户扣减积分，用户的操作并不集中。但也要考虑系统自动赠送积分的并发情况，所以有必要加悲观锁限制一下，防止出现积分加错的情况发生。

这时候就可以使用 MYSQL 中的 select … for update 语法了。

例如：

BEGIN;
SELECT * FROM `sys_user` where user_id=1 
FOR UPDATE;
 
// 业务逻辑处理
 
UPDATE `sys_user` SET score = score -1 WHERE user_id=1;
COMMIT;

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

需要注意的是 for update 前的 id 条件，必须是表的主键或者唯一索引，不然行锁可能会失效，有可能变成表锁。

9.on duplicate key update

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

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

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

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

这种情况可以使用 on duplicate key update 语法。

该语法会在插入数据之前判断，如果主键或唯一索引不存在，则插入数据。如果主键或唯一索引存在，则执行更新操作。

具体需要更新的字段可以指定，例如：

INSERT INTO `sys_user`(`user_id`, `dept_id`, `user_name`, `create_time`) 
VALUES (null,  103 ,  JM , now())
OM DUPLICATE KEY UPDATE user_name= JM ,create_time=now();

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

但需要注意的是，在高并发的场景下使用 on duplicate key update 语法，可能会存在死锁的问题，所以要根据实际情况酌情使用。

10.show create table

有时候，我们想快速查看某张表的字段情况，通常会使用 desc 命令，比如：

DESC `sys_dept`;

结果如图所示：

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

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

答：使用 show index 命令。

比如：

SHOW INDEX FROM sys_dept;

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

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

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

例如：

show create table `order`;

执行结果如图所示：

  其中 Table 表示表名，Create Table 就是我们需要看的建表信息，将数据展开：我们能够看到非常完整的建表语句，表名、字段名、字段类型、字段长度、字符集、主键、索引、执行引擎等都能看到。

非常直接明了。

11.create table … select

有时候，我们需要快速备份表。

通常情况下，可以分两步走：

创建一张临时表

将数据插入临时表

创建临时表可以使用命令：

CREATE TABLE user_20221219 LIKE `sys_user`;

创建成功之后，就会生成一张名称叫：user_20221219，表结构跟 sys_user 一模一样的新表，只是该表的数据为空而已。

接下来使用命令：

INSERT INTO user_20221219 SELECT * FROM `sys_user`;

执行之后就会将 order 表的数据插入到 user_20221219 表中，也就是实现数据备份的功能。

但有没有命令，一个命令就能实现上面这两步的功能呢？

答：用 create table … select 命令。

例如：

CREATE TABLE user_20221219SELECT * FROM `sys_user`;

执行完之后，就会将 user_20221219 表创建好，并且将 sys_user 表中的数据自动插入到新创建的 user_20221219 中。

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

12.explain

很多时候，我们优化一条 sql 语句的性能，需要查看索引执行情况。

答：可以使用 explain 命令，查看 mysql 的执行计划，它会显示索引的使用情况。

例如：

EXPLAIN SELECT * FROM `sys_user` WHERE dept_id=103;

结果：

  通过这几列可以判断索引使用情况，执行计划包含列的含义如下图所示：

说实话，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

关于“MySQL 中实用的知识点有哪些”这篇文章的内容就介绍到这里，感谢各位的阅读！相信大家对“MySQL 中实用的知识点有哪些”知识都有一定的了解，大家如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道。