How to read non-latin characters from MySQL using Node?

Question

I have a node application that queries a MySQL database and writes results to a file.

The query result is a piece of text with translations to most world languages.

My issue is that most non ASCII chars don't display properly. For example

Wir möchten

Is written to file as:

Wir mÃ¶chten

This is how I connect to the database:

  const mysql = require("mysql");
  const connection = mysql.createConnection({
    host: "",
    user: "",
    password: "",
    database: "",
    charset: "utf8", // I have tried 'utf8'/'latin1'/'BIG5'
  });

This is the function that writes query result to file:

query(sql, (err, data) => {
  fs.writeFile("x.json", JSON.stringify(data), "utf8", function (err) {
    if (err) throw err;
    console.log("Saved!");
  });
});

score 1 · Accepted Answer · answered Dec 03 '21 at 16:26

I eventually found a way that works on the MySQL SELECT statement level:

SELECT CONVERT(CAST(CONVERT(column_name USING LATIN1) AS BINARY) USING UTF8) AS column_name from table_name;

I used the above statement as input to my node app and it worked for all the alphabets I need (Latin, Cyrillic, Chinese ...etc.).

Source.

score 1 · Answer 2 · answered Dec 03 '21 at 20:30

Ã¶ is "Mojibake" for ö. See Mojibake in Trouble with UTF-8 characters; what I see is not what I stored

As for the right way to fix the data you have, pick the right situation from the 7 cases here: http://mysql.rjweb.org/doc.php/charcoll#fixes_for_various_cases

Note that "double-encoding" is not the same as "Mojibake".

How to read non-latin characters from MySQL using Node?

2 Answers2