sqlshapeのうちMySQLに属するものを1枚にまとめる。スキーマが名乗るバージョンとサーバ設定、規則が使う型と制約名、database/sqlの上のランタイム、マイグレーションコマンド、検査器が何を埋め込みどう検証しているか。規則そのものはchecks.ja.mdにあり、どのデータベースでも同じである。規則に出てくる名前・番号・型が、mysqlを名乗るスキーマではどこから来るのか——それがこのページの中身である。
MySQLで使い始めるとき、列を受けるGo型、接続のしかた、失敗モードの名前を確かめたいときに読む。診断に載ったMySQLのエラー番号の意味はmysql-errors.ja.mdにある。
- スキーマがバージョンとサーバ設定を名乗る
- Go型の表
- ランタイム: database/sql: 接続、文の実行、サーバの設定の確認
- 制約名と失敗モード(トリガやルーチンが送出するエラーを含む)
- 規則が使うもの:
Oneの証明、グループ化 - MySQLに無いもの
- マイグレーション
- 検査器が予測するエラー
- 検査器が埋め込んでいるもの
-- sqlshape: mysql 8.4
-- sqlshape: server sql_mode = 'ANSI,STRICT_ALL_TABLES'
-- sqlshape: server lower_case_table_names = 1
CREATE TABLE ...schema.sqlは、どのバージョンのMySQL向けのスキーマかを宣言する。対応しているのは8.4。宣言すると、MySQL自身の文法がスキーマとすべての文を解析し、MySQLの規則で式に型が付く。値の違う宣言が2つある場合や、postgresを同時に名乗る場合はスキーマのエラーになる。
宣言が決めるのは文を判定する規則であって、接続するサーバを制限するものではない。プログラムはgo-sql-driver/mysqlが接続できるサーバ(他のバージョンのMySQL、MariaDBやその他のMySQL互換データベースを含む)の上で動く。ただし判定は8.4の規則に従うので、そのサーバの挙動が違う箇所では、判定がサーバの実際の動きと一致することは保証しない(README: 制約事項)。
文の判定を変えるサーバ変数は、バージョンと並べて1行に1つ宣言する。検査器の前提と本番の接続を同じ設定に揃えるための行である(checks.ja.md)。MySQLで読むのは3つ:
-
sql_mode。カンマ区切りの名前(大文字小文字は問わない)、空文字列、組み合わせモードのANSIとTRADITIONALを受け付け、組み合わせはサーバと同じに展開する。判定を変えるモードは次のとおり。- 字句解析のビット(
ANSI_QUOTES、PIPES_AS_CONCAT、IGNORE_SPACE、NO_BACKSLASH_ESCAPES、HIGH_NOT_PRECEDENCE、REAL_AS_FLOAT): パーサがテキストをどう読むか ONLY_FULL_GROUP_BY: グループ検査を行うかどうか- 厳密モード(
STRICT_TRANS_TABLESかSTRICT_ALL_TABLES): 文字列関数がNULL可になるか、NOT NULL列へのNULLが失敗モードになるか NO_UNSIGNED_SUBTRACTION: 符号なし同士の減算が符号付きになる
残りのモードは実行時にしか効かないので、宣言はそのまま受け付ける。
- 字句解析のビット(
-
lower_case_table_names。0は表名とビュー名を大文字小文字で区別する(Linuxの既定)、1は小文字に変換して保持する、2は綴りを保ったまま区別せずに照合する。2は大文字小文字を区別しないファイルシステム(macOSやWindows)専用で、区別するファイルシステムではmysqldが警告を出して0で起動するため、2を宣言したスキーマとはずれが生じる(そのずれはmysql.Verifyが検出して返す)。 -
max_sp_recursion_depth。0〜255。0(既定)なら、自分自身をCALLするPROCEDUREは呼び出しのたびに1456になる。0より大きければ、呼び出しがどの深さまで達するかは静的に決まらないので、検査器は何も予測しない。
宣言が無ければ、Linuxで初期化したままの8.4を仮定する。すなわち既定のsql_mode(ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION)、lower_case_table_names = 0、max_sp_recursion_depth = 0である。
go-sql-driver/mysqlがparseTime=trueで実際に返す型を、動いているサーバと照合して表にしてある。整数はint64(BIGINT UNSIGNEDはuint64)、DECIMALはその文字列、時刻型はtime.Timeか文字列、バイナリ文字列とJSONは[]byteで届く。比較と論理演算子の結果はbigint(1)で、boolで受けられる。TINYINT(1)も同じ。
| MySQL | Go |
|---|---|
TINYINT / SMALLINT / MEDIUMINT / INT / YEAR |
int64 / int32 / int(UNSIGNEDならuint64 / uint32 / uintも)。TINYINT(1)はboolも |
BIGINT |
int64 / int(UNSIGNEDならuint64 / int64)。bigint(1)はboolも |
DECIMAL |
string |
FLOAT |
float32 / float64 |
DOUBLE |
float64 |
BIT |
[]byte |
CHAR / VARCHAR / TEXT / ENUM / SET |
string / []byte |
BINARY / VARBINARY / BLOB |
[]byte |
JSON |
[]byte / string |
DATE / DATETIME / TIMESTAMP |
time.Time / string |
TIME |
string |
ENUM列、キーの同一性 |
Goのnamed type(型に意味を持たせる)。ENUMはCHECK (col IN (...))と同じ値集合 |
MySQLには// sqlshape: typeの束縛は無い。束縛先となる名前付きの型がMySQLに無いためである。
宣言された列の型と、結果集合で実際に届く型が違うケースがいくつかある。
- ウィンドウ関数の整数(
MIN(id) OVER ()、FIRST_VALUE、NTH_VALUE、LAG、LEADなど)は、ウィンドウの一時表を経由してクライアントに届くため型が広がる。INTとBIGINTはBIGINTに、TINYINT/SMALLINT/MEDIUMINTはINTになり、符号は保たれ、YEARはINT UNSIGNEDになる。ふつうの集約は列の型を保つ(SMALLINTのMIN(small)はSMALLINT)。ROLLUPのグループ列も型を保つが、実行計画がたまたまグループ化を一時表に落とした場合だけは広がる——この場合は検査器は予測しない ROLLUPの下では、グループ列を読む結果列がnullableになる(超集約行でNULLになるため)。選択リストの定数はならないDATE'...'/TIME'...'/TIMESTAMP'...'リテラルはその型を持ち(小数桁は書かれた桁数)、NULLにならない。サーバがちょうどその型として読めないもの——時刻部のあるDATE、時刻部の無いTIMESTAMP、NO_ZERO_IN_DATE下のゼロの月、-14:00〜+14:00の外の時差——は文のエラー1525になる。<=>も被演算子にかかわらずNULLにならないUSER()、CURRENT_USER()、DATABASE()、SCHEMA()、VERSION()、CURRENT_ROLE()はバイナリ文字列ではなく文字列(utf8mb3)である
$ go get github.com/kr9ly/sqlshape/v2 # Query / One: 検査器が読む宣言
$ go get github.com/kr9ly/sqlshape/mysql/v2 # database/sql と go-sql-driver/mysql の上で実行する
github.com/kr9ly/sqlshape/mysql/v2は、宣言した文をdatabase/sqlとgo-sql-driver/mysqlを通して実行する。mysql.DBは*sql.DB、*sql.Tx、*sql.Connのどれでもよい。テンプレートの{{.X}}はワイヤ上ではMySQLの位置指定?になり、引数はプレースホルダの出現順に並べ直される(2回使ったパラメータは2回送る)。
DSNにはparseTime=trueが必須である。上のGo型の表はこれを前提にしている。無いとドライバはDATE・DATETIME・TIMESTAMPの列をバイト列で返し、time.Timeのフィールドでは受けられない。
db, err := sql.Open("mysql", "app:secret@tcp(localhost:3306)/app?parseTime=true")既に同じ値を持つ行へ書くExecOneのUPDATEもその行を数えてほしいなら、clientFoundRows=trueも付ける(下)。
for o, err := range mysql.Run(ctx, db, ListOrders, p) { ... } // iter.Seq2[Order, error]、逐次読み出し
orders, err := mysql.Collect(ctx, db, ListOrders, p) // []Order
first, err := mysql.First(ctx, db, ListOrders, p) // 無ければ ErrNoRows(sql.ErrNoRows)
res, err := mysql.Exec(ctx, db, MarkPaid, p) // sql.Result
u, err := mysql.Get(ctx, db, UserByEmail, p) // One: 無ければ ErrNoRows
u, ok, err := mysql.Find(ctx, db, UserByEmail, p) // One: ok が有無
res, err := mysql.ExecOne(ctx, db, MarkOrderPaid, p) // One: 1行も触らなければ ErrNoRowsどのランタイムでも同じこと(行のマッピング、One、検査済みのSQLだけが走る保証、expect行の名前で返るエラー)はruntime.ja.mdにある。MySQL固有のもの:
- 受け型はドライバのもので、上の表のとおり。
DECIMALはその文字列で届くので、自前のmoney型で包める。 - 制約違反は
*mysql.ConstraintErrorとして返り、そのKeyは下のスキーマ上の名前である。mysql.Violates(err, key)で判定する。トリガやルーチン自身のSIGNALも同じように返り、キーは下のとおりである。 ExecOneはRowsAffectedで判定する。MySQLは変更のあった行を数えるので、既に同じ値を持つ行へのUPDATEは、DSNにclientFoundRows=trueが無いとErrNoRowsになる。INSERT ... ON DUPLICATE KEY UPDATEとREPLACEがその1行について報告する0・1・2行は、どれも1行とみなす。
mysql.Verify(ctx, db, schemaSQL)は、接続のセッションの@@sql_modeとサーバのlower_case_table_namesを読み、スキーマの宣言(無ければ初期化したままの8.4の既定値)と違えばエラーを返す。DSNのsql_mode=...、プールのセッション初期化、別の設定で立てたサーバは、検査器が判定に使わなかった規則で文を走らせることになるからである。8.4が定義しないモード名(他のバージョンのMySQLやMariaDBでは使われうる)は、報告せずに照合から外す。エラーになるのは8.4が知っているモードの違いだけで、そのメッセージは外したモードの名前も挙げる。プールを開いた直後に1回呼ぶ。
if err := mysql.Verify(ctx, db, schemaSQL); err != nil { ... }失敗モードの名前はMySQLが制約に付ける名前そのもので、番号はMySQLのエラー番号である。
| 制約 | 名前 | エラー |
|---|---|---|
| 主キー | PRIMARY |
1062 |
UNIQUEキー |
キーの名前 | 1062(サーバが自分で番号を振るキーと、NULLが避けるキーは違反しない。プレフィックスキーUNIQUE (c(10))と式キーUNIQUE ((n * 2))は読む列を通して違反する) |
| 外部キー | CONSTRAINT名。無ければ<table>_ibfk_<n> |
子側は1452、親側は1451(ON DELETE / ON UPDATE CASCADEを追う) |
CHECK |
CONSTRAINT名。無ければ<table>_chk_<n> |
3819 |
NOT NULL |
<table>.<column> |
1048 |
ビューのWITH CHECK OPTION |
ビュー自身の名前(MySQLのエラーメッセージはビューを名指しする。PostgreSQLの44000は何も名指ししない) | 1369 |
| ビュー経由のINSERTが代入しない、既定値の無い基底列 | ビュー自身の名前(サーバのメッセージは列でなくビューを名指しする。ビューがその列を見せているかどうかは関係ない) | 1423 |
どの違反がありうるかは、文の形によっても変わる:
INSERT IGNOREは何にも違反しないON DUPLICATE KEY UPDATEはINSERTのキー違反を吸収するREPLACEはキーには違反せず、参照している外部キーには違反しうる(1451)- 厳密モードでなければ、
NOT NULL列へのNULLを拒むのは1行のINSERTとREPLACE(そのON DUPLICATE KEY UPDATEを含む)だけ。複数行、INSERT ... SELECT、UPDATEは型の暗黙の既定値を警告付きで格納するので、これらには1048を挙げない UPDATE IGNOREは、キーやNOT NULLの違反と同じようにWITH CHECK OPTIONビューの1369も吸収する。トリガ自身のSIGNALはどのIGNOREも吸収しないのと対照的である
実行時のエラーはmysql.Violates(err, key)が同じ名前で判定する。Run / Execの外で走らせた文のエラーは、mysql.WrapError(err)で先に同じ包み方にしておく。
列が決して格納できないリテラルは、失敗モードではなく文自身のエラーになる(mysql-errors.ja.md)。
失敗モードにしないもの: 長さ・範囲・ENUM値の切り詰め(1265 / 1406 / 1366 / 1264)。これらは型の側の性質(パラメータのGo型、リテラル自身の値集合)なので、型検査の側で捕まえる。
トリガとストアドルーチンは、自分の失敗モードを、発火させた文・呼び出した文に持ち込む。表への文は、その表のトリガのうち該当する事象のものの失敗モードを引き継ぐ(INSERT / UPDATE / DELETE)。REPLACEはINSERTとDELETEのトリガを、ON DUPLICATE KEY UPDATEはINSERTとUPDATEのトリガを発火させる。トリガ・ルーチンの本体自身の書き込みは、その書き込み自身の失敗モード——スキーマの制約、その書き込みが発火させるトリガの失敗モード(再帰は打ち切り)——を本体の失敗モードに持ち込む。
SIGNALのキーは、ランタイムがエラーを読み戻すのと同じ規則で決まる:
MYSQL_ERRNOを設定したSIGNALは、その番号の10進表記がキーになる。組み込みの番号を名乗っても同じで、SQLSTATE'23000'の下でSET MYSQL_ERRNO = 1062とすればキーは1062になる。メッセージに書かれていないキー名を探しにいくことはないMYSQL_ERRNOを設定しないSIGNALは、SQLSTATEがキーになる- SQLSTATEクラス
01は警告なので失敗モードにならない。未処理のクラス02は1643、それ以外の未処理は1644になる
名前付きCONDITIONはその値に解決する。値の無いRESIGNALは、最も内側のHANDLERが処理中のものをそのまま再送する。DECLARE ... HANDLER FORは、そのブロック内の一致する失敗モードを吸収する(SQLEXCEPTIONはクラス01と02以外の全部、SQLWARNING / NOT FOUNDはそのクラス、SQLSTATEや番号そのものは一致するもの)。INSERT / UPDATE IGNOREはトリガのSIGNALを何も吸収しない(文はそれでも失敗する)。SELECT ... INTOは、Oneが使うのと同じ証明で「多くとも1行」と示せない限り1172を持つ(1行も無い場合はNOT FOUNDの警告で、失敗にはならない)。
CREATE TRIGGER / FUNCTION / PROCEDUREの上に書く-- sqlshape: error <key> = <Name>は、PostgreSQLの同じ注釈(checks.ja.md)と同じもので、<key>にNameを与える。プログラムはそれをsqlshape.Error(<key>)で写し取り、vetがスキーマと両方向に照合する。expect行ではNameとキーのどちらで綴っても同じである。実行時にmysql.Violatesが比べるのはコード(30001)で、sqlshape.Error("30001")から作ったsqlshape.Failureはそのコードだけを運んでいる。
本体の作成時にサーバが拒むものと、ルーチン呼び出しが持ちうる番号はmysql-errors.ja.mdにある。
WITH CHECK OPTIONを宣言したビュー経由の書き込みは、checks.ja.mdのとおり基底表のrequire pinned(<列>)を満たせる(sqlshape checkはok(view)と出す)。裏付けは上の1369そのもので、CHECK OPTIONを宣言していないビューはこの経路を持たない(そこを経由した書き込みは行をビューから静かに外すだけで、サーバは拒まない)。MySQL固有の点:
WITH CHECK OPTIONとだけ書けばCASCADEDなので、下位の全ビューのWHEREも数える(結合の向こうにあっても)。WITH LOCAL CHECK OPTIONはそのビュー自身で止まるが、自前のCHECK OPTIONを宣言した下位ビューはサーバが検査し続けるので数えるUPDATEにもINSERT/REPLACEにも効く。DELETEはCHECK OPTIONを検査しない- 下位のビューが自分のCHECK OPTIONを宣言していれば、書き込む先のビューに宣言が無くても検査され、1369は書き込む先のビューを名指しする
checks.ja.mdの規則は、MySQLのスキーマにもそのまま適用される。変わるのは、判定を下すアナライザーがPostgreSQL用からMySQL用になることだけである。結果列・パラメータとGo型の対応、NULLの扱い、型の意味、失敗モード、Oneの証明、第2部の宣言のすべて(visible where、pinned、via view、EXISTS、aggregate、transitions、never、paired、single、sensitive、context)が同じに働く。診断にはMySQLのエラー番号とメッセージ文がそのまま載る(Unknown column 'nope' in 'field list' (MySQL error 1054))。{{.X}}はワイヤ上では?になる。
PRIMARY KEYと列全体にかかるUNIQUEキー、LIMIT 1、GROUP BYの無い集約から証明する。集約はどこに置かれていてもよい(COALESCE(SUM(total), 0)もSUM(total)と同じく1行。サブクエリが自分の列だけで集約するものはそのサブクエリの証明になる)。MySQLには部分インデックスが無い。
MySQLはsql_modeのONLY_FULL_GROUP_BYで検査を行い、検査器も同じ検査を同じ番号で行う。グループ化または集約する問い合わせでは、SELECTリスト、HAVING、ORDER BY、ウィンドウのPARTITION BY / ORDER BYの各式が、GROUP BYの式か、集約か、グループ列に関数従属する列だけからできていなければならない(1055。GROUP BYが無ければ1140)。サーバが認める従属は検査器が認める従属と同じである:
PRIMARYかUNIQUEキーが既知になった表の全列(NULL可のキー列は、そのNULLを退ける条件があるときだけ)WHEREと内部結合のcol = colとcol = リテラル- 外部結合の
ONがNULL可側に与えるもの - 派生表やビューの本体が出力列を通して与えるもの
ROLLUPの下ではグループ式そのものだけ
隣り合う検査も一緒に行う。
| 規則 | エラー |
|---|---|
HAVINGで集約の外に書く列は、SELECTリストの列か別名かGROUP BYの列でなければならない |
1054 |
DISTINCTがあるとき、SELECTリストに無いORDER BYの式が読めるのはSELECTリストの列だけ |
3065 |
他で集約していない問い合わせのORDER BYの集約 |
3029 |
集合演算のORDER BYの集約 |
3028 |
SELECT email, count(*) FROM users GROUP BY name
-- Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
-- 'users.email' which is not functionally dependent on columns in GROUP BY clause; this is
-- incompatible with sql_mode=only_full_group_by (MySQL error 1055)
SELECT name, count(*) FROM users GROUP BY id -- OK: id は主キー次の2つの形は、スキーマの規約(require、visible where)を判定するとき、書き込みを2つ持つ文として扱われる:
| 文 | 記録する書き込み | 理由 |
|---|---|---|
INSERT ... ON DUPLICATE KEY UPDATE |
INSERTと、枝が代入する列へのUPDATE | 枝は既存行を動かす |
REPLACE |
INSERTとDELETE | キーが衝突すると既存行を先に削除する(その表のAFTER DELETEトリガが発火する) |
MySQLに無いものはMySQLでは検査しない: Batch、Copy、MatView、PL/pgSQL、ドメイン、複合型と配列、-schemas(MySQLのスキーマは1つのデータベース)、// sqlshape: type、マイグレーションのseed表。
sqlshape diff、apply、verify-schemaは、MySQLのスキーマにもPostgreSQLと同じに働く。データベースとschema.sqlの差分がDDLになり、DDLは到達する状態で検査され、それから実行される。両側はサーバ自身のSHOW CREATE TABLE / SHOW CREATE VIEWで読み、目標側は-dbのサーバ上の一時データベースで正準化する。MySQLのDDLは暗黙にコミットされるので、DDLは1文ずつ実行する。何を比較するか、enum宣言の形、必要な環境はmigrations.ja.mdにある。
MySQLのエラー番号が載った診断には2種類ある。エラーは確実に起きるもので、文を実行するたびに同じように失敗するため、vetが報告し、文を直す必要がある。失敗モードは実行時に一部の行や値で起こりうるもので、文のexpect行に書き、戻ってきたときはmysql.Violatesが一致する。上の制約名は失敗モードである。それ以外に検査器が文の形から予測するもの——列に格納する値、定数の式・変換・関数引数、空間型の値、トリガとルーチンの本体、名前解決、ビュー、結果集合を返さない文——は、mysql-errors.ja.mdに話題ごとにまとめ、エラー番号の索引を付けてある。
パーサと字句解析器はMySQL 8.4自身のもので、サーバのソースから切り出して(文法はアクションを剥がし、字句解析器はそのまま)WebAssemblyにしてある。関数の表も同じソースから読む。アナライザーはその構文木の上に建てたpure Goの実装で、検査時にMySQLへ接続することはない。MySQLのパーサを抱えているため、検査器のMySQL側(check/mysql)はGNU General Public License v2である。リンクされるのはsqlshapeバイナリだけで、利用者のプログラムには入らない。
判定は動いているmysqldと照合してある。型を付けた5,033文(組み込み関数の全部について引数の組み合わせごとの結果型)、エラーになる65文、ONLY_FULL_GROUP_BY検査の376文、既定でないsql_modeの下の22文と6つの書き込みが8.4と一致する。このページとmysql-errors.ja.mdに書いたサーバの挙動は、すべてmysqld 8.4に対して実測したものである。
MySQL自身のテストコーパスも並走させてある。mysql-test/tの1,281ファイル(約137,000文)をmysqldとアナライザーの両方に流し、SELECTの列は名前・型ファミリー・nullabilityで、エラーは番号で突き合わせる。両者が一致しないと分かっている文は2,525件。1件ずつベースライン(check/mysql/internal/analyze/testdata/corpus_baseline.txt)に固定してあり、新しい不一致が出るとビルドが落ちる。各行には不一致の種別が付いている: アナライザーが予測しないエラー、別の番号で予測するエラー、型の違う列、ローダーが扱わないスキーマ構文。このファイルが、MySQLアナライザーがまだサーバと同じには判定できないものの正直な一覧である。
The MySQL side of the checker (check/mysql) carries MySQL's own parser and is under the GNU
General Public License v2, see cmd/sqlshape/LICENSE; it is linked into
the sqlshape binary, a development tool, and nothing under it is linked into your program. The
runtime (mysql) is Apache License 2.0, like the declarations.