集群与client 端字符集不一致时,
导出select 语句中表名、
列名或别名包含中文字
符需要在执行前设置客户端charset 为gbk。如:
集群字符集为UTF8,客户端字符集为gbk 时,select 中文字段进行数据导出需要
连接数据库时指定集群客户端默认字符集为gbk,否则导出会报错。
示例中所用的表及数据:
gbase> set global gcluster_extend_ident=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> create table t(天varchar(10), b varchar(10));
Query OK, 0 rows affected (Elapsed: 00:00:00.13)
gbase> insert into t values('中','国');
Query OK, 1 row affected (Elapsed: 00:00:00.08)
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953(2022-04-10)
南大通用数据技术股份有限公司
1266
字符集状态:
客户端编辑器字符集为GBK
集群字符集为
gbase> show variables like '%character_set%';
+--------------------------+-------------+
| Variable_name
| Value
|
+--------------------------+-------------+
| character_set_client
| utf8
|
| character_set_connection | utf8
|
| character_set_database
| utf8
|
| character_set_filesystem | binary
|
| character_set_results
| utf8
|
| character_set_server
| utf8
|
| character_set_sort
| binary
|
| character_set_system
| utf8mb4
|
+--------------------------+-------------+
直接导出报错,因为字符集不同无法识别中文字段名:
$ gccli
GBase client 9.5.2.39.126761. Copyright (c) 2004-2021, GBase.
All Rights
Reserved.
gbase> use testdb;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase>
rmt:select
天
from
tc into
outfile '/home/gbase/2.dat'
fields
terminated by ',' WRITEMODE BY OVERWRITES ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your GBase server version for the right syntax to use near '??
from tc into outfile '/home/gbase/2.dat' fields terminated by ',' WRITEMODE B' at
line 1
gbase> select 天from tc into outfile '/home/gbase/2.dat' fields terminated by
',' WRITEMODE BY OVERWRITES ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your GBase server version for the right syntax to use near '??
from tc into outfile '/home/gbase/2.dat' fields terminated by ',' WRITEMODE B' at
line 1
连接数据库时指定客户端默认字符集为gbk,可成功导出数据
$ gccli --default_character_set=gbk
GBase client 9.5.2.39.126761. Copyright (c) 2004-2021, GBase.
All Rights
Reserved.
gbase> show variables like '%character_set%';
+--------------------------+-----------+
| Variable_name
| Value
|
GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953(2022-04-10)
南大通用数据技术股份有限公司
1267
+--------------------------+-----------+
| character_set_client
| gbk
|
| character_set_connection | gbk
|
| character_set_database
| utf8
|
| character_set_filesystem | binary
|
| character_set_results
| gbk
|
| character_set_server
| utf8
|
| character_set_sort
| binary
|
| character_set_system
| utf8mb4
|
+--------------------------+-----------+
gbase> use testdb;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select 天from tc into outfile '/home/gbase/2.dat' fields terminated by
',' WRITEMODE BY OVERWRITES ;
Query OK, 1 row affected (Elapsed: 00:00:00.07)
gbase> rmt:select 天from tc into outfile '/home/gbase/天_2.dat' fields
terminated by ',' WRITEMODE BY OVERWRITES ;
Query OK, 1 row affected (Elapsed: 00:00:00.54)