Delete

Bob を使用した MySQL の DELETE クエリの例を示します。

基本的な DELETE

DELETE FROM users WHERE id = ?

mysql.Delete("users").
    Where(mysql.Quote("id").EQ(mysql.Arg(1)))

複数条件での DELETE

DELETE FROM users 
WHERE active = ? AND created_at < ?

mysql.Delete("users").
    Where(
        mysql.Quote("active").EQ(mysql.Arg(false)),
        mysql.Quote("created_at").LT(mysql.Arg(time.Now().AddDate(0, -1, 0))),
    )

JOIN を使用した DELETE

DELETE u FROM users u
JOIN user_profiles p ON u.id = p.user_id
WHERE p.status = ?

mysql.Delete("users").As("u").
    Join("user_profiles").As("p").
    On(mysql.Quote("u", "id").EQ(mysql.Quote("p", "user_id"))).
    Where(mysql.Quote("p", "status").EQ(mysql.Arg("inactive")))

LIMIT 付きの DELETE

DELETE FROM logs 
WHERE created_at < ?
ORDER BY created_at ASC
LIMIT ?

mysql.Delete("logs").
    Where(mysql.Quote("created_at").LT(mysql.Arg(time.Now().AddDate(0, -1, 0)))).
    OrderBy(mysql.Quote("created_at").Asc()).
    Limit(1000)

IN 句を使用した DELETE

DELETE FROM users WHERE id IN (?, ?, ?)

mysql.Delete("users").
    Where(mysql.Quote("id").In(mysql.Arg(1), mysql.Arg(2), mysql.Arg(3)))

サブクエリを使用した DELETE

DELETE FROM users 
WHERE id IN (
    SELECT user_id FROM user_sessions 
    WHERE last_activity < ?
)

subquery := mysql.Select("user_id").
    From("user_sessions").
    Where(mysql.Quote("last_activity").LT(mysql.Arg(time.Now().AddDate(0, -1, 0))))

mysql.Delete("users").
    Where(mysql.Quote("id").In(subquery))

複数テーブルからの DELETE

DELETE u, p FROM users u
JOIN user_profiles p ON u.id = p.user_id
WHERE u.active = ?

mysql.Delete("users", "user_profiles").
    From("users").As("u").
    Join("user_profiles").As("p").
    On(mysql.Quote("u", "id").EQ(mysql.Quote("p", "user_id"))).
    Where(mysql.Quote("u", "active").EQ(mysql.Arg(false)))

条件付きの一括削除

DELETE FROM audit_logs 
WHERE created_at < ? 
  AND level = 'DEBUG'
ORDER BY created_at ASC
LIMIT ?

mysql.Delete("audit_logs").
    Where(
        mysql.Quote("created_at").LT(mysql.Arg(time.Now().AddDate(0, -3, 0))),
        mysql.Quote("level").EQ(mysql.Arg("DEBUG")),
    ).
    OrderBy(mysql.Quote("created_at").Asc()).
    Limit(10000)

これらの例は、Bob を使用して MySQL で実行可能な様々な DELETE クエリパターンを示しています。

Insert