Resolving 'Illegal-mix-of-collations' MYSQL error in node

Question

I am connecting to a mysql database from node using mysql2 and have been experiencing intermittent illegal-mix-of-collations errors - see this question. The collation of the database is utf8mb4_0900_ai_ci.

I have tried adding charset: 'utf8mb4_0900_ai_ci' to my connection config:

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    database: 'test',
    charset: 'utf8mb4_0900_ai_ci'
});

Based on this answer charset and collation are not interchangeable, which would suggest 'utf8mb4_0900_ai_ci' is an invalid charset value, but it seems to have fixed the problem and I'd prefer a solution that works at connection level, as it's cleaner that setting collation on individual columns in my queries (as advised here).

Is this a valid approach to resolving these issues? If not, is there a better solution?

The charset of the database is utf8mb4.

Answer 1

charset: 'utf8mb4_0900_ai_ci'

That's a collation. The "charset" is utf8mb4.

The "connection" is stating what encoding (charset) is in use in the client. MySQL will convert between charsets if necessary, but it is based on the connection charset and the column charset.