Query text with emoji show ����? But mysql1 no this problem.

[Arnold134777]

env:

• mysql2: v3.14.0；

• mysql: v2.18.1；

• starrocks: v3.4.1；

• show create database example_db;
  +------------+------------------------------+
  | Database | Create Database |
  +------------+------------------------------+
  | example_db | CREATE DATABASE example_db |
  +------------+------------------------------+

• show create table emoji_test;
  +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table |
  +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | emoji_test | CREATE TABLE emoji_test (
  id int(11) NULL COMMENT "",
  name varchar(255) NULL COMMENT ""
  ) ENGINE=OLAP
  DUPLICATE KEY(id, name)
  DISTRIBUTED BY RANDOM
  PROPERTIES (
  "bucket_size" = "4294967296",
  "compression" = "LZ4",
  "fast_schema_evolution" = "true",
  "replicated_storage" = "true",
  "replication_num" = "1"
  ); |
  +------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

• insert
  INSERT INTO emoji_test (name) VALUES
  ('你好 😊'),
  ('Python 🐍'),
  ('庆祝 🎉'),
  ('爱心 ❤️'),
  ('笑脸 😃');

Test

mysql2:

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: "localhost",
  port: 9030,
  user: "root",
  database: "example_db",
});

const res = await pool.execute("select * from emoji_test;");
for (const row of res) {
  console.log(row);
}

// output:
[
  {
    id: null,
    name: "Python ����",
  }, {
    id: null,
    name: "你好 ����",
  }, {
    id: null,
    name: "庆祝 ����",
  }, {
    id: null,
    name: "爱心 ❤️",
  }, {
    id: null,
    name: "笑脸 ����",
  }
]
[
  `id` INT, `name` VARCHAR(192)
]

mysql: https://github.com/mysqljs/mysql

import mysql from 'mysql';

const conn = mysql.createConnection({
  host: 'localhost',
  port: 9030,
  user: 'root',
  database: "example_db",
});

conn.query("select * from emoji_test;", (err, result) => {
  if (err) {
    console.log(err);
  } else {
    for (const row of result) {
      console.log(row);
    }
  }
});

output:
RowDataPacket {
  id: null,
  name: "Python 🐍",
  parse: [Function: parse],
  _typeCast: [Function: typeCast],
}
RowDataPacket {
  id: null,
  name: "你好 😊",
  parse: [Function: parse],
  _typeCast: [Function: typeCast],
}
RowDataPacket {
  id: null,
  name: "庆祝 🎉",
  parse: [Function: parse],
  _typeCast: [Function: typeCast],
}
RowDataPacket {
  id: null,
  name: "爱心 ❤️",
  parse: [Function: parse],
  _typeCast: [Function: typeCast],
}
RowDataPacket {
  id: null,
  name: "笑脸 😃",
  parse: [Function: parse],
  _typeCast: [Function: typeCast],
}

[kris-swann]

Also using StarRocks and seeing this, found a workaround by setting typeCast on createPool

createPool({
  ...
  typeCast: (field, next) => {
    if (field.type === "VAR_STRING" || field.type === "STRING") {
      return field.buffer()?.toString("utf8");
    }
    return next();
  },
  ...
});