盗墓笔记,懒人听书,怎样写网络小说

主頁(yè) > 知識(shí)庫(kù) > SQL Server數(shù)據(jù)遷移至PostgreSQL出錯(cuò)的解釋以及解決方案

SQL Server數(shù)據(jù)遷移至PostgreSQL出錯(cuò)的解釋以及解決方案

問(wèn)題重現(xiàn)：

1、PG客戶端:

postgres=# create table text_test (id int,info text);
CREATE TABLE
postgres=# insert into text_test values (1,E'\0x00');
ERROR: invalid byte sequence for encoding "UTF8": 0x00

2、SQL Server產(chǎn)生數(shù)據(jù)

create table test_varchar(id int,name varchar(20));
insert into test_varchar values (1, 'name' + char(0));
insert into test_varchar values (1, 'name' + '');

然后通過(guò)java程序進(jìn)行獲取數(shù)據(jù)并插入到PG，同樣會(huì)得到錯(cuò)誤信息：

invalid byte sequence for encoding "UTF8": 0x00

首先我們認(rèn)為此為gb2312轉(zhuǎn)化到UTF8時(shí)，發(fā)生了無(wú)法轉(zhuǎn)化的錯(cuò)誤。經(jīng)查UTF8是變長(zhǎng)的, 1-6個(gè)字節(jié)。他的編碼規(guī)則如下：

Bits	Last code point	Byte 1	Byte 2	Byte 3	Byte 4	Byte 5	Byte 6
7	U+007F	0xxxxxxx
11	U+07FF	110xxxxx	10xxxxxx
16	U+FFFF	1110xxxx	10xxxxxx	10xxxxxx
21	U+1FFFFF	11110xxx	10xxxxxx	10xxxxxx	10xxxxxx
26	U+3FFFFFF	111110xx	10xxxxxx	10xxxxxx	10xxxxxx	10xxxxxx
31	U+7FFFFFFF	1111110x	10xxxxxx	10xxxxxx	10xxxxxx	10xxxxxx	10xxxxxx

而0x00是符合UTF8規(guī)則的。這就使我們非常詫異。然后我們發(fā)現(xiàn)有兩點(diǎn)繼而確認(rèn)了問(wèn)題：
1、

PostgreSQL doesn't support storing NULL (\0x00) characters in text fields (this is obviously different from the database NULL value, which is fully supported).

If you need to store the NULL character, you must use a bytea field - which should store anything you want, but won't support text operations on it.

Given that PostgreSQL doesn't support it in text values, there's no good way to get it to remove it. You could import your data into bytea and later convert it to text using a special function (in perl or something, maybe?), but it's likely going to be easier to do that in preprocessing before you load it.

Source:http://stackoverflow.com/questions/1347646/postgres-error-on-insert-error-invalid-byte-sequence-for-encoding-utf8-0x0

2、

Terminating character	Indicated by
Tab	\t This is the default field terminator.
Newline character	\n This is the default row terminator.
Carriage return/line feed	\r
Backslash1	\\
Null terminator (nonvisible terminator)2	\0
Any printable character (control characters are not printable, except null, tab, newline, and carriage return)	(*, A, t, l, and so on)
String of up to 10 printable characters, including some or all of the terminators listed earlier	(\t, end, !!!!!!!!!!, \t—\n, and so on)

Source:http://msdn.microsoft.com/en-us/library/ms191485.aspx

由此我們確定，是pg對(duì)null的處理和SQL Server處理是不相同的，所以在這里出現(xiàn)了錯(cuò)誤。

而導(dǎo)致這一問(wèn)題的PG具體代碼如下（src/backend/utils/mb/wchar.c的pg_verify_mbstr_len）：

if (!IS_HIGHBIT_SET(*mbstr))
    {
      if (*mbstr != '\0')
      {
        mb_len++;
        mbstr++;
        len--;
        continue;
      }
      if (noError)
        return -1;
      report_invalid_encoding(encoding, mbstr, len);
    }

#define IS_HIGHBIT_SET(ch)   ((unsigned char)(ch)  HIGHBIT)
#define HIGHBIT         (0x80)

report_invalid_encoding函數(shù)是將錯(cuò)誤信息返回，也就是

invalid byte sequence for encoding "UTF8": 0x00
而真正導(dǎo)致這一問(wèn)題的就是：
!IS_HIGHBIT_SET(*mbstr)當(dāng)*mbstr為0x00時(shí)進(jìn)入判斷，然后進(jìn)而判斷*mbstr是否為\0,當(dāng)為\0時(shí)，直接進(jìn)入函數(shù)report_invalid_encoding報(bào)錯(cuò)。

所以出現(xiàn)此問(wèn)題的原因是PG和SQL Server對(duì)null的處理是不相同的。

處理方案：

1、將SQL Server源數(shù)據(jù)進(jìn)行修改方法，

UPDATE: This seems to work:
 
Select * from TABLE
where UNICODE(SUBSTRING(naughtyField, LEN(naughtyField), 1)) = 0
So:
 
Update TABLE
SET naughtyField = SUBSTRING(naughtyField, 1, LEN(naughtyField) - 1)
where UNICODE(SUBSTRING(naughtyField, LEN(naughtyField), 1)) = 0
Source:http://stackoverflow.com/questions/3533320/sql-server-remove-end-string-character-0-from-data

2、對(duì)應(yīng)用進(jìn)行修改，獲取到SQL Server數(shù)據(jù)時(shí)，將數(shù)據(jù)進(jìn)行轉(zhuǎn)化，和第一種方法異曲同工。

您可能感興趣的文章:

在.NET Core類庫(kù)中使用EF Core遷移數(shù)據(jù)庫(kù)到SQL Server的方法
sql server2008數(shù)據(jù)庫(kù)遷移的兩種方法
如何將Oracle的一個(gè)大數(shù)據(jù)表快速遷移到 Sqlserver2008數(shù)據(jù)庫(kù)(圖文教程)
如何把sqlserver數(shù)據(jù)遷移到mysql數(shù)據(jù)庫(kù)及需要注意事項(xiàng)
在SQL Server中遷移數(shù)據(jù)的幾種方法
將ACCESS數(shù)據(jù)庫(kù)遷移到SQLSERVER數(shù)據(jù)庫(kù)兩種方法(圖文詳解)
sqlserver數(shù)據(jù)庫(kù)遷移后,孤立賬號(hào)解決辦法
使用SQL SERVER存儲(chǔ)過(guò)程實(shí)現(xiàn)歷史數(shù)據(jù)遷移方式

標(biāo)簽：池州楚雄黃石安順白山舟山菏澤呼倫貝爾

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《SQL Server數(shù)據(jù)遷移至PostgreSQL出錯(cuò)的解釋以及解決方案》，本文關(guān)鍵詞 SQL,Server,數(shù)據(jù),遷,移至,PostgreSQL,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。