mysql - 尝试将坐标从 .txt 文件加载到 MySQL 时出错

Question

我正在尝试将数据从文本文件加载到数据库中，但数据库中的一列的类型为 Point(lat,lng)。

.txt 文件中的行示例:

1    3    exampleString1    POINT(34.123456, 45.678909)   default    NULL    strg2

MySQL 查询:

LOAD DATA LOCAL INFILE '/location/file.txt' INTO TABLE ExampleTable;

MySQL 工作台错误:错误代码:1416。无法从发送到 GEOMETRY 字段的数据中获取几何对象 0.003 秒

Answer 1

尝试:

文件:

1;3;"example String1";POINT(34.123456, 45.678909);strg2

MySQL 命令行:

mysql> DROP TABLE IF EXISTS `ExampleTable`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `ExampleTable` (
    ->   `column0` INT UNSIGNED NOT NULL,
    ->   `column1` INT UNSIGNED NOT NULL,
    ->   `column2` VARCHAR(255) NOT NULL,
    ->   `column3` GEOMETRY NOT NULL,
    ->   `column4` VARCHAR(255) NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA LOCAL INFILE '/location/file.txt'
    ->   INTO TABLE `ExampleTable`
    ->   FIELDS TERMINATED BY ';' ENCLOSED BY '"'
    ->   LINES TERMINATED BY '\r'
    ->   (`column0`, `column1`, `column2`, @`column3`, `column4`)
    ->   SET `column3` := ST_GeomFromText(REPLACE(@`column3`, ',', ''));
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT
    ->   `column0`,
    ->   `column1`,
    ->   `column2`,
    ->   ST_AsText(`column3`) `column3`,
    ->   `column4`
    -> FROM
    ->   `ExampleTable`\G
*************************** 1. row ***************************
column0: 1
column1: 3
column2: example String1
column3: POINT(34.123456 45.678909)
column4: strg2

1 row in set (0.00 sec)