mysql点坐标的导出/导入问题

2hh7jdfx 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(361)

我在导出mysql数据库并将其从本地导入到prod时遇到了一个问题：
（我通过phpmyadmin以“简单模式”和默认选项导出和导入。）

源数据库：本地环境MySQL5.7.19（工作）

表格： lieux |innodb | utf8 | unicode | ci
列：
名称=> coords 类型=> point 值=> 'POINT(48.6863316 6.1703782)',0 （好的那个）

目标数据库：生产环境MySQL5.6。？

表格： lieux |innodb | utf8 | unicode | ci
列：
名称=> coords 类型=> point 值=> 'POINT(-0.000000000029046067630853117 -3.583174595546599e227)',0 （坏的那个）
导出/导入后，点值更改，所有坐标都为false。
在vscode编辑器中打开.sql export时，也会出现一个奇怪的显示：
点值如下所示：

（文件为utf-8格式）。
你知道utf-8在mysql中的坐标点是否有问题，或者prod-server上较旧的mysql版本是否会导致这个问题？我应该使用utf8\u mb4字符集吗？

编辑：添加更多详细信息

在我的项目（laravel+google maps javascript api）中，我（在本地环境中）用以下坐标创建一个地方： 48°41'10.8"N 6°10'13.4"E ，存储在数据库中作为 POINT(48.6863316 6.1703782) 位于：

然后，我进入导出选项卡中的phpmyadmin并进行一个简单的sql导出（使用默认选项）。
然后我在prod服务器上登录phpmyadmin，进入import选项卡并导入我的sql文件（也使用默认选项）。
当我查看新数据库（在prod服务器上）时，值存储为 POINT(-0.000000000029046067630853117 -3.583174595546599e227) ，并位于此处（在google maps通过删除多余字符进行更正后）：

我通过使用heidi sql而不是phpmyadmin来解决这个问题，phpmyadmin以不同的方式存储值：
在phpmyadmin export.sql文件中：

在heidi sql export.sql文件中：

编辑：导出文件

-- phpMyAdmin SQL Dump
-- version 4.7.4
-- https://www.phpmyadmin.net/
--
-- Hôte : 127.0.0.1:3306
-- Généré le :  jeu. 26 juil. 2018 à 03:46
-- Version du serveur :  5.7.19
-- Version de PHP :  7.1.9

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- --------------------------------------------------------

--
-- Structure de la table `lieux`
--

DROP TABLE IF EXISTS `lieux`;
CREATE TABLE IF NOT EXISTS `lieux` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  [...],
  `coords` point DEFAULT NULL,
  [...],
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Déchargement des données de la table `lieux`
--

INSERT INTO `lieux` (`id`, `created_at`, `updated_at`, [...], `coords`, [...]) VALUES
(1, '2018-03-23 09:13:45', '2018-04-19 19:47:22', [...], '\0\0\0\0\0\0\0��I�mH@�D[@', [...]),
(2, '2018-03-23 18:11:59', '2018-07-12 16:15:06', [...], '\0\0\0\0\0\0\0���WH@.�s�w�@', [...]),
(3, '2018-04-02 14:00:29', '2018-04-19 19:47:32', [...], '\0\0\0\0\0\0\04j��E@�mWCu@', [...]);

-- --------------------------------------------------------

我将多余的数据替换为 [...] ###编辑：尝试phpmyadmin选项和命令行mysqldump
我在phpmyadmin中尝试了所有相关选项，但没有任何改变。我还尝试从远程环境导出数据库，该环境的phpmyadmin=>版本不同，但仍然无法工作。
我正在命令行中尝试从本地导出，使用以下指令：

C:\laragon\bin\mysql\mysql-5.7.19-winx64\bin
λ mysqldump.exe --host=localhost --user=root mydatabase > mydatabase.sql

但它还是给了我一个糟糕的编码：

-- MySQL dump 10.13  Distrib 5.7.19, for Win64 (x86_64)
--
-- Host: localhost    Database: db
-- ------------------------------------------------------
-- Server version   5.7.19

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `lieux`
--

