MySQL ELT関数とFIELD関数

MySQLでバルクアップデートを実行する際に使用するELT関数とFIELD関数について解説する
 
 
・全レコードをバルクアップデート：「ELT関数」を使用
・一部のレコードのみをバルクアップデート：「ELT関数」と「FIELD関数」を組み合わせる
 

■ELT関数

ELT(n, str1, str2, str3, …)
ELT関数は文字列リストのn番目の要素を返す
要素が無い場合はNullを返す
 

select elt(1, '両津', '中川', '麗子'); -- 「両津」を返す
select elt(3, '両津', '中川', '麗子'); -- 「麗子」を返す
select elt(5, '両津', '中川', '麗子'); -- 「Null」を返す

 
またELT関数はレコード数の分、値を返す
 

--3レコードのtestテーブルを作成
create table test (id int, name varchar(20));
insert into test values (1, 'hoge'), (2, 'fuga'), (3, 'mumu');

select elt(1, '両津', '中川', '麗子') from test; -- 1番目の文字列リスト「両津」を3レコード返す

 
この特性を利用すると下記のようなUPDATE文を実行できる

select * from test;
---------------
| id   | name |
---------------
|    1 | hoge |
|    2 | fuga |
|    3 | mumu |
---------------

--ELT関数で全レコードをバルクアップデート
update test set name =  elt(id, '両津', '中川', '麗子');

select * from test;
---------------
| id   | name |
---------------
|    1 | 両津 |
|    2 | 中川 |
|    3 | 麗子 |
---------------

 
 

---

■FIELD関数

FIELD(str,str1,str2,str3,…)
FIELD関数はstr1、str2、str3、… リスト内で str のインデックス (位置) を返す
要素が無い場合は0を返す
 

select field('両津', '両津', '中川', '麗子'); -- 「1」を返す
select field('麗子', '両津', '中川', '麗子'); -- 「3」を返す
select field('大原', '両津', '中川', '麗子'); -- 「0」を返す

 
またELT関数同様、FIELD関数もレコード数の分、値を返す
 

select field('両津', '両津', '中川', '麗子') from test; -- 「両津」の文字列リストのインデックスを3レコード返す

 
 

---

■ELT関数とFEILD関数を組み合わせたバルクアップデート

前述のELT関数だけでもバルクアップデートは可能である
FEILD関数を組み合わせるメリットは特定のレコードをバルクアップデートを可能にするところにある
 
testテーブルのid：1と3をバルクアップデートしたい場合は下記のようにすればよい

select * from test;
---------------
| id   | name |
---------------
|    1 | 両津 |
|    2 | 中川 |
|    3 | 麗子 |
---------------

update test set name = elt(field(id, 1, 3), '勘吉', '秋本') where id in (1, 3);

select * from test;
---------------
| id   | name |
---------------
|    1 | 勘吉 |
|    2 | 中川 |
|    3 | 秋本 |
---------------

 

このSQLは下記の動きとなる

・最初に「where id = 1」を処理
field(1, 1, 3) ⇒ 1
elt(1, ‘勘吉’, ‘秋本’) ⇒ 勘吉
 
つまり「update test set name = ‘勘吉’ where id = 1;」を実行している
 
・次に「where id = 3」を処理
field(3, 1, 3) ⇒ 2
elt(2, ‘勘吉’, ‘秋本’) ⇒ 秋本
 
つまり「update test set name = ‘秋本’ where id = 3;」を実行している