MySQL：如果行尚不存在，则插入行？

Question

如何更改 MySQL 语句，以便仅在属性的 id 不在 table_b 中时才将行插入到 table_b 中？

INSERT INTO table_b( property_id, siteaddress, area_name, result) 
SELECT property_id, siteaddress,   "Area A" AS area_name,  IS_POINT_IN_POLYGON(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT( 'POLYGON((44.933690000000006, -111.07178
44.96479, -104.1504
41.062780000000004, -104.04053
41.01306, -111.07178
44.887010000000004, -111.04981000000001
))' )
)AS result
FROM table_a
WHERE IS_POINT_IN_POLYGON(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT( 'POLYGON((44.933690000000006, -111.07178
44.96479, -104.1504
41.062780000000004, -104.04053
41.01306, -111.07178
44.887010000000004, -111.04981000000001 ))' )
) = 1;

INSERT INTO table_b( property_id, siteaddress, area_name, result) 
SELECT property_id, siteaddress,   "Area B" AS area_name,  IS_POINT_IN_POLYGON(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT( 'POLYGON((37.909530000000004, -87.69288
37.89219000000001, -82.5293
36.40359, -83.58399
35.78217, -86.33057000000001
37.90872, -87.69356
37.909530000000004, -87.69288
))' )
)AS result
FROM table_a
WHERE IS_POINT_IN_POLYGON(
POINTFROMTEXT( CONCAT( 'POINT(', latitude, ' ', longitude, ')' ) ) , POLYFROMTEXT( 'POLYGON((37.909530000000004, -87.69288
37.89219000000001, -82.5293
36.40359, -83.58399
35.78217, -86.33057000000001
37.90872, -87.69356
37.909530000000004, -87.69288 ))' )
) = 1;

Answer 1

您可以使用 MySQL 的 INSERT IGNORE 语法，如本问题所示：如何在 MySQL 中“如果不存在则插入”？

例如：

INSERT IGNORE INTO tbl1 (id, col) VALUES (1, 2);