This is a cache of http://dbflute.seasar.org/ja/manual/reference/dbway/mysql/supplement.html. It is a snapshot of the page at 2024-11-01T00:35:40.678+0000.
MySQLの補足資料 | DBFlute

MySQLの補足資料

概要

DBFluteとは直接は無関係ながらも関わりの深い、MySQLに関する補足資料です。 厳密な仕様はDBMSのオフィシャルドキュメントの通りではありますが、ここでは簡易な資料としてまとめています。

MySQLの取扱い

SQL周り

全文検索

MyISAM もしくは "Tritonn + Senna" において全文検索ができます。 

e.g. foo という文字列が含まれる会員名称を全文検索 @SQL
match(MEMBER_NAME) against ('foo' in boolean mode)

DBFluteでは、この構文が ConditionBean で利用できます。

ConditionBeanで全文検索

更新時のサブクエリでの制限

update 文の where 句のサブクエリにて更新対象のテーブルを参照できないという制限があります。 また、delete 文も同じです。DBFlute の queryUpdate(), queryDelete() では、その制限により ConditionBean の条件で指定できない機能が存在します。

QueryUpdate/Deleteの実現

SQL関数

現在日付

current_date()
yyyy-MM-dd
current_time()
HH:mm:ss
current_timestamp()
yyyy-MM-dd HH:mm:ss
now()
current_timestamp() と同じ

null の場合のデフォルト値

coalesce() 関数を利用することで、対象値が null の場合のデフォルト値を設定することができます。 

e.g. 購入数量の平均値が null の場合は 0 にする @SQL
coalesce(avg(PURCHASE_COUNT), 0)
  • デフォルト値の部分はバインド変数が適用できます

DBFluteでは、ConditionBeanのSQL関数フィルタの coalesce() にてこれを利用しています。

CB - SQL関数フィルタ - coalesce()

数値の丸め

round() 関数を利用することで数値の丸めができます。厳密な丸め仕様はMySQLの仕様に依存します。 

e.g. 購入数量の平均値の小数点を丸める @SQL
round(avg(PURCHASE_COUNT), 0)
  • 数値の部分はバインド変数が適用できます

DBFluteでは、ConditionBeanのSQL関数フィルタの round() にてこれを利用しています。

CB - SQL関数フィルタ - round()

数値の切り捨て

truncate() 関数を利用することで数値の切り捨てができます。 

e.g. 購入数量の平均値の小数点を切り捨て @SQL
truncate(avg(PURCHASE_COUNT), 0)
  • 数値の部分はバインド変数が適用できます

DBFluteでは、ConditionBeanのSQL関数フィルタの trunc() にてこれを利用しています。メソッドは trunc() ですが、内部的に truncate() として解決されます。

CB - SQL関数フィルタ - trunc()

日付のフォーマット

date_format() 関数を利用することでフォーマットすることができます。 

e.g. 更新日時を yyyy/MM/dd 形式でフォーマット @SQL
date_format(UPDATE_DATETIME, '%Y/%m/%d')

日付の切り捨て

素直にはできないので、様々な関数を組み合わせて実現します。実現方法は一つに限りません。 

e.g. 更新日時の時分秒ミリ秒を切り捨てて日付に @SQL
cast(substring(UPDATE_DATETIME, 1, 10) as date)

e.g. 更新日時の日と時分秒ミリ秒を切り捨てて日付に (月初めになる) @SQL
cast(concat(substring(UPDATE_DATETIME, 1, 10), '-01') as date)

DBFluteでは、ConditionBeanのSQL関数フィルタの日付の切り捨てにてこれを利用しています。

CB - SQL関数フィルタ - 日付の切り捨て

日付の加算・減算

date_add() 関数を利用することで加算と減算ができます。 

e.g. 更新日時を一日進める @SQL
date_add(UPDATE_DATETIME, interval 1 day)
  • 数値の部分はバインド変数が適用できます
  • マイナス値を指定すると減算になります
  • day の他に、year, month, hour などが指定できます

DBFluteでは、ConditionBeanのSQL関数フィルタの日付の加算にてこれを利用しています。

CB - SQL関数フィルタ - 日付の加算

テーブル定義

Identity設定

e.g. Identity設定 {MEMBER_IDにIdentity} @SQL
create table MEMBER (
    MEMBER_ID INTEGER AUTO_INCREMENT NOT NULL,
    MEMBER_NAME VARCHAR(200) NOT NULL,
    ...
)

テーブルコメントの設定

テーブルやカラムのコメントは、comment on ではなく create table に設定します。最大文字数は、テーブルは 60 文字、カラムは (約!?) 255 文字です。 

e.g. テーブルやカラムのコメントを設定 @SQL
CREATE TABLE PURCHASE(
    PURCHASE_ID BIGINT AUTO_INCREMENT NOT NULL COMMENT '購入ID: 連番',
    MEMBER_ID INTEGER NOT NULL COMMENT '会員ID: 会員を参照するID。'
    PURCHASE_COUNT INTEGER NOT NULL COMMENT '購入数量: 購入した商品の（一回の購入における）数量。',
    ...
) COMMENT='一つの商品に対する一回の購入を表現する。
一回の購入で一つの商品を複数個買うこともある。' ;

ユニーク制約の特徴

NotNull の FOO カラムと、そうでない BAR カラム で複合ユニーク制約を付けた場合、"A と null" と "A と null" の二つのレコードは登録できます。つまり、この場合の二つの null はそれぞれ別の値として扱われます。

