mysql

type DB ¶

type DB struct {
	sql2.Base
	// contains filtered or unexported fields
}

DB is the goradd driver for mysql databases. It works through the excellent go-sql-driver driver, to supply functionality above go's built in driver. To use it, call NewDB, but afterward, work through the DB parent interface so that the underlying database can be swapped out later if needed.

Primary Keys ¶

Historically, Mysql uses auto generated integers as primary keys. Newer versions of Mysql are able to generate UUIDs, but the implementation is v1 only, and is not the most secure, nor the best for balancing storage needs. To use UUIDs, instead override the getXXXinsertFields function and manually set your own UUID.

Timezones ¶

Mysql has some interesting quirks:

• Datetime types are internally stored in the timezone of the server, and then returned based on the timezone of the client.
• Timestamp types are internally stored in UTC and returned in the timezone of the client.

This means that when querying based on DateTime fields, you need to be aware that the timezone will have shifted to the timezone of the server. Also, when transferring or syncing between databases in different timezones, you will need to account for the time differences.

The easiest way to handle this is to set the timezone of the server to UTC, and make sure all time values are stored in UTC.

When displaying times to users, change the timezone to that of the user, which may not be the Mysql server's timezone, and may not be the timezone of the computer running the go-sql-driver (which from MySQL's perspective is the client).

The mysql-go-driver has the ability to set a default timezone in the Loc configuration parameter. It defaults to UTC. It appears to convert all times to this timezone before sending them to the database, and then when receiving times, it will set this as the timezone of the date. It is best to set this and your database to UTC, as this will make your database portable to other timezones.

These issues are further compounded by the fact that MYSQL can initialize date and time values to what it believes is the current date and time in its server's timezone, but will not save the timezone itself. Because of that, you should initialize values in the application, rather than using MySQL's ability to set a default value.

Set the ParseTime configuration parameter to TRUE so that the driver will parse the times into the correct timezone, navigating the GO server and database server timezones. Otherwise, we can only assume that the database is in UTC time, since we will not get any timezone info from the server.

Be aware that when you view the data in SQL, it will appear in whatever timezone the MYSQL server is set to.

func NewDB(dbKey string,
	connectionString string,
	config *mysql.Config) (*DB, error)

func (m *DB) DeleteUsesAlias() bool

func (m *DB) ExtractSchema(options map[string]any) schema2.Database

func (m *DB) FormatArgument(_ int) string

func (m *DB) Insert(ctx context.Context, table string, fields map[string]any, autoPkKey string) error

func (m *DB) OperationSql(op Operator, operands []Node, operandStrings []string) (sql string)

func (m *DB) QuoteIdentifier(v string) string

func (m *DB) SupportsForUpdate() bool

func (m *DB) TableDefinitionSql(d *schema2.Database, table *schema2.Table) (tableSql string, extraClauses []string)

func (m *DB) Update(ctx context.Context,
	table string,
	primaryKey map[string]any,
	changes map[string]any,
	optLockFieldName string,
	optLockFieldValue int64,
) (err error)

func (m *DB) WithConstraintsOff(ctx context.Context, f func(ctx context.Context) error) (err error)