mysql笔记

1. GROUP_CONCAT， CONCAT_WS，， ORDER BY 组合使用

SELECT
	tmi.id,
	tmi.task_name AS taskName,
	JSON_UNQUOTE( JSON_EXTRACT( tmi.cooperate_dept, '$[*].name' ) ) AS cooperateDeptName,
	tmi.create_time AS createTime,
	GROUP_CONCAT( CONCAT_WS ( ' : ', tmr.create_time, tmr.content ) ORDER BY tmr.create_time DESC SEPARATOR ' | ' ) content 
FROM
	`task_manager_info` tmi
	LEFT JOIN task_manager_record tmr ON tmi.id = task_id 
GROUP BY
	tmi.id

2. mysql修改json类型指定name的value值

UPDATE project_approve_info 
SET detail_data = JSON_REPLACE(
  detail_data,
  JSON_UNQUOTE( REPLACE ( JSON_SEARCH( detail_data, 'all', '合同编号', NULL, '$[*].name' ), 'name', 'value' ) ),
  '1234' 
) 
WHERE
  id = 801

detail_data原数据

detail_data修改后数据