Insert Chinese character into MySQL through node.js

Question

I am now connecting to an old MySQL db (version: 4.0.18-nt) with Node.js with mysql driver. The database default collation is latin1.

mysql character setting:

character set   latin1  latin1
character sets  latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5   latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5

When I insert Chinese Character into the db, it will become garbled code.

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

connection.query(`update user set chi_surname = '陳', chi_other_name = '陳大文' where user_id = '1'`
    , async function (error, results, fields) {
        if (error) throw error;
        console.log('The solution is: ', results);
        console.log(fields)
    });

Result in mysql

chi_surname   chi_other_name
�䒰          �䒰憭扳��

I tried to convert above text (�䒰�䒰憭扳��) to utf-8, then it show 陳大文. 

If I update the field in phyMyAdmin directly, it can update properly.

chi_surname   chi_other_name
陳            大文

It seems that the mysql driver will send the query to Mysql in utf-8 no matter what charset I put in the connection config.

I know that changing the default collation of the db to utf-8 can solve the problem, but as there are many old program calling the database as well, so changing db collation can't be an option.

I also tried by using the third party libraries (iconv-lite, encoding) to change the word to BIG5/latin1 before inserting, but still fail.

Is there any method I could insert Chinese character into this db? Thank you.

Node version:node v15.6.0
node-mysql version: 2.18.1

(from comment)

CREATE TABLE department (
    'dept_id' varchar(10) NOT NULL default '', 
    'dept_code' varchar(10) default NULL, 
    'chi_name' varchar(50) default NULL, 
    'eng_name' varchar(50) NOT NULL default ''
) TYPE=MyISAM 

Answer 1

A4FBA6C0C657 is the Big5 encoding for 牛池灣. So you need CHARACTER SET big5.

The connection, the column definition, etc.

Please provide SHOW VARIABLES LIKE 'char%'; and SHOW CREATE TABLE.

You seem to have this already:

var connection = mysql.createConnection({ ... , charset : 'big5'});

So the probably is probably elsewhere.

It is OK to have the column in a different charset than the connection; the conversion should be handled correctly.

Please check the MySQL version with

SELECT @@version;