使用UTF-8编码导出HSQLDB数据库

问题描述

我正在尝试导出GeoTools HSQL 2数据库并将其重新加载到HSQL 1中，以用于需要较旧数据库格式的旧系统.表格中包含诸如度数符号之类的字符.但是，它以转义序列\u0080而不是编码字符的形式出现.我需要解决此问题，或者需要HSQL 1导入将转义的字符转换回正确的编码.

I'm trying to export the GeoTools HSQL 2 database and load it back into HSQL 1 for a legacy system that needs the older database format. The tables include characters like the degree symbol. However, it's coming out as the escape sequence \u0080 rather the encoded character. I need to either fix that or have HSQL 1 import convert the escaped characters back into the correct encoding.

例如

cp modules/plugin/epsg-hsql/src/main/resources/org/geotools/referencing/factory/epsg/EPSG.zip /tmp
cd /tmp
unzip EPSG.zip
java -jar hsqldb-2.4.1.jar
# For the file, put jdbc:hsqldb:file:/tmp/EPSG
SELECT 'epsg-dump'

在结果中，我看到了类似\u00b5的东西:

And in the results I see things like this \u00b5:

INSERT INTO EPSG_ALIAS VALUES(389,'epsg_unitofmeasure',9109,7302,'\u00b5rad','')

在hsqldb中，我不确定如何控制要写入的数据的编码，前提是这是要查找的正确位置:

Looking into hsqldb, I'm not sure how to control the encoding the of the data being written, assuming that this is the correct location to look:

https://github.com/ryenus/hsqldb/blob/master/src/org/hsqldb/scriptio/ScriptWriterText.java

推荐答案

您可以使用以下过程:

1. 在源数据库中，使用与原始表完全相同的列创建TEXT表.对每个表使用CREATE TEXT TABLE thecopyname (LIKE thesourcename).
2. 对每个copy表使用SET TABLE thecopyname SOURCE 'thecopyname.csv;encoding=UTF-8'.
3. 使用SELECT * FROM thesourcename插入每个thecopyname表.
4. 为每个thecopyname
使用SET TABLE thecopyname SOURCE OFF
5. 您现在将拥有几个具有UTF8编码的thecopyname.csv文件(每个文件都有自己的名称).
6. 对目标数据库使用相反的过程.您需要显式创建TEXT表，然后使用SET TABLE thecopyname SOURCE 'thecopyname.csv;encoding=UTF-8'

1. In the source database, create TEXT tables with exactly the same columns as the original tables. Use CREATE TEXT TABLE thecopyname (LIKE thesourcename) for each table.
2. Use SET TABLE thecopyname SOURCE 'thecopyname.csv;encoding=UTF-8' for each of the copy tables.
3. INSERT into each thecopyname table with SELECT * FROM thesourcename.
4. Use SET TABLE thecopyname SOURCE OFF for each thecopyname
5. You will now have several thecopyname.csv files (each with its own name) with UTF8 encoding.
6. Use the reverse procedure on the target database. You need to explicity create the TEXT tables then use SET TABLE thecopyname SOURCE 'thecopyname.csv;encoding=UTF-8'

这篇关于使用UTF-8编码导出HSQLDB数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！