MySQLの補足資料
概要
SQL周り
全文検索
MyISAM もしくは "Tritonn + Senna" において全文検索ができます。
e.g. foo という文字列が含まれる会員名称を全文検索 @SQL
match(MEMBER_NAME) against ('foo' in boolean mode)
更新時のサブクエリでの制限
update 文の where 句のサブクエリにて更新対象のテーブルを参照できないという制限があります。
また、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)
- デフォルト値の部分はバインド変数が適用できます
数値の丸め
round() 関数を利用することで数値の丸めができます。厳密な丸め仕様はMySQLの仕様に依存します。
e.g. 購入数量の平均値の小数点を丸める @SQL
round(avg(PURCHASE_COUNT), 0)
- 数値の部分はバインド変数が適用できます
数値の切り捨て
truncate() 関数を利用することで数値の切り捨てができます。
e.g. 購入数量の平均値の小数点を切り捨て @SQL
truncate(avg(PURCHASE_COUNT), 0)
- 数値の部分はバインド変数が適用できます
日付のフォーマット
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)
日付の加算・減算
date_add() 関数を利用することで加算と減算ができます。
e.g. 更新日時を一日進める @SQL
date_add(UPDATE_DATETIME, interval 1 day)
- 数値の部分はバインド変数が適用できます
- マイナス値を指定すると減算になります
- day の他に、year, month, hour などが指定できます
テーブル定義
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();
環境周り
ストレージエンジンの設定
例えば、(デフォルトのストレージエンジンとして) 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からテーブル名やカラム名をメタ情報として取得すると、全て小文字になっています。
例えばキャメルケースでテーブル名を表現しているような場合、
トランザクション分離レベルの設定
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"
- 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 '"';
データの中のダブルクォーテーションは、バックスラッシュ "\" でエスケープされ、改行の前にも "\"
が付与されます。よって、
e.g. 実際に試してデータ登録したときの設定 (足りない可能性もある) @convertValueMap.dataprop
map:{
; $$LINE$$ = map:{
; \\\\ = \\
; \\" = ""
}
; $$ALL$$ = map:{
; \N =
; $$empty$$ = $$empty$$
; contain:\\\n = \n
}
}