MYSQL CSV 导入 - 无法从发送到 GEOMETRY 字段的数据中获取几何对象

我的服务器上有 CSV 文件，其中数据如下所示；

16777216,17039359,"APNIC Debogon Project"
17367040,17432575,"Tmnet, Telekom Malaysia Bhd."
17435136,17435391,"APNIC Debogon Project"
17498112,17563647,"CJ-HELLOVISION"
17563648,17825791,"Beijing Founder Broadband Network Technology Co.,L"
17825792,18087935,"Allocated to KRNIC Member."
18153984,18154239,"Double Cast"
18157056,18163711,"FAMILY NET JAPAN INCORPORATED"

我试图将其插入到我的表中，其结构如下；

ipoid    INTEGER  11 NOT NULL PRIMARY KEY
beginip  INTEGER  14 NOT NULL UNSIGNED
endip    INTEGER  14 NOT NULL UNSIGNED
org      VARCHAR  255
ip_poly  POLYGON

我还在 ip_poly 字段上创建了一个空间索引

我正在尝试使用以下代码插入 csv 数据

LOAD DATA INFILE "/home/GeoIPOrg.csv"
INTO TABLE crm_geo_org
FIELDS
TERMINATED BY ","
ENCLOSED BY "\""
LINES
TERMINATED BY "\n"
(@beginIp,@endIp,@org)
SET
ipoid      := NULL,
beginip := @beginIp,
endip   := @endIp,
ip_poly := GEOMFROMWKB(POLYGON(LINESTRING(
/* clockwise, 4 points and back to 0 */
POINT(@beginIp, -1), /* 0, top left */
POINT(@endIp,   -1), /* 1, top right */
POINT(@endIp,    1), /* 2, bottom right */
POINT(@beginIp,  1), /* 3, bottom left */
POINT(@beginIp, -1)  /* 0, back to start */
))),
org:= @org;

但是，当我尝试这样做时，我收到此错误

错误 1416 (22003)：无法从发送到 GEOMETRY 字段的数据中获取几何对象

有任何想法吗？

在以后的版本中MySQL你不需要WKB/ WKT转换来构建几何对象。

Also, Polygon这里有点矫枉过正了：MBR可以从单个构建LineString也无所谓。

改变你的ip_poly to iprange LINESTRING NOT NULL并使用这个：

LOAD DATA INFILE "/home/GeoIPOrg.csv"
INTO TABLE
        crm_geo_org
FIELDS
        TERMINATED BY
                ","
        ENCLOSED BY
                "\""
LINES
        TERMINATED BY "\n"
        (@beginIp, @endIp, @org)
SET     ipoid   := NULL,
        beginip := @beginIp,
        endip   := @endIp,
        iprange := GeomFromWKB(
                        LineString(
                                Point(@beginIp, -1),
                                Point(@endIp, 1)
                                )
                        ),
        org     := @org;