mysqlx

More Badages

Supported Go version

• Go 1.19+

Supported MySQL data types

• Signed Integers:
  • bigint(n), int(n), smallint(n), tinyint(n)
• Unsigned Integers:
  • bigint(n) unsigned: Should be configured as ubigint(n) in struct tag
  • int(n) unsigned: Should be configured as uint(n) in struct tag
  • smallint(n) unsigned: Should be configured as usmallint(n) in struct tag
  • tinyint(n) unsigned: Should be configured as utinyint(n) in struct tag
• Boolean:
  • BOOL
• String:
  • varchar(n)
  • char(n)
• Date / Time Types:
  • timestamp
  • datetime, datetime(n)
  • time, time(n)
  • date
  • year

Notes

Error sql: **** unsupported Scan, storing driver.Value type []uint8 into type *time.Time

reference: Stackoverflow

This is because sqlx does not parse *time.Time by default. Add "parseTime=true" parameter then opening MySQL with sqlx.

Changelog

Click here

Simple Benchmask Test Result

Please refer to benchmark test file. Temporally only SELECT is tested. Several conditions among mysqlx, sqlx and gorm(v1) are tested.

Benchmark test statement: go test -bench=. -run=none -benchmem -benchtime=10s. Test table sise: 100000.

Select by main key

Select a record by auto-increment main key. This is the most basic way to reading record. We use statements for each package conditions:

• mysqlx: db.Select(&res, mysqlx.Condition("id", "=", id))
• sqlx (with "= 1234"): db.Select(&res, "SELECT * FROM t_student WHERE id=" + idStr)
• sqlx (with "= ?"): db.Select(&res, "SELECT * FROM t_student WHERE id=?", id)
• gorm (with 'Find' function): d.Where("id = ?", id+1).Find(&res)
• gorm (with 'First' function): d.First(&res, id)

Package	nanoseconds/op	bytes/op	allocs/op
mysqlx	1,038,348	1696	37
sqlx (with "= 1234")	1,115,127	1039	18
sqlx (with "= ?")	2,112,185	1247	26
gorm (with 'Find' function)	2,256,562	6641	105
gorm (with 'First' function)	1,114,290	4295	97

Select By A VARCHAR Field

One of the t_student field is generated by uuid. We use statements for each packages:

• mysqlx: db.Select(&res, Condition("name", "=", name))
• sqlx (with "= 'Alice'"): db.Select(&res, "SELECT * FROM t_student WHERE name='" + name + "'")
• sqlx (with "= ?"): db.Select(&res, "SELECT * FROM t_student WHERE name=?", name)
• gorm (with 'Find' function): d.Where("name = ?", name).Find(&res)
• gorm (with 'First' function): d.Where("name = ?", name).First(&res)

Package	nanoseconds/op	bytes/op	allocs/op
mysqlx	1,247,630	1848	37
sqlx (with "= 'Alice'")	1,146,627	1064	18
sqlx (with "= ?")	2,023,415	1240	25
gorm (with 'Find' function)	2,073,272	6625	104
gorm (with 'First' function)	2,207,229	5377	116