本文介绍了MySQL中的一些实用操作，包括IF函数、REPLACE函数的使用，统计字符出现次数的方法，按天聚合数据的SQL，添加、修改和删除字段，批量更新数据的注意事项，以及如何进行多条件count结果的相加等。还分享了如何保持批量查询时结果顺序与输入ID一致的技巧，并提供了表复制和结果复制的示例。
文章目录
1. `IF(Condition,A,B)`
2. `REPLACE(str,from_str,to_str)`
3. 统计指定字符在某个字段中出现的次数
4. 按天聚合数据：
5. 向表中添加新字段
6. 添加或更改字段的注释
7. 更改字段名字或类型
8. 删除字段
9. 批量更新数据(注意：很坑)
9.2 MySQL中的批量更新
10. 将多次`count`结果相加返回
11. 表复制或结果复制
12. 批量查询按传入id的顺序返回记录
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 user_public set description =
when #{userPublic.id,jdbcType=BIGINT} then #{userPublic.description,jdbcType=VARCHAR}
foreach>
update_time =
when #{userPublic.id,jdbcType=BIGINT} then #{userPublic.updateTime,jdbcType=TIMESTAMP}
foreach>
where id in
#{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')
注意两者语法的区别
扫一扫
384
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