DROP TABLE IF EXISTS `lieux`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lieux` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  [...],
  `coords` point DEFAULT NULL,
  [...],
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `lieux`
--

LOCK TABLES `lieux` WRITE;
/*!40000 ALTER TABLE `lieux` DISABLE KEYS */;
INSERT INTO `lieux` VALUES (1,'2018-03-23 09:13:45','2018-04-19 19:47:22',[...],'\0\0\0\0\0\0\0��I\�mH@�D[@',[...]),(2,'2018-03-23 18:11:59','2018-07-12 16:15:06',[...],'\0\0\0\0\0\0\0��\�WH@.\�s�w�@',[...]),(3,'2018-04-02 14:00:29','2018-04-19 19:47:32',[...],'\0\0\0\0\0\0\04j��E@�mWCu@',[...]);
/*!40000 ALTER TABLE `lieux` ENABLE KEYS */;
UNLOCK TABLES;

mysqldump命令行导出文件的一部分
如何获取有关导出过程的更多信息？
您认为这可能是由于行创建时的值存储不正确（我的意思是当用户创建location项时）造成的吗？
也许它与另一个问题有关（也是我的，只是部分解决了）。。。

mysql phpmyadmin google-maps coordinates Point

来源：https://stackoverflow.com/questions/51530268/export-import-issue-with-mysql-point-coordinates

2条答案

按热度按时间

gwo2fgha1#

https://dev.mysql.com/doc/refman/5.7/en/gis-data-formats.html
看起来像wkb格式：

mysql> select HEX(ST_PointFromText('POINT(-0.000000000029046067630853117 -3.583174595546599e227)')); 
+---------------------------------------------------------------------------------------+
| HEX(ST_PointFromText('POINT(-0.000000000029046067630853117 -3.583174595546599e227)')) |
+---------------------------------------------------------------------------------------+
| 0000000001010000000E1BEFBFBDEFBFBDEFBFBD5748402EEF                                    |
+---------------------------------------------------------------------------------------+

以下是“坏”数字中的两个浮点数：

mysql> select HEX(MID(REVERSE(ST_PointFromText('POINT(-0.000000000029046067630853117 -3.583174595546599e227)')), 1, 8));
+-----------------------------------------------------------------------------------------------------------+
| HEX(MID(REVERSE(ST_PointFromText('POINT(-0.000000000029046067630853117 -3.583174595546599e227)')), 1, 8)) |
+-----------------------------------------------------------------------------------------------------------+
| EF2E404857BDBFEF                                                                                          |
+-----------------------------------------------------------------------------------------------------------+

mysql> select HEX(MID(REVERSE(ST_PointFromText('POINT(-0.000000000029046067630853117 -3.583174595546599e227)')), 9, 8));
+-----------------------------------------------------------------------------------------------------------+
| HEX(MID(REVERSE(ST_PointFromText('POINT(-0.000000000029046067630853117 -3.583174595546599e227)')), 9, 8)) |
+-----------------------------------------------------------------------------------------------------------+
| BDBFEFBDBFEF1B0E                                                                                          |
+-----------------------------------------------------------------------------------------------------------+

他们似乎很有道理。
不清楚你所说的“坏”或“假”是什么意思。请提供此类事件的sql证据。
这个 CHARACTER SET 应该完全无关。
啊哈。那么这些数字代表纬度和经度？好， -3.583174595546599e227 （-3.58*10^227）不是有效值。因此，从该值向后操作，并显示生成/导出/导入数字的所有步骤。我们得找出它在哪里被弄坏了。从查看导出文件开始。
mysqldump文件
根据您最近的编辑，mysqldump正在崩溃 POINTs . 我不知道确切的解决办法，但二进制的东西需要在十六进制（或文本以外的东西）完成。
phpadmin可能有一个参数？？
也许你可以使用mysqldump命令？
mysqldump示例
生成测试表：

mysql> CREATE TABLE `lieux` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `coords` point DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO lieux (coords) VALUES (ST_PointFromText('POINT(11.22 33.44)'));
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> SELECT HEX(coords) FROM lieux;
+----------------------------------------------------+
| HEX(coords)                                        |
+----------------------------------------------------+
| 000000000101000000713D0AD7A3702640B81E85EB51B84040 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit 
Bye

然后扔掉它：

~$ mysqldump  --hex-blob try lieux -u root
-- MySQL dump 10.13  Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost    Database: try
-- ------------------------------------------------------
-- Server version   5.6.22-71.0-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `lieux`
--

DROP TABLE IF EXISTS `lieux`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `lieux` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `coords` point DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `lieux`
--

LOCK TABLES `lieux` WRITE;
/*!40000 ALTER TABLE `lieux` DISABLE KEYS */;
INSERT INTO `lieux` VALUES (4,0x000000000101000000713D0AD7A3702640B81E85EB51B84040);
/*!40000 ALTER TABLE `lieux` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-08-20 13:38:24

赞(0）回复(0）举报 2021-06-21

q5lcpyga2#

最后，在phpmyadmin 4.8.6中进行了固定

如您所见：https://github.com/phpmyadmin/phpmyadmin/issues/14588#event-2323932464
这个问题现在由7f454ac修复，并将成为phpmyadmin（4.8.6）下一个版本的一部分

赞(0）回复(0）举报 2021-06-20

我来回答