Support to config character set for mysql

[icefery]

What happens?

• Prepare

  attach '' as wrong_mysql_catalog  (type 'mysql', secret 'wrong_mysql_catalog');
  attach '' as correct_mysql_catalog(type 'mysql', secret 'correct_mysql_catalog');
  
  set mysql_debug_show_queries = true;

• Wrong

  D select * from mysql_query('wrong_mysql_catalog', E'show variables like \'%character%\'');
  SET character_set_results = 'utf8mb4';
  
  START TRANSACTION
  
  show variables like '%character%'
  
  COMMIT
  
  ┌──────────────────────────┬────────────────────────────┐
  │      Variable_name       │           Value            │
  │         varchar          │          varchar           │
  ├──────────────────────────┼────────────────────────────┤
  │ character_set_client     │ latin1                     │
  │ character_set_connection │ latin1                     │
  │ character_set_database   │ latin1                     │
  │ character_set_filesystem │ binary                     │
  │ character_set_results    │ utf8mb4                    │
  │ character_set_server     │ latin1                     │
  │ character_set_system     │ utf8                       │
  │ character_sets_dir       │ /usr/share/mysql/charsets/ │
  └──────────────────────────┴────────────────────────────┘
  Run Time (s): real 0.256 user 0.089240 sys 0.009045
  D select * from mysql_query('wrong_mysql_catalog', E'select \'中文\' as aaa');
  SET character_set_results = 'utf8mb4';
  
  START TRANSACTION
  
  select '中文' as aaa
  
  COMMIT
  
  ┌─────────┐
  │   aaa   │
  │ varchar │
  ├─────────┤
  │ 中文  │
  └─────────┘
  Run Time (s): real 0.246 user 0.091053 sys 0.002791

• Right

  D select * from mysql_query('correct_mysql_catalog', E'show variables like \'%character%\'');
  SET character_set_results = 'utf8mb4';
  
  START TRANSACTION
  
  show variables like '%character%'
  
  COMMIT
  
  ┌──────────────────────────┬────────────────────────────────────┐
  │      Variable_name       │               Value                │
  │         varchar          │              varchar               │
  ├──────────────────────────┼────────────────────────────────────┤
  │ character_set_client     │ utf8mb4                            │
  │ character_set_connection │ utf8mb4                            │
  │ character_set_database   │ utf8mb4                            │
  │ character_set_filesystem │ binary                             │
  │ character_set_results    │ utf8mb4                            │
  │ character_set_server     │ utf8mb4                            │
  │ character_set_system     │ utf8mb3                            │
  │ character_sets_dir       │ /opt/bitnami/mysql/share/charsets/ │
  └──────────────────────────┴────────────────────────────────────┘
  D select * from mysql_query('correct_mysql_catalog', E'select \'中文\' as aaa');
  SET character_set_results = 'utf8mb4';
  
  START TRANSACTION
  
  select '中文' as aaa
  
  COMMIT
  
  ┌─────────┐
  │   aaa   │
  │ varchar │
  ├─────────┤
  │ 中文    │
  └─────────┘

To Reproduce

1. Set mysql server;
2. Test ducdkb behavior;

OS:

Ubuntu 24.04.1 LTS/x86_64

DuckDB Version:

v1.1.3 19864453f7

DuckDB Client:

cli

Hardware:

No response

Full Name:

icefery

Affiliation:

[email protected]

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have