当前位置：首页 > 数据库 > MsSql > 详细页面

在Ubuntu使用SQL Server创建Go应用程序的图文教程

时间：2023-10-26来源：系统城装机大师作者：佚名

一、设置环境

在 Ubuntu 机器上安装 SQL Server 2017和安装运行 GoLang 所需的依赖项。

1.1、安装 SQL Server

为了确保 SQL Server 的最佳性能，计算机应至少具有 4 GB 的内存。

（1）注册 Microsoft Linux 存储库并添加其密钥。

1 2	`curl https://packages.microsoft.com/keys/microsoft.asc \|` `sudo` `apt-key add -` `curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list \|` `sudo` `tee` `/etc/apt/sources.list.d/mssql-server-2017.list`

（2）安装 SQL Server。

1 2	`sudo` `apt-get update` `sudo` `apt-get` `install` `mssql-server`

执行结果：

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following NEW packages will be installed:
mssql-server
...
Unpacking mssql-server ...
Setting up mssql-server ...

（3）设置 SQL Server。

1	`sudo` `/opt/mssql/bin/mssql-conf` `setup`

执行结果：

Microsoft(R) SQL Server(R) Setup

To abort setup at anytime, press Ctrl-C.

The license terms for this product can be downloaded from http://go.microsoft.com/fwlink/?LinkId=746388 and
found in /usr/share/doc/mssql-server/LICENSE.TXT.

Do you accept the license terms? If so, please type YES:
Please enter a password for the system administrator (SA) account:
Please confirm the password for the system administrator (SA) account:

1.2、安装 GoLang

如果您的机器上已经安装了 Go，请跳过此步骤。

（1）下载安装。

1
2
3
4 curl -O https://storage.googleapis.com/golang/go1.8.linux-amd64.tar.gz
tar xvf go1.8.linux-amd64.tar.gz
sudo chown -R root:root ./go
sudo mv go /usr/local

（2）将这两行添加到 ~/.profile 文件中。

1 2	`export` `GOPATH=$HOME/work` `export` `PATH=$PATH:/usr/local/go/bin:$GOPATH/bin`

1.3、安装 ODBC 驱动程序和 SQL 命令行实用工具 SQL 服务器

SQLCMD 是一个命令行工具，能够连接到 SQL Server 并运行查询。

（1）下载适用于操作系统版本的软件包。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

#Ubuntu 16.04
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Ubuntu 18.04
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

#Ubuntu 19.10
curl https://packages.microsoft.com/config/ubuntu/19.10/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install unixodbc-dev

（2）安装 SQLCMD 后，可以使用以下命令连接到 SQL Server：

1 2	`sqlcmd -S localhost -U sa -P yourpassword` `1>` `# You're connected! Type your T-SQL statements here. Use the keyword 'GO' to execute each batch of statements.`

（3）测试数据库。结果将打印到标准输出。

1	`sqlcmd -S localhost -U sa -P yourpassword -Q` `"SELECT @@VERSION"`

--------------------------------------------------------
Microsoft SQL Server vNext (CTP2.0) - 14.0.500.272 (X64)
Apr 2 2023 11:44:40
Copyright (c) Microsoft Corporation
on Linux (Ubuntu 16.04)

1 rows(s) returned

Executed in 1 ns

至此，已成功在 Ubuntu 机器上安装 SQL Server 命令行实用程序，已经在 Ubuntu 计算机上成功安装并设置 GoLang 和 mssql-tools。现在拥有开始使用 SQL Server 编写 Go 应用程序所需的一切。

二、使用 SQL 服务器创建 Go 应用程序

安装 SQL Server 和 GoLang 后，现在可以继续创建新的 Go 项目。在这里，将探讨三个简单的应用程序。其中一个将连接并打印数据库服务器的SQL Server版本，另一个将执行基本的插入，更新，删除和选择操作，第三个将使用GORM，一种流行的对象关系映射（ORM）框架，用于Go执行相同的操作。

2.1、创建连接到 SQL Server 并执行查询的 Go 应用

（1）创建新的项目目录并安装 Go 依赖项。

1
2
3
4
5
6
7
8
9 cd ~/

