随着我们业务越来越复杂的情况下，完全基于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 拆分逗号分隔的字段，并统计
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，表数据不变。