🦡

GolangでSQLServer(その1)

2023/06/12に公開

はじめに

前回は、Golang で Oracle の各種データ型の入出力を検証しました。

https://zenn.dev/robon/articles/9ab6cb9e1b9d1c

今回は、Microsoft の SQLServer を評価します。

SQLServer

データベース

Amazon RDS 上に構築した SQLServer サーバーを使用します。

設定
エンジンバージョン 15.00.4236.7.v1
日本語 SQL_Latin1_General_CP1_CI_AS
インスタンスクラス db.t3.small
vCPU 2
RAM 2GB
ストレージ 20GiB

クライアント

Amazon EC2 の Amazon Linux 2 から接続します。

ツール

sqlcmd を使用しますので、マイクロソフト社のサイト を参考にして導入します。

Amazon Linux 2 の場合は、以下のようにインストールします。

$ sudo su
# curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/msprod.repo
# exit
$ sudo yum install mssql-tools

テスト用のテーブル

データ型の一覧

マニュアル から抜粋します。

型名 範囲 容量 説明
bigint -2^63~2^63-1 8byte
int -2^31~2^31-1 4byte
smallint -2^15~2^15-1 2byte
tinyint 2^0-1~2^8-1 1byte
bit 0, 1, null 1~2byte 0 以外は全て 1
decimal[(p[,s])] -10^38+1~10^38-1 5~17byte numericも同じ
money -922,337,203,685,477.5808~922,337,203,685,477.5807 8byte
smallmoney -214,748.3648~214,748.3647 4byte
float[(n)] -1.79E+308~-2.23E-308、0、2.23E-308~1.79E+308 4 or 8byte
real -3.40E+38~-1.18E-38、0、1.18E-38~3.40E+38 4byte float(24)の別名
date 0001-01-01~9999-12-31 3byte 1900-01-01が規定値
datetime 1753-01-01 00:00:00~9999-12-31 23:59:59 8byte 1900-01-01 00:00:00 が規定値
datetime2 0001-01-01 00:00:00~9999-12-31 23:59:59.9999999 6~8byte 1900-01-01 00:00:00 が規定値
datetimeoffset 0001-01-01 00:00:00+14:00~9999-12-31 23:59:59.9999999-14:00 10byte 1900-01-01 00:00:00 00:00が規定値
smalldatetime 1900-01-01 00:00:00~2079-06-06 23:59:59 4byte 29.999秒で分に丸め。1900-01-01 00:00:00が規定値
time 00:00:00.0000000~23:59:59.9999999 5byte 00:00:00が規定値
char[(n)] 1~8000byte 固定長文字列
varchar[(n)] 1~8000byte 可変長文字列
text 2^31-1byte 可変長文字列
nchar[(n)] 1~8000byte n は 1~4000文字。固定長文字列
nvarchar[(n|max)] 1~8000byte または 2GB n は 1~4000文字。可変長文字列
ntext ~ 2^30-1byte 可変長Unicodeデータ
binary[(n)] 1~8000byte 固定長バイナリ
varbinary[(n|max)] 1~8000byte または 2^31-1byte 可変長バイナリ
image 0~2^31-1byte 可変長バイナリ
uniqueidentifier 16byte GUID。例:6F9619FF-8B86-D011-B42D-00C04FC964FF
sql_variant ~8016byte サポートされている他の型を入れられる
xml [([CONTENT|DOCUMENT]xml_schema_collection)] ~2GB XMLデータ

テスト用のテーブル

そのまま CREATE TABLE文にします。

CREATE TABLE datatypes (
    col01 bigint,
    col02 int,
    col03 smallint,
    col04 tinyint,
    col05 bit,
    col06 decimal(10,4),
    col07 money,
    col08 smallmoney,
    col09 float(53),
    col10 real,
    col11 date,
    col12 datetime,
    col13 datetime2,
    col14 datetimeoffset,
    col15 smalldatetime,
    col16 time,
    col17 char(10),
    col18 varchar(10),
    col19 text,
    col20 nchar(10),
    col21 nvarchar(10),
    col22 ntext,
    col23 binary(10),
    col24 varbinary(10),
    col25 image,
    col26 uniqueidentifier,
    col27 sql_variant,
    col28 xml,
    CONSTRAINT pk_datatypes PRIMARY KEY(col01)
);
go

