Golang – MySQL驱动程序 – 数据库函数

huangapple go评论90阅读模式
英文:

golang - mysql driver - database functions

问题

我已经创建了一个结构体来存储空间类型,并且创建了一个扫描函数来帮助查询数据库中的行。我在插入这种类型时遇到了问题。

我可以使用以下 SQL 语句插入数据:

INSERT INTO 'table' ('spot') VALUES (GeomFromText('POINT(10 10)'));

如果我在 database/sql/driver 中使用 Value 接口;

> type Value interface{}
>
> Value 是驱动程序必须能够处理的值。它可以是 nil,也可以是以下类型之一的实例:
>
> int64
>
> float64
>
> bool
>
> []byte
>
> string [*] 除了 Rows.Next 之外的所有地方。
>
> time.Time

并使用以下代码;

func (p Point) Value() (driver.Value, error) {
    return "GeomFromText('" + p.ToWKT() + "')", nil
}

我最终得到的 SQL 语句发送到数据库如下;

INSERT INTO 'table' ('spot') VALUES ('GeomFromText('POINT(10 10)')');

问题在于函数 GeomFromText 被引号包围。有没有办法避免这种情况?我正在使用 gorm,并尽量减少原始 SQL 查询的使用。

数据库端使用的是 point 类型。

英文:

I have created a struct to store spatial types and I have created a scan function to help query rows in my database. I am having issues inserting this type.

I can insert data using the following sql;

INSERT INTO 'table' ('spot') VALUES (GeomFromText('POINT(10 10)'));

If I use Value interface in database/sql/driver;

> type Value interface{}
>
> Value is a value that drivers must be able to handle. It is either nil or an instance of one of these types:
>
> int64
>
> float64
>
> bool
>
> []byte
>
> string [*] everywhere except from Rows.Next.
>
> time.Time

And use this code;

func (p Point) Value() (driver.Value, error) {
    return "GeomFromText('" + p.ToWKT() + "')", nil
}

I end up with the following sql statement going to the database;

INSERT INTO 'table' ('spot') VALUES ('GeomFromText('POINT(10 10)')');

The issue being that the function GeomFromText is in quotes. Is there a way to avoid this scenario? I am using gorm and trying to keep raw sql queries to a minimum.

The mysql type being used on the database end is a point.

答案1

得分: 1

请查看下面两个链接,其中的概念是从这里借用的:

Schema

创建表Points:

create table Points
(
    id int auto_increment primary key,
    name VARCHAR(20) not null,
    location Point NOT NULL,
    description VARCHAR(200) not null,
    SPATIAL INDEX(location),
    key(name)
)engine=MyISAM; -- 用于使用空间索引和避免错误1464

-- 插入一行数据,以便后面证明Update语句可以工作
INSERT INTO Points (name, location, description) VALUES 
('point1', GeomFromText('POINT(31.5 42.2)'), 'some place');

Update语句

这个概念是从这里借用的:http://stackoverflow.com/a/7135890

UPDATE Points 
set location = PointFromText(CONCAT('POINT(',13.33,' ',26.48,')'))
where id=1;

验证

select * from points;

(当你打开Value Editor查看blob时,点的位置已经更新)

所以,要点是在更新语句中使用concat()函数。

英文:

Please see the two urls below where the concept was poached from

Schema

-- http://howto-use-mysql-spatial-ext.blogspot.com/

create table Points
(	id int auto_increment primary key,
	name VARCHAR(20) not null, 
	location Point NOT NULL, 
	description VARCHAR(200) not null, 
	SPATIAL INDEX(location),
    key(name)
)engine=MyISAM; -- for use of spatial indexes and avoiding error 1464

-- insert a row, so we can prove Update later will work
INSERT INTO Points (name, location, description) VALUES 
( 'point1' , GeomFromText( ' POINT(31.5 42.2) ' ) , 'some place');

Update statement

-- concept borrowed from http://stackoverflow.com/a/7135890
UPDATE Points 
set location = PointFromText(CONCAT('POINT(',13.33,' ',26.48,')'))
where id=1;

Verify

select * from points;

(when you open the Value Editor to see the blob, the point is updated)

So, the takeaway is to play with the concat() inside of the update statement.

huangapple
  • 本文由 发表于 2015年12月12日 07:06:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/34234027.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定