#Create Project Directory
mkdir SqlServerSample
cd SqlServerSample

# Get and install the SQL Server driver for Go
go get github.com/denisenkom/go-mssqldb
go install github.com/denisenkom/go-mssqldb

（2）通过使用 sqlcmd 连接到 SQL Server 并执行以下命令，创建将用于本教程其余部分的数据库。不要忘记使用自己的用户名和密码更新用户名和密码。

1	`sqlcmd -S 127.0.0.1 -U sa -P <你的> -Q` `"CREATE DATABASE SampleDB;"`

（3）创建一个连接到 SQL Server 的简单 Go 应用。

在 SqlServerSample 文件夹中创建一个名为 connect.go 的文件。将以下内容复制并粘贴到文件中。不要忘记使用自己的用户名和密码更新用户名和密码。

此示例使用 GoLang 上下文方法来确保存在与数据库服务器的活动连接。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59 package main

import (
_ "github.com/denisenkom/go-mssqldb"
"database/sql"
"context"
"log"
"fmt"
)

// Replace with your own connection parameters
var server = "localhost"
var port = 1433
var user = "sa"
var password = "xxxxxx"

var db *sql.DB

func main() {
var err error

// Create connection string
connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d",
server, user, password, port)

// Create connection pool
db, err = sql.Open("sqlserver", connString)
if err != nil {
log.Fatal("Error creating connection pool: " + err.Error())
}
log.Printf("Connected!\n")

// Close the database connection pool after program executes
defer db.Close()

SelectVersion()
}

// Gets and prints SQL Server version
func SelectVersion(){
// Use background context
ctx := context.Background()

// Ping database to see if it's still alive.
// Important for handling network issues and long queries.
err := db.PingContext(ctx)
if err != nil {
log.Fatal("Error pinging database: " + err.Error())
}

var result string

// Run query and scan for result
err = db.QueryRowContext(ctx, "SELECT @@version").Scan(&result)
if err != nil {
log.Fatal("Scan failed:", err.Error())
}
fmt.Printf("%s\n", result)
}

（4）运行应用程序。

1	`go` `run connect.go`

执行结果：

Connected!
Microsoft SQL Server 2017 (CTP2.1) - 14.0.600.250 (X64)
Apr 2 2017 12:21:23
Copyright (C) 2017 Microsoft Corporation. All rights reserved.
Developer Edition (64-bit) on Linux (Ubuntu 16.04.2 LTS)

（5）在 SqlServerSample 文件夹中创建一个名为 CreateTestData 的文件.sql。将以下 T-SQL 代码复制并粘贴到其中。这将创建一个架构、表并插入几行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 CREATE SCHEMA TestSchema;
GO

CREATE TABLE TestSchema.Employees (
Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Name NVARCHAR(50),
Location NVARCHAR(50)
);
GO

INSERT INTO TestSchema.Employees (Name, Location) VALUES
(N'Jared', N'Australia'),
(N'Nikita', N'India'),
(N'Tom', N'Germany');
GO

SELECT * FROM TestSchema.Employees;
GO

（6）使用 sqlcmd 连接到数据库并运行 SQL 脚本以创建架构、表并插入一些行。

1	`sqlcmd -S 127.0.0.1 -U sa -P <你的> -d SampleDB -i ./CreateTestData.sql`

执行结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 CREATE SCHEMA TestSchema;

Executed in 0 ms
CREATE TABLE TestSchema.Employees (
Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Name NVARCHAR(50),
Location NVARCHAR(50)
);

Executed in 0 ms
INSERT INTO TestSchema.Employees (Name, Location) VALUES
(N'Jared', N'Australia'),
(N'Nikita', N'India'),
(N'Tom', N'Germany');

Executed in 0 ms
SELECT * FROM TestSchema.Employees;
Id Name Location
-- ------ ---------
1 Jared Australia
2 Nikita India
3 Tom Germany

3 row(s) returned

Executed in 1 ms

（7）在 SqlServerSample 文件夹中创建一个名为 crud.go 的新文件。将以下代码复制并粘贴到其中。这将插入、更新、删除和读取几行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189 package main

