MySQLの取扱い

基本情報

対応バージョン
MySQL 5.0 以上
JDBCドライバの同梱
あり
(同梱)JDBCドライバ
mysql-connector-java-5.1.40-bin.jar
MySQLのJDBCドライバ
JDBCドライバ

データベース接続設定

データベース接続設定(databaseInfoMap.dfprop)について。 

接続設定の仕様 @databaseInfoMap.dfprop
map:{
    ; driver   = com.mysql.jdbc.Driver
    ; url      = jdbc:mysql://[host]:[port]/[dbname]?[key]=[value]&[key]=[value]
    ; schema   = 
    ; user     = [dbuser]
    ; password = [dbpassword]
}
  • url の ? 以降はオプション
  • catalog はURLから自動判別されるため設定不要
  • schema は未使用(空指定でOK)
  • [xxx]の[]は単なる表現上(ドキュメント上)の囲み

以下、実際のテストプロジェクトでの設定例です。 

e.g. dbflute-test-dbms-mysqlの場合 {host=localhost,port=43306} @databaseInfoMap.dfprop
map:{
    ; driver   = com.mysql.jdbc.Driver
    ; url      = jdbc:mysql://localhost:43306/maihamadb?allowPublicKeyRetrieval=true&useSSL=false
    ; schema   = 
    ; user     = maihamadb
    ; password = maihamadb
}
databaseInfoMap.dfprop

SSLExpceptionが発生する場合、かつ、接続にSSLを使わないのであれば、useSSLをfalseにすることで回避できます。 (MySQL-8.0.13からはuseSSLが非推奨なので、sslMode=DISABLEDの方が推奨)

さらに、MySQLNonTransientConnectionException で "Public Key Retrieval is not allowed" が出る場合は、allowPublicKeyRetrieval=true で回避できます。

データ型マッピング

データベース上のデータ型とプログラム型との(デフォルトの)マッピングについて。

java.lang.String
CHAR, VARCHAR, TEXT, ...TEXT
java.lang.Integer
INTEGER, ...INT, {NUMERIC(1-9, 0)}, {DECIMAL(1-9, 0)}
java.lang.Long
BIGINT, {NUMERIC(10-18, 0)}, {DECIMAL(10-18, 0)}
java.math.BigDecimal
FLOAT, DOUBLE, NUMERIC(n, m), DECIMAL(n, m))
java.util.Date
DATE, YEAR
java.sql.Time
TIME
java.sql.Timestamp
DATETIME, TIMESTAMP
java.lang.Boolean
BIT, BOOLEAN
byte[]
BINARY, VARBINARY, BLOB, ...BLOB

未サポートのデータ型

ENUM
文字列として利用可能
SET
カンマ区切りの文字列として利用可能
DBMSごとの取扱い - 未サポートのデータ型

主キーでの自動採番

自動採番(連番)の仕組みとして Identity を利用します。

Identity情報はメタデータから取得できるので、設定なしで利用可能です。

一つ注意なのは、MySQLの仕様としてトランザクション外での insert 後の採番された値の取得ができません。(トランザクションをサポートしているストレージエンジンであることが前提として) 採番された値を登録後に利用する場合は、トランザクション内で実行するようにして下さい。

現場フィット - AutoIncrement
this - MySQLのストレージエンジンに注意

ページング検索の条件

limit を利用します。

ConditionBeanのPaging

e.g. ConditionBeanでページング検索 {81-100} @DisplaySql
...
 order by ...
 limit 80, 20
ConditionBean - Paging

OutsideSqlのManualPaging

e.g. OutsideSqlのManualPagingでページング検索 {81-100} @OutsideSql
/*IF pmb.isPaging()*/
select ...
-- ELSE select count(*)
/*END*/
  from ...
 where ...
 /*IF pmb.isPaging()*/
 order by ...
 limit /*pmb.pageStartIndex*/80, /*pmb.fetchSize*/20
 /*END*/

