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!");
  });
});

Seif · Accepted Answer · 2021-12-03 16:26:45Z

1

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.

answered Dec 3, 2021 at 16:26

Seif

6462 gold badges12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rick James · Accepted Answer · 2021-12-03 20:30:25Z

1

Ã¶ 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".

answered Dec 3, 2021 at 20:30

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related