How to split a string according to specified characters in Mysql

Preface

In some scenarios (for example: users upload files or pictures, etc.), the general approach is to save the file information (file name, file path, file size, etc.) to the file table (user_file ), and then concatenate the IDs of all uploaded files by the user with a specified character and store them in a field in the table (user) (assumed to be: file_ids).
When displaying files uploaded by users, just query the file table directly:

-- 一般的语句是这样的，假设用户唯一键是id
select * from file where id in(select file_ids from user where id = 1);

There is no problem with the sql statement, and the files can also be queried. However, after the uploaded file is greater than 1, use This SQL statement query only returns one record. You may be confused, why only one record is returned? ? ? ;

Someone must have done such verification

-- 先运行下面这个sql，正确返回1，2，3 假设上传的文件id是1，2，3；
select file_ids from user where id = 1
-- 然后返回的文件id写死在sql语句中,运行成功返回3条记录
select file_ids from user where id in ('1' , '2' , '3');
-- 最后再整体试了下，结果返回一条......
select * from file where id in(select file_ids from user where id = 1)

-- 然后可能会想到：我把in里面的拼接成'1','2','3',这样总可以了吧？
select * from file where id in (select concat('\'', replace(file_ids,',','\',\'') ,'\'') from user where id = 1);
-- concat('\'', replace(file_ids,',','\',\'') ,'\'') 确实能拼接成上面说的形式，但是结果还是只有一条

The reason is because this query only returns one field, so only one record will be returned (even if there are multiple commas spliced ​​together, or manually spliced, Mysql only thinks it is a value, and the specific bottom layer is not clear...), the correct approach is as follows:

1: Query in two times (not the focus of this article, but it can be implemented)

select file_ids from user where id = 1

select file_ids from user where id in ('1' , '2' , '3');
或者
select file_ids from user where find_in_set(id , '1,2,3');

2: Will The file_ids field is divided into multiple columns, similar to Mysql's row-to-column conversion.

The difference between row-to-column conversion and Mysql row-to-column conversion is that you need to know the contents of the columns when converting rows to columns. This is not necessary. You only need to know the spliced ​​characters.

-- 下面语句将会把1，2，3，4一个字段转换成四行，依次是1，2，3，4
SELECT
	a.id,
	a.file_ids,
	substring_index(
		substring_index(
			a.file_ids,
			',',
			b.help_topic_id + 1
		),
		',' ,- 1
	) file_id
FROM
	user a
JOIN mysql.help_topic b ON b.help_topic_id < (
	length(a.file_ids) - length(REPLACE(a.file_ids, &#39;,&#39;, &#39;&#39;)) + 1
)
where id = 1
;
-- 然后将上面语句写在in()里面就行了，写在in()里面的话记住只能查询一个字段哦！

The above statement can be copied directly. Just replace the a table and a table fields with your own expressions and fields. As for mysql.help_topic, it comes with Mysql, so don’t worry about it.

Attachment: How does mysql split a string by delimiter

1. String splitting: SUBSTRING_INDEX (pressure 136/70 blood pressure), for example:

SUBSTRING_INDEX(pressure ,',',1)     #截取第一个逗号（,）号以前的字符串
SUBSTRING_INDEX(pressure ,',',-1)    #截取倒数第一个逗号（,）号以后的字符串

2 .Replacement function: replace(str, from_str, to_str). For example:

UPDATE bgs_building_copy1 SET `name`=replace(`name`,'=',"");    #替换等号为空字符串

The above is the detailed content of How to split a string according to specified characters in Mysql. For more information, please follow other related articles on the PHP Chinese website!