ENUM and SET column types cannot be identified

[jennifersp]

Issue description

ENUM and SET column types cannot be identified. MySQL do not return MYSQL_TYPE_ENUM or MYSQL_TYPE_SET, so https://github.com/go-sql-driver/mysql/blob/master/fields.go#L34 and https://github.com/go-sql-driver/mysql/blob/master/fields.go#L73 do not catch those cases. Instead, MySQL set the flag field with ENUM_FLAG and SET_FLAG

Example code

package main

import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
	"log"
)

func main() {
	conn, err := sql.Open("mysql", "root:@tcp(127.0.0.1)/mydb")
	if err != nil {
		log.Fatal(err)
	}

	_, err = conn.Exec("DROP TABLE IF EXISTS test")
	if err != nil {
		log.Fatal(err)
	}

	_, err = conn.Exec("CREATE TABLE `test` (`id` bigint PRIMARY KEY, `e` enum('', 'v1', 'v2'), `s` set('', 'v1', 'v2'))")
	if err != nil {
		log.Fatal(err)
	}
	if err != nil {
		log.Fatal(err)
	}

	_, err = conn.Exec("INSERT INTO test VALUES (1, '', '')")
	if err != nil {
		log.Fatal(err)
	}

	rows, err := conn.Query("SELECT e, s FROM `test`")
	if err != nil {
		log.Fatal(err)
	}

	columnTypes, err := rows.ColumnTypes()
	if err != nil {
		log.Fatal(err)
	}

	for _, t := range columnTypes {
		log.Printf("the type of column '%s': %s\n", t.Name(), t.DatabaseTypeName())
	}
}

The result of above code:

the type of column 'e': CHAR
the type of column 's': CHAR

This can be fixed with the following at https://github.com/go-sql-driver/mysql/blob/master/fields.go#L79:

         case fieldTypeString:
		if mf.flags&flagEnum != 0 {
			return "ENUM"
		} else if mf.flags&flagSet != 0 {
			return "SET"
		}
		if mf.charSet == binaryCollationID {
			return "BINARY"
		}

The result after suggested fix:

the type of column 'e': ENUM
the type of column 's': SET

If this fix is not the correct way to address this issue, I would like to request an interface to expose the enum and set flag info.

Configuration

Driver version (or git SHA): v1.7.1

Go version: go1.21.1

Server version: MySQL 8.1.0

Server OS: MacOS 14.1.1

[methane]

The patch looks good to me. Please create a pull request.

[jennifersp]

Hi @methane, I just create pull request for it mentioning this issue. Thank you!

[methane]

Thank you.