先講結論。在 SQLite 3 的環境下,不要用 BLOB 或者 binary TEXT 來存 IPv6 。

以下是實驗過程。為了顯示方便起見,無法顯示的字元以「■」代替。

首先是 MySQL 5.1.x 。

mysql> create table test.test (id int, value text, note varbinary(16));
mysql> insert test.test values (1, 'ab', x'ab');
mysql> insert test.test values (2, 'ab00', x'ab00'); 
mysql> insert test.test values (3, '00ab', x'00ab');
mysql> insert test.test values (4, 'ab00cd', x'ab00cd');
mysql> insert test.test values (5, 'abcd', x'abcd');
mysql> select * from test.test where note = x'ab';
+------+-------+------+
| id   | value | note |
+------+-------+------+
| 1    | ab    | ■   | 
+------+-------+------+
1 row in set (0.00 sec)

MySQL 支援 binary ,所以沒這個問題。

再來是 PostgreSQL 9.2.x 。

test=> CREATE TABLE test (id int, value bytea);
test=> INSERT INTO test VALUES (1, E'\\xAA');
test=> INSERT INTO test VALUES (2, E'\\xAA00');
test=> INSERT INTO test VALUES (3, E'\\x00AA');
test=> INSERT INTO test VALUES (4, E'\\xAA00AA');
test=> INSERT INTO test VALUES (5, E'\\xAAAA');
test=> SELECT * FROM test WHERE value = E'\\xAA';
id  | value
----+-------
1   | \xaa
(1 筆資料列)

PostgreSQL 用 bytea ,所以也沒有問題。

最後是 SQLite 3.7.x ,我試了 TEXT 和 BLOB 兩種方式。

sqlite> CREATE TABLE test (id INTEGER, value TEXT, note BLOB);
sqlite> INSERT INTO test VALUES (1, X'ab', X'ab');
sqlite> INSERT INTO test VALUES (2, X'ab00', X'ab');
sqlite> INSERT INTO test VALUES (3, X'ab', X'ab00');
sqlite> INSERT INTO test VALUES (4, X'ab00', X'ab00');
sqlite> SELECT * FROM test WHERE value IS X'ab00';
2|■|■
4|■|■
sqlite> SELECT * FROM test WHERE note IS X'ab00';
3|■|■
4|■|■

可以很明顯看出只要 binary TEXT 中含有 '00' 這個 byte 就會發生問題。Orz

 

回到我遇到的 IPv6 問題。

一開始是因為 PHPUnit 吐給我 "Failed asserting that two strings are equal." 的訊息,開始看問題在哪裡。

最後發現我的資料中有一組是 IPv6 2001:4541:9010::200 。

利用 inet_pton() 轉成 binary 以後是 X'20014541901000000000000000000200' ,但是再透過 SQLite 3 塞入資料庫的話,只會剩 X'200145419010' (2001:4541:9010:...) 。

於是開始試很多組 IPv6 ,發現 ::1 就爆炸了。 XD

實驗後,如同上面實驗結果,確認問題出在 '00' 這個 byte 。

Stack Overflow 找到的解法有兩種,一種是把 IPv6 原原本本的以 TEXT '2001:4541:9010::200' 存入,另一種是把 IPv6 轉成 INTEGER 存入,就可以避免這個問題。

 

11/29 Update: BLOB 是可以的。


Tags: IPv6, SQLite, MySQL, PostgreSQL, binary, bytea, text, blob


arrow
arrow
    創作者介紹
    創作者 repeat ❤️ 的頭像
    repeat ❤️

    旅行的記憶

    repeat ❤️ 發表在 痞客邦 留言(0) 人氣()