Golang Database Operations

Source: Internet
Author: User
Tags dsn format
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)
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.