业务实战中如何利用MySQL函数来解决

最新推荐文章于 2023-05-10 16:16:22 发布

暮色里de白雪檐

最新推荐文章于 2023-05-10 16:16:22 发布

阅读量254

点赞数 1

分类专栏： MySQL 文章标签： mysql 字符串 sql

本文链接：https://blog.csdn.net/lucky_love816/article/details/109645655

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

随着我们业务越来越复杂的情况下，完全基于java后台来解决首先是很麻烦，而且性能带来降低，代码的可读性下降，这个时候就需要一些MySQL的函数来解决了，这篇文章对于常见的MySQL函数不予介绍

concat函数

使用方法：
CONCAT(str1,str2,…)
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL，不是“NULL”

注意：

如果所有参数均为非二进制字符串，则结果为非二进制字符串。
如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。
一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如：

SELECT CONCAT(CAST(int_col AS CHAR), char_col)

MySQL的concat函数可以连接一个或者多个字符串,如

mysql> select concat('10');
+--------------+
| concat('10') |
+--------------+
| 10  |
+--------------+
1 row in set (0.00 sec)
 
mysql> select concat('11','22','33');
+------------------------+
| concat('11','22','33') |
+------------------------+
| 112233 |
+------------------------+
1 row in set (0.00 sec)

MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL

mysql> select concat('11','22',null);
+------------------------+
| concat('11','22',null) |
+------------------------+
| NULL  |
+------------------------+
1 row in set (0.00 sec)

ON DUPLICATE KEY UPDATE语句

使用情况：

   当我们要向数据库中插入一条数据时，我们需要保证主键的唯一性。
   如果插入的数据主键不重复，那么就插入；如果主键已存在（重复），那么就执行update之后的语句。

Insert into buyer(buyer_id,buyer_sex,buyer_name,buyer_address) 
VALUES(2,'女','Alice','testaddress2') 
ON DUPLICATE KEY UPDATE buyer_id=buyer_id+1

多句使用（直接加逗号）
SQL语句，我们在update后添加性别的修改

Insert into buyer(buyer_id,buyer_sex,buyer_name,buyer_address) 
VALUES(3,'女','Alice','testaddress2') 
ON DUPLICATE KEY UPDATE buyer_id=buyer_id+1,buyer_sex='无'

	<insert id="updateSubModelPartOrder">
		INSERT INTO `vehicle_sub_model_part` (
		    `id`,model_id,sub_model_id,part_id,part_type_code,part_type_order,part_order,domain_code
		)
		VALUES
		<foreach collection="list" index="index" item="item" separator=",">
			(
			   #{item.id},
			   #{item.modelId},
			   #{item.subModelId},
			   #{item.partId},
			   #{item.partTypeCode},
			   #{item.partTypeOrder},
			   #{item.partOrder},
			   #{domainCode}
			)
		</foreach>
		ON DUPLICATE KEY UPDATE part_type_order=values(part_type_order),part_order=values(part_order)
	</insert>

concat_ws函数

使用方法：
CONCAT_WS(separator,str1,str2,…)

CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。
注意：
如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

如连接后以逗号分隔

使用方法：
CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。
注意：
如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

如连接后以逗号分隔

和MySQL中concat函数不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回NULL

mysql> select concat_ws(',','11','22',NULL);
+-------------------------------+
| concat_ws(',','11','22',NULL) |
+-------------------------------+
| 11,22 |
+-------------------------------+
1 row in set (0.00 sec)

    <sql id="queryPartTypeList_common">
        <where>
            t.domain_code=#{domainCode,jdbcType=VARCHAR}
            <if test="partTypeCode !=null and partTypeCode != '' ">
                AND t.`part_type_code`=#{partTypeCode,jdbcType=VARCHAR}
            </if>
            <if test="searchText !=null and searchText != '' ">
                AND INSTR(CONCAT_WS( " " , t.`part_type_name`, t.`part_type_code`),#{searchText})
            </if>
        </where>
    </sql>

group_concat函数

完整的语法如下：
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])
基本查询

mysql> select * from aa;
+------+------+
| id| name |
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200  |
|3 | 500  |
+------+------+
6 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，逗号分隔(默认)

mysql> select id,group_concat(name) from aa group by id;
+------+--------------------+
| id| group_concat(name) |
+------+--------------------+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+------+--------------------+
3 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，分号分隔

mysql> select id,group_concat(name separator ';') from aa group by id;
+------+----------------------------------+
| id| group_concat(name separator ';') |
+------+----------------------------------+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500  |
+------+----------------------------------+
3 rows in set (0.00 sec)

以id分组，把去冗余的name字段的值打印在一行

mysql> select id,group_concat(distinct name) from aa group by id;
+------+-----------------------------+
| id| group_concat(distinct name) |
+------+-----------------------------+
|1 | 10,20|
|2 | 20  |
|3 | 200,500 |
+------+-----------------------------+
3 rows in set (0.00 sec)

以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序

