在 Ubuntu 使用SQL Server创建 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 |sudotee /etc/apt/sources.list.d/mssql-server-2017.list
（2）安装 SQL Server。
sudoapt-get update
sudoapt-getinstall 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。
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）下载安装。
curl -O https://storage.googleapis.com/golang/go1.8.linux-amd64.tar.gz
tar xvf go1.8.linux-amd64.tar.gz
sudochown -R root:root ./go
sudomv go /usr/local
（2）将这两行添加到 ~/.profile 文件中。
exportGOPATH=$HOME/work
exportPATH=$PATH:/usr/local/go/bin:$GOPATH/bin
1.3、安装 ODBC 驱动程序和 SQL 命令行实用工具 SQL 服务器
SQLCMD 是一个命令行工具，能够连接到 SQL Server 并运行查询。
（1）下载适用于操作系统版本的软件包。
sudosucurl 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.04curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
#Ubuntu 18.04curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
#Ubuntu 19.10curl https://packages.microsoft.com/config/ubuntu/19.10/prod.list > /etc/apt/sources.list.d/mssql-release.list
exitsudoapt-get update
sudoACCEPT_EULA=Y apt-getinstall msodbcsql17
# optional: for bcp and sqlcmdsudoACCEPT_EULA=Y apt-getinstall 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 headerssudoapt-getinstall 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 2202311:44:40
Copyright (c) Microsoft Corporation
on Linux (Ubuntu 16.04)1 rows(s) returned
Executed in1 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 Directorymkdir 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 parametersvar server ="localhost"var port =1433var user ="sa"var password ="xxxxxx"var db *sql.DB
funcmain(){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 executesdefer db.Close()SelectVersion()}// Gets and prints SQL Server versionfuncSelectVersion(){// 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 2201712: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 代码复制并粘贴到其中。这将创建一个架构、表并插入几行。
CREATESCHEMA TestSchema;
GO
CREATETABLE TestSchema.Employees (
Id INTIDENTITY(1,1)NOTNULLPRIMARYKEY,
Name NVARCHAR(50),
Location NVARCHAR(50));
GO
INSERTINTO 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
执行结果：
CREATESCHEMA TestSchema;
Executed in0 ms
CREATETABLE TestSchema.Employees (
Id INTIDENTITY(1,1)NOTNULLPRIMARYKEY,
Name NVARCHAR(50),
Location NVARCHAR(50));
Executed in0 ms
INSERTINTO TestSchema.Employees (Name, Location)VALUES(N'Jared', N'Australia'),(N'Nikita', N'India'),(N'Tom', N'Germany');
Executed in0 ms
SELECT*FROM TestSchema.Employees;
Id Name Location
-- ------ ---------1 Jared Australia
2 Nikita India
3 Tom Germany
3row(s) returned
Executed in1 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 =1433var user ="sa"var password ="你的"var database ="SampleDB"funcmain(){// 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 recordfuncCreateEmployee(name string, location string)(int64,error){
ctx := context.Background()var err errorif 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 recordsfuncReadEmployees()(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 stringvar 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 informationfuncUpdateEmployee(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 databasefuncDeleteEmployee(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: 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 依赖项。
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 =1433var user ="sa"var password ="你的"var database ="SampleDB"// Define a User model structtype User struct{
gorm.Model
FirstName string
LastName string}// Define a Task model structtype Task struct{
gorm.Model
Title string
DueDate string
IsComplete bool
UserID uint}// Read and print all the tasksfuncReadAllTasks(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 userfuncUpdateSomeonesTask(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 userfuncDeleteSomeonesTasks(db *gorm.DB, userId int){
db.Where("user_id = ?", userId).Delete(&Task{})
fmt.Printf("Deleted all tasks for user %d", userId)}funcmain(){
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 taskDeleteSomeonesTasks(db,3)}
（3）运行 orm.go 应用。
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）切换到主目录并为项目创建一个文件夹。
cd ~/
mkdir SqlServerColumnstoreSample
cd 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))SELECTTOP(5000000)
ROW_NUMBER()OVER(ORDERBY a.a)AS OrderItemId
,a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a AS OrderId
,a.a *10AS 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 =1433var user ="sa"var password ="你的"var database ="SampleDB"var db *sql.DB
// Delete an employee from databasefuncExecuteAggregateStatement(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)}funcmain(){// 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)}
3.3、测量运行查询所需的时间
从终端运行 Go 应用。
go run columnstore.go
执行结果：
Connected!
Start time: 2023-04-02 15:33:50.0340976 -0700 PDT
Sum: 50000000
The query took: 601.7463ms
3.4、使用 SQLCMD 向表中添加列存储索引
运行以下命令以在表上创建列存储索引：
sqlcmd -S localhost -U sa -P <你的> -d SampleDB -Q "CREATE CLUSTERED COLUMNSTORE INDEX Columnstoreindex ON Table_with_5M_rows;"
3.5、重新运行 columnstore.go 脚本，并注意这次完成查询所花费的时间
go run columnstore.go
Connected!
Start time: 2017-06-05 16:35:02.5409285 -0700 PDT
Sum: 50000000
The query took: 86.9826ms
总结
- 使用列存储索引使 Go 应用更快。
- 参考文档。
本文转载自: https://blog.csdn.net/Long_xu/article/details/129910894
版权归原作者 Lion Long 所有， 如有侵权，请联系我们删除。
版权归原作者 Lion Long 所有， 如有侵权，请联系我们删除。