Go-MySQL
MySQL ist eine relationale Datenbank. In diesem Artikel wird die Verwendung von MySQL mit Go vorgestellt.
Installation
Zuerst muss der MySQL-Treiber installiert werden.
bash
go get github.com/go-sql-driver/mysqlVerbindung herstellen
go
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
dsn := "user:password@tcp(127.0.0.1:3306)/database"
db, err := sql.Open("mysql", dsn)
if err != nil {
log.Fatal(err)
}
defer db.Close()
if err := db.Ping(); err != nil {
log.Fatal(err)
}
fmt.Println("Verbunden mit MySQL!")
}Tabelle erstellen
go
package main
import (
"database/sql"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, _ := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database")
defer db.Close()
query := `
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
email VARCHAR(100)
)`
_, err := db.Exec(query)
if err != nil {
log.Fatal(err)
}
fmt.Println("Tabelle erstellt!")
}Datensatz einfügen
go
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, _ := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database")
defer db.Close()
result, err := db.Exec(
"INSERT INTO users (name, age, email) VALUES (?, ?, ?)",
"Alice", 25, "alice@example.com",
)
if err != nil {
log.Fatal(err)
}
id, _ := result.LastInsertId()
fmt.Printf("Eingefügt mit ID: %d\n", id)
}Mehrere Datensätze einfügen
go
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, _ := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database")
defer db.Close()
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
}
stmt, err := tx.Prepare("INSERT INTO users (name, age, email) VALUES (?, ?, ?)")
if err != nil {
log.Fatal(err)
}
defer stmt.Close()
users := []struct {
name string
age int
email string
}{
{"Bob", 30, "bob@example.com"},
{"Charlie", 35, "charlie@example.com"},
}
for _, user := range users {
_, err := stmt.Exec(user.name, user.age, user.email)
if err != nil {
log.Fatal(err)
}
}
if err := tx.Commit(); err != nil {
log.Fatal(err)
}
fmt.Println("Mehrere Datensätze eingefügt!")
}Datensatz finden
go
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, _ := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database")
defer db.Close()
var id int
var name string
var age int
var email string
err := db.QueryRow(
"SELECT id, name, age, email FROM users WHERE name = ?",
"Alice",
).Scan(&id, &name, &age, &email)
if err != nil {
log.Fatal(err)
}
fmt.Printf("Gefunden: ID=%d, Name=%s, Alter=%d, Email=%s\n", id, name, age, email)
}Mehrere Datensätze finden
go
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, _ := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database")
defer db.Close()
rows, err := db.Query("SELECT id, name, age, email FROM users")
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var id int
var name string
var age int
var email string
if err := rows.Scan(&id, &name, &age, &email); err != nil {
log.Fatal(err)
}
fmt.Printf("Gefunden: ID=%d, Name=%s, Alter=%d, Email=%s\n", id, name, age, email)
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
}Datensatz aktualisieren
go
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, _ := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database")
defer db.Close()
result, err := db.Exec(
"UPDATE users SET age = ? WHERE name = ?",
26, "Alice",
)
if err != nil {
log.Fatal(err)
}
rows, _ := result.RowsAffected()
fmt.Printf("Aktualisierte Zeilen: %d\n", rows)
}Datensatz löschen
go
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, _ := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database")
defer db.Close()
result, err := db.Exec(
"DELETE FROM users WHERE name = ?",
"Alice",
)
if err != nil {
log.Fatal(err)
}
rows, _ := result.RowsAffected()
fmt.Printf("Gelöschte Zeilen: %d\n", rows)
}Transaktionen
go
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, _ := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database")
defer db.Close()
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
}
_, err = tx.Exec("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", "David", 40, "david@example.com")
if err != nil {
tx.Rollback()
log.Fatal(err)
}
_, err = tx.Exec("UPDATE users SET age = ? WHERE name = ?", 27, "Bob")
if err != nil {
tx.Rollback()
log.Fatal(err)
}
if err := tx.Commit(); err != nil {
log.Fatal(err)
}
fmt.Println("Transaktion erfolgreich!")
}Prepared Statements
go
package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, _ := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database")
defer db.Close()
stmt, err := db.Prepare("INSERT INTO users (name, age, email) VALUES (?, ?, ?)")
if err != nil {
log.Fatal(err)
}
defer stmt.Close()
for i := 0; i < 10; i++ {
_, err := stmt.Exec(fmt.Sprintf("User%d", i), 20+i, fmt.Sprintf("user%d@example.com", i))
if err != nil {
log.Fatal(err)
}
}
fmt.Println("Prepared Statements ausgeführt!")
}Connection Pool
go
package main
import (
"database/sql"
"log"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, _ := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database")
// Connection Pool konfigurieren
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(5 * time.Minute)
defer db.Close()
if err := db.Ping(); err != nil {
log.Fatal(err)
}
fmt.Println("Connection Pool konfiguriert!")
}