mysql point 修改_关于MySQL Point类型数据的简单操作

MySQL中的point用于表示GIS中的地理坐标，在GIS中广泛使用，该文章只介绍了Point类型的初级数据操作摘要。

1、创建带有point类型的表格：

CREATE TABLE `test-point` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序号',

`point` point NOT NULL COMMENT '经纬度',

`text` varchar(50) DEFAULT NULL COMMENT '描述',

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

2、添加数据：

(1)、

//添加单条数据

INSERT INTO `test-point` ( point,text ) VALUES ( GeomFromText ( 'POINT(1 1)' ),'第一个点');

//添加多条数据

INSERT INTO `test-point` ( point,text ) VALUES ( GeomFromText ( 'POINT(2 2)' ),'第二个点'),( GeomFromText ( 'POINT(3 3)' ),'第三个点'),( GeomFromText ( 'POINT(4 4)' ),'第四个点');

(2)、

//添加单条数据

INSERT INTO `test-point` ( point,text ) VALUES ( PointFromText ( 'POINT(1 1)' ),'第一个点');

//添加多条数据

INSERT INTO `test-point` ( point,text ) VALUES ( PointFromText ( 'POINT(2 2)' ),'第二个点'),( PointFromText ( 'POINT(3 3)' ),'第三个点'),( PointFromText ( 'POINT(4 4)' ),'第四个点');

(3)、

SET @temp = 'POINT(7 7)';

INSERT INTO `test-point`(point,text) VALUES (PointFromText( @temp ),'第七个点' );

SET @temp = 'POINT(8 8)';

INSERT INTO `test-point` (point,text) VALUES (GeomFromText( @temp ),'第八个点' );

(4)、

SET @temp = PointFromText('POINT(9 9)');

INSERT INTO `test-point`(point,text) VALUES (@temp ,'第九个点' );

SET @temp = GeomFromText('POINT(10 10)');

INSERT INTO `test-point`(point,text) VALUES (@temp ,'第十个点' );

3、查询数据：

(1)、

SELECT id ,x(point) x ,y(point) y ,point,text FROM `test-point`

(2)、

SELECT * FROM `test-point`

(3)、

SELECT id,AsText(point),text FROM `test-point`

4、更新数据：

(1)、

update `test-point` set point=PointFromText('POINT(5 5)') where id =10;

SET @temp = 'POINT(5 5)';

update `test-point` set point=PointFromText(@temp) where id =11;

SET @temp = PointFromText('POINT(5 5)');

update `test-point` set point=@temp where id =12;

(2)、

update `test-point` set point=GeomFromText('POINT(6 6)') where id =10;

SET @temp = 'POINT(6 6)';

update `test-point` set point=GeomFromText(@temp ) where id =11;

SET @temp = GeomFromText('POINT(6 6)');

update `test-point` set point=@temp where id =12;

使用GeomFromText这个函数将字符串转成point

使用astext读取point类型数据

如有不当，望指正！