MySQL常用函数或功能实现记录

1. IF(Condition,A,B)

 类似于Java中的三目运算函数，Condition是一个条件(可以是一个表达式)，如果条件成立返回A，否则返回B，如：

SELECT IF(1>2, 3, 4) as result

返回结果为4。

2. REPLACE(str,from_str,to_str)

 替换指定字段中的指定子串，上述的SQL语句就表示将str字段中的from_str子串替换为to_str。

3. 统计指定字符在某个字段中出现的次数

 比如parametric_parameters字段是一个字符串或者文本类型的字段，现在想知道每个parametric_parameters字段中有多少个逗号,，那么如下：

length(parametric_parameters) - length(REPLACE (parametric_parameters, ',', ''))

实现的思想就是，将原来的总长度减去去除,后的总长度就是,出现的次数。

4. 按天聚合数据：

SELECT
	DATE_FORMAT(create_time, '%Y-%m-%d') create_date,
	COUNT(1) count
FROM
	`user`
WHERE
	create_time BETWEEN '2019-02-20'
AND DATE_ADD('2019-04-29', INTERVAL 1 DAY)
GROUP BY create_date

注：

• 默认BETWEEN x AND x函数是包含只包头不包尾的，但往往需求是包头包尾的，所以使用DATE_ADD('2019-04-29', INTERVAL 1 DAY)函数将结束日期向后推了一天，这样就可以包尾（即上述的2019-04-29）了；
• 如果需求是按月聚合，需要改动上述SQL：
  • 将日期格式只保留到月份（'%Y-%m-%d' -> '%Y-%m）；
  • 将日期步进值单位改为月份（INTERVAL 1 DAY -> INTERVAL 1 MONTH）

5. 向表中添加新字段

比如向xxx表中添加新字段new_field（类型为bigint(20)），SQL如下：

ALTER TABLE xxx ADD COLUMN new_field bigint(20) DEFAULT NULL COMMENT '添加的新字段';

6. 添加或更改字段的注释

比如更改xxx表中status字段（类型为int(11)）的注解：

ALTER TABLE xxx MODIFY COLUMN status int(11) COMMENT '新注解';

7. 更改字段名字或类型

比如修改xxx表中old_name字段名为new_name（类型为bigint(20)）。注：不关心旧字段的类型

ALTER TABLE xxx CHANGE old_name new_name bigint(20) DEFAULT NULL COMMENT '新字段名'

8. 删除字段

比如删除xxx表中的字段deprecated_field：

alter table xxx drop column xx

9. 批量更新数据（注意：很坑）

 在批量更新数据时，不能使用分页去更新，会出现下面的现象：查询得到待更新的数据有3页，第一页（就是上述3页中的第1页）更新完，再去更新第二页（此时的第二页是上述3页中的第3页），最后更新第3页时你会发现没有数据了，因为已经前面已经更新了，此时实际待更新的数据就只剩1页了，所以再去更新时加上分页条件时就会出现数据不一致！！这个要十分注意，稍不留意排查半天可能也找不到问题。

 上述的过程再细化一下就是这样的：3页更新完第一页数据后，实际只剩2页了，当更新完第2页后，实际就只剩1页了，千万不要在这样的情况下加偏移量！！

【解决方案】

 应该是不断的去取前pageSize个数据不断循环更新，即limit 1000或limit 0,1000，偏移量始终为0。

9.2 MySQL中的批量更新

 在MySQL中，若存在大量的数据需要更新，如果仅仅是循环update效率会很低，建议使用如下的方式进行批量操作：

UPDATE user_public
SET description = CASE id
  WHEN 1 THEN "description1"
  WHEN 2 THEN "description2"
  WHEN 3 THEN "description3"
END,
 update_time = CASE id
  WHEN 1 THEN "time1"
  WHEN 2 THEN "time2"
  WHEN 3 THEN "time3"
END
WHERE id IN (1, 2, 3)