※試しに、会員の生年月日とバージョンNOに複合ユニーク制約を付けてもExampleのテストデータは正常に登録された

発行された連番を insert 実行後に取得

同じトランザクション内での実行が前提です。トランザクション外で実行しても正常な値は取得されません。 ゆえに MyISAM では利用できません。 

e.g. 発行された連番を insert 実行後に取得 @SQL
select last_insert_id();

DBFluteでは、Behavior の insert() の処理の中で、このSQLを内部的に発行して採番された値を Entity に格納しています。デバッグログにもこのSQLが表示されます。

MySQLの取扱い - 主キーでの自動採番

環境周り

ストレージエンジンの設定

例えば、(デフォルトのストレージエンジンとして) InnoDB を利用する場合以下のように設定します。

MySQLのストレージエンジンに注意
e.g. ストレージエンジンを InnoDB に設定 @my.cnf
[mysqld]
default-table-type=InnoDB

テーブル名のケース区別なし管理

MySQLは、Linux や Windows などの環境ごとに、テーブル名やカラム名のSQL上での大文字小文字の識別に違いがあります。 例えば、Linux では大文字小文字を区別し、Windows では区別しません。

これにより、開発は Windows で大文字小文字区別せずにSQLを書いていて、いざ結合テストなどでサーバの Linux で動かしたときにSQLが動かない、というような状況が発生する可能性があります。

環境に依存せず大文字小文字を区別しないようにするためには、my.cnf(ini) に以下のような設定をします。 

e.g. SQLでのテーブル名やカラム名の大文字小文字を区別しない設定 @my.cnf
[mysqld]
lower_case_table_names=1

このようにするとMySQL内部では全て小文字で管理されるようになり、SQL文での大文字で書かれたテーブル名やカラム名が実行時に小文字に変換されて処理されます。

ただし、元の大文字小文字が判別できなくなってしまうため、その判別がどこかしらの処理で必要になるような場合は、この設定は適切ではありません。 MySQLの中での管理が全て小文字になるため、MySQLからテーブル名やカラム名をメタ情報として取得すると、全て小文字になっています。 例えばキャメルケースでテーブル名を表現しているような場合、DBFlute のような自動生成ツールで、MemberStatus が全て Memberstatus となってしまい、動作に問題はありませんが見づらくなります。そういうときは、この "lower_case_table_names" の別の値も検討する必要があるかもしれません。

トランザクション分離レベルの設定

MySQLではトランザクション分離レベルはデフォルトで "Repeatable Read" になっていますが、 他のデータベースのデフォルトである "Read Committed" に合わせたい場合があります。 

e.g. トランザクション分離レベルをRead-Committedに設定 @my.cnf
[mysqld]
transaction-isolation=Read-Committed

キャラセットの設定

e.g. キャラセットを "UTF-8" に設定 @my.cnf
[mysqld]
default-character-set=utf8

SQLモード

SQLのデフォルト挙動を微調整するための様々な設定が存在します。

例えば、MySQL は以下のような group by 句に存在しないカラムを select 句に指定してもエラーになりません。(他のDBMSでは大抵はしっかりとエラーになる) 

e.g. 解決できないカラムがある group by のSQL {SQLモードを指定すればエラーにできる} @SQL
select MEMBER_STATUS_CODE, MEMBER_NAME
  from MEMBER
 group by MEMBER_STATUS_CODE;

こういった "安全ではない割り切り" が多数存在します。SQLモードを設定することでそれらをしっかり実行時エラーにすることが可能です。 SQLモードの設定は、my.cnf(ini) に以下のように設定することが可能です。カンマ区切りで複数指定が可能です。但し、"a, b" というように空白を一つ開けるのは不可なので注意して下さい("a,b" と指定すること)。 

e.g. SQLモードの設定 {SQLモードを指定することによりエラーになる} @my.cnf
[mysqld]
sql_mode="ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,PIPES_AS_CONCAT,ANSI_QUOTES,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

DBFluteであれば、型の解決などは自動生成時点で担保されるため、あまり厳密にせずとも問題は発生しにくいですが、 それでも設定しておいた方が良いと思われる代表的なSQLモードを挙げておきます。

ONLY_FULL_GROUP_BY
間違ったGroupByがちゃんとエラーになる
PIPES_AS_CONCAT
SQL上の文字列連結で '||' が利用可能になる
ANSI_QUOTES
ダブルクォーテーションがバッククォートと同様の識別子となる

デリミタデータの取得

テーブルのデータをデリミタデータ(TSVやCSVなど)として出力するコマンドがあります。 

e.g. キャラセットを "UTF-8" に設定 @SQL
select * from MEMBER
into outfile "/tmp/MEMBER.tsv"
fields terminated by '\t'
enclosed by '"';

データの中のダブルクォーテーションは、バックスラッシュ "\" でエスケープされ、改行の前にも "\" が付与されます。よって、DBFlute の ReplaceSchema のデータ登録のための TSV を作成する場合は、convertValueMap.dataprop でこれらをうまく置換して仕様を合わせる必要があります。

データ登録(TSV) - 値のコンバート設定(dataprop)
e.g. 実際に試してデータ登録したときの設定 (足りない可能性もある) @convertValueMap.dataprop
map:{
    ; $$LINE$$ = map:{
        ; \\\\ = \\
        ; \\" = ""
    }
    ; $$ALL$$ = map:{
        ; \N = 
        ; $$empty$$ = $$empty$$
        ; contain:\\\n = \n
    }
}