How to split and merge multiple values ​​in a single field in MySQL

Multiple values ​​combined display

Now we have the requirements shown in Figure 1 to Figure 2

How to do it?

The following sql:

SELECT id,GROUP_CONCAT(DISTINCT str) as str from test GROUP BY id

Related knowledge points

GROUP_CONCAT

GROUP_CONCAT([DISTINCT] Fields to be connected [Order BY ASC/DESC sorting fields] [Separator 'separator'])

Multiple values ​​split display

Now our requirements are the opposite of the first example.

From Figure 3

To Figure 4

This is a little complicated. First, we have done the processing of str in advance as shown in the figure, that is, one-to-one processing of characters + ','. Secondly, because MySQL does not have a sequence function, we need to prepare a sequence table in advance. The maximum id should be greater than the maximum number of items in a single field.

The specific sql is as follows:

SELECT t.id,SUBSTRING_INDEX(SUBSTRING_INDEX(str,',',s.id),',',-1) as str 
	from test2 t 
		join sequence s on s.id<(LENGTH(t.str)/2+1) 
			ORDER BY t.id

Related knowledge points

SUBSTRING_INDEX(str,delim,count)

SUBSTRING_INDEX(string to process, separator, count)

If count is a positive number, then it is all the content to the left of the Nth delimiter, counting from left to right.

If it is a negative number, then it starts from the right and all the contents to the right of the Nth separator are counted.

Summarize

This is the end of this article about MySQL single field multi-value splitting and merging processing. For more relevant MySQL single field multi-value splitting and merging content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!