Go语言的数据库操作

Go语言的数据库操作数据库操作基础Go语言提供了标准的数据库接口database/sql包用于与各种数据库进行交互。同时需要为特定数据库安装相应的驱动。基本使用安装驱动以MySQL为例安装驱动go get github.com/go-sql-driver/mysql连接数据库package main import ( database/sql fmt _ github.com/go-sql-driver/mysql ) func main() { db, err : sql.Open(mysql, username:passwordtcp(localhost:3306)/database) if err ! nil { fmt.Println(Error connecting to database:, err) return } defer db.Close() // 测试连接 err db.Ping() if err ! nil { fmt.Println(Error pinging database:, err) return } fmt.Println(Connected to database successfully) }执行SQL语句查询数据package main import ( database/sql fmt _ github.com/go-sql-driver/mysql ) type User struct { ID int Name string Age int } func main() { db, _ : sql.Open(mysql, username:passwordtcp(localhost:3306)/database) defer db.Close() rows, err : db.Query(SELECT id, name, age FROM users) if err ! nil { fmt.Println(Error querying database:, err) return } defer rows.Close() var users []User for rows.Next() { var user User err : rows.Scan(user.ID, user.Name, user.Age) if err ! nil { fmt.Println(Error scanning row:, err) return } users append(users, user) } for _, user : range users { fmt.Printf(ID: %d, Name: %s, Age: %d\n, user.ID, user.Name, user.Age) } }插入数据package main import ( database/sql fmt _ github.com/go-sql-driver/mysql ) func main() { db, _ : sql.Open(mysql, username:passwordtcp(localhost:3306)/database) defer db.Close() result, err : db.Exec(INSERT INTO users (name, age) VALUES (?, ?), John, 30) if err ! nil { fmt.Println(Error inserting data:, err) return } id, err : result.LastInsertId() if err ! nil { fmt.Println(Error getting last insert ID:, err) return } fmt.Printf(Inserted user with ID: %d\n, id) }更新数据package main import ( database/sql fmt _ github.com/go-sql-driver/mysql ) func main() { db, _ : sql.Open(mysql, username:passwordtcp(localhost:3306)/database) defer db.Close() result, err : db.Exec(UPDATE users SET age ? WHERE id ?, 31, 1) if err ! nil { fmt.Println(Error updating data:, err) return } rowsAffected, err : result.RowsAffected() if err ! nil { fmt.Println(Error getting rows affected:, err) return } fmt.Printf(Updated %d rows\n, rowsAffected) }删除数据package main import ( database/sql fmt _ github.com/go-sql-driver/mysql ) func main() { db, _ : sql.Open(mysql, username:passwordtcp(localhost:3306)/database) defer db.Close() result, err : db.Exec(DELETE FROM users WHERE id ?, 1) if err ! nil { fmt.Println(Error deleting data:, err) return } rowsAffected, err : result.RowsAffected() if err ! nil { fmt.Println(Error getting rows affected:, err) return } fmt.Printf(Deleted %d rows\n, rowsAffected) }使用事务package main import ( database/sql fmt _ github.com/go-sql-driver/mysql ) func main() { db, _ : sql.Open(mysql, username:passwordtcp(localhost:3306)/database) defer db.Close() tx, err : db.Begin() if err ! nil { fmt.Println(Error starting transaction:, err) return } // 执行多个SQL语句 _, err tx.Exec(INSERT INTO users (name, age) VALUES (?, ?), John, 30) if err ! nil { tx.Rollback() fmt.Println(Error inserting user:, err) return } _, err tx.Exec(INSERT INTO orders (user_id, product) VALUES (?, ?), 1, Product 1) if err ! nil { tx.Rollback() fmt.Println(Error inserting order:, err) return } // 提交事务 err tx.Commit() if err ! nil { fmt.Println(Error committing transaction:, err) return } fmt.Println(Transaction completed successfully) }使用预处理语句package main import ( database/sql fmt _ github.com/go-sql-driver/mysql ) func main() { db, _ : sql.Open(mysql, username:passwordtcp(localhost:3306)/database) defer db.Close() // 准备预处理语句 stmt, err : db.Prepare(SELECT id, name, age FROM users WHERE age ?) if err ! nil { fmt.Println(Error preparing statement:, err) return } defer stmt.Close() // 执行预处理语句 rows, err : stmt.Query(25) if err ! nil { fmt.Println(Error executing statement:, err) return } defer rows.Close() for rows.Next() { var id int var name string var age int err : rows.Scan(id, name, age) if err ! nil { fmt.Println(Error scanning row:, err) return } fmt.Printf(ID: %d, Name: %s, Age: %d\n, id, name, age) } }数据库连接池package main import ( database/sql fmt _ github.com/go-sql-driver/mysql ) func main() { db, _ : sql.Open(mysql, username:passwordtcp(localhost:3306)/database) defer db.Close() // 配置连接池 db.SetMaxOpenConns(25) // 最大打开连接数 db.SetMaxIdleConns(5) // 最大空闲连接数 fmt.Println(Connection pool configured successfully) }示例完整的数据库操作package main import ( database/sql fmt log _ github.com/go-sql-driver/mysql ) type User struct { ID int Name string Age int } func main() { // 连接数据库 db, err : sql.Open(mysql, username:passwordtcp(localhost:3306)/database) if err ! nil { log.Fatal(Error connecting to database:, err) } defer db.Close() // 测试连接 err db.Ping() if err ! nil { log.Fatal(Error pinging database:, err) } // 创建表 _, err db.Exec(CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age INT NOT NULL )) if err ! nil { log.Fatal(Error creating table:, err) } // 插入数据 result, err : db.Exec(INSERT INTO users (name, age) VALUES (?, ?), John, 30) if err ! nil { log.Fatal(Error inserting data:, err) } id, _ : result.LastInsertId() fmt.Printf(Inserted user with ID: %d\n, id) // 查询数据 rows, err : db.Query(SELECT id, name, age FROM users) if err ! nil { log.Fatal(Error querying data:, err) } defer rows.Close() var users []User for rows.Next() { var user User err : rows.Scan(user.ID, user.Name, user.Age) if err ! nil { log.Fatal(Error scanning row:, err) } users append(users, user) } fmt.Println(Users:) for _, user : range users { fmt.Printf(ID: %d, Name: %s, Age: %d\n, user.ID, user.Name, user.Age) } // 更新数据 _, err db.Exec(UPDATE users SET age ? WHERE id ?, 31, id) if err ! nil { log.Fatal(Error updating data:, err) } // 删除数据 _, err db.Exec(DELETE FROM users WHERE id ?, id) if err ! nil { log.Fatal(Error deleting data:, err) } fmt.Println(Database operations completed successfully) }总结Go语言的database/sql包提供了统一的数据库操作接口支持多种数据库。通过合理使用连接池、预处理语句和事务可以编写高效、安全的数据库操作代码。