MySQL - 文字列関数

概要

MySQLでは、豊富な文字列関数が提供されており、文字列の結合、分割、検索、置換、フォーマット等の処理を行うことができる。
これらの関数は、SELECT文、UPDATE文、WHERE句等で使用でき、データ操作において重要な役割を果たす。

MySQL 5.7とMySQL 8.0では、文字列関数の動作に一部差異がある。
特に、文字コードと照合順序の扱いに変更がある。
MySQL 8.0では、デフォルト文字セットが utf8mb4、デフォルト照合順序が utf8mb4_0900_ai_ci に変更された。
MySQL 5.7では、デフォルト文字セットは latin1、デフォルト照合順序は latin1_swedish_ci であった。

文字列関数は、マルチバイト文字セット (utf8mb4) を考慮して使用する必要がある。
LENGTH 関数 と CHAR_LENGTH 関数は、マルチバイト文字での動作が異なる。
LENGTH 関数はバイト長を返し、CHAR_LENGTH 関数は文字数を返す。

UTF-8エンコーディングでは、日本語は1文字あたり3バイトで表現されるため、LENGTH関数の戻り値は文字数の3倍となる。

文字列関数をWHERE句で使用すると、インデックスが使用されない場合が多い。
パフォーマンスを考慮する場合は、関数を使用しないクエリ設計を検討する必要がある。

TEXT型およびBLOB型での文字列関数使用は、処理速度が低下する傾向がある。
大量のテキストデータを処理する場合は、VARCHAR型の使用を推奨する。

文字列結合

CONCAT

CONCAT 関数は、複数の文字列を結合する。

基本構文を以下に示す。

 CONCAT(str1, str2, ...)

引数のいずれかが NULL の場合、結果は NULL となる。
これは、CONCAT_WS 関数との重要な違いである。

使用例を以下に示す。

 SELECT CONCAT('Hello', ' ', 'World');
 -- 結果: 'Hello World'
 
 SELECT CONCAT('ID: ', id, ' Name: ', name) FROM users;
 
 SELECT CONCAT('MySQL', NULL, 'Database');
 -- 結果: NULL

CONCAT 関数は、数値型の引数を文字列に自動変換する。

CONCAT_WS

CONCAT_WS 関数は、セパレータを使用して複数の文字列を結合する。

基本構文を以下に示す。

 CONCAT_WS(separator, str1, str2, ...)

CONCAT 関数と異なり、CONCAT_WS 関数は、NULL の引数をスキップする。
セパレータが NULL の場合のみ、結果は NULL となる。

使用例を以下に示す。

 SELECT CONCAT_WS(', ', 'Apple', 'Banana', 'Cherry');
 -- 結果: 'Apple, Banana, Cherry'
 
 SELECT CONCAT_WS('-', '2025', '02', '15');
 -- 結果: '2025-02-15'
 
 SELECT CONCAT_WS(', ', 'Alice', NULL, 'Bob', 'Charlie');
 -- 結果: 'Alice, Bob, Charlie' (NULLはスキップされる)
 
 SELECT CONCAT_WS(NULL, 'Alice', 'Bob', 'Charlie');
 -- 結果: NULL (セパレータがNULLのため)

CONCAT_WS 関数は、CSV形式のデータ生成に便利である。

部分文字列の取得

SUBSTRING / SUBSTR / MID

SUBSTRING 関数、SUBSTR 関数、MID 関数は、文字列から部分文字列を取得する。
これらの関数は、同一の動作をする別名である。

基本構文を以下に示す。

 SUBSTRING(str, pos)
 SUBSTRING(str, pos, len)
 SUBSTR(str, pos, len)
 MID(str, pos, len)

引数の意味

引数	説明
第1引数	対象文字列
第2引数	開始位置 (1始まり、負の値は末尾から)
第3引数	取得する文字数 (省略時は末尾まで)

使用例を以下に示す。

 SELECT SUBSTRING('HelloWorld', 6);
 -- 結果: 'World'
 
 SELECT SUBSTRING('HelloWorld', 1, 5);
 -- 結果: 'Hello'
 
 SELECT SUBSTRING('HelloWorld', -5);
 -- 結果: 'World' (末尾から5文字)
 
 SELECT SUBSTRING('HelloWorld', -5, 3);
 -- 結果: 'Wor' (末尾から5文字目から3文字)

負の位置指定により、末尾からの取得が可能である。

LEFT / RIGHT

LEFT 関数は、文字列の左端から指定文字数を取得する。
RIGHT 関数は、文字列の右端から指定文字数を取得する。

基本構文を以下に示す。

 LEFT(str, len)
 RIGHT(str, len)

使用例を以下に示す。

 SELECT LEFT('HelloWorld', 5);
 -- 結果: 'Hello'
 
 SELECT RIGHT('HelloWorld', 5);
 -- 結果: 'World'
 
 SELECT LEFT('2025-02-15', 4);
 -- 結果: '2025' (年を取得)
 
 SELECT RIGHT('2025-02-15', 2);
 -- 結果: '15' (日を取得)

これらの関数は、SUBSTRING 関数の簡易版である。

SUBSTRING_INDEX

SUBSTRING_INDEX 関数は、デリミタを基準に文字列を分割し、指定された部分を取得する。

基本構文を以下に示す。

 SUBSTRING_INDEX(str, delim, count)

引数の意味

引数	説明
第1引数	対象文字列
第2引数	デリミタ文字列
第3引数	デリミタの出現回数 (正数は左から、負数は右から)

使用例を以下に示す。

 SELECT SUBSTRING_INDEX('192.168.1.100', '.', 2);
 -- 結果: '192.168' (左から2番目のピリオドまで)
 
 SELECT SUBSTRING_INDEX('192.168.1.100', '.', -2);
 -- 結果: '1.100' (右から2番目のピリオドまで)
 
 SELECT SUBSTRING_INDEX('user@example.com', '@', 1);
 -- 結果: 'user' (ユーザ名部分を取得)
 
 SELECT SUBSTRING_INDEX('user@example.com', '@', -1);
 -- 結果: 'example.com' (ドメイン部分を取得)
 
 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('192.168.1.100', '.', 3), '.', -1);
 -- 結果: '1' (3番目のオクテットを取得)

ネストして使用することで、複雑な分割が可能である。

文字列置換・挿入

REPLACE

REPLACE 関数は、文字列内のすべての出現を置換する。

基本構文を以下に示す。

 REPLACE(str, from_str, to_str)

REPLACE 関数は、大文字小文字を区別する。
照合順序が大文字小文字を区別しない場合でも、REPLACE 関数は区別する。

使用例を以下に示す。

 SELECT REPLACE('Hello World', 'World', 'MySQL');
 -- 結果: 'Hello MySQL'
 
 SELECT REPLACE('192.168.1.100', '.', '-');
 -- 結果: '192-168-1-100'
 
 SELECT REPLACE('apple,apple,orange', 'apple', 'banana');
 -- 結果: 'banana,banana,orange' (すべての出現を置換)
 
 UPDATE products SET description = REPLACE(description, 'old_term', 'new_term');

全ての出現が置換される点に注意する必要がある。

INSERT

INSERT 関数は、指定位置の文字列を新しい文字列で置換する。

基本構文を以下に示す。

 INSERT(str, pos, len, newstr)

引数の意味

引数	説明
第1引数	対象文字列
第2引数	開始位置 (1始まり)
第3引数	置換する文字数
第4引数	挿入する文字列

使用例を以下に示す。

 SELECT INSERT('HelloWorld', 6, 5, 'MySQL');
 -- 結果: 'HelloMySQL'
 
 SELECT INSERT('12345', 3, 0, 'XX');
 -- 結果: '12XX345' (文字を挿入、置換なし)
 
 SELECT INSERT('abcdefg', 2, 3, 'XX');
 -- 結果: 'aXXefg' (2文字目から3文字を'XX'で置換)

第3引数に0を指定した場合は、文字列の挿入のみが行われる。

空白除去・パディング

TRIM / LTRIM / RTRIM

TRIM 関数は、文字列の両端から指定文字を除去する。
LTRIM 関数は、文字列の左端から空白を除去する。
RTRIM 関数は、文字列の右端から空白を除去する。

基本構文を以下に示す。

 TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
 LTRIM(str)
 RTRIM(str)

TRIM関数のオプション

オプション	説明
BOTH	両端から除去 (デフォルト)
LEADING	左端から除去
TRAILING	右端から除去
remstr	除去する文字列 (デフォルトは空白)

使用例を以下に示す。

 SELECT TRIM('   Hello World   ');
 -- 結果: 'Hello World'
 
 SELECT LTRIM('   Hello World   ');
 -- 結果: 'Hello World   '
 
 SELECT RTRIM('   Hello World   ');
 -- 結果: '   Hello World'
 
 SELECT TRIM(BOTH 'x' FROM 'xxxHelloxxx');
 -- 結果: 'Hello'
 
 SELECT TRIM(LEADING '0' FROM '000123');
 -- 結果: '123'
 
 SELECT TRIM(TRAILING '.' FROM 'example.com...');
 -- 結果: 'example.com'

TRIM 関数は、ユーザ入力データのクリーニングに有用である。

LPAD / RPAD

LPAD 関数は、文字列の左側を指定文字でパディングする。
RPAD 関数は、文字列の右側を指定文字でパディングする。

基本構文を以下に示す。

 LPAD(str, len, padstr)
 RPAD(str, len, padstr)

引数の意味

引数	説明
第1引数	対象文字列
第2引数	結果の文字列長
第3引数	パディング文字列

使用例を以下に示す。

 SELECT LPAD('123', 6, '0');
 -- 結果: '000123'
 
 SELECT RPAD('Hello', 10, '.');
 -- 結果: 'Hello.....'
 
 SELECT LPAD('ID', 10, '-');
 -- 結果: '--------ID'
 
 SELECT RPAD('Name', 20, ' ');
 -- 結果: 'Name                ' (空白でパディング)

元の文字列が指定長より長い場合、切り詰められる。

大文字・小文字変換

UPPER 関数 および UCASE 関数は、文字列を大文字に変換する。
LOWER 関数 および LCASE 関数は、文字列を小文字に変換する。

基本構文を以下に示す。

 UPPER(str)
 UCASE(str)
 LOWER(str)
 LCASE(str)

UPPER 関数 と UCASE 関数は同一である。
LOWER 関数 と LCASE 関数は同一である。

使用例を以下に示す。

 SELECT UPPER('Hello World');
 -- 結果: 'HELLO WORLD'
 
 SELECT LOWER('Hello World');
 -- 結果: 'hello world'
 
 SELECT UCASE('mysql database');
 -- 結果: 'MYSQL DATABASE'
 
 SELECT LCASE('MYSQL DATABASE');
 -- 結果: 'mysql database'

バイナリ文字列 (BINARY, VARBINARY, BLOB) に対しては効果がない。

照合順序によっては、大文字小文字変換が期待通りに動作しない場合がある。
utf8mb4_bin 等のバイナリ照合順序では、ASCIIアルファベット以外の変換が行われない場合がある。

文字列検索

LOCATE / POSITION

LOCATE 関数 および POSITION 関数は、部分文字列の位置を検索する。

基本構文を以下に示す。

 LOCATE(substr, str)
 LOCATE(substr, str, pos)
 POSITION(substr IN str)

引数の意味

引数	説明
第1引数	検索する部分文字列
第2引数	対象文字列
第3引数	検索開始位置 (省略時は1)

戻り値は、部分文字列が最初に出現する位置 (1始まり) である。
見つからない場合は0を返す。

使用例を以下に示す。

 SELECT LOCATE('World', 'Hello World');
 -- 結果: 7
 
 SELECT POSITION('MySQL' IN 'Hello MySQL Database');
 -- 結果: 7
 
 SELECT LOCATE('o', 'Hello World');
 -- 結果: 5 (最初の'o'の位置)
 
 SELECT LOCATE('o', 'Hello World', 6);
 -- 結果: 8 (6文字目以降で検索)
 
 SELECT LOCATE('xyz', 'Hello World');
 -- 結果: 0 (見つからない)

POSITION 関数は、SQL標準構文である。

INSTR

INSTR 関数は、LOCATE 関数と同様に部分文字列の位置を検索する。
引数の順序が LOCATE 関数と逆である点が異なる。

基本構文を以下に示す。

 INSTR(str, substr)

使用例を以下に示す。

 SELECT INSTR('Hello World', 'World');
 -- 結果: 7
 
 SELECT INSTR('Hello World', 'o');
 -- 結果: 5
 
 SELECT INSTR('Hello World', 'xyz');
 -- 結果: 0

INSTR 関数は、検索開始位置を指定できない。
検索開始位置を指定する場合は、LOCATE 関数を使用する。

FIND_IN_SET

FIND_IN_SET 関数は、カンマ区切りリスト内での文字列の位置を検索する。

基本構文を以下に示す。

 FIND_IN_SET(str, strlist)

引数の意味

引数	説明
第1引数	検索する文字列
第2引数	カンマ区切りの文字列リスト

戻り値は、リスト内での位置 (1始まり) である。
見つからない場合は0を返す。

使用例を以下に示す。

 SELECT FIND_IN_SET('b', 'a,b,c,d');
 -- 結果: 2
 
 SELECT FIND_IN_SET('MySQL', 'Oracle,MySQL,PostgreSQL');
 -- 結果: 2
 
 SELECT FIND_IN_SET('x', 'a,b,c,d');
 -- 結果: 0
 
 SELECT * FROM users WHERE FIND_IN_SET('admin', roles) > 0;

FIND_IN_SET 関数は、SET型カラムの検索に有用である。

FIELD / ELT

FIELD 関数は、引数リスト内での文字列の位置を返す。
ELT 関数は、指定位置の要素を返す。
これらは、互いに補関数の関係にある。

基本構文を以下に示す。

 FIELD(str, str1, str2, str3, ...)
 ELT(N, str1, str2, str3, ...)

使用例を以下に示す。

 SELECT FIELD('MySQL', 'Oracle', 'MySQL', 'PostgreSQL');
 -- 結果: 2
 
 SELECT ELT(2, 'Oracle', 'MySQL', 'PostgreSQL');
 -- 結果: 'MySQL'
 
 SELECT FIELD('xyz', 'a', 'b', 'c');
 -- 結果: 0 (見つからない)
 
 SELECT ELT(0, 'a', 'b', 'c');
 -- 結果: NULL (位置0は無効)
 
 SELECT ELT(FIELD('b', 'a', 'b', 'c'), 'a', 'b', 'c');
 -- 結果: 'b' (FIELD/ELTの組み合わせ)

FIELD 関数は、カスタムソート順序の実装に使用できる。

文字列長

LENGTH 関数、CHAR_LENGTH 関数、BIT_LENGTH 関数は、文字列の長さを取得する。
これらの関数は、異なる単位で長さを返す。

基本構文を以下に示す。

 LENGTH(str)
 CHAR_LENGTH(str)
 CHARACTER_LENGTH(str)
 BIT_LENGTH(str)
 OCTET_LENGTH(str)

下表に、各関数の戻り値の単位を示す。

文字列長関数の比較

関数	単位	説明
LENGTH	バイト	バイト長 (マルチバイト文字は複数バイト)
CHAR_LENGTH	文字	文字数
CHARACTER_LENGTH	文字	CHAR_LENGTHの別名
BIT_LENGTH	ビット	ビット長 (LENGTH * 8)
OCTET_LENGTH	バイト	LENGTHの別名

マルチバイト文字セットでの動作の違いを以下に示す。

 SELECT LENGTH('Hello');
 -- 結果: 5 (ASCII文字は1バイト/文字)
 
 SELECT CHAR_LENGTH('Hello');
 -- 結果: 5
 
 SELECT LENGTH('こんにちは');
 -- 結果: 15 (UTF-8では日本語は3バイト/文字)
 
 SELECT CHAR_LENGTH('こんにちは');
 -- 結果: 5 (文字数)
 
 SELECT BIT_LENGTH('Hello');
 -- 結果: 40 (5バイト * 8ビット)
 
 SELECT LENGTH('MySQL') = CHAR_LENGTH('MySQL');
 -- 結果: 1 (ASCIIのみの場合は一致)
 
 SELECT LENGTH('データベース') = CHAR_LENGTH('データベース');
 -- 結果: 0 (マルチバイト文字の場合は不一致)

VARCHAR型の文字数制限は、バイト数ではなく文字数で指定される。
例えば、VARCHAR(100) は、100文字まで格納できる。
UTF-8エンコーディングでは、日本語100文字は最大300バイトとなる。

WHERE句での文字列長検査には、CHAR_LENGTH 関数を使用することを推奨する。

 SELECT * FROM articles WHERE CHAR_LENGTH(title) > 50;

その他の文字列関数

MySQLには、その他にも多数の文字列関数が提供されている。

下表に、主要な関数を示す。

その他の文字列関数

関数	説明	使用例
REVERSE	文字列を反転	REVERSE('Hello') → 'olleH'
REPEAT	文字列を繰り返し	REPEAT('X', 5) → 'XXXXX'
SPACE	指定数の空白を生成	SPACE(3) → ' '
FORMAT	数値を千の位区切りでフォーマット	FORMAT(1234567.89, 2) → '1,234,567.89'
QUOTE	SQL文字列エスケープ	QUOTE("It's") → 'It\'s'
SOUNDEX	サウンデックス (音韻表現)	SOUNDEX('Smith') → 'S530'
STRCMP	文字列比較 (0=等しい, -1=小, 1=大)	STRCMP('a', 'b') → -1

REVERSE

REVERSE 関数は、文字列を反転する。

 SELECT REVERSE('Hello World');
 -- 結果: 'dlroW olleH'
 
 SELECT REVERSE('12345');
 -- 結果: '54321'

REPEAT

REPEAT 関数は、文字列を指定回数繰り返す。

 SELECT REPEAT('*', 10);
 -- 結果: '**********'
 
 SELECT REPEAT('MySQL ', 3);
 -- 結果: 'MySQL MySQL MySQL '

SPACE

SPACE 関数は、指定数の空白文字を生成する。

 SELECT CONCAT('Hello', SPACE(5), 'World');
 -- 結果: 'Hello     World'

FORMAT

FORMAT 関数は、数値を千の位区切りでフォーマットする。

 SELECT FORMAT(1234567.89, 2);
 -- 結果: '1,234,567.89'
 
 SELECT FORMAT(1234567.89, 0);
 -- 結果: '1,234,568' (小数点以下四捨五入)
 
 SELECT FORMAT(1234567.89, 2, 'de_DE');
 -- 結果: '1.234.567,89' (ドイツ語ロケール)

QUOTE

QUOTE 関数は、SQL文字列としてエスケープする。

 SELECT QUOTE("It's a test");
 -- 結果: 'It\'s a test'
 
 SELECT QUOTE('He said "Hello"');
 -- 結果: 'He said \"Hello\"'
 
 SELECT QUOTE(NULL);
 -- 結果: NULL

SOUNDEX

SOUNDEX 関数は、英語の音韻表現を生成する。
類似した発音の単語を検索する場合に使用する。

 SELECT SOUNDEX('Smith');
 -- 結果: 'S530'
 
 SELECT SOUNDEX('Smythe');
 -- 結果: 'S530' (Smithと同じ音韻コード)
 
 SELECT * FROM users WHERE SOUNDEX(name) = SOUNDEX('Jon');
 -- 'John', 'Jon', 'Jonn' 等を検索

STRCMP

STRCMP 関数は、2つの文字列を比較する。

 SELECT STRCMP('abc', 'abc');
 -- 結果: 0 (等しい)
 
 SELECT STRCMP('abc', 'def');
 -- 結果: -1 (第1引数が小)
 
 SELECT STRCMP('def', 'abc');
 -- 結果: 1 (第1引数が大)

エンコード関連

MySQLは、文字列とバイナリデータのエンコード変換関数を提供している。

下表に、主要なエンコード関連関数を示す。

エンコード関連関数

関数	説明	使用例
HEX	16進数変換	HEX('MySQL') → '4D7953514C'
UNHEX	16進数→バイナリ文字列	UNHEX('4D7953514C') → 'MySQL'
ASCII	最初の文字のASCIIコード	ASCII('A') → 65
ORD	最初の文字のUnicodeコードポイント	ORD('あ') → 12354
CHAR	整数コード→文字列	CHAR(65, 66, 67) → 'ABC'
BIN	2進数変換	BIN(12) → '1100'
OCT	8進数変換	OCT(12) → '14'
TO_BASE64	Base64エンコード	TO_BASE64('MySQL') → 'TXlTUUw='
FROM_BASE64	Base64デコード	FROM_BASE64('TXlTUUw=') → 'MySQL'

HEX / UNHEX

HEX 関数は、文字列またはバイナリデータを16進数表現に変換する。
UNHEX 関数は、16進数表現をバイナリデータに変換する。

 SELECT HEX('MySQL');
 -- 結果: '4D7953514C'
 
 SELECT UNHEX('4D7953514C');
 -- 結果: 'MySQL'
 
 SELECT HEX(255);
 -- 結果: 'FF'
 
 SELECT HEX('あ');
 -- 結果: 'E38182' (UTF-8エンコーディング)

ASCII / ORD

ASCII 関数は、文字列の最初の文字のASCIIコードを返す。
ORD 関数は、文字列の最初の文字のUNICODEコードポイントを返す。

 SELECT ASCII('A');
 -- 結果: 65
 
 SELECT ASCII('MySQL');
 -- 結果: 77 (最初の文字 'M')
 
 SELECT ORD('あ');
 -- 結果: 12354 (Unicodeコードポイント U+3042)
 
 SELECT ORD('MySQL');
 -- 結果: 77

ORD 関数は、マルチバイト文字に対応している。

CHAR

CHAR 関数は、整数コードから文字列を生成する。

 SELECT CHAR(65, 66, 67);
 -- 結果: 'ABC'
 
 SELECT CHAR(77, 121, 83, 81, 76);
 -- 結果: 'MySQL'
 
 SELECT CHAR(12354 USING utf8mb4);
 -- 結果: 'あ' (Unicodeコードポイント U+3042)

USING 句で文字セットを指定できる。

BIN / OCT

BIN 関数は、整数を2進数表現に変換する。
OCT 関数は、整数を8進数表現に変換する。

 SELECT BIN(12);
 -- 結果: '1100'
 
 SELECT BIN(255);
 -- 結果: '11111111'
 
 SELECT OCT(12);
 -- 結果: '14'
 
 SELECT OCT(255);
 -- 結果: '377'

TO_BASE64 / FROM_BASE64

TO_BASE64 関数は、文字列をBase64エンコードする。
FROM_BASE64 関数は、Base64文字列をデコードする。

これらの関数は、MySQL 5.6以降で使用可能である。

 SELECT TO_BASE64('MySQL');
 -- 結果: 'TXlTUUw='
 
 SELECT FROM_BASE64('TXlTUUw=');
 -- 結果: 'MySQL'
 
 SELECT TO_BASE64('こんにちは');
 -- 結果: '44GT44KT44Gr44Gh44Gv'
 
 SELECT FROM_BASE64('44GT44KT44Gr44Gh44Gv');
 -- 結果: 'こんにちは'

Base64エンコーディングは、バイナリデータをテキスト形式で格納する場合に有用である。

照合順序との関係

文字列関数の動作は、照合順序 (Collation) に依存する場合がある。
照合順序は、文字列比較、ソート、大文字小文字の区別を制御する。

MySQL 8.0のデフォルト照合順序は、utf8mb4_0900_ai_ci である。

照合順序の種類を以下に示す。

主要な照合順序

照合順序	特性	説明
utf8mb4_general_ci	大小文字区別なし、高速	MySQL 5.7以前のデフォルト
utf8mb4_unicode_ci	大小文字区別なし、Unicode準拠	多言語対応
utf8mb4_0900_ai_ci	アクセント非依存、大小文字区別なし	MySQL 8.0デフォルト
utf8mb4_bin	バイナリ比較	大文字小文字、アクセントを区別

COLLATE句での照合順序指定

COLLATE 句により、クエリレベルで照合順序を指定できる。

 SELECT 'abc' = 'ABC';
 -- 結果: 1 (デフォルトでは大文字小文字区別なし)
 
 SELECT 'abc' = 'ABC' COLLATE utf8mb4_bin;
 -- 結果: 0 (バイナリ比較では区別)
 
 SELECT 'Müller' = 'Mueller' COLLATE utf8mb4_0900_ai_ci;
 -- 結果: 1 (アクセント非依存)
 
 SELECT 'Müller' = 'Mueller' COLLATE utf8mb4_bin;
 -- 結果: 0 (バイナリ比較では区別)

BINARY演算子

BINARY 演算子により、バイナリ比較を強制できる。

 SELECT 'abc' = 'ABC';
 -- 結果: 1
 
 SELECT BINARY 'abc' = 'ABC';
 -- 結果: 0
 
 SELECT 'abc' = BINARY 'ABC';
 -- 結果: 0
 
 SELECT LOCATE('WORLD', 'Hello World');
 -- 結果: 7 (大文字小文字区別なし)
 
 SELECT LOCATE(BINARY 'WORLD', 'Hello World');
 -- 結果: 0 (バイナリ比較で不一致)

文字セットと照合順序の推奨

MySQLでは、utf8mb4 文字セットの使用を強く推奨する。
utf8 文字セットは、最大3バイト/文字であり、絵文字等の4バイト文字を格納できない。
utf8mb4 文字セットは、最大4バイト/文字であり、全てのUNICODE文字を格納できる。

• テーブル作成時の推奨設定

   CREATE TABLE users (
      id INT PRIMARY KEY,
      name VARCHAR(100)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  

  
  

• データベース全体のデフォルト文字セットを設定する場合

   CREATE DATABASE mydb
      DEFAULT CHARACTER SET utf8mb4
      DEFAULT COLLATE utf8mb4_unicode_ci;
  

文字列関数を使用する場合の注意

文字列関数とインデックス

WHERE句で文字列関数を使用すると、インデックスが使用されない場合が多い。
これにより、クエリのパフォーマンスが大幅に低下する可能性がある。

インデックスが使用されない例

 -- インデックスが使用されない
 SELECT * FROM users WHERE UPPER(name) = 'JOHN';
 
 -- インデックスが使用されない
 SELECT * FROM articles WHERE SUBSTRING(title, 1, 5) = 'MySQL';

インデックスを使用する改善例

 -- インデックスが使用される
 SELECT * FROM users WHERE name = 'John';
 
 -- インデックスが使用される (前方一致)
 SELECT * FROM articles WHERE title LIKE 'MySQL%';

関数ベースインデックス (Generated Column) を使用する例

 -- Generated Columnを作成
 ALTER TABLE users ADD COLUMN name_upper VARCHAR(100) AS (UPPER(name)) STORED;
 
 -- インデックスを作成
 CREATE INDEX idx_name_upper ON users(name_upper);
 
 -- インデックスが使用される
 SELECT * FROM users WHERE name_upper = 'JOHN';

MySQL 8.0以降では、関数インデックスが直接サポートされている。

 -- MySQL 8.0以降
 CREATE INDEX idx_upper_name ON users((UPPER(name)));
 
 -- インデックスが使用される
 SELECT * FROM users WHERE UPPER(name) = 'JOHN';

大きなテキストデータ

TEXT型およびBLOB型での文字列関数使用は、パフォーマンスが低下する傾向がある。

理由を以下に示す。

• TEXT / BLOB型は、オフページ格納される場合がある。

  データが別の場所に格納され、アクセスが遅くなる。

• インデックスの制限

  TEXT/BLOB型の全体にインデックスを作成できない。

• メモリ使用量

  一時テーブルがディスクに作成される場合がある。

大きなテキストデータの処理には、以下に示す対策を推奨する。

大きなテキストデータの処理に対する推奨対策

対策	説明
VARCHAR型を優先	VARCHAR型は、最大65,535バイトまで格納可能 VARCHAR(5000) 等で十分な場合が多い。
全文検索インデックス (FULLTEXT) を使用	テキスト検索にはFULLTEXTインデックスが効率的である。
アプリケーションレイヤでの処理	複雑な文字列処理は、アプリケーション側で実施する。

マルチバイト文字の考慮

マルチバイト文字セット (utf8mb4) では、以下に示すに注意する。

文字列処理に関する注意事項

項目	説明
LENGTH 関数 と CHAR_LENGTH 関数の違い	LENGTH 関数はバイト長、CHAR_LENGTH 関数は文字数 日本語等では、LENGTH関数の戻り値は文字数の3倍
VARCHAR型の長さ制限	VARCHAR(100) は、100文字 (最大400バイト) バイト長制限ではなく文字数制限
インデックスキーのサイズ制限	InnoDBのインデックスキー最大長は767バイト (デフォルト) VARCHAR(255) のutf8mb4カラムは、最大1020バイトとなり、インデックス作成に失敗する可能性 innodb_large_prefix=ON (MySQL 5.7以降デフォルト) で3072バイトまで拡張可能

インデックス作成時の注意例を以下に示す。

 -- VARCHAR(255) utf8mb4カラムのインデックス作成
 CREATE INDEX idx_title ON articles(title);
 -- ERROR: Specified key was too long; max key length is 767 bytes
 
 -- 解決策1: プレフィックスインデックス
 CREATE INDEX idx_title ON articles(title(191));
 -- 191文字 * 4バイト = 764バイト (767バイト以下)
 
 -- 解決策2: innodb_large_prefix有効化 (MySQL 5.7以降デフォルト)
 SET GLOBAL innodb_large_prefix = 1;
 SET GLOBAL innodb_file_format = 'Barracuda';
 
 -- テーブルにROW_FORMAT=DYNAMICを指定
 CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(255)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
 
 CREATE INDEX idx_title ON articles(title);
 -- 成功 (最大3072バイトまで許容)

NULL処理

多くの文字列関数は、NULL を特別に扱う。

NULL 処理の例を以下に示す。

 SELECT CONCAT('Hello', NULL, 'World');
 -- 結果: NULL (いずれかの引数がNULLなら結果はNULL)
 
 SELECT CONCAT_WS(',', 'A', NULL, 'B');
 -- 結果: 'A,B' (NULLはスキップ)
 
 SELECT LENGTH(NULL);
 -- 結果: NULL
 
 SELECT UPPER(NULL);
 -- 結果: NULL
 
 SELECT COALESCE(name, '(未設定)') FROM users;
 -- NULLを'(未設定)'で置換

NULL を扱う場合は、COALESCE 関数 や IFNULL 関数を併用することを推奨する。