Search code examples
mysqlsqlmariadbfind-in-setmariasql

MySQL FIND_IN_SET function with special character not working

I have a stored procedure:

CREATE PROCEDURE MyDB.`tag_quicktrend`(
    IN taglist TEXT
)
BEGIN
    SELECT *
    FROM taginfo
    WHERE FIND_IN_SET(tagname,taglist);
END;

When I call SP: call sp_chart_band_tag_quicktrend('YH05_한글') it return correct tag. call sp_chart_band_tag_quicktrend('YH05_한글,~!@#$%^&*()_+|}{":?><./'';[]\\=-')`

but it return only YH05_한글 tag.

The value ~!@#$%^&*()_+|}{":?><./'';[]\=-` is a tagname in Database.

enter image description here

And this is data in database:

enter image description here

And version MySQL:

SELECT VERSION();

10.3.9-MariaDB

Solution

  • Finally I found problem because I set this mode.

    SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_BACKSLASH_ESCAPES');

    This equal (=) query return empty

    select * from taginfo where tagname = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`'

    When I remove the NO_BACKSLASH_ESCAPES by SET @@SQL_MODE = 'NO_ENGINE_SUBSTITUTION';

    select * from taginfo where tagname = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`'

    it returns row with tagname = ~!@#$%^&*()_+|}{":?><./';[]\=-`

    so that FIND_IN_SET function matches correct values