mysql> select id,group_concat(name order by name desc) from aa group by id;
+------+---------------------------------------+
| id| group_concat(name order by name desc) |
+------+---------------------------------------+
|1 | 20,20,10  |
|2 | 20|
|3 | 500,200|
+------+---------------------------------------+
3 rows in set (0.00 sec)

repeat()函数

用来复制字符串,如下’ab’表示要复制的字符串，2表示复制的份数

mysql> select repeat('ab',2);
 
+----------------+
| repeat('ab',2) |
+----------------+
| abab      |
+----------------+
 
1 row in set (0.00 sec)

又如

mysql> select repeat('a',2);
 
+---------------+
| repeat('a',2) |
+---------------+
| aa      |
+---------------+
1 row in set (0.00 sec)

MySQL以逗号隔开的字符串查询方式整理

单个值查询使用函数进行处理，FIND_IN_SET()实用

SELECT * FROM `by_info` WHERE FIND_IN_SET('7',type_id);

多个值模糊查询，使用正则，此方式相当于 ( LIKE ‘%1%’ OR LIKE ‘%2%’ OR LIKE ‘%5%’ )

SELECT * FROM `by_info` WHERE type_id REGEXP '(1|2|5)';

多个值匹配查询，使用正则，此方式相当于 ( FIND_IN_SET(‘1’,type_id) OR FIND_IN_SET(‘2’,type_id) OR FIND_IN_SET(‘5’,type_id) )

SELECT * FROM `by_info` WHERE type_id REGEXP '(^|,)(1|12|5|9)(,|$)';

PS:对于第二、三中情况的话，缺点就是只能传一个参数去匹配/模糊匹配多条记录，但是有时候传值是a,b这么办呢，这样说，现在表里有个字段是这样的
比如table1中有两条记录
name no
a 2,9
b 8,10

然后有一串字符串,是0,1,2,3,4

