如何在mysql中将一个表作为值插入另一个表？

I have these two tables, the first one is called item_coordinates, the types are INT, DOUBLE, DOUBLE

我有这两个表，第一个叫做item_coordinates，类型是INT，DOUBLE，DOUBLE

itemID       | latitude  |  longitude
-------------+-----------+-----------
1679323860   | 36.531398 |  -82.98085
1679340420   | 29.178171 | -74.075391
1679386982   | 40.73235  |   -94.6884

And now I have another table called Geocoordinates, I created this table as follow:

现在我有另一个名为Geocoordinates的表，我创建了这个表如下：

CREATE TABLE Geocoordinates (ItemID INT PRIMARY KEY,
 Geo_Coordinates POINT) ENGINE = MyISAM;

Now I want to insert the values in table 1 into table 2, but keep getting error messages. This is my attempt:

现在我想将表1中的值插入表2中，但不断收到错误消息。这是我的尝试：

INSERT INTO Geocoordinates (ItemID, Geo_Coordinates) VALUES (item_id, 
POINT(latitude, longitude)) SELECT item_id, latitude, longitude FROM 
item_coordinates);

Thanks in advance.

提前致谢。

2 个解决方案

#1

I think you need to use the CONCAT function to group the lat/long into one field

我认为您需要使用CONCAT函数将lat / long分组到一个字段中

INSERT INTO Geocoordinates (ItemID, Geo_Coordinates) VALUES (item_id, 
POINT(latitude, longitude)) SELECT item_id, CONCAT(latitude, longitude) FROM item_coordinates);

I don't think POINT is needed in this case.

在这种情况下我不认为需要POINT。

#2

Use INSERT INTO SELECT, no VALUES keyword should be specified. Below is the complete demo.

使用INSERT INTO SELECT，不应指定VALUES关键字。以下是完整的演示。

SQL:

SQL：

-- Data
create table item_coordinates( itemID bigint,  latitude decimal(10,6), longitude decimal(10,6));
CREATE TABLE Geocoordinates (ItemID INT PRIMARY KEY,
 Geo_Coordinates POINT) ENGINE = MyISAM;
INSERT INTO item_coordinates values
(1679323860   , 36.531398 ,  -82.98085),
(1679340420   , 29.178171 , -74.075391),
(1679386982   , 40.73235  ,   -94.6884);
SELECT * FROM item_coordinates;

-- SQL needed
INSERT INTO Geocoordinates (itemID, Geo_Coordinates)  SELECT itemID, POINT(latitude, longitude)  FROM item_coordinates;
SELECT itemID, ST_AsText(Geo_Coordinates) FROM Geocoordinates;

Output:

输出：

mysql> SELECT * FROM item_coordinates;
+------------+-----------+------------+
| itemID     | latitude  | longitude  |
+------------+-----------+------------+
| 1679323860 | 36.531398 | -82.980850 |
| 1679340420 | 29.178171 | -74.075391 |
| 1679386982 | 40.732350 | -94.688400 |
+------------+-----------+------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO Geocoordinates (itemID, Geo_Coordinates)  SELECT itemID, POINT(latitude, longitude)  FROM item_coordinates;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT itemID, ST_AsText(Geo_Coordinates) FROM Geocoordinates;
+------------+-----------------------------+
| itemID     | ST_AsText(Geo_Coordinates)  |
+------------+-----------------------------+
| 1679323860 | POINT(36.531398 -82.98085)  |
| 1679340420 | POINT(29.178171 -74.075391) |
| 1679386982 | POINT(40.73235 -94.6884)    |
+------------+-----------------------------+
3 rows in set (0.00 sec)

#1