search

Home >Backend Development >Golang >What is sql in go language

What is sql in go language

青灯夜游
青灯夜游Original
2022-12-22 11:55:4324759browse

SQL refers to "Structured Query Language", which is a language for operating databases, including creating databases, deleting databases, querying records, modifying records, adding fields, etc. SQL is the standard language for relational databases. All relational database management systems (RDBMS), such as MySQL, Oracle, SQL Server, MS Access, Sybase, Informix, Postgres, etc., use SQL as their standard processing language.

What is sql in go language

The operating environment of this tutorial: Windows 7 system, GO version 1.18, Dell G3 computer.

What is SQL

SQL is a language for operating databases, including creating databases, deleting databases, querying records, modifying records, and adding fields etc. Although SQL is a language standardized by ANSI, it has many different implementations.

SQL is the abbreviation of Structured Query Language, which is translated into Chinese as "Structured Query Language". SQL is a computer language used to store, retrieve, and modify data stored in relational databases.

SQL is the standard language for relational databases. All relational database management systems (RDBMS), such as MySQL, Oracle, SQL Server, MS Access, Sybase, Informix, Postgres, etc., use SQL as their standard Processing language.

Purposes of SQL

SQL is popular because of its uses:

  • Allow user access Data in a relational database system;

  • allows users to describe data;

  • allows users to define data in the database and process the data;

  • Allows SQL modules, libraries or preprocessors to be embedded into other programming languages;

  • Allows users to create and delete databases, tables, Data items (records);

  • Allows users to create views, stored procedures, and functions in the database;

  • Allows users to set up tables and storage Permissions for procedures and views.

Go language operation database (MySQL)

The Go language standard library provides a sql library for database operations , you can use SQL language to operate the database.

1.1 Connection

##1.1.1 Download dependencies
go get -u github.com/go-sql-driver/mysql

1.1.2 Using the MySQL driver 
func Open(driverName, dataSourceName string) (*DB, error)

OpenOpen a database specified by dirverName, specified by dataSourceName Data source , generally includes at least the database file name and other information necessary for connection. 

import (
	"database/sql"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
   // DSN:Data Source Name
	dsn := "user:password@tcp(127.0.0.1:3306)/dbname"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		panic(err)
	}
	defer db.Close()  // 注意这行代码要写在上面err判断的下面
}

1.1.3 Initializing the connection

The Open function may only verify whether its parameter format is correct, but actually does not No connection to the database is created. If you want to check whether the data source name is real and valid , you should call the Ping method 

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

//需要注意 这里需要引用自己的mysql文件

var db *sql.DB
func initDB()(err error)  {
	//账号 密码 端口号(tcp)127.0.0.1:3306 表名 字符集  校验时间
	dsn := "root:123456@tcp(127.0.0.1:3306)/gomysql?charset=utf8mb4&parseTime=true"
	//加载驱动
	//这里需要是=而不是:=因为我们是给全局变量(db)赋值
	db,err = sql.Open("mysql",dsn)
	if err!=nil {
		return err
	}
	//尝试和数据库建立连接(校验dsn正确)
	//然后用了ping命令
	err=db.Ping()
	if err!=nil {
		return err
	}
	return nil
}
func main() {
	err := initDB()
	if err!=nil {
		fmt.Printf("connect failed,err:%v\n",err)
		return
	}
}

##1.1.4SetMaxOpenConns

SetMaxOpenConns

Set the maximum number of connections to be established with the database. If n is greater than 0 and less than the maximum number of idle connections, the maximum number of idle connections will be reduced to a limit that matches the maximum number of open connections. If n<=0, the maximum number of open connections will not be limited, The default is 0 (no limit)

##1.1.5SetMaxIdleConns

func (db *DB) SetMaxIdleConns(n int)
Maximum number of idle connections in the connection pool

If n is greater than the maximum number of open connections, the new maximum number of idle connections will be reduced to Matches the

limit of the maximum number of open connections

. If n<=0, idle connections will not be retained.

1.2CRUD

##1.2.1 Create database and table

Let’s first create a database in MySQL Create a database named

sql_test
CREATE DATABASE sql_test;

Enter the database:

use sql_test;
Execute the following command to create a data table for testing:
CREATE TABLE `user` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT &#39;&#39;,
    `age` INT(11) DEFAULT &#39;0&#39;,
    PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

1.2.2 Query

Single row query

Single row query
db.QueryRow() Execute a query and expect to return at most one row of results (i.e. Row). QueryRow always returns a non-nil value and will not return a delayed error until the Scan method that returns the value is called. (For example: no result found) 
func (db *DB) QueryRow(query string, args ...interface{}) *Row
func queryRowDemo() {
   sqlStr := "select id, name, age from user where id=?"
   var u user
   // 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
   err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name, &u.age)
   if err != nil {
      fmt.Printf("scan failed, err:%v\n", err)
      return
   }
   fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}

Multiple row query

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)
// 查询多条数据示例
func queryMultiRowDemo() {
	sqlStr := "select id, name, age from user where id > ?"
	rows, err := db.Query(sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	// 非常重要：关闭rows释放持有的数据库链接
	defer rows.Close()

	// 循环读取结果集中的数据
	for rows.Next() {
		var u user
		err := rows.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			return
		}
		fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
	}
}
1.2.3 Insert data

Insert, update and delete operations all use the Exec method. 
func (db *DB) Exec(query string, args ...interface{}) (Result, error)

Exec executes a command (including query, delete, update, insert, etc.), and the returned Result is a summary of the executed SQL commands. The parameter args represents the placeholder parameters in the query.

The specific sample code for inserting data is as follows: 

// 插入数据
func insertRowDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "王五", 38)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() // 新插入数据的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}

##1.2.4 Update data

The specific sample code for updating data is as follows: 
// 更新数据
func updateRowDemo() {
	sqlStr := "update user set age=? where id = ?"
	ret, err := db.Exec(sqlStr, 39, 3)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

1.2.5删除数据

具体删除数据的示例代码如下：

// 删除数据
func deleteRowDemo() {
	sqlStr := "delete from user where id = ?"
	ret, err := db.Exec(sqlStr, 3)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}

总体

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

// 定义一个全局对象db
var db *sql.DB

// 定义一个初始化数据库的函数
func initDB() (err error) {
	// DSN:Data Source Name
	dsn := "root:123456@tcp(127.0.0.1:3306)/sql_test?charset=utf8&parseTime=True"
	// 不会校验账号密码是否正确
	// 注意！！！这里不要使用:=，我们是给全局变量赋值，然后在main函数中使用全局变量db
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	// 尝试与数据库建立连接（校验dsn是否正确）
	err = db.Ping()
	if err != nil {
		return err
	}
	return nil
}
type user struct {
	id   int
	age  int
	name string
}
func queryRowDemo() {
	sqlStr := "select id, name, age from user where id=?"
	var u user
	// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name, &u.age)
	if err != nil {
		fmt.Printf("scan failed, err:%v\n", err)
		return
	}
	fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}
// 查询多条数据示例
func queryMultiRowDemo() {
	sqlStr := "select id, name, age from user where id > ?"
	rows, err := db.Query(sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	// 非常重要：关闭rows释放持有的数据库链接
	defer rows.Close()

	// 循环读取结果集中的数据
	for rows.Next() {
		var u user
		err := rows.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			return
		}
		fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
	}
}
func insertRowDemo()  {
	sqlStr := "insert into user(name,age) value (?,?)"
	//
	ret,err := db.Exec(sqlStr,"王五",40)
	if err!=nil {
		fmt.Printf("inserf failed,err:%v\n",err)
		return
	}
	//插入成功之后需要返回这个id
	theID,err:=ret.LastInsertId()
	if err != nil{
		fmt.Printf("get the last insertid failed,err:%v\n",theID)
		return
	}
	fmt.Printf("insert success,theID is:%v\n",theID)

}
func updateRowDemo()  {

	sqlStr := "update user set name =? where id = ?"
	//执行含有sqlStr参数的语句
	ret,err:=db.Exec(sqlStr,"赵四",4)
	if err!=nil {
		fmt.Printf("update failed,err:%v\n",err)
		return
	}
	AnoID,err:=ret.RowsAffected()
	if err!=nil {
		fmt.Printf("updateRowAffected failed,err:%v\n",err)
		return
	}
	fmt.Printf("update success AnoID:%v\n",AnoID)

}
// 删除数据
func deleteRowDemo() {
	sqlStr := "delete from user where id = ?"
	ret, err := db.Exec(sqlStr, 5)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}
func main() {
	err := initDB() // 调用输出化数据库的函数
	if err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
		return
	}
	//queryRowDemo()
	//insertRowDemo()
    //updateRowDemo()
    deleteRowDemo()
	queryMultiRowDemo()
}

