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

3.1、使用 sqlcmd 创建一个包含 5 万个的新表
3.2、创建一个 Go 应用程序，用于查询此表并测量所花费的时间
3.3、测量运行查询所需的时间
3.4、使用 SQLCMD 向表中添加列存储索引
3.5、重新运行 columnstore.go 脚本，并注意这次完成查询所花费的时间

总结

一、设置环境

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

1.1、安装 SQL Server

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

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

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。

sudo apt-get update
sudo apt-get install mssql-server

执行结果：

Reading package lists… DoneBuilding dependency treeReading state information… DoneThe following NEW packages will be installed: mssql-server…Unpacking mssql-server …Setting up mssql-server …

（3）设置 SQL Server。

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 andfound 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）下载安装。

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 文件中。

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

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

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

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

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：

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）测试数据库。结果将打印到标准输出。

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 依赖项。

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 并执行以下命令，创建将用于本教程其余部分的数据库。不要忘记使用自己的用户名和密码更新用户名和密码。

sqlcmd -S 127.0.0.1 -U sa -P <你的> -Q \”CREATE DATABASE SampleDB;\”

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

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

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

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）运行应用程序。

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 代码复制并粘贴到其中。这将创建一个架构、表并插入几行。

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 脚本以创建架构、表并插入一些行。

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

执行结果：

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 的新文件。将以下代码复制并粘贴到其中。这将插入、更新、删除和读取几行。

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 应用以查看结果。

go run crud.go

执行结果：

Connected!Inserted ID: 4 successfully.ID: 1, Name: Jared, Location: AustraliaID: 2, Name: Nikita, Location: IndiaID: 3, Name: Tom, Location: GermanyID: 4, Name: Jake, Location: United StatesRead 4 row(s) successfully.Updated 1 row(s) successfully.Deleted 1 row(s) successfully.

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

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

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的文件中。确保将密码变量替换为您自己的变量。

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 应用。

go run orm.go

执行结果：

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

Reading all the tasks…Andrea Lam's tasks:Title: Do laundryDueDate: 2017-03-30IsComplete:false

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

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

Title: Watch TVDueDate: 2017-03-30IsComplete:false

Updating Andrea's task…Title: Buy donuts for LuisDueDate: 2017-03-30IsComplete:false

Deleted all tasks for user 3

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

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

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

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

cd ~/mkdir SqlServerColumnstoreSamplecd SqlServerColumnstoreSample

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

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 万行的表。这可能需要几分钟才能运行。

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

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

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

go get github.com/denisenkom/go-mssqldb
go install github.com/denisenkom/go-mssqldb

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

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()

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)
}