MySQLの補足資料

• 概要
• SQL周り
  • 全文検索
  • 更新時のサブクエリでの制限
• SQL関数
  • 現在日付
  • null の場合のデフォルト値
  • 数値の丸め
  • 数値の切り捨て
  • 日付のフォーマット
  • 日付の切り捨て
  • 日付の加算・減算
• テーブル定義
  • Identity設定
  • テーブルコメントの設定
  • ユニーク制約の特徴
  • 発行された連番を insert 実行後に取得
• 環境周り
  • ストレージエンジンの設定
  • テーブル名のケース区別なし管理
  • トランザクション分離レベルの設定
  • キャラセットの設定
  • SQLモード
  • デリミタデータの取得

概要

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

SQL周り

全文検索

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

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

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

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

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

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() にてこれを利用しています。

数値の丸め

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

e.g. 購入数量の平均値の小数点を丸める @SQL
round(avg(PURCHASE_COUNT), 0)

• 数値の部分はバインド変数が適用できます

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

数値の切り捨て

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

e.g. 購入数量の平均値の小数点を切り捨て @SQL
truncate(avg(PURCHASE_COUNT), 0)

• 数値の部分はバインド変数が適用できます

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

日付のフォーマット

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関数フィルタの日付の切り捨てにてこれを利用しています。

日付の加算・減算

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

e.g. 更新日時を一日進める @SQL
date_add(UPDATE_DATETIME, interval 1 day)

• 数値の部分はバインド変数が適用できます
• マイナス値を指定すると減算になります
• day の他に、year, month, hour などが指定できます

DBFluteでは、ConditionBeanの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が表示されます。

環境周り

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

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

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 でこれらをうまく置換して仕様を合わせる必要があります。

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

SiteMap Author's BlogBTS