TypedParameterBean における ManualPaging の自動判別ロジックは、"limit" かつ "pmb.fetchSize" という文字列が含まれることです。("limit" は大文字小文字は区別せず)

OutsideSql - manualPaging().selectPage()
TypedParameterBean

found_rows()関数の利用

MySQLでは、sql_calc_found_rows & found_rows() 構文の利用で、カウント検索のコストを抑えられる可能性があります。 (MySQLはカウント検索がネックになりやすいため有効です)

ConditionBean で found_rows()

ConditionBeanでは、Union を利用しない検索で CountLater オプションを指定した場合に、found_rows() が利用されます(@since 0.9.8.8)。ただ、CountLater オプションはデフォルトで有効になっていますので(@since 0.9.9.0A)、MySQL利用時はデフォルトで found_rows() が利用されます。

ConditionBean - カウント検索を後に

外だしSQL で found_rows()

外だしSQLでは、CountLater オプションをした上で、IFコメントを使って切り替えます。 CountLater オプションはデフォルトで有効になっています(@since 0.9.9.0A)。

外だしSQL - カウント検索を後に
e.g. found_rows() を利用したページング検索 {81-100} @OutsideSql
/*IF pmb.isPaging()*/
select sql_calc_found_rows
       foo_id
     , bar_name
     , ...
  from ... 
 where ...
 order by ...
 limit /*pmb.pageStartIndex*/80, /*pmb.fetchSize*/20
-- ELSE select found_rows()
 /*END*/

Union と found_rows()

Union を利用すると fetch_rows() より厳密なカウントが取得できないため(正確には union all でない union の場合)、Union が利用されている場合はこの方式は利用されません。ConditionBeanでは Union がある場合は通常のカウント検索となります。 外だしSQLでは Union との併用に注意が必要です。

トランザクション外での found_rows()

トランザクションの有無に関わらず found_rows() は利用できます。通常、利用するデータソース(DataSource)次第で、トランザクションを利用しない場合に一定のコネクションが提供されない可能性があるため、 マルチスレッドな環境だと厳密には found_rows() が動作しない可能性があるのですが、DBFlute がカウント検索とページング実データ検索で利用するコネクションを独自に必ず同じものが利用されるようにしています。

更新ロックの取得

ConditionBean の lockForUpdate() では、for update を利用します。 

e.g. ConditionBeanで更新ロックの取得 (cb.lockForUpdate()) @DisplaySql
select ...
  from MEMBER dfloc
 where ...
   and ...
 for update

プロシージャ

ストアドプロシージャ を(DBFluteの機能としての)プロシージャとしてサポートしています。

現場フィット - プロシージャコール
INパラメータ
サポート
OUTパラメータ
サポート
INOUTパラメータ
サポート
プロシージャリターン
サポート
ResultSetパラメータ
DBMSにて未サポート
ResultSetリターン
DBMSにて未サポート
NotParamResult
サポート
パッケージプロシージャ
DBMSにて未サポート ※そもそもパッケージの概念なし
プロシージャシノニム
DBMSにて未サポート ※そもそもシノニムの概念なし
プロシージャリターン
ResultSetパラメータ
ResultSetリターン
NotParamResult(Set)

別カタログのプロシージャ

別カタログ(別データベース)のプロシージャもオプションで自動生成できます。

this - 別カタログ(別データベース)の利用

自動生成対象プロパティの有効項目

Catalog
サポート (AdditionalSchemaの利用)
Schema
利用不可 (MySQLにスキーマという概念がないため)
Name
サポート
dfprop - プロシージャの自動生成対象プロパティ
Catalog(カタログ)

データベース依存機能

データベース依存機能を有効にした場合の利用可能な機能について。

現場フィット - データベース依存機能(メソッド)の利用

ConditionBeanで全文検索

