CODEWITHSUNDEEP
javascriptmysqlutf-8iso-8859-1
Maf
Maf

Reputation: 725

How to get data from a latin1 (iso-8859-1) database using javascript/nodejs?

I have an old mysql database (mysql 5.0.2) in latin1 and I want to get data from it. For non-ascii characters I'm getting always the same output (eg., Â, À and Á are being presented as something like 'ef bf bd' in hex), that's to say different chars being presented the same way.

I just need to get these chars differently so I can map each one to the right corresponding utf-8 char.

I've already been trying to change the charset but it's not working for me!

Would someone please help me to get some data making sense?

var mysql = require('mysql')

var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    //charset: "utf8mb4",
    //charset: "utf8",
    charset: "latin1",
    database : 'my_db'
})

con.connect()

var query = con.query("SELECT data from my_table where id='07'", function 
(error, results, fields) {
    var b = Buffer.from (results[0].data)
    console.log ('Retrieved data in hex -> ', b)
})

con.end()

When I go to the db and update the data to some ascii-only string, I can get the data in js without any problem, but when I replace that data to something like 'á' or 'à', I get always 'ef bf bd' in hex (-17 -65 -67 in decimal).

Upvotes: 0

Views: 563

Answers (1)

Rick James
Rick James

Reputation: 142356

Latin1 hex EF BF BD translates to �. Perhaps you are referring to a BOM (Byte-Order-Mark), which is EF BB BF, which looks like . See http://mysql.rjweb.org/doc.php/charcoll#bom_byte_order_mark .

Â, À and Á often show up when improperly mixing latin1 and utf8.

For further discussion, please provide a snippet of text in hex and characters. And/or see Trouble with UTF-8 characters; what I see is not what I stored for troubleshooting common problems.

OH! MySQL 4.0 had no concept of character sets. You could store and fetch strings without anything being tested or anything happening to them. However, things like LENGTH() provided byte counts, not character counts. And inequality string comparisons (col_a < col_b) would not provide reasonable results for non-ascii characters.

It might be safe to do nothing in PHP with your characters. Just feed them in and out of VARCHAR/TEXT (or BLOB) columns.

Upvotes: 1

Related Questions