Golang Database Operations

This is a creation in Article, where the information may have evolved or changed. Golang Database Operations


Golang Database MySQL


The Golang Support DB operation is located under the database package and supports basic CRUD operations, transactions, and prepared Statement, in this case, MySQL.


MySQL driver installation
Before using the database, you must install the relevant MySQL data driver [1], if the driver is not installed or not introduced to the driver Library will report the following error:
Failed to open Database:sql:unknown driver "MySQL" (Forgotten import?)
Install Golang MySQL Driver
Go get Github.com/go-sql-driver/mysql
The code also needs to register the database driver, by introducing a blank to pour [2]mysql package to complete


Import
"Database/sql"
Introduction of database-driven registration and initialization
_ "Github.com/go-sql-driver/mysql")
This blank is poured into the code that actually executes the initialization code of the MySQL package, located in the%gopath%/github.com/go-sql-driver/mysql/driver.go


Func init () {
Sql. Register ("MySQL", &mysqldriver{})
}
Preparing test data
Connect to MySQL
Mysql-uroot-p
Select Database Test
Use test
Create the Users and order tables for the test and insert the test data


#创建user表
DROP TABLE IF EXISTS ' order ';
DROP TABLE IF EXISTS ' user ';
CREATE TABLE IF not EXISTS ' user ' (' uid ' SERIAL PRIMARY KEY, ' name ' varchar (a) ' not ' null ', ' password ' varchar ') ' Engine= ' InnoDB ', CHARACTER Set=utf8;
#创建order表
CREATE TABLE IF not EXISTS ' order ' (' oid ' SERIAL PRIMARY KEY, ' uid ' BIGINT (a) UNSIGNED not NULL, ' date ' TIMESTAMP not NUL L DEFAULT Current_timestamp, FOREIGN KEY (' uid ') REFERENCES ' user ' (' uid ')) Engine=innodb,character Set=utf8;
#插入测试数据
INSERT into ' user ' (' name ', ' Password ') VALUES (' Nick ', ' Nick '), (' Jacky ', ' Jacky ');
INSERT into ' order ' (' uid ') VALUES (1), (2);
Connecting to a database
The DSN format for the connection data is:
Username:password@protocol (address)/dbname?param=value
Example code:


DB, err: = SQL. Open ("MySQL", "root:root@tcp (127.0.0.1:3306)/test?charset=utf8")
If err! = Nil {
Fmt. Println ("Failed to open database:", err.) Error ())
Return
}
If err: = db. Ping (); Err! = Nil {
Fmt. Println ("%s Error ping database:%s", err.) Error ())
Return
}
Defer db. Close ()
The returned DB object, which actually encapsulates a database connection pool, is thread-safe for goroutine and can be used with confidence. This database connection pool is automatically created and reclaimed by the "Database/sql" package. The size of the connection pool can be specified by Setmaxidleconns.
It should be noted that the successful creation of the DB object does not mean that the database has been successfully connected, and that the database connection is created only when it is really needed. Therefore, if you want to verify that the database connection is valid after you create the DB object, you can call ping () or


Close the database


Defer db. Close ()
Close the database and release all open resources. In fact, close operations are rarely required, and DB objects are actually long-lived and shared across all goroutine


Crud
Execute SQL in DB through the Exec and query methods, the query is done through query and it returns a SQL. Rows result set containing a cursor used to traverse the query results; The Exec method returns a Sql.result object that detects the result of the operation and the number of records affected


Inquire


Get the first 10 rows of records in the Users table
Rows, err: = db. Query ("SELECT * from User")
If err! = Nil {
Fmt. Println ("Fetech Data failed:", err. Error ())
Return
}
Defer rows. Close ()
For rows. Next () {
var uid int
var name, password string
Rows. Scan (&uid, &name, &password)
Fmt. Println ("UID:", UID, "name:", Name, "Password:", password)
}
Note: Rows must be closed or the database connection cannot be closed, and long runs will cause "too many connections"
Insert


Insert a new piece of data
result, err: = db. Exec ("INSERT into ' user ' (' name ', ' Password ') VALUES (' Tom ', ' Tom ') ')
If err! = Nil {
Fmt. Println ("Insert data failed:", err.) Error ())
Return
}
ID, err: = result. Lastinsertid ()
If err! = Nil {
Fmt. PRINTLN ("Fetch last Insert ID failed:", err.) Error ())
Return
}
Fmt. Println ("Insert new record", id)
Update


Update One piece of data
result, err = db. Exec ("UPDATE ' user ' SET ' password ' =?") WHERE ' name ' =? "," Tom_new_password "," Tom ")
If err! = Nil {
Fmt. PRINTLN ("Update data failed:", err.) Error ())
Return
}
num, err: = result. Rowsaffected ()
If err! = Nil {
Fmt. PRINTLN ("Fetch row affected failed:", err.) Error ())
Return
}
Fmt. Println ("Update recors number", num)
Delete


Delete data
result, err = db. Exec ("DELETE from ' user ' WHERE ' name ' =?", "Tom")
If err! = Nil {
Fmt. Println ("Delete data failed:", err.) Error ())
Return
}
num, err = result. Rowsaffected ()
If err! = Nil {
Fmt. PRINTLN ("Fetch row affected failed:", err.) Error ())
Return
}
Fmt. Println ("Delete record number", num)
Transaction support
Sql. TX is used to support transaction processing


Transaction processing
Completely delete user data with user number 2
TX, Err: = db. Begin ()
result, err = TX. Exec ("DELETE from ' order ' WHERE uid=?", 2)
If err! = Nil {
Fmt. Println ("Delete data failed:", err.) Error ())
Return
}
num, err = result. Rowsaffected ()
If err! = Nil {
Fmt. PRINTLN ("Fetch row affected failed:", err.) Error ())
Return
}
Fmt. Println ("Delete record number", num)
result, err = TX. Exec ("DELETE from ' user ' WHERE uid=?", 2)
If err! = Nil {
Fmt. Println ("Delete data failed:", err.) Error ())
Return
}
num, err = result. Rowsaffected ()
If err! = Nil {
Fmt. PRINTLN ("Fetch row affected failed:", err.) Error ())
Return
}
Fmt. Println ("Delete record number", num)
Rollback or commit based on condition
Tx. Rollback ()
Tx.commit ()
Prepared Statement
Sql. STMT supports preliminary expressions that can be used to optimize SQL queries to improve performance and reduce the risk of SQL injection, DB. Both Prepare () and Tx.prepare () provide support for the pre-expression.


Preliminary expressions
stmt, err: = db. Prepare ("DELETE from ' order ' WHERE ' oid ' =?")
If err! = Nil {
Fmt. PRINTLN ("Fetch row affected failed:", err.) Error ())
Return
}
result, err = stmt. Exec (1)
If err! = Nil {
Fmt. Println ("Delete data failed:", err.) Error ())
Return
}
num, err = result. Rowsaffected ()
If err! = Nil {
Fmt. PRINTLN ("Fetch row affected failed:", err.) Error ())
Return
}
Fmt. Println ("Delete record number", num)