"Tritonn + Senna" や、(純粋な)MyISAM の全文検索、加えて (5.6からサポートされた)InnoDB の全文検索の条件を指定できます。 (要は match against 構文のSQLを出力します)

Tritonn
Senna
e.g. 会員名称を "foo" で全文検索
MemberDbm dbm = MemberDbm.getInstance();
cb.query().match(dbm.columnMemberName(), "foo", null);
// where match(dfloc.MEMBER_NAME) against ('foo')

e.g. 会員名称と会員アカウントを "foo" で BooleanMode のテキスト検索
MemberDbm dbm = MemberDbm.getInstance();
List<ColumnInfo> textColumnList = new ArrayList();
textColumnList.add(dbm.columnMemberName());
textColumnList.add(dbm.columnMemberAccount());
cb.query().match(textColumnList, "foo"
  , WayOfMySQL.FullTextSearchModifier.InBooleanMode);
// where match(dfloc.MEMBER_NAME, dfloc.MEMBER_ACCOUNT)
//       against ('foo' in boolean mode)

有効なモードは以下の通りです。

  • InBooleanMode
  • InNaturalLanguageMode
  • InNaturalLanguageModeWithQueryExpansion
  • WithQueryExpansion

条件値が null もしくは空文字の場合は、ConditionBeanの基本仕様と同じく条件は無効になります。

ConditionBeanの使い方 - 基本仕様

条件値はバインド変数とはなりません。これは MySQL の仕様としてバインド変数が利用できないためです。 条件値に含まれるシングルクォーテーションとバックスラッシュに関しては、DBFluteが内部処理として自動でエスケープします(@since 0.9.8.3)。

DBMS独自の利用方法

別カタログ(別データベース)の利用

別カタログ(別データベース)のテーブルを自動生成できます。 MySQLにはスキーマの概念が存在しませんが、DBFlute内部において "無名のスキーマ" という概念を導入し、AdditionalSchema (追加スキーマ)として扱えるようにしています。

現場フィット - AdditionalSchema
追加スキーマ(AdditionalSchema)
別カタログ(OtherCatalog)
別データベース(OtherDatabase)

カーソル検索をべたページング方式で

MySQLのJDBCドライバでは、カーソル検索は色々と問題を抱えています。

Integer.MIN_VALUEを利用した場合のパフォーマンス劣化対策として、カーソル検索の内部動作を "べたなページング" 方式にするオプションがあります。 単に 1000 件ずつページング検索して、カーソル検索っぽく振る舞うやり方です。@since 1.0.3

MySQLの本当のカーソル検索 - キャッシュなしの一件ずつフェッチ!?

LikeSearch で utf8_unicode_ci

LikeSearch検索で utf8_unicode_ci を利用したい場合は、LikeSearchOption の getQueryClauseArranger() をオーバーライドして、CollateUTF8UnicodeArranger を戻すように実装することで実現できます。 @since 0.9.9.1C 

e.g. LikeSearch検索で utf8_unicode_ci を利用するLikeSearchOption @Java
LikeSearchOption option = new LikeSearchOption() {
    private static final long serialVersionUID = 1L;

    @Override
    public QueryClauseArranger getWhereClauseArranger() {
        return new CollateUTF8UnicodeArranger();
    }
};

1.1.x (Java版) では、LikeSearchOptionはアプリ側ではnewしないので、仕組みの中で差し替える必要があります。 テーブル単位で有効にするのであれば、ConditionQuery (e.g. MemberCQ) の newLikeSearchOption() メソッドをオーバーライドすると良いでしょう。 特定のカラムだけを有効にするのであれば、setメソッド (e.g. setMemberName_LikeSearch()) をオーバーライドしましょう。 (いずれにせよ、ソースコードを読んで確認してから)

DBMS独自の注意点

カーソル検索でのメモリ対策

