查询技巧

1、匹配逗号连接值

SELECT t.tname, b.* FROM w_topic t ,w_dwxxb b WHERE  CONCAT(',',t.orgcode,',') LIKE CONCAT('%,',b.orgcode,',%');

SELECT * FROM pay_config WHERE CONCAT (',',product_ids,',') REGEXP ',(1|43|15),';

# 有序匹配 locate
SELECT * FROM `test` where LOCATE('1,3',type);

2、逗号连接拆分成行

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) FROM mysql.help_topic
WHERE help_topic_id < (LENGTH('7654,7698,7782,7788') - LENGTH(REPLACE('7654,7698,7782,7788', ',', '')) + 1);

3、某个字段值 按照 给定顺序排序

SELECT id,inspect_number,applicant_id,applicant_name,`status` FROM rd_inspect ORDER BY FIELD(`status`,0,8,1,2,3,4,5,6,7,-1) DESC;


SELECT id,inspect_number,applicant_id,applicant_name,`status` FROM rd_inspect ORDER BY INSTR("0,8,1,2,3,4,5,6,7,-1", `status`) DESC;


SELECT * FROM user ORDER BY FIND_IN_SET(status,'2,4,1,3');

4、时间转换

# 秒级时间戳：1606371113
UNIX_TIMESTAMP(NOW())
# 毫秒级时间戳：1606371209293
REPLACE(unix_timestamp(current_timestamp(3)),'.','')

# 1、秒值转换为指定的日期格式

select FROM_UNIXTIME(1602668106,'%Y-%m-%d %h:%i:%s') as date;

# 2、毫秒值转换为指定的日期格式，将其中unix_timestamp为字段值/1000.

select FROM_UNIXTIME(1602668106000/1000,'%Y-%m-%d %h:%i:%s') as date;

使用函数：Now,current_timestamp,unix_timestamp

SELECT
NOW(),
NOW(3),
NOW(6),
current_timestamp(),
current_timestamp(6),
unix_timestamp(current_timestamp(3)),
REPLACE(unix_timestamp(current_timestamp(3)),'.',''),
unix_timestamp(current_timestamp(3))*1000;

效果如下图所示

5、查找替换

UPDATE yoffi_miqu.m_language_translation SET translation = REPLACE(translation, 'semaChat', 'yoffiChat') WHERE translation LIKE '%semaChat%';