Search code examples
mysqlsqllocatemysql-variables

MySQL Illegal mix of collations when using user-defined variables inside LOCATE

I have a query like below to remove from a table column certain substrings which begin and end with particular substrings:

UPDATE om_posts SET post_content=REPLACE(post_content, SUBSTRING(
    post_content, 
    LOCATE(' style="', post_content), 
    LOCATE('"', post_content, LOCATE(' style="', post_content  )+ 8) - LOCATE(' style="', post_content ) + 1
),'')
where post_type="post";

I want to make this better reusable, so I'd like to abstract out those strings. I came across user-defined variables in mysql and refactored like this:

SET @beginning = ' style="';
SET @ending ='"';

UPDATE om_posts SET post_content=REPLACE(post_content, SUBSTRING(
    post_content, 
    LOCATE(@beginning, post_content), 
    LOCATE(@ending, post_content, LOCATE(@beginning, post_content  )+ 8) - LOCATE(@beginning, post_content ) + 1
),'')
where post_type="post";

but this gives an error: Error in query (1267): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation 'locate'. As far as I can tell my syntax should be correct. What am I missing?

Solution

  • Every character string literal has a character set and a collation.

    For the simple statement SELECT 'string', the string has the connection default character set and collation defined by the character_set_connection and collation_connection system variables.

    A character string literal may have an optional character set introducer and COLLATE clause, to designate it as a string that uses a particular character set and collation:

    [_charset_name]'string' [COLLATE collation_name]

    from the official documentation

    example from that page: _utf8mb4'abc' COLLATE utf8mb4_danish_ci