使用mysqldump备份带有GEOMETRY列的表？ [英] Back up a table with a GEOMETRY column using mysqldump?

问题描述

当我使用mysqldump备份表时，它会将我的几何列输出为带引号的字符串，包含一些像\0这样的转义字符，还有一些字符在上面的ASCII范围内看起来像原始二进制字节。

当我尝试将转储还原到另一个数据库失败并显示错误：

无法从发送给Geometry字段的数据中获取GEOMETRY对象。

我尝试在命令行中添加--hex-blob，但这不会改变输出或解决问题。

我确定有人没有在MySQL中创建数据类型并忘记包含一种备份方法。我缺少什么？

谢谢。

解决方案

，这个错误在非空几何列中空几何值特别出现。

在我的例子中，空几何是合法的情况未知的几何体，所以我通过更改列来允许空值，然后运行 UPDATE来解决此问题... SET geom = NULL WHERE IsEmpty（geom）;

在此之后，我可以重新运行 mysqldump 并成功将生成的sql导入到单独的数据库中。

（说实话，我不确定空几何值是如何到达那里的 - 我甚至不知道创建空几何值的语法） p>

I recently created a MySQL table with a column of type GEOMETRY.

When I back up the table with mysqldump, it outputs my geometry column as a quoted string, containing some escaped characters like \0, and also some characters that look like raw binary bytes in the upper-ASCII range.

When I try to restore the dump to another database it fails with an error:

"Cannot get GEOMETRY object from the data you send to the Geometry field".

I tried adding --hex-blob to my command line but this does not change the output or fix the problem.

I'm sure someone didn't create a data type in MySQL and forget to include a way to back it up. What am I missing?

Thanks.

解决方案

In my case, this error appeared specifically with empty geometry values in a non-null geometry column.

In my case, the empty geometries were legitimate cases of unknown geometry, so I addressed this by changing the column to allow null values, and then running UPDATE ... SET geom = NULL WHERE IsEmpty(geom);

After this, I was able to re-run mysqldump and successfully import the resulting sql into a separate database.

(To be honest, I'm not sure how the empty geometry values got there in the first place - I don't even know the syntax to create an empty geometry value)

这篇关于使用mysqldump备份带有GEOMETRY列的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！