MySQLのJDBCドライバでは、カーソル検索をしてもメモリ対策になりません。 検索結果のResultSetを構築した時点で、全ての検索結果のデータをメモリに持ってしまうからです。 (少なくとも Connector/J-5.1.46 においては)

JDBCパラメータの FetchSize に Integer.MIN_VALUE を指定すると一件ずつフェッチの動作になり、大量件数の検索に対応できます。

MySQLの本当のカーソル検索

QueryUpdate/Deleteの実現

update/delete 文の where 句のサブクエリにて更新対象のテーブルを参照できない、という制約があるため、QueryUpdate や QueryDelete において、サブクエリ方式ではなく update 文の where 句に条件を埋め込む方式を採用しています。

Behavior - queryUpdate(entity, cb)
e.g. サブクエリ方式でなく where 句埋め込み方式 (複雑な条件がやりづらい) @DisplaySql
update MEMBER
   set MEMBER_STATUS_CODE = 'PRV'
     , FORMALIZED_DATETIME = null
     , UPDATE_DATETIME = ...
     , UPDATE_USER = ...
     , VERSION_NO = VERSION_NO + 1
 where MEMBER_STATUS_CODE = 'FML'

関連テーブルを利用した絞り込み条件は、MySQL特有のupdate文に対する結合を利用して実現しています。QueryUpdate では、update文のテーブルの後でset句の前にjoin句を入れることで実現しています(@since 0.9.9.5B)。QueryDelete では、delete文のテーブルの後でwhere句の前にjoin句を入れて、削除対象のテーブルのAlias名をdeleteの直後に指定することで実現しています(@since 1.0.4C)。 

e.g. リマインダ質問に2が含まれている会員を仮会員に一括更新 @DisplaySql
update MEMBER mb
    inner join MEMBER_SECURITY scu
      on mb.MEMBER_ID = scu.MEMBER_ID
   set mb.MEMBER_STATUS_CODE = 'PRV'
     , mb.UPDATE_DATETIME = ...
     , mb.UPDATE_USER = ...
     , mb.VERSION_NO = mb.VERSION_NO + 1
 where scu.REMINDER_QUESTION like '%2%' escape '|'

e.g. 会員ステータスの表示順が2以上の会員を一括削除 @DisplaySql
delete mb MEMBER mb
    inner join MEMBER_STATUS stat
      on mb.MEMBER_STATUS_CODE = stat.MEMBER_STATUS_CODE
 where stat.DISPLAY_ORDER >= 2

ただ、複雑な条件になるとSQL表現の限界を超えてしまう可能性があるため、そのときは同じ条件の ConditionBean でリスト検索し、Entity の値を変更してからバッチ更新(batchUpdateNonstrict(), batchDeleteNonstrict())を利用するようにして下さい。

Behavior - batchUpdateNonstrict(entityList)
Behavior - batchDeleteNonstrict(entityList)

バッチ更新はバッチじゃない!?

batchInsert() や batchUpdate() などのバッチ更新ですが、MySQLのJDBCドライバの制限で実際にはバッチ更新されず、JDBCドライバの中でループでそれぞれ一件ずつ処理されます。 よって、他のDBMSに比べてバッチ更新のメリットは極端に少ないです。(DBFlute内の処理が一括で行われる、ログが一まとまりで見やすい、それぐらい)

一方で MySQL は、一つのinsert文で values 部分をカンマ区切り(複数values方式)でつなげて複数レコードを登録することができます。 外だしSQLのFORコメントを利用してそのinsert文を構築することで、一括での登録ができます。 (ただ、パフォーマンス的な優位性はどの程度あるのかは実際に試してみないとわかりません)

また、Connectionのプロパティで、rewriteBatchedStatements を true に設定すると、MySQLがバッチ登録時に内部的に "複数values方式" に変換して登録を行ってくれます。ReplaceSchemaもバッチ登録を利用しているため、このプロパティを true にすることで、実行スピードを速めることができるかもしれません。 ただ、アプリ実行時にこのプロパティを利用する場合は、このプロパティの挙動がどのくらい信頼性のあるものなのか、しっかり確認をする方が良いでしょう。 (jfluteが、アプリ実行時に利用している現場をあまり見たことがないため)