import (
_ "github.com/denisenkom/go-mssqldb"
"database/sql"
"context"
"log"
"fmt"
"errors"
)

var db *sql.DB

var server = "localhost"
var port = 1433
var user = "sa"
var password = "你的"
var database = "SampleDB"

func main() {
// Build connection string
connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;",
server, user, password, port, database)

var err error

// Create connection pool
db, err = sql.Open("sqlserver", connString)
if err != nil {
log.Fatal("Error creating connection pool: ", err.Error())
}
ctx := context.Background()
err = db.PingContext(ctx)
if err != nil {
log.Fatal(err.Error())
}
fmt.Printf("Connected!\n")

// Create employee
createID, err := CreateEmployee("Jake", "United States")
if err != nil {
log.Fatal("Error creating Employee: ", err.Error())
}
fmt.Printf("Inserted ID: %d successfully.\n", createID)

// Read employees
count, err := ReadEmployees()
if err != nil {
log.Fatal("Error reading Employees: ", err.Error())
}
fmt.Printf("Read %d row(s) successfully.\n", count)

// Update from database
updatedRows, err := UpdateEmployee("Jake", "Poland")
if err != nil {
log.Fatal("Error updating Employee: ", err.Error())
}
fmt.Printf("Updated %d row(s) successfully.\n", updatedRows)

// Delete from database
deletedRows, err := DeleteEmployee("Jake")
if err != nil {
log.Fatal("Error deleting Employee: ", err.Error())
}
fmt.Printf("Deleted %d row(s) successfully.\n", deletedRows)
}

// CreateEmployee inserts an employee record
func CreateEmployee(name string, location string) (int64, error) {
ctx := context.Background()
var err error

if db == nil {
err = errors.New("CreateEmployee: db is null")
return -1, err
}

// Check if database is alive.
err = db.PingContext(ctx)
if err != nil {
return -1, err
}

tsql := "INSERT INTO TestSchema.Employees (Name, Location) VALUES (@Name, @Location); select convert(bigint, SCOPE_IDENTITY());"

stmt, err := db.Prepare(tsql)
if err != nil {
return -1, err
}
defer stmt.Close()

row := stmt.QueryRowContext(
ctx,
sql.Named("Name", name),
sql.Named("Location", location))
var newID int64
err = row.Scan(&newID)
if err != nil {
return -1, err
}

return newID, nil
}

// ReadEmployees reads all employee records
func ReadEmployees() (int, error) {
ctx := context.Background()

// Check if database is alive.
err := db.PingContext(ctx)
if err != nil {
return -1, err
}

tsql := fmt.Sprintf("SELECT Id, Name, Location FROM TestSchema.Employees;")

// Execute query
rows, err := db.QueryContext(ctx, tsql)
if err != nil {
return -1, err
}

defer rows.Close()

var count int

// Iterate through the result set.
for rows.Next() {
var name, location string
var id int

// Get values from row.
err := rows.Scan(&id, &name, &location)
if err != nil {
return -1, err
}

fmt.Printf("ID: %d, Name: %s, Location: %s\n", id, name, location)
count++
}

return count, nil
}

// UpdateEmployee updates an employee's information
func UpdateEmployee(name string, location string) (int64, error) {
ctx := context.Background()

// Check if database is alive.
err := db.PingContext(ctx)
if err != nil {
return -1, err
}

tsql := fmt.Sprintf("UPDATE TestSchema.Employees SET Location = @Location WHERE Name = @Name")

// Execute non-query with named parameters
result, err := db.ExecContext(
ctx,
tsql,
sql.Named("Location", location),
sql.Named("Name", name))
if err != nil {
return -1, err
}

return result.RowsAffected()
}

// DeleteEmployee deletes an employee from the database
func DeleteEmployee(name string) (int64, error) {
ctx := context.Background()

// Check if database is alive.
err := db.PingContext(ctx)
if err != nil {
return -1, err
}

tsql := fmt.Sprintf("DELETE FROM TestSchema.Employees WHERE Name = @Name;")

// Execute non-query with named parameters
result, err := db.ExecContext(ctx, tsql, sql.Named("Name", name))
if err != nil {
return -1, err
}

return result.RowsAffected()
}

