Analysis of find_in_set string search function in mysql

This article brings you relevant knowledge about mysql. It mainly introduces the find_in_set string search function in mysql. Let’s take a look at it based on the example. It has a good reference value. I hope to be helpful.

Recommended learning: mysql video tutorial

Requirements

In the system, whether it is an order or a return order, Some platforms require seller review, while others do not, so you can set process nodes for orders or returns in the system.

For each process node, the customer's requirement is that each node must be distributed according to the role of the employee under the system, so that the order can be assigned to an individual instead of being processed by employees of the entire company.

Plan

For the above scenario, the solution I implemented is to add the corresponding node to be processed field to both the order table and the return order table. If multiple people can process it, use, to separate, Save the id collection of corresponding employees.

Therefore, we also need to make corresponding modifications in the method of querying orders. Everyone can only see the orders that they can process.

The sample content of the table is as follows:

When employees log in to the system, we can get their corresponding id, and we need to Find the id in the corresponding field to see if it is in the business review, financial review or shipper collection. At this time, how should the SQL be changed?

Function

Before changing SQL, I thought that many functions of MYSQL have been summarized before, and for the above implementation, there will definitely be corresponding functions in MYSQL to help us achieve it. Sure enough, I found it.

1. Introduction

MySQL provides a built-in string function called FIND_IN_SET(), which allows you to find the position of a specified string in a comma-separated list of strings.

2. Syntax: FIND_IN_SET(needle,haystack)

FIND_IN_SET() function accepts two parameters:

• needle is the string to be found.
• haystack is a comma separated list of strings to search for.

The FIND_IN_SET() function returns an integer or a NULL value based on the value of the parameter:

• If needle or haystack is NULL, the function returns NULL value.
• If needle is not in haystack, or haystack is an empty string, zero is returned.
• If needle is in haystack, return a positive integer.

3. Practical practice

The following is the SQL of orders pending business review that can be processed by employees with number 8910 in the project:

SELECT
	t.id_,t.order_sn,t.create_time, t.business_employee_ids,cus.customer_title
FROM
	wms_orders t
LEFT JOIN wms_customer cus ON cus.id = t.buyer_id
WHERE
	t.order_status != 100
AND t.comp_id = 8815
AND FIND_IN_SET(
	'8910',
	t.business_employee_ids
)
AND t.order_status = 14
AND (
	t.parent_order_sn IS NULL
	OR t.parent_order_sn = ''
)
ORDER BY
	t.id_ DESC

The results are as follows:

In the above result screenshot, the three orders framed with IDs 6594, 6523, and 6373 can only be processed by employee number 8910. We change the above SQL employee number to 8912, then these three orders should not be within the result range, thus verifying whether it is feasible to use this function to query.

The query results are as follows:

The results prove that there is no problem with the query.

Interlude

Two days ago, a colleague posted a screenshot in the group and asked a question, as follows:

A brief description:

The image address column stores the address of our old data center, and now that the new data center is online, it needs to be replaced with the new address to obtain images.

After reading this, I thought of the REPLACE() function in MYSQL that I had used before, which can replace strings. Therefore, the update statement is as follows:

UPDATE wms_platform_wear_brand
SET brand_img = REPLACE (
	brand_img,
	"http://***/udata/interface/timer/pic/getAttachPic.do?attachId=",
	"wms/orderGoods.do?method=getAttachPic&attachId="
)

Recommended learning: mysql video tutorial

The above is the detailed content of Analysis of find_in_set string search function in mysql. For more information, please follow other related articles on the PHP Chinese website!