空振りdeleteでお手軽デッドロック

詳しくは、ブログ記事をご覧ください。

外部サイト - MySQLでお手軽デッドロック

DBFluteプロパティで、全ての queryUpdate(), queryDelete() の実処理の直前に select count(*) して0件だったら更新処理しない、というチェックが入れることができます(@since 0.9.9.7F)。 littleAdjustmentMap.dfprop の isQueryUpdateCountPreCheck を true に設定して再自動生成するとそのようになります。 ただ、デッドロックとは縁のない queryUpdate() や queryDelete() でも、select count(*) のチェックが入ってしまいます。

DBFluteプロパティを設定せず、その場その場の実装で対応するのであれば、ConditionBean で enableQueryUpdateCountPreCheck() を呼び出すと、その時の queryUpdate(), queryDelete() だけに select count(*) のチェックを入れることができます(@since 1.0.4D)。

Sql2EntityでunionしたBoolean型

Sql2Entityでは、CustomizeEntityのプロパティ型はJDBCのメタデータから自動判別されます。 通常、Boolean型のカラムはBooleanにマッピングされますが、unionしたBoolean型のカラムに関しては Integer 型になってしまいます。 その場合は、CustomizeEntityのプログラム型の強制指定を利用してBoolean型にすると良いでしょう。

CustomizeEntity - プログラム型の強制指定

Integer型のまま取り扱うと 1/0 の値が取得されるため、もしそのカラムがコード値 Boolean(true/false) のフラグ系区分値に関連付けていると、値が正常に取り扱えない可能性があります。 CDefが絡む処理で区分値を認識できないためです。その場合、姉妹コード(sisterCode)を使って 1/0 でも取り扱えるようにすることで解決できます。 この問題の有無にかかわらず、MySQL側が true/false と 1/0 の違いを吸収して取り扱う仕様に対応して、DBFlute側でも吸収できるようにしておいても良いでしょう。

dfprop - sisterCode

NullsFirst/Lastの実現

nulls first/last 構文をサポートしていないため、case when 構文を使って NullsFirst/Last を実現しています。特にそのことによる制約はありませんが(UnionQuery との併用も可能)、 パフォーマンス上の懸念が若干拭えないので、大量レコードをソートするときは意識しておいた方が良いでしょう。

ConditionBean - NullsFirst/Last

MySQLのストレージエンジンに注意

MySQL にはストレージエンジンという概念があり、同じ MySQL とはいえストレージエンジンが違えば挙動が変わります。

例えば、MyISAM では外部キー制約がないため、DBFluteではテーブル間のリレーションを自動生成時に活用することができません。AdditionalForeignKey を使って、DBFlute側で手動でリレーションを定義する必要があります。また、MyISAM にはトランザクションがないため、例外発生時のロールバックが効かないという大きな問題があるのもさることながら、 DBFluteでデータを登録(insert)したときに、Identityで自動採番をした後の採番された後の値の取得ができないという細かい問題も発生します。

additionalForeignKeyMap.dfprop
this - 主キーでの自動採番

もし、MyISAM である特別な理由がないのであれば、外部キー制約をサポートしている InnoDB などのストレージエンジンを利用することで、そういった手間の発生を抑えることができます。

MySQL補足資料 - ストレージエンジンの設定

ケース区別なし管理のススメ

Linux 上での MySQL は、SQL文でのテーブル名の大文字小文字を区別します。 開発はローカルPCの Windows で大文字小文字区別せずにSQLを書いていて、 いざ結合テストなどでサーバの Linux で動かしたときにSQLが動かない、というような状況が発生する可能性があります。 MySQL自体に、環境に依存せず大文字小文字を区別しないようにする設定があります。