（8）运行 crud.go 应用以查看结果。

1	`go` `run crud.go`

执行结果：

Connected!
Inserted ID: 4 successfully.
ID: 1, Name: Jared, Location: Australia
ID: 2, Name: Nikita, Location: India
ID: 3, Name: Tom, Location: Germany
ID: 4, Name: Jake, Location: United States
Read 4 row(s) successfully.
Updated 1 row(s) successfully.
Deleted 1 row(s) successfully.

2.2、创建一个使用 GORM 连接到 SQL Server 的 Go 应用程序

（1）创建应用目录并初始化 Go 依赖项。

1
2
3
4
5
6
7 cd ~/
mkdir SqlServerGormSample
cd SqlServerGormSample

# Get and install the SQL Server driver for Go
go get github.com/denisenkom/go-mssqldb
go install github.com/denisenkom/go-mssqldb

（2）将以下内容粘贴到名为orm.go的文件中。确保将密码变量替换为您自己的变量。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104 package main

import (
"fmt"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mssql"
"log"
)

var server = "localhost"
var port = 1433
var user = "sa"
var password = "你的"
var database = "SampleDB"

// Define a User model struct
type User struct {
gorm.Model
FirstName string
LastName string
}

// Define a Task model struct
type Task struct {
gorm.Model
Title string
DueDate string
IsComplete bool
UserID uint
}

// Read and print all the tasks
func ReadAllTasks(db *gorm.DB){
var users []User
var tasks []Task
db.Find(&users)

for _, user := range users{
db.Model(&user).Related(&tasks)
fmt.Printf("%s %s's tasks:\n", user.FirstName, user.LastName)
for _, task := range tasks {
fmt.Printf("Title: %s\nDueDate: %s\nIsComplete:%t\n\n",
task.Title, task.DueDate, task.IsComplete)
}
}
}

// Update a task based on a user
func UpdateSomeonesTask(db *gorm.DB, userId int){
var task Task
db.Where("user_id = ?", userId).First(&task).Update("Title", "Buy donuts for Luis")
fmt.Printf("Title: %s\nDueDate: %s\nIsComplete:%t\n\n",
task.Title, task.DueDate, task.IsComplete)
}

// Delete all the tasks for a user
func DeleteSomeonesTasks(db *gorm.DB, userId int){
db.Where("user_id = ?", userId).Delete(&Task{})
fmt.Printf("Deleted all tasks for user %d", userId)
}

func main() {
connectionString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s",
server, user, password, port, database)
db, err := gorm.Open("mssql", connectionString)

if err != nil {
log.Fatal("Failed to create connection pool. Error: " + err.Error())
}
gorm.DefaultCallback.Create().Remove("mssql:set_identity_insert")
defer db.Close()

fmt.Println("Migrating models...")
db.AutoMigrate(&User{})
db.AutoMigrate(&Task{})

// Create awesome Users
fmt.Println("Creating awesome users...")
db.Create(&User{FirstName: "Andrea", LastName: "Lam"}) //UserID: 1
db.Create(&User{FirstName: "Meet", LastName: "Bhagdev"}) //UserID: 2
db.Create(&User{FirstName: "Luis", LastName: "Bosquez"}) //UserID: 3

// Create appropriate Tasks for each user
fmt.Println("Creating new appropriate tasks...")
db.Create(&Task{
Title: "Do laundry", DueDate: "2017-03-30", IsComplete: false, UserID: 1})
db.Create(&Task{
Title: "Mow the lawn", DueDate: "2017-03-30", IsComplete: false, UserID: 2})
db.Create(&Task{
Title: "Do more laundry", DueDate: "2017-03-30", IsComplete: false, UserID: 3})
db.Create(&Task{
Title: "Watch TV", DueDate: "2017-03-30", IsComplete: false, UserID: 3})

// Read
fmt.Println("\nReading all the tasks...")
ReadAllTasks(db)

// Update - update Task title to something more appropriate
fmt.Println("Updating Andrea's task...")
UpdateSomeonesTask(db, 1)

// Delete - delete Luis's task
DeleteSomeonesTasks(db, 3)
}

（3）运行 orm.go 应用。

