database/sqlͰNULLΛѻ͏ ྺ࢙ͱsql.Null[T]ͷొ৔ kamakura.go #6 @mackee_w a.k.a macopy 2024-05-17

஻Δਓ • X: @mackee_w • GitHub: @mackee • ໘ന๏ਓΧϠοΫ • Tonamel αʔόʔαΠυΤϯδχΞ

database/sqlͰNULLΛѻ͏ ͱ͸ʁ

database/sqlͰNULLΛѻ͏

database/sqlͰNULLΛѻ͏ • sql.Null**ͱ͍͏ܕͰϥοϓͯ͠ग़͠ೖΕΛ͢Δ • ϙΠϯλܕͰ͸ͳ͍(͕ɺϙΠϯλܕͷnilΛαϙʔτ͍ͯ͠Δέʔε΋͋Δ) • sql.Null**͸Validͱ͍͏ϑΟʔϧυ͕͋Δ • Valid͕falseͩͱNull, trueͩͱNullͰ͸ͳ͍ • Validͱ͸ผʹ஋Λ֨ೲ͢ΔϑΟʔϧυ͕͋Δ • sql.NullStringͰ͋Ε͹ `String string`, sql.NullInt64Ͱ͋Ε͹`Int64 int64`

ͦ΋ͦ΋database/sqlͰѻ͏஋ͬͯͳΜͩʁ • جຊతʹ͸ҎԼͷinterface͕࣮૷͞Ε͍ͯΔܕ • SQL಺ͷ஋ͱͯ͠࢖͏৔߹͸ database/sql/driver.Valuer • ΫΤϦͷ݁Ռ͔Βड͚औͬͨ΋ͷΛGoͷม਺ʹόΠϯυ͢Δ৔߹͸ɺ database/sql.Scanner • GoͷϓϦϛςΟϒܕ΍driver͕഑ྀ͍ͯ͠Δܕ͸ͦͷ··ಥͬࠐΊΔ • ޙऀͷྫ: json.RawMessage(go-sql-driver/mysql)

driver.ValuerͰNULLΛSQL಺ʹೖΕΔʹ͸ • driver.Valuer.Value()͕nilΛฦͤ͹ྑ͍ • sql.Null**ͳͲ͸ͦ͏ͳ͍ͬͯΔ • driver.Valuer.Value()͕typed-nilΛฦͨ͠৔߹͸ʁ • driver΋͘͠͸database/sql/driverͰuntyped-nilʹม׵͞Ε͍ͯΔ ͷͰηʔϑ • Ͱ΋untyped-nilΛฦͨ͠ํ͕(͓ߦّ͸)ྑ͍Ͱ͢Ͷ

driver.ScanͰNULLΛड͚Δʹ͸ʁ • driver.Scanner.Scanʹuntyped-nil͕ೖͬͯ͘ΔͷͰɺͦΕͰValidͷ൑ ఆΛߦ͏ • sql.Null**ʹnil͕ೖͬͨ৔߹͸Validʹfalse͕ೖΓɺ஋͕ೖΔ fi eldʹ͸θ ϩ஋͕ೖΔΑ͏ʹͳ͍ͬͯΔ • driver.ScannerͰ͸ͳ͘ϙΠϯλܕͰ΋nullΛड͚ͨΒnil͕ೖΔ • Ͱ΋θϩ஋nilͱݟ෼͚͕͔ͭͳ͍͔΋ʁ ؾ͕͢Δ͚ͩͳͷͰ۩ମతͳ έʔε͕͋ͬͨΒڭ͍͑ͯͩ͘͞

database/sqlʹ͓͚Δsql.Null**ͷྺ࢙ • sql.NullString͸database/sqlύοέʔδ͕࡞ΒΕͨ࣌఺Ͱଘࡏ͢Δ(2011/09/29) • ౰࣌͸sql.NullableStringͱ͍͏໊લͰScannerInfo interface͕࣮૷͞Ε͍ͯͨ • sql.NullInt64, sql.NullFloat64, sql.Bool௥Ճ(2012/01/26) • ͜ͷޙgo1͕ϦϦʔε • sql.NullTime௥Ճ(2019/04/06), sql.NullInt32௥Ճ(2019/04/27), sql.NullInt16, sql.NullBytes௥Ճ(2021/05/05)

௕Β͘sql.NullTime͕ͳ͔ͬͨ • ͳ͔ͬͨͷͰgo-sql-driver/mysql.NullTimeΛ࢖͍ͬͯͨਓ΋͍ͨͰ ͠ΐ͏(2013/04/26͔Βଘࡏ) • ͦͷଞsql.Null**(͜͜ʹ೚ҙͷܕ͕ೖΔ)͕ཉ͍͠ํ΋͍ͬͺ͍͍ͨͰ ͠ΐ͏ • ϥΠϒϥϦͳΓࣗલ࣮૷ͳΓ͕ඞཁͰ͋ͬͨ… • ͦΕ͸ͦΕͰϓϩδΣΫτ͝ͱʹҧ͏ͱ͍͏ࠞཚͷ΋ͱʹ…

from Go1.22 Release Notes The new Null[T] type provide a way to scan nullable columns for any column types.

sql.Null[T]ͱ͸ • sql.Null[T(͜͜ʹ೚ҙͷܕ͕ೖΔ)]ͷΑ͏ʹ࢖༻͢Δ͜ͱͰɺsql.Null**ͱಉ͡ޮ ྗΛ࣋ͭܕ • sql.Null**Λ૿΍͞ͳͯ͘ྑ͘ͳͬͨʂ • user de fi ned typeͱͷ૊Έ߹Θ͕ͤྑ͍ • type HogeID uint64 ͷΑ͏ͳIDܕ࡞ͬͨ৔߹ʹɺͦͷNullableͳܕͱͯ͠ `sql.Null[HogeID]`͕࢖͑Δ • methane͞ΜฒͼʹGoνʔϜͷօ͞Μ͋Γ͕ͱ͏ʂ

FAQ • Q: ࣮࣭Option/MaybeܕͰ͸ʁ • A: Issueʹ΋ͦΜͳٞ࿦͕͋Γ·͕ͨ͠ɺSQLΛॻ͘ͱ͖ɺDB͔Βͷ ΫΤϦ݁ՌΛಡΉ࣌ʹͷΈ࢖͍·͠ΐ͏Ͷ • Q: ͜Ε͕ೖͬͨܦҢ͸ʁ • A: ΋ͱ΋ͱsql.NullUInt64ͷఏҊ͕͞Εͨ࣌ʹʮδΣωϦΫεΛ଴ͬ ͯͶʯͱ͍͏࿩ʹͳ͍ͬͯͨ

Ҏ্ʂ