MySQL補足資料 - テーブル名のケース区別なし管理

ただし、DBのメタ情報においてテーブル名が小文字になるため、ConditionBean や Behavior で生成される SQL 上のテーブル名も小文字になってしまいます。 可読性のためにせめてSQL上のテーブル名だけは大文字にしたいと思うような場合は、オプションで大文字にすることができます。

dfprop - isTableSqlNameUpperCase

SchemaHTML や JavaDoc 上の表示用のテーブル名も大文字にするオプションがあります。

dfprop - isTableDispNameUpperCase

SQLモードのススメ

MySQLのSQLモードを設定することをお奨めします。なぜなら、MySQLのデフォルト設定だと幾つか開発現場に相性の悪い仕様があるからです。 特に、間違ったカラムを指定した group by がしっかりとエラーになるように設定すると良いでしょう。

MySQL補足資料 - SQLモード

Sql2Entityで関数値のデータ型

Sql2Entityで CustomizeEntity を自動生成する際に、例えば max() や sum() のような関数を利用した項目に対応するプログラム型が想定しない型になる場合があります。

通常、Sql2EntityではSQLを実行して得られるメタ情報から、項目のデータ型や小数点などの情報から対応するプログラム型を判別しますが、 MySQLではその情報が少なく判別し切れない場合があります。例えば、本来 Long 型になって欲しいところが BigDecimal 型になってしまったりなど。その場合は、プログラム型の強制指定を利用して微調整すると良いでしょう。

CustomizeEntity - プログラム型の強制指定

また、複数の関数を利用した場合に、その処理の順序でデータ型が変わることがあります。 例えば、min(date(FORMALIZED_DATETIME)) と date(min(FORMALIZED_DATETIME)) で違う型にマッピングされるとフィードバックを受けています(ただし、MySQLのバージョンなどに依存する可能性あり)。 この場合、後者だと java.util.Date にマッピングされます。 バッドノウハウと言えますが、このように(結果を変わらないことを前提に)関数の処理順序を調整することで、想定するプログラム型にマッピングさせることができる場合もあります。

Exampleのススメ

MySQL を使ったExample実装 dbflute-mysql-example があります。

DBFlute Example - データベース

DBFlute内部での取扱い

DBFlute内部でどのようにMySQLと付き合っているか、特殊なパターンを挙げます。 将来的に同じ状況・同じ方法かどうかは保証されませんので、ここに書かれることに依存した利用はしないようにして下さい。 (DBFluteを深く理解するためのドキュメントと思って下さい)

DBコメントの取得
JDBC経由ではコメント情報が取得できないため(nullが戻ってくる)、information_schema.tables および information_schema.columns を直接参照して取得しています。ちなみに、MySQLにはスキーマの概念はないのですが、table_schema カラムの値にはデータベース名が格納されています。また、InnoDB だと アプリで設定したコメントに加えて、InnoDB が提供するテーブルの物理情報も一緒に取得されてしまうため、結構無理矢理切り離しています。(自動生成に利用した環境の物理情報はあまり重要でないため)
メタ情報取得時の大文字小文字リトライ
メタ情報取得時に大文字小文字を区別せずに取得できるように(念のため)リトライ処理を行いますが、MySQL で Windows で lower_case_table_names = 0 の場合に、"errno: 121" のエラーが発生してしまうため、MySQL ではリトライはしていません。 リトライせずとも正常にメタ情報が取得できるので特に問題ありません。
プロシージャ取得時のカタログ名
DatabaseMetaData.getProcedureColumns() にて他のデータベースのプロシージャのメタ情報を取得する際、 第三引数のプロシージャ名にデータベース名(カタログ名)を prefix として付与しなければならないため、そのようにしています。 (第一引数にてデータベース名を明示的に指定しているにも関わらず)

MySQL補足資料

MySQLの補足資料をまとめています。

MySQLの補足資料