草庐IT

MySQL的14个小技巧

叫我峰兄 2023-03-28 原文
我最近几年用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
通常情况下，我们在插入数据之前，一般会先查询一下，该数据是否存在。如果不存在，则插入数据。如果已存在，则不插入数据，而直接返回结果。

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

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

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

这种情况可以使用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
有时候，我们需要快速备份表。

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

创建一张临时表

将数据插入临时表

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

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
---END--- ​

技巧MySQLcodebackground-colorpx数据插入数据sqlMySQL数据库

有关MySQL的14个小技巧的更多相关文章

  1. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  2. 动漫制作技巧如何制作动漫视频 - 2

    动漫制作技巧是很多新人想了解的问题，今天小编就来解答与大家分享一下动漫制作流程，为了帮助有兴趣的同学理解，大多数人会选择动漫培训机构，那么今天小编就带大家来看看动漫制作要掌握哪些技巧？一、动漫作品首先完成草图设计和原型制作。设计草图要有目的、有对象、有步骤、要形象、要简单、符合实际。设计图要一致性，以保证制作的顺利进行。二、原型制作是根据设计图纸和制作材料，可以是手绘也可以是3d软件创建。在此步骤中，要注意的问题是色彩和平面布局。三、动漫制作制作完成后，加工成型。完成不同的表现形式后，就要对设计稿进行加工处理，使加工的难易度降低，并得到一些基本准确的概念，以便于后续的大样、准确的尺寸制定。四、

  3. ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2

    我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby​​目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin

  4. ruby - 如何使用 ruby​​ mysql2 执行事务 - 2

    我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi

  5. ruby - 在 Ubuntu 14.04 中使用 Curl 安装 RVM 时出错 - 2

    我试图在Ubuntu14.04中使用Curl安装RVM。我运行了以下命令:\curl-sSLhttps://get.rvm.io|bash-sstable出现如下错误:curl:(7)Failedtoconnecttoget.rvm.ioport80:Networkisunreachable非常感谢解决此问题的任何帮助。谢谢 最佳答案 在执行curl之前尝试这个:echoipv4>>~/.curlrc 关于ruby-在Ubuntu14.04中使用Curl安装RVM时出错，我们在Stack

  6. ruby - gem install pg error : couldn't understand kern. osversion `14.0.0' on Yosemite w/Ruby 2.1.5 - 2

    我使用RVM安装Ruby-2.1.5并再次运行bundle。现在pggem不会安装，我得到这个错误:geminstallpg-v'0.17.1'----with-pg-config=/Applications/Postgres.app/Contents/Versions/9.3/bin/pg_configBuildingnativeextensionswith:'--with-pg-config=/Applications/Postgres.app/Contents/Versions/9.3/bin/pg_config'Thiscouldtakeawhile...ERROR:Error

  7. ruby-on-rails - Ubuntu 14.04 Rails 丢失文件 - 2

    安装Rails时，一切都很好，但后来，我写道:rails-v和输出:/home/toshiba/.rvm/rubies/ruby-2.2.1/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in`require':cannotloadsuchfile--rails/cli(LoadError)from/home/toshiba/.rvm/rubies/ruby-2.2.1/lib/ruby/site_ruby/2.2.0/rubygems/core_ext/kernel_require.rb:54:in`r

  8. Unity Shader 学习笔记（5）Shader变体、Shader属性定义技巧、自定义材质面板 - 2

    写在之前Shader变体、Shader属性定义技巧、自定义材质面板，这三个知识点任何一个单拿出来都是一套知识体系，不能一概而论，本文章目的在于将学习和实际工作中遇见的问题进行总结，类似于网络笔记之用，方便后续回顾查看，如有以偏概全、不祥不尽之处，还望海涵。1、Shader变体先看一段代码......Properties{ [KeywordEnum(on,off)]USL_USE_COL("IsUseColorMixTex?",int)=0 [Toggle(IS_RED_ON)]_IsRed("IsRed?",int)=0}......//中间省略，后续会有完整代码 #pragmamulti_c

  9. ruby-on-rails - 当我通过 rvm 使用 rails3 时，如何在 ubuntu 上安装 mysql2 gem？ - 2

    我正在尝试绕过rails配置这个极其复杂的迷宫。到目前为止，我设法在ubuntu上设置了rvm(出于某种原因，ruby在ubuntu存储库中已经过时了)。我设法建立了一个Rails项目。我希望我的测试项目使用mysql而不是mysqlite。当我尝试“rakedb:migrate”时，出现错误:“!!!缺少mysql2gem。将其添加到您的Gemfile:gem'mysql2'”当我尝试“geminstallmysql”时，出现错误，告诉我需要为安装命令提供参数。但是，参数列表很大，我不知道该选择哪些。如何通过在ubuntu上运行的rvm和mysql获取rails3？谢谢。

  10. 使用时 Rubygems 2.0.14 不是线程安全的 bundle 程序安装消息 - RUBYGEMS VERSION : 2. 4.5.1 - 2

    运行bundle安装时，我收到以下消息:Rubygems2.0.14isnotthreadsafe,soyourgemswillbeinstalledoneatatime.UpgradetoRubygems2.1.0orhighertoenableparallelgeminstallation.这很奇怪，因为在我的RubyGems环境中它说我的RubyGems版本是:2.4.5.1(见下文)~/w/Rafftopia❯❯❯gemenvRubyGemsEnvironment:-RUBYGEMSVERSION:2.4.5.1-RUBYVERSION:2.2.5(2016-04-26patc

随机推荐