1.3MySQL预处理

1.3.1什么是预处理？

普通SQL语句执行过程：

  • 客户端对SQL语句进行占位符替换得到完整的SQL语句。

  • 客户端发送完整SQL语句到MySQL服务端

  • MySQL服务端执行完整的SQL语句并将结果返回给客户端。

预处理执行过程：

  • 把SQL语句分成两部分，命令部分与数据部分。

  • 先把命令部分发送给MySQL服务端，MySQL服务端进行SQL预处理。

  • 然后把数据部分发送给MySQL服务端，MySQL服务端对SQL语句进行占位符替换。

  • MySQL服务端执行完整的SQL语句并将结果返回给客户端

1.3.2为什么要预处理？

  • 优化MySQL服务器重复执行SQL的方法，可以提升服务器性能，提前让服务器编译，一次编译多次执行，节省后续编译的成本。

  • 避免SQL注入问题。

1.3.3 Go实现MySQL预处理 

func (db *DB) Prepare(query string) (*Stmt, error)

查询操作的预处理示例代码如下：

// 预处理查询示例
func prepareQueryDemo() {
	sqlStr := "select id, name, age from user where id > ?"
	stmt, err := db.Prepare(sqlStr)
	if err != nil {
		fmt.Printf("prepare failed, err:%v\n", err)
		return
	}
	defer stmt.Close()
	rows, err := stmt.Query(0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	defer rows.Close()
	// 循环读取结果集中的数据
	for rows.Next() {
		var u user
		err := rows.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			return
		}
		fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
	}
}

插入、更新和删除操作的预处理十分类似，这里以插入操作的预处理为例：

// 预处理插入示例
func prepareInsertDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	stmt, err := db.Prepare(sqlStr)
	if err != nil {
		fmt.Printf("prepare failed, err:%v\n", err)
		return
	}
	defer stmt.Close()
	_, err = stmt.Exec("小王子", 18)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	_, err = stmt.Exec("沙河娜扎", 18)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	fmt.Println("insert success.")
}

总结 其实就多了一个db.Prepare(sqlStr)

1.3.4 SQL注入问题

我们任何时候都不应该自己拼接SQL语句！

// sql注入示例
func sqlInjectDemo(name string) {
	sqlStr := fmt.Sprintf("select id, name, age from user where name=&#39;%s&#39;", name)
	fmt.Printf("SQL:%s\n", sqlStr)
	var u user
	err := db.QueryRow(sqlStr).Scan(&u.id, &u.name, &u.age)
	if err != nil {
		fmt.Printf("exec failed, err:%v\n", err)
		return
	}
	fmt.Printf("user:%#v\n", u)
}

此时以下输入字符串都可以引发SQL注入问题：

sqlInjectDemo("xxx&#39; or 1=1#")
sqlInjectDemo("xxx&#39; union select * from user #")
sqlInjectDemo("xxx&#39; and (select count(*) from user) <10 #")
数据库 占位符语法
MySQL <span style="font-family:Microsoft Yahei, Hiragino Sans GB, Helvetica, Helvetica Neue, 微软雅黑, Tahoma, Arial, sans-serif">?</span>
PostgreSQL $1, $2等
SQLite ? 和$1
Oracle <span style="font-family:Microsoft Yahei, Hiragino Sans GB, Helvetica, Helvetica Neue, 微软雅黑, Tahoma, Arial, sans-serif">:name</span>

【相关推荐：Go视频教程、编程教学】

The above is the detailed content of What is sql in go language. For more information, please follow other related articles on the PHP Chinese website!

Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：What are the disadvantages of go language?Next article：What are the disadvantages of go language?

Related articles

See more