Can MySQL's FIND_IN_SET or an Equivalent Function Utilize Indices?

Can MySQL FIND_IN_SET or an Equivalent Utilize Indices?

The query SELECT * FROM Foo WHERE FIND_IN_SET(id, '2,3') does not take advantage of the primary key index. This is evident when comparing its EXPLAIN output to the output for SELECT * FROM Foo WHERE id IN (2,3) which does utilize the index.

Objective: Construct a stored procedure that behaves like the second query, utilizing the index without prior knowledge of the comma-separated string containing the IDs.

Solution:

Create a prepared statement that accepts a string as a parameter:

CREATE PROCEDURE my_sp(@id_string NCHAR(1000))

The prepared statement can then be executed with the comma-separated string as an argument:

EXEC my_sp '2,3'

This approach achieves the following desired conditions:

• type is range (not ALL)
• key is not NULL

These conditions indicate that the index is being used.

Security Considerations:

Prepared statements mitigate the risk of SQL injection attacks. However, it's still crucial to ensure that user-supplied data is sanitized to prevent 2nd level SQL injection attacks.

The above is the detailed content of Can MySQL's FIND_IN_SET or an Equivalent Function Utilize Indices?. For more information, please follow other related articles on the PHP Chinese website!