1	`go` `run orm.go`

执行结果：

[info] removing callback `mssql:set_identity_insert` from C:/Projects/golang-experiments/tutorials/orm.go:70
Migrating models...
Creating awesome users...
Creating new appropriate tasks...

Reading all the tasks...
Andrea Lam's tasks:
Title: Do laundry
DueDate: 2017-03-30
IsComplete:false

Meet Bhagdev's tasks:
Title: Mow the lawn
DueDate: 2017-03-30
IsComplete:false

Luis Bosquez's tasks:
Title: Do more laundry
DueDate: 2017-03-30
IsComplete:false

Title: Watch TV
DueDate: 2017-03-30
IsComplete:false

Updating Andrea's task...
Title: Buy donuts for Luis
DueDate: 2017-03-30
IsComplete:false

Deleted all tasks for user 3

三、让 Go 应用的速度提高 100 倍

已了解基础知识，接下来可以了解如何使用 SQL Server 改进应用。通过列存储索引的简单示例，以及它们如何提高数据处理速度。与传统行存储索引相比，列存储索引在分析工作负荷上可实现高达 100 倍的性能，并将数据压缩提高多达 10 倍。

3.1、使用 sqlcmd 创建一个包含 5 万个的新表

（1）切换到主目录并为项目创建一个文件夹。

1	`cd` `~/mkdir` `SqlServerColumnstoreSamplecd SqlServerColumnstoreSample`

（2）在 SqlServerColumnstoreSample 文件夹中创建一个名为 CreateSampleTable 的新文件.sql文件。将下面的 T-SQL 代码粘贴到新的 SQL 文件中。保存并关闭文件。

1
2
3
4
5
6
7
8 WITH a AS (SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a))
SELECT TOP(5000000)
ROW_NUMBER() OVER (ORDER BY a.a) AS OrderItemId
,a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a AS OrderId
,a.a * 10 AS Price
,CONCAT(a.a, N' ', b.a, N' ', c.a, N' ', d.a, N' ', e.a, N' ', f.a, N' ', g.a, N' ', h.a) AS ProductName
INTO Table_with_5M_rows
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h;

（3）使用 sqlcmd 连接到数据库并运行 SQL 脚本以创建包含 5 万行的表。这可能需要几分钟才能运行。

1	`sqlcmd -S 127.0.0.1 -U sa -P <你的> -d SampleDB -i ./CreateSampleTable.sql`

3.2、创建一个 Go 应用程序，用于查询此表并测量所花费的时间

（1）在项目文件夹中，初始化 Go 依赖项。

1 2	`go` `get github.com/denisenkom/go-mssqldb` `go` `install github.com/denisenkom/go-mssqldb`

（2）在您的文件夹中创建一个名为 columnstore.go 的文件。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64 package main

import (
_ "github.com/denisenkom/go-mssqldb"
"database/sql"
"context"
"log"
"fmt"
"time"
)

var server = "localhost"
var port = 1433
var user = "sa"
var password = "你的"
var database = "SampleDB"

var db *sql.DB

// Delete an employee from database
func ExecuteAggregateStatement(db *sql.DB) {
ctx := context.Background()

// Ping database to see if it's still alive.
// Important for handling network issues and long queries.
err := db.PingContext(ctx)
if err != nil {
log.Fatal("Error pinging database: " + err.Error())
}

var result string

// Execute long non-query to aggregate rows
err = db.QueryRowContext(ctx, "SELECT SUM(Price) as sum FROM Table_with_5M_rows").Scan(&result)
if err != nil {
log.Fatal("Error executing query: " + err.Error())
}

fmt.Printf("Sum: %s\n", result)
}

func main() {
// Connect to database
connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;",
server, user, password, port, database)
var err error

// Create connection pool
db, err = sql.Open("sqlserver", connString)
if err != nil {
log.Fatal("Open connection failed:", err.Error())
}
fmt.Printf("Connected!\n")

defer db.Close()

t1 := time.Now()
fmt.Printf("Start time: %s\n", t1)

ExecuteAggregateStatement(db)

t2 := time.Since(t1)
fmt.Printf("The query took: %s\n", t2)
}