本文小编为大家详细介绍“Ubuntu怎么使用SQLServer创建Go应用程序”,内容详细,步骤清晰,细节处理妥当,希望这篇“Ubuntu怎么使用SQLServer创建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... 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 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! ") // 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 ", 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! ") // Create employee createID, err := CreateEmployee("Jake", "United States") if err != nil { log.Fatal("Error creating Employee: ", err.Error()) } fmt.Printf("Inserted ID: %d successfully. ", createID) // Read employees count, err := ReadEmployees() if err != nil { log.Fatal("Error reading Employees: ", err.Error()) } fmt.Printf("Read %d row(s) successfully. ", 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. ", 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. ", 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 ", 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: 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 = 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: ", user.FirstName, user.LastName) for _, task := range tasks { fmt.Printf("Title: %s DueDate: %s IsComplete:%t ", 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 DueDate: %s IsComplete:%t ", 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(" Reading 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: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 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() // 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 ", 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! ") defer db.Close() t1 := time.Now() fmt.Printf("Start time: %s ", t1) ExecuteAggregateStatement(db) t2 := time.Since(t1) fmt.Printf("The query took: %s ", 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