MySQL SUBSTR関数の使い方と具体例

はい、承知いたしました。MySQLのSUBSTR関数に関する詳細な解説記事を、約5000語で記述し、ここに直接表示します。

MySQLのSUBSTR関数徹底解説：文字列操作の基本から応用まで

はじめに

データベースに格納された文字列データは、多くの場合、そのままの形で利用されるだけでなく、その一部を抽出したり、加工したりする必要が生じます。例えば、メールアドレスからドメイン名だけを取り出したり、特定のコード番号の先頭部分だけを利用して集計を行ったり、固定長データの特定のフィールドを切り出したりする場合です。このような文字列の部分抽出において、MySQLで中心的な役割を果たすのがSUBSTR関数です。

SUBSTR関数は、指定した文字列から、指定した開始位置から指定した長さだけ、あるいは文字列の最後までを抽出するための非常に基本的ながら強力なツールです。SQLにおける文字列操作の基礎であり、データ分析、データ整形、レポート作成など、幅広いシーンで不可欠な関数と言えるでしょう。

本記事では、MySQLのSUBSTR関数について、その基本的な構文から、引数の詳細な挙動（特に位置指定や長さ指定における様々なケース）、マルチバイト文字（日本語など）の扱い、さらには応用的な使用例、パフォーマンスに関する考慮事項、そして関連する他の文字列関数との比較に至るまで、網羅的かつ詳細に解説します。

この記事を読むことで、あなたはSUBSTR関数を自信を持って使いこなし、複雑な文字列操作タスクを効率的に処理できるようになるでしょう。

さあ、MySQLのSUBSTR関数の世界に深く diving していきましょう。

1. SUBSTR関数の基本構文

MySQLにおいて、文字列の部分抽出を行う関数には、主にSUBSTRING()とSUBSTR()の二つがあります。これらは機能的には完全に同一であり、互換性のあるエイリアス（別名）として提供されています。どちらを使用しても結果は変わりませんが、一般的にはSUBSTRING()の方がより標準的なSQL関数名として広く認識されている傾向にあります。しかし、MySQLにおいてはSUBSTR()も同じくらい頻繁に使用されます。本記事では、主にSUBSTR()という名前を用いて説明を進めます。

SUBSTR()関数の基本的な構文は以下の二通りです。

構文1: カンマ区切り形式

sql SUBSTR(str, pos, len)

または

sql SUBSTRING(str, pos, len)

この構文が最も一般的です。引数はカンマで区切られます。

str: 抽出元となる文字列です。カラム名、文字列リテラル、あるいは他の関数や式の結果などが指定できます。
pos: 抽出を開始する位置を指定します。数値で指定します。この引数については後ほど詳しく解説しますが、MySQLでは1から数え始めるのが標準的な動作です。負の値を指定することも可能で、その場合は文字列の末尾から位置を数えます。
len: 抽出する文字数（長さ）を指定します。数値で指定します。この引数を省略することも可能です。省略した場合、posで指定した位置から文字列の最後までが抽出されます。

構文2: ANSI SQL標準形式

sql SUBSTR(str FROM pos FOR len)

または

sql SUBSTRING(str FROM pos FOR len)

この構文は、ANSI SQL標準に近い形式です。可読性を重視する場合に利用されることがありますが、機能的にはカンマ区切り形式と全く同じです。

str: 抽出元となる文字列です。
pos: 抽出を開始する位置を指定します。FROMキーワードの後ろに指定します。
len: 抽出する文字数を指定します。FORキーワードの後ろに指定します。FOR lenの部分は省略可能です。

どちらの構文を使うかは、個人の好みやプロジェクトのコーディング規約によります。機能に違いはないため、慣れている方、あるいはより読みやすいと感じる方を選択すれば良いでしょう。以降の例では、主に一般的なカンマ区切り形式を使用します。

2. SUBSTR関数の具体的な使い方と例

ここでは、SUBSTR関数の様々な使い方を具体的なSQL例を通して見ていきます。

2.1. 基本的な抽出（`pos`と`len`を指定）

最も基本的な使い方は、文字列の途中から、指定した長さだけ文字を抽出することです。

“`sql
— 例1: 文字列の先頭から5文字を抽出
SELECT SUBSTR(‘abcdefghijklmn’, 1, 5);
— 結果: ‘abcde’

— 例2: 文字列の3文字目から4文字を抽出
SELECT SUBSTR(‘abcdefghijklmn’, 3, 4);
— 結果: ‘cdef’

— 例3: カラムから値を抽出（テーブルの例を想定）
— 仮に ‘products’ テーブルに ‘product_code’ VARCHAR(10) カラムがあるとします。
— product_code が ‘A123-XYZ-456’ のような形式で、先頭4文字を抽出する場合
— SELECT SUBSTR(product_code, 1, 4) FROM products WHERE …;
“`

このように、posに開始位置（1から始まる）、lenに抽出したい文字数を指定することで、簡単に文字列の部分抽出が行えます。

2.2. `len`を省略した場合

len引数を省略した場合、posで指定した位置から文字列の最後までがすべて抽出されます。

“`sql
— 例4: 文字列の7文字目から最後までを抽出
SELECT SUBSTR(‘abcdefghijklmn’, 7);
— 結果: ‘ghijklmn’

— 例5: 1文字目から最後まで（つまり文字列全体）を抽出
SELECT SUBSTR(‘abcdefghijklmn’, 1);
— 結果: ‘abcdefghijklmn’

— 例6: カラムから、特定の接頭辞を除いた部分を抽出
— 仮に ‘file_path’ VARCHAR(255) カラムがあり、常に ‘/data/’ から始まるとします。
— ‘/data/’ 以降のパスを抽出する場合（’/data/’ は6文字）
— SELECT SUBSTR(file_path, 7) FROM files WHERE …;
“`

lenの省略は、文字列の後半部分を取り出したい場合に非常に便利です。

2.3. `pos`に負の値を指定した場合

MySQLでは、posに負の値を指定することで、文字列の末尾から位置を数えることができます。末尾の文字が-1、その左隣が-2、というように数えます。

“`sql
— 例7: 文字列の末尾から数えて1文字目（つまり最後の文字）を抽出
SELECT SUBSTR(‘abcdefghijklmn’, -1, 1);
— 結果: ‘n’

— 例8: 文字列の末尾から数えて4文字目から2文字を抽出
— 末尾から -1(‘n’), -2(‘m’), -3(‘l’), -4(‘k’) なので、’k’ から始まります。
SELECT SUBSTR(‘abcdefghijklmn’, -4, 2);
— 結果: ‘kl’

— 例9: 文字列の末尾から数えて5文字目から最後までを抽出（len省略）
— 末尾から -1(‘n’), -2(‘m’), -3(‘l’), -4(‘k’), -5(‘j’) なので、’j’ から始まります。
SELECT SUBSTR(‘abcdefghijklmn’, -5);
— 結果: ‘jklmn’

— 例10: ファイル名から拡張子を抽出（’.’の位置が分からない場合）
— ファイル名の末尾から3文字（例: ‘.txt’, ‘.jpg’ など、’.’を含む）を抽出
— SELECT SUBSTR(file_name, -4, 4) FROM documents WHERE …; — ‘.txt’などの場合
— 注意：拡張子が常に3文字とは限らないため、より厳密にはINSTR/LOCATEと組み合わせる必要があります（後述）。
“`

負のposは、文字列の末尾から決まった長さの部分を抽出する場合や、末尾を基準にした位置から抽出を開始する場合に役立ちます。

2.4. `pos`が0の場合

MySQLでは、posに0を指定した場合、その挙動は1を指定した場合と同じになります。これは他のデータベースシステム（例えばPostgreSQLではエラーになることがあります）とは異なるMySQL特有の挙動の一つです。

“`sql
— 例11: posに0を指定 – 1を指定した場合と同じ結果になる
SELECT SUBSTR(‘abcdefg’, 0, 3);
— 結果: ‘abc’

— 例12: posに1を指定 – 0を指定した場合と同じ結果になる
SELECT SUBSTR(‘abcdefg’, 1, 3);
— 結果: ‘abc’
“`

ただし、混乱を避けるため、明示的に先頭から抽出したい場合は1を使用することをお勧めします。

2.5. 境界値や不正な値の扱い

SUBSTR関数に与える引数が、文字列の長さや有効な範囲を超える場合、MySQLはエラーではなく、定義された挙動に従って結果を返します。

posが文字列長より大きい場合（正の値）:
- 抽出開始位置が文字列の終端を超えているため、空文字列 ('') が返されます。
sql SELECT SUBSTR('abcdefg', 10, 3); -- 結果: ''
posが文字列長を超えて小さい場合（負の値）:
- 末尾からの開始位置が文字列の先頭を超えている場合も、空文字列 ('') が返されます。
sql SELECT SUBSTR('abcdefg', -10, 3); -- 文字列長は7。末尾から10文字目は文字列の先頭より左。 -- 結果: ''
lenが0の場合:
- 抽出する文字数が0のため、空文字列 ('') が返されます。posの値にかかわらず同様です。
sql SELECT SUBSTR('abcdefg', 3, 0); -- 結果: '' SELECT SUBSTR('abcdefg', -3, 0); -- 結果: ''
lenが負の値の場合:
- MySQLのバージョンによって挙動が異なる可能性がありますが、多くの場合は無効な引数としてNULLまたは空文字列を返します。現在のバージョンではNULLを返すことが多いようです。負の長さは論理的に意味をなさないため、避けるべきです。
sql SELECT SUBSTR('abcdefg', 3, -2); -- 結果: NULL (バージョンによる)
lenがposからの残りの文字列長より大きい場合:
- posで指定した位置から始まり、文字列の終端までのすべての文字が抽出されます。エラーにはなりません。これはlenを省略した場合の挙動に近いです。
sql SELECT SUBSTR('abcdefg', 3, 10); -- 'cdefg' が残りの文字列 (5文字) -- 結果: 'cdefg'
引数がNULLの場合:
- str, pos, lenのいずれかの引数がNULLである場合、SUBSTR関数の結果はNULLになります。
sql SELECT SUBSTR('abc', 1, NULL); -- 結果: NULL SELECT SUBSTR('abc', NULL, 2); -- 結果: NULL SELECT SUBSTR(NULL, 1, 2); -- 結果: NULL

これらの挙動を理解しておくと、予期せぬNULLや空文字列の結果を防ぎ、堅牢なSQLクエリを作成するのに役立ちます。

2.6. マルチバイト文字（日本語など）の扱い

MySQLのSUBSTR関数は、デフォルトで文字単位で動作します。これは、サロゲートペアを使用しない一般的なUTF-8文字やShift_JIS、EUC-JPなどのマルチバイト文字においても同様です。つまり、1文字が複数バイトで表現される場合でも、posやlenはバイト数ではなく文字数として解釈されます。

“`sql
— 例13: 日本語文字列からの抽出
SELECT SUBSTR(‘こんにちは世界’, 4, 3);
— 結果: ‘ち世界’
— 解説: 1文字目’こ’, 2文字目’ん’, 3文字目’に’, 4文字目’ち’ から開始し、3文字(‘ち’, ‘世’, ‘界’)を抽出。

— 例14: 日本語文字列で負のposを使用
SELECT SUBSTR(‘こんにちは世界’, -3, 2);
— 結果: ‘世界’
— 解説: 末尾から数えて -1(‘界’), -2(‘世’), -3(‘界’)ではありません！
— MySQLのドキュメントによると、負のposは末尾からのバイト位置ではなく文字位置を指します。
— 末尾から数えて -1 (‘界’), -2 (‘界’), -3 (‘世’) … と数えます。
— したがって、末尾から3文字目は ‘世’ です。そこから2文字抽出すると ‘世界’ になります。

— 例15: 日本語文字列でlen省略
SELECT SUBSTR(‘こんにちは世界’, 5);
— 結果: ‘は世界’
— 解説: 5文字目’は’から最後まで。
“`

このように、日本語を含むマルチバイト文字列を扱う場合でも、バイト数を意識する必要はなく、直感的に文字数としてposやlenを指定できます。ただし、使用している文字セットや照合順序によっては、特定の特殊文字や絵文字などで複雑な挙動を示す可能性もゼロではありません。一般的な日本語文字列においては、文字単位で処理されると理解しておけば問題ないでしょう。

もし、バイト単位で処理を行いたい場合は、SUBSTRING（またはSUBSTR）関数の代わりにSUBSTRING_BYTES()関数を使用する必要があります。

sql -- 例16: 日本語文字列をバイト単位で抽出（UTF-8の場合） -- 'こんにちは世界' (UTF-8) は 5文字 * 3バイト = 15バイト (ASCII部分は除く) SELECT SUBSTRING_BYTES('こんにちは世界', 7, 6); -- 解説: 7バイト目から6バイトを抽出。 -- 1-3バイト: 'こ', 4-6バイト: 'ん', 7-9バイト: 'に', 10-12バイト: 'ち', 13-15バイト: 'は', 16-18バイト: '世', 19-21バイト: '界' -- 7バイト目は 'に' の最初のバイト。そこから6バイトは 'に' (3バイト) + 'ち' (3バイト) -- 結果: 'にち'

通常の使用では文字単位のSUBSTR()で十分ですが、バイナリデータの処理や、文字コードの詳細を意識した処理が必要な場合はSUBSTRING_BYTES()が存在することを覚えておくと良いでしょう。

また、文字列の長さを取得する際にも、文字数かバイト数かで関数が異なります。
* CHARACTER_LENGTH(str) または CHAR_LENGTH(str): 文字数を返します。
* LENGTH(str): バイト数を返します。

これらの関数とSUBSTR()を組み合わせて使用することで、より柔軟な文字列処理が可能になります。

3. SUBSTR関数の応用例

SUBSTR関数は単体でも便利ですが、他のSQL関数と組み合わせることで、さらに強力なデータ操作が可能になります。ここでは、いくつかの応用例を見ていきましょう。

3.1. 特定の区切り文字に基づいた文字列の分割

文字列の中に特定の区切り文字（例: ,, -, .など）が含まれている場合、SUBSTR関数と区切り文字の位置を特定する関数（INSTR()やLOCATE()）を組み合わせることで、文字列を分割することができます。

INSTR(str, substr) または LOCATE(substr, str) は、strの中でsubstrが最初に出現する位置（1から始まる）を返します。見つからない場合は0を返します。LOCATEは開始位置を指定できる第3引数も取りますが、基本的にはINSTRと同じ用途で使えます。

例: メールアドレスからユーザー名とドメイン名を抽出

メールアドレス形式: [email protected]

“`sql
— 仮のメールアドレス
SET @email = ‘[email protected]’;

— ‘@’の位置を取得
SET @at_pos = INSTR(@email, ‘@’);

— ユーザー名を抽出 (@の前の部分)
— 開始位置は1、長さは ‘@’ の位置 – 1
SELECT SUBSTR(@email, 1, @at_pos – 1) AS username;
— 結果: ‘testuser’

— ドメイン名を抽出 (@の後の部分)
— 開始位置は ‘@’ の位置 + 1、長さはそこから最後まで (len省略)
SELECT SUBSTR(@email, @at_pos + 1) AS domain;
— 結果: ‘example.com’
“`

これを実際のテーブル操作に適用する場合は、サブクエリや共通テーブル式 (CTE) を利用したり、直接SELECTリストに関数式を記述したりします。

sql -- 仮に 'users' テーブルに 'email' VARCHAR(255) カラムがあるとします。 SELECT email, SUBSTR(email, 1, INSTR(email, '@') - 1) AS username, SUBSTR(email, INSTR(email, '@') + 1) AS domain FROM users WHERE INSTR(email, '@') > 0; -- '@' が含まれるレコードのみ処理

例: ファイルパスからディレクトリパスとファイル名を抽出

ファイルパス形式: /path/to/directory/filename.txt

最後の / の位置を特定する必要があります。INSTRやLOCATEは最初に出現する位置を返すため、最後の出現位置を知るためには工夫が必要です。MySQL 8.0以降ではREGEXP_INSTRが使えますが、それ以前のバージョンではSUBSTRING_INDEXを使うか、REVERSEとINSTRを組み合わせる方法が一般的です。

“`sql
— 仮のファイルパス
SET @filepath = ‘/usr/local/data/report.csv’;

— ファイル名を取得 (最後の ‘/’ の後)
— SUBSTRING_INDEX(str, delim, count): 区切り文字 delim で str を分割し、count 番目の部分文字列を返す。
— count が正の場合は先頭から、負の場合は末尾から数える。
SELECT SUBSTRING_INDEX(@filepath, ‘/’, -1) AS filename;
— 結果: ‘report.csv’

— ディレクトリパスを取得 (最後の ‘/’ の前)
— count に -1 を指定すると最後の区切り文字の後ろが取得されるので、それ以外の部分を取得するには…
— SUBSTRING_INDEX(str, delim, count) で count に 1 を指定すると最初の区切り文字まで。
— 最後の ‘/’ までを取得するには count に最後の ‘/’ より前の区切り文字の数+1 を指定する必要があり複雑。
— もっと簡単なのは、文字列全体からファイル名と最後の ‘/’ を除いた部分を取得する方法。
— 全体の長さからファイル名の長さと ‘/’ 1文字の長さを引いて、先頭からその長さだけ抽出。
SELECT
SUBSTR(@filepath, 1, LENGTH(@filepath) – LENGTH(SUBSTRING_INDEX(@filepath, ‘/’, -1)) – 1) AS directory_path;
— 結果: ‘/usr/local/data’

— あるいは、末尾からファイル名の長さと ‘/’ 1文字分を考慮して抽出する方法も考えられますが、
— SUBSTRING_INDEX(-1) でファイル名、SUBSTRING_INDEX(-2) で最後のディレクトリを取得し、そこからファイル名部分を除去…など、状況によって様々な組み合わせが考えられます。
— ここでは SUBSTR と LENGTH, SUBSTRING_INDEX を組み合わせる方法を示しました。
“`

SUBSTRING_INDEXはSUBSTRとは少し異なる関数ですが、文字列分割タスクにおいてはSUBSTRと組み合わせて、あるいはSUBSTRよりも簡潔に目的を達成できる強力な関数です。

3.2. 固定長データの抽出

ログファイルやレガシーシステムからインポートされたデータなど、固定長形式の文字列を扱う場合があります。このようなデータから特定のフィールドを抽出するのにSUBSTRは最適です。

例: 固定長レコードのパース

レコード形式: YYYYMMDD商品コード数量 (例: 20231027A123450050)

日付 (YYYYMMDD): 1文字目から8文字
商品コード: 9文字目から5文字
数量: 14文字目から4文字

“`sql
SET @record = ‘20231027A123450050’;

— 日付部分を抽出
SELECT SUBSTR(@record, 1, 8) AS order_date;
— 結果: ‘20231027’

— 商品コードを抽出
SELECT SUBSTR(@record, 9, 5) AS product_code;
— 結果: ‘A1234’

— 数量を抽出
SELECT SUBSTR(@record, 14, 4) AS quantity;
— 結果: ‘0050’
“`

実際のテーブルに対して行う場合は、カラム名を@recordの代わりに使用します。

sql -- 仮に 'raw_data' テーブルに 'record_string' VARCHAR(20) カラムがあるとします。 SELECT SUBSTR(record_string, 1, 8) AS order_date, SUBSTR(record_string, 9, 5) AS product_code, SUBSTR(record_string, 14, 4) AS quantity FROM raw_data;

固定長データのパースは、SUBSTR関数の最も典型的な応用例の一つです。

3.3. データ整形と切り詰め

表示上の都合やデータの一貫性を保つために、文字列を特定の長さに切り詰めたい場合があります。

例: 長すぎる文字列を切り詰めて末尾に’…’を追加

もし文字列が指定した長さ（例: 20文字）より長い場合に、20文字で切り詰めて末尾に’…’を追加したい。

“`sql
— 仮の文字列
SET @text1 = ‘これは20文字よりずっと長いとても長い文字列です。’;
SET @text2 = ‘短い文字列’;

— 長さをチェックし、必要なら切り詰める
SELECT
IF(
CHAR_LENGTH(@text1) > 20,
CONCAT(SUBSTR(@text1, 1, 20), ‘…’),
@text1
) AS truncated_text_1,
IF(
CHAR_LENGTH(@text2) > 20,
CONCAT(SUBSTR(@text2, 1, 20), ‘…’),
@text2
) AS truncated_text_2;
“`

ここで、CHAR_LENGTH()は文字数を返し、IF()は条件によって異なる値を返す関数、CONCAT()は文字列を結合する関数です。これらの関数とSUBSTRを組み合わせることで、条件に応じたデータ整形が可能になります。

3.4. WHERE句やORDER BY句での利用

SUBSTR関数は、SELECTリストだけでなく、WHERE句で条件を指定したり、ORDER BY句で並べ替え順序を決定したりするためにも利用できます。

例: 特定の接頭辞を持つレコードを検索

商品コードの最初の2文字が’SP’であるすべての商品を検索したい。

sql -- 仮に 'products' テーブルに 'product_code' VARCHAR(10) カラムがあるとします。 SELECT product_name, product_code FROM products WHERE SUBSTR(product_code, 1, 2) = 'SP';

例: 文字列の部分に基づいて並べ替え

顧客名の苗字（ここでは仮に最初の3文字とする）に基づいて並べ替えたい。

sql -- 仮に 'customers' テーブルに 'customer_name' VARCHAR(50) カラムがあるとします。 SELECT customer_name FROM customers ORDER BY SUBSTR(customer_name, 1, 3);

WHERE句やORDER BY句でSUBSTRを使用する場合、パフォーマンスに関する重要な考慮事項があります（後述）。

3.5. UPDATE文での利用

SUBSTR関数は、既存のデータを更新するUPDATE文の中でも利用できます。例えば、カラムの値の一部を抽出して別のカラムに格納したり、カラムの値の一部を更新条件として使用したりする場合です。

例: カラムの一部を抽出して別のカラムに格納

ファイルパスからファイル名のみを抽出して、専用のfile_nameカラムに格納したい。

sql -- 仮に 'documents' テーブルに 'file_path' VARCHAR(255) と 'file_name' VARCHAR(100) カラムがあるとします。 UPDATE documents SET file_name = SUBSTRING_INDEX(file_path, '/', -1); -- ファイル名抽出に SUBSTRING_INDEX を使用 -- あるいは、SUBSTR と LENGTH, INSTR を組み合わせる -- UPDATE documents -- SET file_name = SUBSTR(file_path, INSTR(file_path, '/') + 1); -- 仮に '/' が一つしかない場合 -- UPDATE documents -- SET file_name = SUBSTR(file_path, LENGTH(file_path) - LOCATE('/', REVERSE(file_path)) + 2); -- 最後の '/' の後を取得する複雑な方法

この例ではSUBSTRING_INDEXが簡潔ですが、文字列の構造や抽出したい部分に応じてSUBSTRも使用できます。

例: カラムの値の一部に基づいて更新

商品コードの最初の2文字が’OLD’である商品のコードを、最初の2文字を’NEW’に変更して更新したい。

sql -- 仮に 'products' テーブルに 'product_code' VARCHAR(10) カラムがあるとします。 UPDATE products SET product_code = CONCAT('NEW', SUBSTR(product_code, 3)) WHERE SUBSTR(product_code, 1, 3) = 'OLD';

ここでは、CONCAT()関数を使って新しい接頭辞と元のコードの残りの部分を結合しています。

4. SUBSTR関数使用時の注意点とパフォーマンス

SUBSTR関数は非常に便利ですが、特に大規模なデータセットを扱う際には、いくつかの注意点があります。

4.1. パフォーマンスへの影響

WHERE句やORDER BY句でSUBSTR関数をカラムに適用すると、多くの場合、そのカラムに作成されたインデックスが利用できなくなります。インデックスは、特定のカラムの値に基づいてデータを効率的に検索・並べ替えするために使用されますが、関数が適用されると、データベースシステムはインデックスが示す元のカラムの値ではなく、関数の計算結果に基づいて操作を行う必要があります。データベースは通常、カラムの生の値をインデックス化しており、関数の計算結果をインデックス化しているわけではないため、インデックスを効率的に使用できません。

これにより、データベースはテーブル全体をスキャンして各行に関数を適用し、その結果に基づいてフィルタリングや並べ替えを行う必要が生じます。これは「フルテーブルスキャン」と呼ばれ、特に大きなテーブルでは非常に遅くなる可能性があります。

sql -- 例: このクエリは product_code カラムのインデックスを利用できない可能性が高い SELECT * FROM products WHERE SUBSTR(product_code, 1, 2) = 'SP';

パフォーマンス劣化を避けるための対策:

条件を工夫する: 可能であれば、SUBSTR関数を使わずにインデックスが利用できる条件に書き換えます。例えば、「先頭2文字が’SP’」という条件は、LIKE演算子とワイルドカードを使ってインデックスフレンドリーな形に書き換えられる場合があります。

sql -- LIKE を使用 - 条件によってはインデックスが利用可能 SELECT * FROM products WHERE product_code LIKE 'SP%';
LIKE 'prefix%' の形式は、多くの場合、B-treeインデックスを利用できます。
関数インデックス/仮想カラムを利用する: MySQL 5.7以降でサポートされている生成列 (Generated Columns) や、特定のストレージエンジン（例: InnoDB）でサポートされている関数インデックスのような機能を利用することで、SUBSTR関数の結果を物理的または仮想的に格納し、その結果にインデックスを作成することが可能です。

“`sql
— 例: product_code の最初の2文字を格納する仮想カラムを作成し、インデックスを作成
ALTER TABLE products
ADD product_code_prefix VARCHAR(2)
GENERATED ALWAYS AS (SUBSTR(product_code, 1, 2)) VIRTUAL; — または STORED

ALTER TABLE products
ADD INDEX idx_product_code_prefix (product_code_prefix);

— このクエリは新しく作成したインデックスを利用できる
SELECT * FROM products WHERE product_code_prefix = ‘SP’;
``VIRTUALカラムはストレージを消費しませんが計算コストがかかります（ただしインデックスは利用可能）。STORED`カラムはストレージを消費しますが、値は物理的に保存されるため読み取りが高速です。どちらを選択するかはユースケースによります。
アプリケーション側で処理する: 大量のデータに対するフィルタリングや並べ替えを頻繁に行う必要がある場合は、アプリケーション側で文字列処理を行い、データベースではシンプルな条件でデータを取得する方が効率的な場合があります。

SUBSTR関数をSELECTリストで使用する（つまり値を抽出して表示するだけ）場合は、通常、パフォーマンスへの影響は限定的です。問題となるのは、その結果をフィルタリングや並べ替えの条件として利用する場合です。

4.2. 文字コードの正確な理解

前述のように、MySQLのSUBSTRはデフォルトで文字単位で動作します。しかし、異なる文字セットや照合順序が混在する環境、あるいは非常に特殊な文字（絵文字のシーケンスや合字など）を扱う場合、予期しない挙動をすることが全くないとは言い切れません。文字列の文字数やバイト数を正確に把握するためには、CHARACTER_LENGTH()やLENGTH()関数、そしてSUBSTRING_BYTES()関数を理解しておくことが重要です。特に、バイト単位の処理が必要な場合は、必ずSUBSTRING_BYTES()を使用してください。

データベース、テーブル、そしてカラムの文字セットと照合順序の設定を確認し、扱うデータと整合性が取れていることを確認してください。SHOW CREATE TABLE table_name; コマンドなどで確認できます。

4.3. 可読性と保守性

複雑な文字列操作をすべてSQLの関数だけで記述すると、クエリが非常に長くなり、可読性や保守性が低下する可能性があります。特に、複数のSUBSTRや他の文字列関数をネストしたり組み合わせたりする場合です。

複雑なロジック: 特定のパターンに基づいて文字列を分割するなど、複雑なロジックが必要な場合は、SQLではなくアプリケーションコード（Python, PHP, Javaなど）で処理する方が、デバッグや変更が容易な場合があります。
標準化されていない形式: データ形式が固定されておらず、様々なパターンが存在するような場合は、SQL関数での処理は難しくなります。正規表現関数（MySQL 8.0以降で強化）を利用したり、アプリケーション側で柔軟なパース処理を実装したりすることを検討してください。

SQLはデータ操作に特化した言語であり、文字列処理は得意分野の一つではありますが、すべてをSQLで解決しようとせず、アプリケーション層との役割分担を適切に行うことも重要です。

4.4. エラーハンドリング

SUBSTR関数自体は、不正な数値（非数値のposやlen）が渡された場合にエラーを発生させるのではなく、NULLを返すなどの定義された挙動をします（例: SELECT SUBSTR('abc', 'x', 2); は警告を出す可能性がありますが、NULLを返すことが多いです）。しかし、クエリ全体として予期しない結果になる可能性があります。

例えば、INSTR()などの結果をSUBSTRの引数として使用する場合、INSTR()が0を返すとSUBSTRのposが0になるなど、連鎖的に影響を与える可能性があります。INSTRが0を返すケース（区切り文字が見つからない）を想定して、WHERE句でフィルタリングしたり、CASE文で条件分岐させたりするなど、NULLや予期しない値に対するハンドリングを考慮することが重要です。

sql -- 例: '@' が見つからない場合にエラーやNULLを防ぐ SELECT email, IF( INSTR(email, '@') > 0, -- '@' が含まれるかチェック SUBSTR(email, 1, INSTR(email, '@') - 1), NULL -- または空文字列 ) AS username FROM users;

5. SUBSTR vs LEFT, RIGHT

MySQLには、SUBSTRと似た機能を持つ関数としてLEFT()とRIGHT()があります。

LEFT(str, len): 文字列strの先頭からlen文字を抽出します。これは SUBSTR(str, 1, len) と全く同じです。
RIGHT(str, len): 文字列strの末尾からlen文字を抽出します。これは SUBSTR(str, -len, len) とほぼ同じです（正確には SUBSTR(str, CHAR_LENGTH(str) - len + 1, len) と等価ですが、負のposでlen文字を抽出するという意味では-len, lenが直感的です）。

使い分け:

文字列の先頭から一定文字数抽出したい場合は、LEFT()を使用するのが最も意図が明確で可読性が高いです。
文字列の末尾から一定文字数抽出したい場合は、RIGHT()を使用するのが最も意図が明確で可読性が高いです。
文字列の途中から抽出したい場合や、末尾から数えた位置を基準に抽出したい場合は、SUBSTR()を使用します。特に負のposを指定する場合はSUBSTR()が必須となります。

機能的な重複はありますが、LEFTとRIGHTは最も頻繁に行われる「先頭からの抽出」「末尾からの抽出」をより簡潔に表現するための関数と言えます。SUBSTRはこれらを含む、より汎用的な部分文字列抽出関数です。

“`sql
— LEFT の例
SELECT LEFT(‘abcdefg’, 3);
— 結果: ‘abc’
— SUBSTR で同じことをする場合:
SELECT SUBSTR(‘abcdefg’, 1, 3);
— 結果: ‘abc’

— RIGHT の例
SELECT RIGHT(‘abcdefg’, 3);
— 結果: ‘efg’
— SUBSTR で同じことをする場合 (負のpos):
SELECT SUBSTR(‘abcdefg’, -3, 3);
— 結果: ‘efg’
— SUBSTR で同じことをする場合 (正のpos + 長さ計算):
SELECT SUBSTR(‘abcdefg’, CHAR_LENGTH(‘abcdefg’) – 3 + 1, 3);
— 結果: ‘efg’
“`

コードの意図を明確にするために、先頭・末尾からの抽出にはLEFT/RIGHTを優先的に使用し、それ以外のケースでSUBSTRを使用するという方針が考えられます。

6. まとめ

MySQLのSUBSTR関数（またはSUBSTRING関数）は、文字列の一部を抽出するための、SQLにおける基本的な操作の一つです。

基本構文: SUBSTR(str, pos, len) または SUBSTR(str FROM pos FOR len)
引数: str (文字列), pos (開始位置、1から数える、負の値で末尾から), len (長さ、省略可能で最後まで)
挙動: 正確なpos, lenの指定、負のpos、len省略、境界値やNULL値の扱いについて理解することが重要です。
マルチバイト文字: 日本語などのマルチバイト文字もデフォルトで文字単位で処理されます。バイト単位の処理にはSUBSTRING_BYTES()を使用します。
応用: 他の文字列関数（INSTR, LOCATE, LENGTH, CHAR_LENGTH, CONCAT, SUBSTRING_INDEX, IF, CASEなど）と組み合わせることで、文字列の分割、固定長データのパース、データ整形など、様々なタスクを実行できます。
パフォーマンス: WHERE句やORDER BY句でSUBSTRをカラムに適用すると、インデックスが利用できずパフォーマンスが劣化する可能性があります。LIKEや関数インデックス/仮想カラムなどを活用して、パフォーマンスへの影響を最小限に抑える工夫が必要です。
LEFT/RIGHT: LEFT()とRIGHT()は、それぞれSUBSTRの特定の用途（先頭/末尾からの抽出）に特化した関数であり、可読性を高めるために使い分けることができます。

SUBSTR関数は、データベース内の文字列データを効率的に操作するために不可欠なツールです。その基本的な使い方から、応用的な組み合わせ、そしてパフォーマンスに関する考慮事項までをしっかりと理解することで、より効果的なSQLクエリを作成できるようになります。

この記事が、あなたのMySQLにおける文字列操作能力向上の一助となれば幸いです。