Examples for

MySQL

22 examples, in learning order. Unqualified names resolve in the connection's database; RETURNING is off unless enabled.

Start at 001: Your first query
Parameters
? or :name
RETURNING
Off unless enabled
Go code for
database/sql
Migrations
Built in (MySQL)
Driver
go-sql-driver/mysql
All about the MySQL dialect

Getting started

4 examples
  1. 001

    Your first query

    A tasks table and three queries; MySQL has no RETURNING, so the insert is execresult and LastInsertId hands back the AUTO_INCREMENT id.

Types and nullability

3 examples
  1. 005

    From SQL types to Go types

    One landing of fish read back column by column, printing the Go type each MySQL column type became, unsigned integers and DECIMAL included.

  2. 006

    JSON columns

    Chip shop orders kept in JSON columns, encoded into the generated JSON type and searched with -> and ->>, with MySQL handing back its own normalised text.

Dynamic queries

5 examples
  1. 010

    IN lists from Go slices

    Beach huts found and booked by passing Go slices to IN lists, including an empty slice and a slice after a scalar.

  2. 011

    Optional filters

    One boat trip search run four ways, where each optional filter is a pointer field and a nil pointer switches its filter off.

  3. 012

    Validated ORDER BY

    Regatta entries listed in the order a caller picks from generated constants, with raw text adding no ORDER BY at all.

  5. 014

    The runtime query builder

    Unclaimed lost property searched with filters, an order, a limit and a count chosen at run time, with unsafe columns, operators and directions refused.

Shaping results

4 examples
  1. 015

    Nested structs with sgl.embed

    Boats read with their harbour as a nested value through an INNER JOIN and their skipper as a nil-able pointer through a LEFT JOIN.

  2. 016

    Parents with child slices

    Chip shop orders collapsed from a LEFT JOIN into one row each, with their lines as a slice, by group_by.

  3. 017

    Range over rows

    Tide gauge readings ranged over one row at a time with a range-over-func iterator, once to the end and once stopping early.

Writing data

2 examples
  1. 021

    Insert or update

    INSERT ... ON DUPLICATE KEY UPDATE adds to an existing row through VALUES(), and its affected-row count of 1 or 2 says whether the row was inserted or updated.

  2. 022

    Batch and copyfrom

    A batch query takes a slice of Params and writes it as multi-row INSERTs, 16,383 four-column rows a statement to stay under MySQL's 65,535 bind variables.

Connections and transactions

1 examples
  1. 023

    Transactions

    RunInTx commits a transfer when its function returns nil and rolls it back on an error, and WithTx binds the queries to a transaction you begin yourself.

Migrations and seeds

1 examples
  1. 027

    Migrations at start-up

    The migration files are embedded in the binary, and each start-up runs migrate.NewMigrator with the MySQL backend and Up, which applies only what the database lacks.

Engine features

2 examples
  1. 030

    Functions in migrations

    Stored functions created in a migration type the queries that call them, and their data-access characteristic, body or a sgl.migration(readonly:) line decides whether each query goes to the writer or the reader.

  2. 039

    MySQL dialect features

    A harbour register kept with INSERT IGNORE, REPLACE INTO, a SET column read with FIND_IN_SET, unsigned integers and GROUP_CONCAT, printing what each statement did.

Show 31 not available for MySQL

Some ideas belong to other engines, and some need a feature MySQL does not have. Each one says why, and where to read it instead.