ただメモができるだけのブラウザアプリ「TextArea」はこちらw

MySQLの知らないと怖い話-注意点を記載

今回はMySQLの怖い話(注意点)について書いていきます。知っていれば大丈夫!

具体的には以下の点について書いていきます。

  • デフォルト設定では大文字と小文字を区別しません
  • NULLと空文字は別ものです
  • IN句の中に大量の値の入れるとパフォーマンスが劣化する
  • REPLACE句の挙動
  • CONCATで文字列連結時に、ひとつでもNULLな項目があると、結果はNULLになる
  • UNIQUE インデックス制約では、文字列の後のスペースが無視される

知っていれば大丈夫なので、1つずつ確実に理解していきましょ★

デフォルト設定では大文字と小文字を区別しません

  • 検索時に「hoge」をキーとして検索した場合、テーブルデータに「hoge」「HOGE」「Hoge」があったとすると、全てヒットします
    • 大文字・小文字を区別せずに検索します
  • UNIQUE インデックスがあるテーブルにデータ登録する場合、テーブルデータに「hoge」があると、新しく「HOGE」のレコードは追加できません
    • UNIQUE インデックスも大文字・小文字区別せずにユニークとなる制約になります

大文字と小文字を区別するには、カラムにBINARY属性をつけます

ALTER TABLE `hoge_table` MODIFY `hoge_field` VARCHAR(255) BINARY;

検索で使用するカラムなど、大文字と小文字を区別しなくていい場合は、BINARY属性は不要です。検索時に大文字と小文字を区別せず取得可能なのは結構便利だったりします。

NULLと空文字は別ものです

NULLと空文字は別ものです。

NULLを検索したい場合のSQL

SELECT * FROM `hoge_table` WHERE `hoge_field` IS NULL;

空文字を検索したい場合のSQL

SELECT * FROM `hoge_table` WHERE `hoge_field` = '';

NULLと空文字は別ものということを理解して、どちらを検索したいかによってSQLを使い分けましょ!

IN句の中に大量の値の入れるとパフォーマンスが劣化する

IN句の中に大量の値の入れるとパフォーマンスが劣化することがあります。

IN句に数万単位の値を入れると、インデックスを貼っていたカラムでも、テーブルフルスキャンとなり、パフォーマンスが劣化する可能性があります。

以下のようなSQLのイメージです。

SELECT * FROM `hoge_table` WHERE `hoge_field` IN (hoge1,hoge2・・・・,hoge200000);

IN句にが大量になる場合は、SQLを分割して、IN句の中身を減らしましょ

REPLACE句の挙動

REPLACE句の挙動は、

REPLACE = DELETE + INSERT

となります。

テーブルの PRIMARY KEY または UNIQUE インデックスの値をキーとして、既存の行を削除して登録します。

削除して登録なので、オートインクリメントのカラムがあれば、カウントアップされます。

オートインクリメントの値を変更したくない場合は、SELECTして、なければINSERT、あればUPDATEとして、REPLACEを使わないようにしましょ

CONCATで文字列連結時に、ひとつでもNULLな項目があると、結果はNULLになる

以下のようなSQLで「hoge1_column」と「hoge2_column」を連結する場合、「hoge1_column」「hoge2_column」のどちらかがNULLの場合、結果はNULLになります。

SELECT CONCAT(hoge1_column, hoge2_column) AS hoge1_hoge2_column FROM `hoge_table`;

IFNULLを使用すると回避できます。

SELECT CONCAT(IFNULL(hoge1_column, ""), IFNULL(hoge2_column, "")) AS hoge1_hoge2_column FROM `hoge_table`;

UNIQUE インデックス制約では、文字列の後のスペースが無視される

UNIQUE インデックス制約では、文字列の後のスペースが無視されます。

hoge_columnにUNIQUE インデックス制約がある場合、以下の登録(「fuga」「fuga 」の登録)は末尾のスペースが無視されるので、重複登録エラーとなり、登録できません。

INSERT INTO `hoge_table` (`hoge_column`) VALUES ('fuga'), ('fuga ');

アプリケーションでトリムしてからデータ登録すれば大丈夫です。(「fuga 」は「fuga」として登録されるようアプリケーションで加工する)

女性誌多数掲載!
普通の子に会える

まとめ

MySQLの怖い話について記載しました。

  • デフォルト設定では大文字と小文字を区別しません
    • 区別が必要な時はBINARY属性をつける!
  • NULLと空文字は別ものです
    • 正しいSELECT文を使い分ける
  • IN句の中に大量の値の入れるとパフォーマンスが劣化する
    • SQL文を分割する
  • REPLACE句の挙動
    • 必要に応じて、SELECTして無ければINSERT、あればUPDATEとしてREPLACEを使わないようにする
  • CONCATで文字列連結時に、ひとつでもNULLな項目があると、結果はNULLになる
    • IFNULLを使用すると回避できる
  • UNIQUE インデックス制約では、文字列の後のスペースが無視される
    • アプリケーションでトリムしてからデータ登録する

これ以外にも、レコードサイズに上限があったりと、色々と注意点あるかと思いますが、注意して使っていきましょ!

DELETEする際の注意点の記事もあるので、よろしければ!

意外と知らないMySQLの落とし穴ーDELETEする際の注意点
MySQLのDELETEの注意点について、物理削除してもディスク容量は空かないので、ディスク容量削減のための物理削除の場合は特に要注意です。また、パフォーマンスが劣化することがあるので、これはどんなアプリケーションにおいても要注意です。あとオートインクリメントの値が巻き戻ることがありますが、対処法はあります!
スポンサーリンク
MySQL
フォローする
さゆフィクション