Golang

処理系

$ go version
go version go1.18.6 linux/amd64

ライブラリ

github.com/microsoft/go-mssqldb の v0.21.0 を使用します。データ型については、以下の仕様でつくられているようです。

https://github.com/microsoft/go-mssqldb/blob/v0.21.0/types.go#L1022-L1590

アプリケーション

テスト用のテーブルに対して入出力を行うアプリケーションを実装します。歯抜けの状態から徐々に埋めていけるように、Nullable 型で入出力します。

main.go
package main

import (
	"database/sql"
	"log"
	"os"
	"reflect"
	"time"

	"github.com/google/go-cmp/cmp"
	"github.com/jmoiron/sqlx"
	_ "github.com/microsoft/go-mssqldb"
)

// Datatypes は、データ型テストテーブル
type Datatypes struct {
	Col01 sql.NullInt64   `db:"col01"` // bigint
	Col02 sql.NullInt64   `db:"col02"` // int
	Col03 sql.NullInt64   `db:"col03"` // smallint
	Col04 sql.NullInt64   `db:"col04"` // tinyint
	Col05 sql.NullBool    `db:"col05"` // bit
	Col06 sql.NullString  `db:"col06"` // decimal(10,4)
	Col07 sql.NullString  `db:"col07"` // money
	Col08 sql.NullString  `db:"col08"` // smallmoney
	Col09 sql.NullFloat64 `db:"col09"` // float(53)
	Col10 sql.NullFloat64 `db:"col10"` // real
	Col11 sql.NullTime    `db:"col11"` // date
	Col12 sql.NullTime    `db:"col12"` // datetime
	Col13 sql.NullTime    `db:"col13"` // datetime2
	Col14 sql.NullTime    `db:"col14"` // datetimeoffset
	Col15 sql.NullTime    `db:"col15"` // smalldatetime
	Col16 sql.NullTime    `db:"col16"` // time
	Col17 sql.NullString  `db:"col17"` // char(10)
	Col18 sql.NullString  `db:"col18"` // varchar(10)
	Col19 sql.NullString  `db:"col19"` // text
	Col20 sql.NullString  `db:"col20"` // nchar(10)
	Col21 sql.NullString  `db:"col21"` // nvarchar(10)
	Col22 sql.NullString  `db:"col22"` // ntext
	Col23 []byte          `db:"col23"` // binary(10)
	Col24 []byte          `db:"col24"` // varbinary(10)
	Col25 []byte          `db:"col25"` // image
	Col26 []byte          `db:"col26"` // uniqueidentifier
	// Col27 []byte          `db:"col27"` // sql_variant mssql: Operand type clash: varbinary(max) is incompatible with sql_variant
	Col28 sql.NullString `db:"col28"` // xml
}

// Key は、データ型テストテーブルのキー
type Key struct {
	Col01 int64 `db:"col01"`
}

