创建测试表

create database xxx;
use xxx;
create table abcd (a longblob);
mysql> insert into abcd values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into abcd values ('1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into abcd values ('担任的社会职务包括福建省太阳能光伏商会会长');
Query OK, 1 row affected (0.00 sec)

默认mysqldump导出二进制列

mysqldump -uroot -p -P 5757 --databases xxx abcd > abcd.sql
可以看到mysql有自己的格式(_binary ‘1’)

LOCK TABLES `abcd` WRITE;
/*!40000 ALTER TABLE `abcd` DISABLE KEYS */;
INSERT INTO `abcd` VALUES (_binary '1'),(_binary '1'),(_binary '担任的社会职务包括福建省太阳能光伏商会会长');
/*!40000 ALTER TABLE `abcd` ENABLE KEYS */;
UNLOCK TABLES;

使用mysqldump指定–hex-blob导出

mysqldump -uroot -p -P 5757 --extended-insert=FALSE --hex-blob --databases xxx abcd > abcd.sql

查看mysqldump导出内容：

INSERT INTO `abcd` VALUES (0x31);
INSERT INTO `abcd` VALUES (0x31);
INSERT INTO `abcd` VALUES (0xE68B85E4BBBBE79A84E7A4BEE4BC9AE8818CE58AA1E58C85E68BACE7A68FE5BBBAE79C81E5A4AAE998B3E883BDE58589E4BC8FE59586E4BC9AE4BC9AE995BF);

在LightDB-A中恢复

说明默认上述0x开头的语句不支持，LightDB中插入要加引号，而MySQL导出来的没有引号，并且LightDB的16进制是\x开头，MySQL的16进制是0x开头

CREATE TABLE abcd (a bytea);
lightdb@postgres=# INSERT INTO `abcd` VALUES (0x31);
ERROR:  syntax error at or near "x31"
LINE 1: INSERT INTO `abcd` VALUES (0x31);

lightdb@postgres=# INSERT INTO `abcd` VALUES (0xE68B85E4BBBBE79A84E7A4BEE4BC9AE8818CE58AA1E58C85E68BACE7A68FE5BBBAE79C81E5A4AAE998B3E883BDE58589E4BC8FE59586E4BC9AE4BC9AE995BF);
NOTICE:  identifier "xe68b85e4bbbbe79a84e7a4bee4bc9ae8818ce58aa1e58c85e68bace7a68fe5bbbae79c81e5a4aae998b3e883bde58589e4bc8fe59586e4bc9ae4bc9ae995bf" will be truncated to "xe68b85e4bbbbe79a84e7a4bee4bc9ae8818ce58aa1e58c85e68bace7a68fe5"
ERROR:  syntax error at or near "xE68B85E4BBBBE79A84E7A4BEE4BC9AE8818CE58AA1E58C85E68BACE7A68FE5BBBAE79C81E5A4AAE998B3E883BDE58589E4BC8FE59586E4BC9AE4BC9AE995BF"
LINE 1: INSERT INTO `abcd` VALUES (0xE68B85E4BBBBE79A84E7A4BEE4BC9AE...

LightDB如何将16进制转换为文本

SELECT convert_from (decode (E'E68B85E4BBBBE79A84E7A4BEE4BC9AE8818CE58AA1E58C85E68BACE7A68FE5BBBAE79C81E5A4AAE998B3E883BDE58589E4BC8FE59586E4BC9AE4BC9AE995BF', 'hex'),'UTF8');
                convert_from                
--------------------------------------------
 担任的社会职务包括福建省太阳能光伏商会会长
(1 row)

PG中16进制转换成PG的16进制格式，然后再转换成文本

postgres=# select decode('e68b85e4bbbbe79a84e7a4bee4bc9ae8818ce58aa1e58c85e68bace7a68fe5bbbae79c81e5a4aae998b3e883bde58589e4bc8fe59586e4bc9ae4bc9ae995bf','hex') ;
                                                              decode                                                              
----------------------------------------------------------------------------------------------------------------------------------
 \xe68b85e4bbbbe79a84e7a4bee4bc9ae8818ce58aa1e58c85e68bace7a68fe5bbbae79c81e5a4aae998b3e883bde58589e4bc8fe59586e4bc9ae4bc9ae995bf
(1 row)
lightdb@postgres=# select convert_from('\xE68B85E4BBBBE79A84E7A4BEE4BC9AE8818CE58AA1E58C85E68BACE7A68FE5BBBAE79C81E5A4AAE998B3E883BDE58589E4BC8FE59586E4BC9AE4BC9AE995BF'::bytea,'UTF8');
                convert_from                
--------------------------------------------
 担任的社会职务包括福建省太阳能光伏商会会长
(1 row)

导出csv

select a 
 into outfile '/data/mysql57/mysql_data/abcd.csv' 
 fields terminated by ','
 lines terminated by '\n'
 from xxx.abcd;

[root@hs-10-20-30-193 yc]# cat /data/mysql57/mysql_data/abcd.csv
1
1
担任的社会职务包括福建省太阳能光伏商会会长

lightdb@test=# COPY abcd FROM '/home/lightdb/yc/abcd.csv' DELIMITER ',' CSV HEADER;
COPY 3
lightdb@postgres=# select * from abcd;
                                                                a                                                                 
--------------------------------------------------------------------------------------------
\x31
\xe68b85e4bbbbe79a84e7a4bee4bc9ae8818ce58aa1e58c85e68bace7a68fe5bbbae79c81e5a4aae998b3e883bde58589e4bc8fe59586e4bc9ae4bc9ae995bf
(2 rows)
lightdb@postgres=# select convert_from(a,'UTF8') from abcd;
                convert_from                
--------------------------------------------
 1
 担任的社会职务包括福建省太阳能光伏商会会长
(2 rows)

参考：
https://www.coder.work/article/2605314
https://stackoverflow.com/questions/51038905/how-do-i-get-a-string-value-from-its-hex-in-postgresql
https://www.postgresql.org/docs/current/datatype-binary.html