1

The problem

I use the native function FIND_IN_SET with a variable @myVar for the list of string, like :

FIND_IN_SET(col, @myVar)

If I set @myVar from a direct value :

SET @myVAr = 'one,two,three';
... FIND_IN_SET(col, @myVar)

=> It works great

If I set @myVar from a function returning the concatenated values from one column of a table :

SET @myVAr = '';
SELECT @myVAr := getStringOfValues();
... FIND_IN_SET(col, @myVar)

=> I get Erreur SQL (1267) : Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation 'find_in_set'

So, I do i get rid of this error ?

It is important to note that I don't want to change the collations of my table. I wish I could change the collation of my variable so it match the collation of my column, but I cannot force it with the keyword "COLLATE" during its initialization :

SET @myVar= '' COLLATE utf8_unicode_ci;

=> I get Erreur SQL (1253) : COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4'

Useful informations

All my table and their columns have the collation utf8_unicode_ci

SHOW VARIABLES LIKE '%char%'

character_set_client = utf8mb4

character_set_connection = utf8mb4

character_set_database = utf8

character_set_filesystem = binary

character_set_results = utf8mb4

character_set_server = latin1

character_set_system = utf8

SHOW VARIABLES LIKE 'coll%'

collation_connection = utf8mb4_general_ci

collation_database = utf8_general_ci

collation_server = latin1_swedish_ci

SHOW CREATE FUNCTION getStringOfValues

character_set_client = utf8mb4

collation_connection = utf8m4_general_ci

Database Collation = utf8_general_ci

The SQL code (anonymous and simplified version)

-- Function to return as a string the result from a SELECT in my_table_1
DELIMITER $$
CREATE FUNCTION getStringOfValues()
    RETURNS TEXT

    -- Variables to build the string of values
    DECLARE curr_value VARCHAR(255) DEFAULT '';
    DECLARE string_of_values TEXT DEFAULT '';

    -- Variables to enter and exit the loop over values
    DECLARE done INT DEFAULT 0;
    DECLARE my_cursor CURSOR FOR SELECT col FROM my_table_1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN my_cursor;
        my_loop:LOOP
            FETCH my_cursor INTO curr_value;
            IF done THEN
              LEAVE my_loop;
            ELSE
                SET string_of_values = CONCAT(curr_value, ',', string_of_values);
            END IF;
        END LOOP;
    CLOSE my_cursor;

    -- Remove the trailing comma and quote the string. Result in something like 'one,two,three'
    RETURN QUOTE(TRIM(TRAILING ',' FROM string_of_values));
$$
DELIMITER ;

SET @myVAr = 'one,two,three';
-- The statement below will WORK
SELECT * FROM my_table_2 WHERE FIND_IN_SET(col, @myVAr);

SELECT @myVAr := getStringOfValues();
-- The statement below will NOT WORK as there is a 'Illegal mix of collations'
SELECT * FROM my_table_2 WHERE FIND_IN_SET(col, @myVAr);
Flyout91
  • 782
  • 10
  • 31
  • 1
    *"Erreur SQL (1253) : COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4'"* Simply `utf8 <> utf8mb4` try `SET @myVar= '' COLLATE utf8mb4_unicode_ci;` – Raymond Nijland May 24 '19 at 16:57
  • 1
    What about `character_set_client`? What values for `coll%`? What charset/collation was specified when connecting? When _creating_ the `FUNCTION`? (`SHOW CREATE FUNCTION getStringOfValues\G`) – Rick James May 24 '19 at 21:13
  • 2
    Are you doing all this to avoid doing a `JOIN` between `my_table_1` and `my_table_2`? – Bill Karwin May 27 '19 at 06:08
  • @RaymondNijland : It does not change anything, same sql error about illegal collation mix. – Flyout91 May 27 '19 at 06:20
  • @RickJames : I added what you asked. – Flyout91 May 27 '19 at 06:22
  • @BillKarwin : No, because theses tables are not in the same schema. But may be I can still join them...thank you for the idea – Flyout91 May 27 '19 at 06:22
  • @BillKarwin : But still though, I need to use the result of my function multiple times (so with your idea it means join my_table_1 with my_table_3, then join it with my_table_4, and so on. So that is why I thought of a function in the first place. – Flyout91 May 27 '19 at 06:33
  • @BillKarwin : It is not sexy with so many joins across different requests, but it achieves what I want so i will go with it I guess, thank you. – Flyout91 May 27 '19 at 07:19
  • 1
    JOINs are not from the SQL devil.. And if you need to reuse your query/resultset multiple times in a query you need to install MySQL 8 and use a CTE (`WITH .. AS ()` statement), CTE is designed to do that.. – Raymond Nijland May 27 '19 at 10:36
  • 2
    You can join tables in different schemas, but not different MySQL Server instances. I think even doing three joins will have better performance (if you use indexes well) than the table-scans you are doing with FIND_IN_SET(). – Bill Karwin May 27 '19 at 12:39

0 Answers0