对应的MyBatis中的xml就是：

	<update id="batchUpdateDescription" parameterType="java.util.List">
		update user_public set description =
		<foreach collection="userPublics" item="userPublic" open="CASE id" separator=" " close="end,">
			when #{userPublic.id,jdbcType=BIGINT} then #{userPublic.description,jdbcType=VARCHAR}
		</foreach>
		update_time =
		<foreach collection="userPublics" item="userPublic" open="CASE id" separator=" " close="end">
			when #{userPublic.id,jdbcType=BIGINT} then #{userPublic.updateTime,jdbcType=TIMESTAMP}
		</foreach>
		where id in
		<foreach collection="userPublics" item="userPublic" open="(" separator="," close=")">
			#{userPublic.id,jdbcType=BIGINT}
		</foreach>
	</update>

我在测试的过程中发现MySQL可能有时不能接受这么长的SQL，所以注意批次的长度，我这里设置的每个批次为500。

10. 将多次count结果相加返回

将多次count的结果相加返回，两个count之间使用UNION ALL连接即可。示例如下：

SELECT
SUM(s.countData) totalCount
FROM (
	SELECT COUNT(id) countData FROM product WHERE user_id IS NULL
	UNION ALL
	SELECT COUNT(DISTINCT user_id) countData FROM product WHERE user_id IS NOT NULL
) as s

注：上述SQL中的s是UNION ALL联合表的别名（存储的是多个列，每个列都是一个count的结果），这个别名必须要有，否则将会有SQL异常！即使这个别名并没有地方用到，SUM(s.countData)中使用的s只是为了给他点面子（不然多尴尬），实际因为只有一张表，完全可以写成SUM(countData)，将多个count的结果相加直接调用内部函数SUM指定列名即可。利用这个特性可以实现多个count结果的相加，不仅仅局限于2个。

11. 表复制或结果复制

 表复制可以使用INSERT INTO xxx SELECT ...形式完成，将SELECT后面查询到的内容插入到指定的表xxx中，此时SELECT语句查询到的列数必须与xxx表中的列数严格一致（即插入所有字段），当然也可以指定某些字段，此时必须带上列名，形如INSERT INTO xxx (column_name1, column_name2...) SELECT ...。根据业务需求后面的SELECT语句可能非常复杂，下面是示例：

INSERT IGNORE INTO n_table(id,shop_id,status,r_id,name,category_id,create_time,update_time,store_id)
SELECT n.id, 1630989180882304 shop_id, 2 status, rn.rfa_id,n.name,rn.category_id,n.create_time,n.update_time,n.store_id FROM n_table n 
LEFT JOIN rn_able rn ON rn.n_id = n.id
WHERE n.p_id=1546205923080448 AND n.type=0 AND n.status=1 and rn.n_id is not null

注：上述是将联合查询的结果作为SELECT（也确实是本人在业务开发上使用的），由于不是插入n_table中的所有字段，必须指定插入的列名(column_name...)，此外可能存在主键冲突，将INSERT INTO改进为INSERT IGNORE INTO，出现冲突时直接跳过（即xxx表中已经存在相同id时，则进行操作，否则插入）。

12. 批量查询按传入id的顺序返回记录

场景：在MySQL中作in条件查询时，如下：

SELECT
	*
FROM
	store
WHERE
	id IN (560684, 560007, 560678)

实际返回的结果是依次是id=560007，id=560678，id=560684的3条记录，和传入的560684, 560007, 560678不一致，事实是in查询在扫表的时候是先将记录按id排序，然后再查询的，所以给出来的查询结果是按库中id进排序的，但业务场景中又确实有按照传入id的顺序返回，MySQL提供了一些方法：

【方法1】

SELECT
	*
FROM
	store
WHERE
	id IN (560684, 560007, 560678)
ORDER BY
	FIELD(id, 560684, 560007, 560678)

【方法2】

SELECT
	*
FROM
	store
WHERE
	id IN (560684, 560007, 560678)
ORDER BY
	FIND_IN_SET(id, '560684, 560007, 560678')

注意两者语法的区别