MySQL & Cleaning up a chain of "LIKE" operators-CodePudding

I've created a stored procedure that combines a set of book related tables and then returns results based on matches from more than one of a book's various attributes.

For example: If I search for the word "fall", then the query might return books with titles and publishers containing the word "fall".

Code:

The procedure I currently have is as follows...

CREATE PROCEDURE library.SearchForBooks(IN searchTerm varchar(50))
begin
    select bookID,title,series_name,series_part,first_name,middle_name,last_name,publisher_name,isbn,edition,copyright,genre_type,genre_name,format,pages,language_name,notes 
    from book 
    where title like concat('%',searchTerm,'%')
        or copyright like concat('%',searchTerm,'%') 
        or publisher_name like concat('%',searchTerm,'%') 
        or first_name like concat('%',searchTerm,'%')
        or middle_name like concat('%',searchTerm,'%')
        or last_name like concat('%',searchTerm,'%');
END

NOTE:

The actual stored procedure has a fair bit more going on within it but this is the section that's relevant. I can include more if it's needed.

Explanation

The part I want to focus on is that series of "OR" operators. As it stands, they seem really sloppy and I'd like to clean it up. One thing I tried was concatenating the three separate parts of the author's name and then using LIKE in this manner. or concat(first_name,' ',middle_name,' ',last_name) like concat('%',searchTerm,'%'); but this only had a mixed success rate. Sometimes it worked like it was supposed to and other times it wouldn't return books with values that I know for a fact are in the database.

Is there a way to store the concat('%',searchTerm,'%') as a variable in such a way that it can then be used without causing any errors? Something like or copyright like savedVariable? That would go a long way and I tried implementing it but I kept getting syntax errors.

It should be noted that the procedure actually does work as it's intended to. My question is one of curiosity and attempting to create much cleaner code.

I'm still quite new to MySQL and don't know all the nuances of the the language so I apologize if this is a very dumb question. Thanks!

CodePudding user response：

Is there a way to store the concat('%',searchTerm,'%') as a variable in such a way that it can then be used without causing any errors? Something like or copyright like savedVariable?

Easily.

SELECT ...
FROM table
CROSS JOIN ( SELECT CONCAT('%',searchTerm,'%') AS pattern ) pattern
WHERE table.column LIKE pattern.pattern
  ...

From the other side this makes no sense - the result of CONCAT('%',searchTerm,'%') where searchTerm is a variable is totally a constant during definite query execution, and it is evaluated once during the execution plan building, not on the execution stage.