然后通过一条sql,找出no为2,9的记录来```

因为字符串中有2,数据中也有2

详细解释

表的字段就是
name no
a 2,9
b 8,10
字符串是str=“0,1,2,3,4”
接下来就是查 no字段里跟str里有交集的记录
查询的结果就是name=a的,no=2,9的记录。

select * from table1 where  

concat(',',no,',') regexp concat(',0,|,1,|,2,|,3,|,4,');

或者

select * from table1 where

concat(',',no,',') regexp concat(',(',replace('0,1,2,3,4',',','|'),'),');

但是你也可以使用第一种FIND_IN_SET函数实现

mysql FIND_IN_SET 多值查询

FIND_IN_SET 一般都是单个查一个逗号分隔字段的，

比如

FIND_IN_SET('a','a,b,c')

如果要查出a,b这种值就不行了

用OR的方法解决，下边这个mybatis没试验过，等有空再试试，先记录下解决思路

1=1 AND (
<foreach collection="item.ids.split(',')" item="aaa" index="index" open="OR" close="" eparator="">
　　FIND_IN_SET(#{aaa},'a,b,c')
</foreach>

比如有一个tag表，然后另一张表中有一个多个tagId逗号分隔的字段，想查出多个tag的名字，用逗号分隔

SELECT a.tagIds,
(SELECT GROUP_CONCAT(b.tag_name) FROM tag b WHERE FIND_IN_SET(b.tag_id,a.tag_ids)) tagNames 
FROM article a

mysql 拆分逗号分隔的字段，并统计

mysql 拆分逗号分隔的字段，并统计

IFNULL

IFNULL(expr1,expr2)的用法：

假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1;
否则其返回值为expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。如下图所示：
在这里插入图片描述

注意：当整条sql返回的结果记录数为：0 ，它会返回 null

在这里插入图片描述

IF函数

IF函数语法如下：

IF(expr,if_true_expr,if_false_expr)

IF函数通常与SUM()函数组合

SELECT
    SUM(IF(status = 'Shipped', 1, 0)) AS Shipped,
    SUM(IF(status = 'Cancelled', 1, 0)) AS Cancelled
FROM
    orders;

IF函数与COUNT()函数组合，因为COUNT函数不计算空值，所以如果状态不在选定状态，IF函数将返回NULL，否则将返回1

SELECT
    COUNT(IF(status = 'Cancelled', 1, NULL)) Cancelled,
    COUNT(IF(status = 'Disputed', 1, NULL)) Disputed,
    COUNT(IF(status = 'In Process', 1, NULL)) 'In Process',
    COUNT(IF(status = 'On Hold', 1, NULL)) 'On Hold',
    COUNT(IF(status = 'Resolved', 1, NULL)) 'Resolved',
    COUNT(IF(status = 'Shipped', 1, NULL)) 'Shipped'
FROM
    orders;

CASE WHEN 和 SUM组合使用

    <select id="countCommentThumbs" resultType="com.izkml.mlyun.partybuilding.response.PartyTopicStaffResponse$Counts">
       SELECT SUM(CASE WHEN a.is_comment=1 THEN 1 ELSE 0 END) AS comments,
       SUM(CASE WHEN a.is_thumbs_up=1 THEN 1 ELSE 0 END) AS thumbs
       FROM party_topic_staff a WHERE a.topic_id = #{topicId}
    </select>

    <select id="getDataCenterSummaryDetail" resultType="com.izkml.energy.data.response.DataCenterinfoResponse$DataCenterSummaryDetail">
        SELECT dic_type.`name`,center.report_year,
			SUM(JFSL) AS JFSL,
			SUM(JFJZMJ) AS JFJZMJ,
			SUM(JGZSL) AS JGZSL,
			SUM(SBZGL) AS SBZGL,
			SUM(ITSBGL) AS ITSBGL,
			SUM(KQTJSBGL) AS KQTJSBGL,
			SUM(PDJFSSBGL) AS PDJFSSBGL,
			SUM(UPSZJRL) AS UPSZJRL,
			SUM(ZYDL) AS ZYDL,
			SUM(ITSBYDL) AS ITSBYDL,
			SUM(KQTJSBYDL) AS KQTJSBYDL,
			SUM(PDJFSSBYDL) AS PDJFSSBYDL,
			SUM(OtherValue) AS OtherValue
		FROM sys_data_centerinfo center,sys_dic_type dic_type
		WHERE center.is_summary = 0
		and center.unit_type= #{unitType}
		and center.unit_type = dic_type.`code`
		and center.unit_id IN
		<foreach collection="unitIds" open="(" close=")" index="index" item="item" separator=",">
			#{item}
		</foreach>
        and center.logic_delete="NO"
        and center.report_year = #{reportYear}
    </select>

@rownum函数

语句：

SELECT @rownum:=@rownum+1 AS rownum, Orderstate.*
FROM (SELECT @rownum:=0) r, Orderstate ;

执行结果：
在这里插入图片描述
SELECT @rownum:=0这个就相当于临时表，这个一定要加，如果不加，每次只需sql这个rownum就不是从0开始而是自增的

语句：

SELECT
        t.id examinationResultId,
        t.examination_id,
        t.staff_id,
        t.real_name,
        t.org_name,
        t.start_time,
        t.end_time,
        t.score,
        t. STATUS,
        (@r0 := @r0 + 1) AS rank1,
        @r1 := IF (@r3 = score ,@r1 ,@r0) AS rank,
        @r3 := score
FROM
        (
                SELECT
                        examination_id,
                        staff_id,
                        real_name,
                        org_name,
                        start_time,
                        end_time,
                        score,
                        STATUS,
                        id,
                        UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time) AS costTime
        FROM
                examination_result
        WHERE
                examination_id = 'SvV7mgbknHX79EbJF53'
        ORDER BY
                score DESC,
                costTime ASC
        ) t,
        (SELECT @r0 := 0) AS rn,
        (SELECT @r1 := 0) AS c,
        (SELECT @r3 := NULL) AS d

mysql不存在则插入，存在则更新或忽略

表名称：employee
表字段：
在这里插入图片描述
初始表数据：

注：以下所有的示例都需要被插入的数据中需要存在UNIQUE索引或PRIMARY KEY字段

不存在则插入，存在则更新-on duplicate key update

如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复，则执行UPDATE语句，例：

INSERT INTO `employee` (`id`, `name`, `age` )
VALUES
	( 2,'wujf', 20 ) 
ON DUPLICATE KEY UPDATE `age` = 18;

-- Affected rows: 2

这里受影响的行数是2，因为数据库中存在name='wujf’的数据，如果不存在此条数据，则受影响的行数为1
最新的表数据如下：
在这里插入图片描述

不存在则插入，存在则更新-replace into

如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复，则先删除旧数据再插入最新的数据，例：

REPLACE INTO `employee` ( `id`, `name`, `age` )
VALUES
	( 2, 'wujf', 20 );
	
-- Affected rows: 2

这里受影响的行数是2，因为数据库中存在name='wujf’的数据，并且id的值会变成2，因为它是先删除旧数据，然后再插入数据，最新的表数据如下：
在这里插入图片描述

避免重复插入-insert ignore into

关键字：insert ignore into，如果插入的数据会导致UNIQUE索引或PRIMARY KEY发生冲突/重复，则忽略此次操作/不插入数据，例：

INSERT IGNORE INTO `employee` ( `id`, `name`, `age` )
VALUES
	( 3, 'wujf', 25 );

-- Affected rows: 0

这里已经存在name='wujf’的数据，所以会忽略掉新插入的数据，受影响行数为0，表数据不变。

暮色里de白雪檐

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
业务实战中如何利用MySQL函数来解决

随着我们业务越来越复杂的情况下，完全基于java后台来解决首先是很麻烦，而且性能带来降低，代码的可读性下降，这个时候就需要一些MySQL的函数来解决了，这篇文章对于常见的MySQL函数不予介绍concat函数使用方法：CONCAT(str1,str2,…)返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL，不是“NULL”注意：如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。一个数字参数被
复制链接

扫一扫