func main() {
	dsn := os.Getenv("DSN")
	db, err := sqlx.Open("sqlserver", dsn)
	if err != nil {
		log.Printf("sql.Open error %s", err)
	}

	JST, _ := time.LoadLocation("Asia/Tokyo")
	key := Key{1}
	src := Datatypes{
		Col01: sql.NullInt64{Int64: 1, Valid: true},
		Col02: sql.NullInt64{Int64: 2, Valid: true},
		Col03: sql.NullInt64{Int64: 3, Valid: true},
		Col04: sql.NullInt64{Int64: 4, Valid: true},
		Col05: sql.NullBool{Bool: true, Valid: true},
		Col06: sql.NullString{String: "123456.1234", Valid: true},
		Col07: sql.NullString{String: "123456.1234", Valid: true},
		Col08: sql.NullString{String: "123456.1234", Valid: true},
		Col09: sql.NullFloat64{Float64: 1.5, Valid: true},
		Col10: sql.NullFloat64{Float64: 2.125, Valid: true},
		Col11: sql.NullTime{Time: time.Date(2001, 2, 3, 0, 0, 0, 0, time.UTC), Valid: true},
		Col12: sql.NullTime{Time: time.Date(2001, 2, 3, 4, 5, 6, 7000000, time.UTC), Valid: true},      // ミリ秒あり
		Col13: sql.NullTime{Time: time.Date(2001, 2, 3, 4, 5, 6, 7008000, time.UTC), Valid: true},      // マイクロ秒あり
		Col14: sql.NullTime{Time: time.Date(2001, 2, 3, 4, 5, 6, 7008000, JST), Valid: true},           // TZあり
		Col15: sql.NullTime{Time: time.Date(2001, 2, 3, 4, 5, 0, 0, time.UTC), Valid: true},            // 秒丸め
		Col16: sql.NullTime{Time: time.Date(0001, 1, 1, 12, 34, 56, 123456700, time.UTC), Valid: true}, // 0001-01-01固定(string は NG)
		Col17: sql.NullString{String: "char(10)  ", Valid: true},
		Col18: sql.NullString{String: "varchar", Valid: true},
		Col19: sql.NullString{String: "text", Valid: true},
		Col20: sql.NullString{String: "nchar(10) ", Valid: true},
		Col21: sql.NullString{String: "nvarchar", Valid: true},
		Col22: sql.NullString{String: "ntext", Valid: true},
		Col23: []byte{1, 2, 3, 0, 0, 0, 0, 0, 0, 0}, // 末尾ゼロ埋め
		Col24: []byte{1, 2, 3, 4},
		Col25: []byte{1, 2, 3, 4, 5},
		Col26: []byte{
			0x6F, 0x96, 0x19, 0xFF, 0x8B, 0x86, 0xD0, 0x11, 0xB4, 0x2D,
			0x00, 0xC0, 0x4F, 0xC9, 0x64, 0xFF,
		},
		Col28: sql.NullString{String: "<xml/><root>a</root>", Valid: true},
	}

	_, err = db.NamedExec(`
		INSERT INTO datatypes (
			col01, col02, col03, col04, col05, col06, col07, col08, col09, col10,
			col11, col12, col13, col14, col15, col16, col17, col18, col19, col20,
			col21, col22, col23, col24, col25, col26,        col28
		) VALUES (
			:col01, :col02, :col03, :col04, :col05, :col06, :col07, :col08, :col09, :col10,
			:col11, :col12, :col13, :col14, :col15, :col16, :col17, :col18, :col19, :col20,
			:col21, :col22, :col23, :col24, :col25, :col26,         :col28
		)`,
		src,
	)
	if err != nil {
		log.Printf("db.Exec error %s", err)
	}

	dst := Datatypes{}
	query, args, err := db.BindNamed(`
		SELECT 
			col01, col02, col03, col04, col05, col06, col07, col08, col09, col10,
			col11, col12, col13, col14, col15, col16, col17, col18, col19, col20,
			col21, col22, col23, col24, col25, col26,        col28
		FROM datatypes 
		WHERE col01 = :col01`,
		key,
	)
	if err != nil {
		log.Printf("db.BindNamed error %s", err)
	}
	err = db.QueryRowx(query,
		args...,
	).StructScan(
		&dst,
	)
	if err != nil {
		log.Printf("db.QueryRow error %s", err)
	}
	if !reflect.DeepEqual(src, dst) {
		// log.Printf("\nsrc = %#v\ndst = %#v\n", src, dst)
		diff := cmp.Diff(src, dst)
		if len(diff) > 0 {
			log.Print(diff)
		}
	}

	_, err = db.NamedExec(`
		DELETE FROM datatypes
		WHERE col01 = :col01`,
		key,
	)
	if err != nil {
		log.Printf("db.Exec error %s", err)
	}
}

おわりに

このドライバでは、SQLServer の time が time.Time に割り当てられているため、0001-01-01 が日付部の規定値として入ります。たとえば、JSON Schema の time は、時刻部だけなので、SQLServer の time 型の入出力に対応できるデータ型を用意すると使いやすいかもしれません。

日本語については、別途、テストしたいと思います。

GitHubで編集を提案
株式会社ROBONの技術ブログ

Discussion