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

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

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

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

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

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

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

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

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

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

NULLを検索したい場合のSQL

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

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

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

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

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

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

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

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

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

REPLACE句の挙動は、

REPLACE = DELETE + INSERT

となります。

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

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

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

以下のような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 インデックス制約では、文字列の後のスペースが無視されます。

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

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

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

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

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

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