[Feature] support utf8mb4 for mysql external table

[nextdreamblue]

Search before asking

• I had searched in the issues and found no similar issues.

Description

I need a mysql external table with utf8mb4 charset, but only support charset utf8 right now.

If it can set charset when create mysql external table, it is better.

for example:
if there is a table with utf8mb4 charset on MySQL server.

CREATE TABLE `abc_utf8mb4` (
 `aaaa` char(100) NOT NULL DEFAULT '',
 `bbbb` varchar(16000) DEFAULT '',
 PRIMARY KEY (`aaaa`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into abc_utf8mb4 values('111','😀 😃 😄 😁 😆 😅 😂 ');

on doirs, i create mysql external table for mysql table abc_utf8mb4, now like this:

CREATE EXTERNAL TABLE `aaa` (
 `aaaa` char(100) NULL COMMENT "",
 `bbbb` varchar(65533) NULL COMMENT ""
) ENGINE=MYSQL
COMMENT "MYSQL"
PROPERTIES (
"host" = "172.16.64.66",
"port" = "3306",
"user" = "root",
"password" = "",
"database" = "aaaa",
"table" = "abc_utf8mb4"
);

select * from aaa;

get result like this:
+------+----------------+
| aaaa | bbbb |
+------+----------------+
| 111 | ? ? ? ? ? ? ? |
+------+----------------+

i expect result like this:
+------+-------------------------------------+
| aaaa | bbbb |
+------+-------------------------------------+
| 111 | 😀 😃 😄 😁 😆 😅 😂
+------+-------------------------------------+

Use case

there is a table with utf8mb4 charset on MySQL server.

CREATE TABLE `abc_utf8mb4` (
 `aaaa` char(100) NOT NULL DEFAULT '',
 `bbbb` varchar(16000) DEFAULT '',
 PRIMARY KEY (`aaaa`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into abc_utf8mb4 values('111','😀 😃 😄 😁 😆 😅 😂 ');

on doirs, i want create mysql external table for mysql table abc_utf8mb4, now like this:

CREATE EXTERNAL TABLE `aaa` (
 `aaaa` char(100) NULL COMMENT "",
 `bbbb` varchar(65533) NULL COMMENT ""
) ENGINE=MYSQL
COMMENT "MYSQL"
PROPERTIES (
"host" = "172.16.64.66",
"port" = "3306",
"user" = "root",
"password" = "",
"database" = "aaaa",
"table" = "abc_utf8mb4"
);

select * from aaa;

get result like this:
+------+----------------+
| aaaa | bbbb |
+------+----------------+
| 111 | ? ? ? ? ? ? ? |
+------+----------------+

i expect result like this:
+------+-------------------------------------+
| aaaa | bbbb |
+------+-------------------------------------+
| 111 | 😀 😃 😄 😁 😆 😅 😂
+------+-------------------------------------+

Related issues

#9137

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct