MySQL - 集約関数のソースを表示

== 概要 ==
MySQLでは、豊富な文字列関数が提供されており、文字列の結合、分割、検索、置換、フォーマット等の処理を行うことができる。<br>
これらの関数は、SELECT文、UPDATE文、WHERE句等で使用でき、データ操作において重要な役割を果たす。<br>
<br>
MySQL 5.7とMySQL 8.0では、文字列関数の動作に一部差異がある。<br>
特に、文字コードと照合順序の扱いに変更がある。<br>
MySQL 8.0では、デフォルト文字セットが <code>utf8mb4</code>、デフォルト照合順序が <code>utf8mb4_0900_ai_ci</code> に変更された。<br>
MySQL 5.7では、デフォルト文字セットは <code>latin1</code>、デフォルト照合順序は <code>latin1_swedish_ci</code> であった。<br>
<br>
文字列関数は、マルチバイト文字セット (utf8mb4) を考慮して使用する必要がある。<br>
<code>LENGTH</code> 関数 と <code>CHAR_LENGTH</code> 関数は、マルチバイト文字での動作が異なる。<br>
<code>LENGTH</code> 関数はバイト長を返し、<code>CHAR_LENGTH</code> 関数は文字数を返す。<br>
<br>
UTF-8エンコーディングでは、日本語は1文字あたり3バイトで表現されるため、<code>LENGTH</code>関数の戻り値は文字数の3倍となる。<br>
<br>
文字列関数をWHERE句で使用すると、インデックスが使用されない場合が多い。<br>
パフォーマンスを考慮する場合は、関数を使用しないクエリ設計を検討する必要がある。<br>
<br>
<u>TEXT型およびBLOB型での文字列関数使用は、処理速度が低下する傾向がある。</u><br>
<u>大量のテキストデータを処理する場合は、VARCHAR型の使用を推奨する。</u><br>
<br><br>

== 文字列結合 ==
==== CONCAT ====
<code>CONCAT</code> 関数は、複数の文字列を結合する。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 CONCAT(str1, str2, ...)
 </syntaxhighlight>
<br>
引数のいずれかが <code>NULL</code> の場合、結果は <code>NULL</code> となる。<br>
これは、<code>CONCAT_WS</code> 関数との重要な違いである。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT CONCAT('Hello', ' ', 'World');
 -- 結果: 'Hello World'
 
 SELECT CONCAT('ID: ', id, ' Name: ', name) FROM users;
 
 SELECT CONCAT('MySQL', NULL, 'Database');
 -- 結果: NULL
 </syntaxhighlight>
<br>
<code>CONCAT</code> 関数は、数値型の引数を文字列に自動変換する。<br>
<br>
==== CONCAT_WS ====
<code>CONCAT_WS</code> 関数は、セパレータを使用して複数の文字列を結合する。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 CONCAT_WS(separator, str1, str2, ...)
 </syntaxhighlight>
<br>
<code>CONCAT</code> 関数と異なり、<code>CONCAT_WS</code> 関数は、<code>NULL</code> の引数をスキップする。<br>
セパレータが <code>NULL</code> の場合のみ、結果は <code>NULL</code> となる。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 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のため)
 </syntaxhighlight>
<br>
<code>CONCAT_WS</code> 関数は、CSV形式のデータ生成に便利である。<br>
<br><br>

== 部分文字列の取得 ==
==== SUBSTRING / SUBSTR / MID ====
<code>SUBSTRING</code> 関数、<code>SUBSTR</code> 関数、<code>MID</code> 関数は、文字列から部分文字列を取得する。<br>
これらの関数は、同一の動作をする別名である。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 SUBSTRING(str, pos)
 SUBSTRING(str, pos, len)
 SUBSTR(str, pos, len)
 MID(str, pos, len)
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ 引数の意味
|-
! 引数 !! 説明
|-
| 第1引数 || 対象文字列
|-
| 第2引数 || 開始位置 (1始まり、負の値は末尾から)
|-
| 第3引数 || 取得する文字数 (省略時は末尾まで)
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 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文字)
 </syntaxhighlight>
<br>
負の位置指定により、末尾からの取得が可能である。<br>
<br>
==== LEFT / RIGHT ====
<code>LEFT</code> 関数は、文字列の左端から指定文字数を取得する。<br>
<code>RIGHT</code> 関数は、文字列の右端から指定文字数を取得する。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 LEFT(str, len)
 RIGHT(str, len)
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT LEFT('HelloWorld', 5);
 -- 結果: 'Hello'
 
 SELECT RIGHT('HelloWorld', 5);
 -- 結果: 'World'
 
 SELECT LEFT('2025-02-15', 4);
 -- 結果: '2025' (年を取得)
 
 SELECT RIGHT('2025-02-15', 2);
 -- 結果: '15' (日を取得)
 </syntaxhighlight>
<br>
これらの関数は、<code>SUBSTRING</code> 関数の簡易版である。<br>
<br>
==== SUBSTRING_INDEX ====
<code>SUBSTRING_INDEX</code> 関数は、デリミタを基準に文字列を分割し、指定された部分を取得する。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 SUBSTRING_INDEX(str, delim, count)
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ 引数の意味
|-
! 引数 !! 説明
|-
| 第1引数 || 対象文字列
|-
| 第2引数 || デリミタ文字列
|-
| 第3引数 || デリミタの出現回数 (正数は左から、負数は右から)
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 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番目のオクテットを取得)
 </syntaxhighlight>
<br>
ネストして使用することで、複雑な分割が可能である。<br>
<br><br>

== 文字列置換・挿入 ==
==== REPLACE ====
<code>REPLACE</code> 関数は、文字列内のすべての出現を置換する。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 REPLACE(str, from_str, to_str)
 </syntaxhighlight>
<br>
<code>REPLACE</code> 関数は、大文字小文字を区別する。<br>
照合順序が大文字小文字を区別しない場合でも、<code>REPLACE</code> 関数は区別する。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 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');
 </syntaxhighlight>
<br>
全ての出現が置換される点に注意する必要がある。<br>
<br>
==== INSERT ====
<code>INSERT</code> 関数は、指定位置の文字列を新しい文字列で置換する。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 INSERT(str, pos, len, newstr)
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ 引数の意味
|-
! 引数 !! 説明
|-
| 第1引数 || 対象文字列
|-
| 第2引数 || 開始位置 (1始まり)
|-
| 第3引数 || 置換する文字数
|-
| 第4引数 || 挿入する文字列
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT INSERT('HelloWorld', 6, 5, 'MySQL');
 -- 結果: 'HelloMySQL'
 
 SELECT INSERT('12345', 3, 0, 'XX');
 -- 結果: '12XX345' (文字を挿入、置換なし)
 
 SELECT INSERT('abcdefg', 2, 3, 'XX');
 -- 結果: 'aXXefg' (2文字目から3文字を'XX'で置換)
 </syntaxhighlight>
<br>
第3引数に0を指定した場合は、文字列の挿入のみが行われる。<br>
<br><br>

== 空白除去・パディング ==
==== TRIM / LTRIM / RTRIM ====
<code>TRIM</code> 関数は、文字列の両端から指定文字を除去する。<br>
<code>LTRIM</code> 関数は、文字列の左端から空白を除去する。<br>
<code>RTRIM</code> 関数は、文字列の右端から空白を除去する。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
 LTRIM(str)
 RTRIM(str)
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ <code>TRIM</code>関数のオプション
|-
! オプション !! 説明
|-
| <code>BOTH</code> || 両端から除去 (デフォルト)
|-
| <code>LEADING</code> || 左端から除去
|-
| <code>TRAILING</code> || 右端から除去
|-
| <code>remstr</code> || 除去する文字列 (デフォルトは空白)
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 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'
 </syntaxhighlight>
<br>
<code>TRIM</code> 関数は、ユーザ入力データのクリーニングに有用である。<br>
<br>
==== LPAD / RPAD ====
<code>LPAD</code> 関数は、文字列の左側を指定文字でパディングする。<br>
<code>RPAD</code> 関数は、文字列の右側を指定文字でパディングする。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 LPAD(str, len, padstr)
 RPAD(str, len, padstr)
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ 引数の意味
|-
! 引数 !! 説明
|-
| 第1引数 || 対象文字列
|-
| 第2引数 || 結果の文字列長
|-
| 第3引数 || パディング文字列
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT LPAD('123', 6, '0');
 -- 結果: '000123'
 
 SELECT RPAD('Hello', 10, '.');
 -- 結果: 'Hello.....'
 
 SELECT LPAD('ID', 10, '-');
 -- 結果: '--------ID'
 
 SELECT RPAD('Name', 20, ' ');
 -- 結果: 'Name                ' (空白でパディング)
 </syntaxhighlight>
<br>
元の文字列が指定長より長い場合、切り詰められる。<br>
<br><br>

== 大文字・小文字変換 ==
<code>UPPER</code> 関数 および <code>UCASE</code> 関数は、文字列を大文字に変換する。<br>
<code>LOWER</code> 関数 および <code>LCASE</code> 関数は、文字列を小文字に変換する。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 UPPER(str)
 UCASE(str)
 LOWER(str)
 LCASE(str)
 </syntaxhighlight>
<br>
<code>UPPER</code> 関数 と <code>UCASE</code> 関数は同一である。<br>
<code>LOWER</code> 関数 と <code>LCASE</code> 関数は同一である。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT UPPER('Hello World');
 -- 結果: 'HELLO WORLD'
 
 SELECT LOWER('Hello World');
 -- 結果: 'hello world'
 
 SELECT UCASE('mysql database');
 -- 結果: 'MYSQL DATABASE'
 
 SELECT LCASE('MYSQL DATABASE');
 -- 結果: 'mysql database'
 </syntaxhighlight>
<br>
バイナリ文字列 (BINARY, VARBINARY, BLOB) に対しては効果がない。<br>
<br>
照合順序によっては、大文字小文字変換が期待通りに動作しない場合がある。<br>
<code>utf8mb4_bin</code> 等のバイナリ照合順序では、ASCIIアルファベット以外の変換が行われない場合がある。<br>
<br><br>

== 文字列検索 ==
==== LOCATE / POSITION ====
<code>LOCATE</code> 関数 および <code>POSITION</code> 関数は、部分文字列の位置を検索する。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 LOCATE(substr, str)
 LOCATE(substr, str, pos)
 POSITION(substr IN str)
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ 引数の意味
|-
! 引数 !! 説明
|-
| 第1引数 || 検索する部分文字列
|-
| 第2引数 || 対象文字列
|-
| 第3引数 || 検索開始位置 (省略時は1)
|}
</center>
<br>
戻り値は、部分文字列が最初に出現する位置 (1始まり) である。<br>
見つからない場合は0を返す。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 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 (見つからない)
 </syntaxhighlight>
<br>
<code>POSITION</code> 関数は、SQL標準構文である。<br>
<br>
==== INSTR ====
<code>INSTR</code> 関数は、<code>LOCATE</code> 関数と同様に部分文字列の位置を検索する。<br>
引数の順序が <code>LOCATE</code> 関数と逆である点が異なる。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 INSTR(str, substr)
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT INSTR('Hello World', 'World');
 -- 結果: 7
 
 SELECT INSTR('Hello World', 'o');
 -- 結果: 5
 
 SELECT INSTR('Hello World', 'xyz');
 -- 結果: 0
 </syntaxhighlight>
<br>
<code>INSTR</code> 関数は、検索開始位置を指定できない。<br>
検索開始位置を指定する場合は、<code>LOCATE</code> 関数を使用する。<br>
<br>
==== FIND_IN_SET ====
<code>FIND_IN_SET</code> 関数は、カンマ区切りリスト内での文字列の位置を検索する。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 FIND_IN_SET(str, strlist)
 </syntaxhighlight>
<br>
<center>
{| class="wikitable"
|+ 引数の意味
|-
! 引数 !! 説明
|-
| 第1引数 || 検索する文字列
|-
| 第2引数 || カンマ区切りの文字列リスト
|}
</center>
<br>
戻り値は、リスト内での位置 (1始まり) である。<br>
見つからない場合は0を返す。<br>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 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;
 </syntaxhighlight>
<br>
<code>FIND_IN_SET</code> 関数は、SET型カラムの検索に有用である。<br>
<br>
==== FIELD / ELT ====
<code>FIELD</code> 関数は、引数リスト内での文字列の位置を返す。<br>
<code>ELT</code> 関数は、指定位置の要素を返す。<br>
これらは、互いに補関数の関係にある。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 FIELD(str, str1, str2, str3, ...)
 ELT(N, str1, str2, str3, ...)
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 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の組み合わせ)
 </syntaxhighlight>
<br>
<code>FIELD</code> 関数は、カスタムソート順序の実装に使用できる。<br>
<br><br>

== 文字列長 ==
<code>LENGTH</code> 関数、<code>CHAR_LENGTH</code> 関数、<code>BIT_LENGTH</code> 関数は、文字列の長さを取得する。<br>
これらの関数は、異なる単位で長さを返す。<br>
<br>
基本構文を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 LENGTH(str)
 CHAR_LENGTH(str)
 CHARACTER_LENGTH(str)
 BIT_LENGTH(str)
 OCTET_LENGTH(str)
 </syntaxhighlight>
<br>
下表に、各関数の戻り値の単位を示す。<br>
<br>
<center>
{| class="wikitable"
|+ 文字列長関数の比較
! 関数 !! 単位 !! 説明
|-
| LENGTH || バイト || バイト長 (マルチバイト文字は複数バイト)
|-
| CHAR_LENGTH || 文字 || 文字数
|-
| CHARACTER_LENGTH || 文字 || CHAR_LENGTHの別名
|-
| BIT_LENGTH || ビット || ビット長 (LENGTH * 8)
|-
| OCTET_LENGTH || バイト || LENGTHの別名
|}
</center>
<br>
マルチバイト文字セットでの動作の違いを以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 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 (マルチバイト文字の場合は不一致)
 </syntaxhighlight>
<br>
<u>VARCHAR型の文字数制限は、バイト数ではなく文字数で指定される。</u><br>
例えば、VARCHAR(100) は、100文字まで格納できる。<br>
UTF-8エンコーディングでは、日本語100文字は最大300バイトとなる。<br>
<br>
WHERE句での文字列長検査には、<code>CHAR_LENGTH</code> 関数を使用することを推奨する。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT * FROM articles WHERE CHAR_LENGTH(title) > 50;
 </syntaxhighlight>
<br><br>

== その他の文字列関数 ==
MySQLには、その他にも多数の文字列関数が提供されている。<br>
<br>
下表に、主要な関数を示す。<br>
<br>
<center>
{| class="wikitable"
|+ その他の文字列関数
! 関数 !! 説明 !! 使用例
|-
| 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
|}
</center>
<br>
===== REVERSE =====
<code>REVERSE</code> 関数は、文字列を反転する。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT REVERSE('Hello World');
 -- 結果: 'dlroW olleH'
 
 SELECT REVERSE('12345');
 -- 結果: '54321'
 </syntaxhighlight>
<br>
===== REPEAT =====
<code>REPEAT</code> 関数は、文字列を指定回数繰り返す。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT REPEAT('*', 10);
 -- 結果: '**********'
 
 SELECT REPEAT('MySQL ', 3);
 -- 結果: 'MySQL MySQL MySQL '
 </syntaxhighlight>
<br>
===== SPACE =====
<code>SPACE</code> 関数は、指定数の空白文字を生成する。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT CONCAT('Hello', SPACE(5), 'World');
 -- 結果: 'Hello     World'
 </syntaxhighlight>
<br>
===== FORMAT =====
<code>FORMAT</code> 関数は、数値を千の位区切りでフォーマットする。<br>
<br>
 <syntaxhighlight lang="sql">
 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' (ドイツ語ロケール)
 </syntaxhighlight>
<br>
===== QUOTE =====
<code>QUOTE</code> 関数は、SQL文字列としてエスケープする。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT QUOTE("It's a test");
 -- 結果: 'It\'s a test'
 
 SELECT QUOTE('He said "Hello"');
 -- 結果: 'He said \"Hello\"'
 
 SELECT QUOTE(NULL);
 -- 結果: NULL
 </syntaxhighlight>
<br>
===== SOUNDEX =====
<code>SOUNDEX</code> 関数は、英語の音韻表現を生成する。<br>
類似した発音の単語を検索する場合に使用する。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT SOUNDEX('Smith');
 -- 結果: 'S530'
 
 SELECT SOUNDEX('Smythe');
 -- 結果: 'S530' (Smithと同じ音韻コード)
 
 SELECT * FROM users WHERE SOUNDEX(name) = SOUNDEX('Jon');
 -- 'John', 'Jon', 'Jonn' 等を検索
 </syntaxhighlight>
<br>
===== STRCMP =====
<code>STRCMP</code> 関数は、2つの文字列を比較する。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT STRCMP('abc', 'abc');
 -- 結果: 0 (等しい)
 
 SELECT STRCMP('abc', 'def');
 -- 結果: -1 (第1引数が小)
 
 SELECT STRCMP('def', 'abc');
 -- 結果: 1 (第1引数が大)
 </syntaxhighlight>
<br><br>

== エンコード関連 ==
MySQLは、文字列とバイナリデータのエンコード変換関数を提供している。<br>
<br>
下表に、主要なエンコード関連関数を示す。<br>
<br>
<center>
{| class="wikitable"
|+ エンコード関連関数
! 関数 !! 説明 !! 使用例
|-
| 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'
|}
</center>
<br>
===== HEX / UNHEX =====
<code>HEX</code> 関数は、文字列またはバイナリデータを16進数表現に変換する。<br>
<code>UNHEX</code> 関数は、16進数表現をバイナリデータに変換する。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT HEX('MySQL');
 -- 結果: '4D7953514C'
 
 SELECT UNHEX('4D7953514C');
 -- 結果: 'MySQL'
 
 SELECT HEX(255);
 -- 結果: 'FF'
 
 SELECT HEX('あ');
 -- 結果: 'E38182' (UTF-8エンコーディング)
 </syntaxhighlight>
<br>
===== ASCII / ORD =====
<code>ASCII</code> 関数は、文字列の最初の文字のASCIIコードを返す。<br>
<code>ORD</code> 関数は、文字列の最初の文字のUNICODEコードポイントを返す。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT ASCII('A');
 -- 結果: 65
 
 SELECT ASCII('MySQL');
 -- 結果: 77 (最初の文字 'M')
 
 SELECT ORD('あ');
 -- 結果: 12354 (Unicodeコードポイント U+3042)
 
 SELECT ORD('MySQL');
 -- 結果: 77
 </syntaxhighlight>
<br>
<code>ORD</code> 関数は、マルチバイト文字に対応している。<br>
<br>
===== CHAR =====
<code>CHAR</code> 関数は、整数コードから文字列を生成する。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT CHAR(65, 66, 67);
 -- 結果: 'ABC'
 
 SELECT CHAR(77, 121, 83, 81, 76);
 -- 結果: 'MySQL'
 
 SELECT CHAR(12354 USING utf8mb4);
 -- 結果: 'あ' (Unicodeコードポイント U+3042)
 </syntaxhighlight>
<br>
<code>USING</code> 句で文字セットを指定できる。<br>
<br>
===== BIN / OCT =====
<code>BIN</code> 関数は、整数を2進数表現に変換する。<br>
<code>OCT</code> 関数は、整数を8進数表現に変換する。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT BIN(12);
 -- 結果: '1100'
 
 SELECT BIN(255);
 -- 結果: '11111111'
 
 SELECT OCT(12);
 -- 結果: '14'
 
 SELECT OCT(255);
 -- 結果: '377'
 </syntaxhighlight>
<br>
===== TO_BASE64 / FROM_BASE64 =====
<code>TO_BASE64</code> 関数は、文字列をBase64エンコードする。<br>
<code>FROM_BASE64</code> 関数は、Base64文字列をデコードする。<br>
<br>
これらの関数は、MySQL 5.6以降で使用可能である。<br>
<br>
 <syntaxhighlight lang="sql">
 SELECT TO_BASE64('MySQL');
 -- 結果: 'TXlTUUw='
 
 SELECT FROM_BASE64('TXlTUUw=');
 -- 結果: 'MySQL'
 
 SELECT TO_BASE64('こんにちは');
 -- 結果: '44GT44KT44Gr44Gh44Gv'
 
 SELECT FROM_BASE64('44GT44KT44Gr44Gh44Gv');
 -- 結果: 'こんにちは'
 </syntaxhighlight>
<br>
Base64エンコーディングは、バイナリデータをテキスト形式で格納する場合に有用である。<br>
<br><br>

== 照合順序との関係 ==
文字列関数の動作は、照合順序 (Collation) に依存する場合がある。<br>
照合順序は、文字列比較、ソート、大文字小文字の区別を制御する。<br>
<br>
MySQL 8.0のデフォルト照合順序は、<u>utf8mb4_0900_ai_ci</u> である。<br>
<br>
照合順序の種類を以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ 主要な照合順序
! 照合順序 !! 特性 !! 説明
|-
| utf8mb4_general_ci || 大小文字区別なし、高速 || MySQL 5.7以前のデフォルト
|-
| utf8mb4_unicode_ci || 大小文字区別なし、Unicode準拠 || 多言語対応
|-
| utf8mb4_0900_ai_ci || アクセント非依存、大小文字区別なし || MySQL 8.0デフォルト
|-
| utf8mb4_bin || バイナリ比較 || 大文字小文字、アクセントを区別
|}
</center>
<br>
===== COLLATE句での照合順序指定 =====
<code>COLLATE</code> 句により、クエリレベルで照合順序を指定できる。<br>
<br>
 <syntaxhighlight lang="sql">
 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 (バイナリ比較では区別)
 </syntaxhighlight>
<br>
===== BINARY演算子 =====
<code>BINARY</code> 演算子により、バイナリ比較を強制できる。<br>
<br>
 <syntaxhighlight lang="sql">
 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 (バイナリ比較で不一致)
 </syntaxhighlight>
<br>
===== 文字セットと照合順序の推奨 =====
MySQLでは、<u>utf8mb4</u> 文字セットの使用を強く推奨する。<br>
<u>utf8</u> 文字セットは、最大3バイト/文字であり、絵文字等の4バイト文字を格納できない。<br>
<u>utf8mb4</u> 文字セットは、最大4バイト/文字であり、全てのUNICODE文字を格納できる。<br>
<br>
* テーブル作成時の推奨設定
*: <syntaxhighlight lang="sql">
 CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 </syntaxhighlight>
*: <br>
* データベース全体のデフォルト文字セットを設定する場合
*: <syntaxhighlight lang="sql">
 CREATE DATABASE mydb
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;
 </syntaxhighlight>
<br><br>

== 文字列関数を使用する場合の注意 ==
==== 文字列関数とインデックス ====
WHERE句で文字列関数を使用すると、インデックスが使用されない場合が多い。<br>
これにより、クエリのパフォーマンスが大幅に低下する可能性がある。<br>
<br>
===== インデックスが使用されない例 =====
 <syntaxhighlight lang="sql">
 -- インデックスが使用されない
 SELECT * FROM users WHERE UPPER(name) = 'JOHN';
 
 -- インデックスが使用されない
 SELECT * FROM articles WHERE SUBSTRING(title, 1, 5) = 'MySQL';
 </syntaxhighlight>
<br>
===== インデックスを使用する改善例 =====
 <syntaxhighlight lang="sql">
 -- インデックスが使用される
 SELECT * FROM users WHERE name = 'John';
 
 -- インデックスが使用される (前方一致)
 SELECT * FROM articles WHERE title LIKE 'MySQL%';
 </syntaxhighlight>
<br>
===== 関数ベースインデックス (Generated Column) を使用する例 =====
 <syntaxhighlight lang="sql">
 -- 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';
 </syntaxhighlight>
<br>
MySQL 8.0以降では、関数インデックスが直接サポートされている。<br>
 <syntaxhighlight lang="sql">
 -- MySQL 8.0以降
 CREATE INDEX idx_upper_name ON users((UPPER(name)));
 
 -- インデックスが使用される
 SELECT * FROM users WHERE UPPER(name) = 'JOHN';
 </syntaxhighlight>
<br>
==== 大きなテキストデータ ====
TEXT型およびBLOB型での文字列関数使用は、パフォーマンスが低下する傾向がある。<br>
<br>
理由を以下に示す。<br>
* TEXT / BLOB型は、オフページ格納される場合がある。
*: データが別の場所に格納され、アクセスが遅くなる。
* インデックスの制限
*: TEXT/BLOB型の全体にインデックスを作成できない。
* メモリ使用量
*: 一時テーブルがディスクに作成される場合がある。
<br>
大きなテキストデータの処理には、以下に示す対策を推奨する。<br>
<br>
<center>
{| class="wikitable"
|+ 大きなテキストデータの処理に対する推奨対策
|-
! 対策 !! 説明
|-
| VARCHAR型を優先 || VARCHAR型は、最大65,535バイトまで格納可能<br>VARCHAR(5000) 等で十分な場合が多い。
|-
| 全文検索インデックス (FULLTEXT) を使用 || テキスト検索にはFULLTEXTインデックスが効率的である。
|-
| アプリケーションレイヤでの処理 || 複雑な文字列処理は、アプリケーション側で実施する。
|}
</center>
<br>
===== マルチバイト文字の考慮 =====
マルチバイト文字セット (utf8mb4) では、以下に示すに注意する。<br>
<br>
<center>
{| class="wikitable"
|+ 文字列処理に関する注意事項
|-
! 項目 !! 説明
|-
| <code>LENGTH</code> 関数 と <code>CHAR_LENGTH</code> 関数の違い || <code>LENGTH</code> 関数はバイト長、<code>CHAR_LENGTH</code> 関数は文字数<br>日本語等では、<code>LENGTH</code>関数の戻り値は文字数の3倍
|-
| VARCHAR型の長さ制限 || VARCHAR(100) は、100文字 (最大400バイト)<br>バイト長制限ではなく文字数制限
|-
| インデックスキーのサイズ制限 || InnoDBのインデックスキー最大長は767バイト (デフォルト)<br>VARCHAR(255) のutf8mb4カラムは、最大1020バイトとなり、インデックス作成に失敗する可能性<br><code>innodb_large_prefix=ON</code> (MySQL 5.7以降デフォルト) で3072バイトまで拡張可能
|}
</center>
<br>
インデックス作成時の注意例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 -- 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バイトまで許容)
 </syntaxhighlight>
<br>
===== NULL処理 =====
多くの文字列関数は、<code>NULL</code> を特別に扱う。<br>
<br>
<code>NULL</code> 処理の例を以下に示す。<br>
<br>
 <syntaxhighlight lang="sql">
 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を'(未設定)'で置換
 </syntaxhighlight>
<br>
<u><code>NULL</code> を扱う場合は、<code>COALESCE</code> 関数 や <code>IFNULL</code> 関数を併用することを推奨する。</u><br>
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,MySQL,Database,SQL,String Functions,CONCAT,SUBSTRING,REPLACE,TRIM,UPPER,LOWER,LENGTH,CHAR_LENGTH,文字列関数,データベース,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - MySQLの文字列関数に関する包括的なリファレンス | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]