はじめに
WITH RECURSIVE を使用した際に Data too long for column というエラーが出てしまい、SQLを実行できない現象が起きた。
この記事ではそのエラーへの対処方法をまとめる。
環境はMariaDBを使うが、参考にした資料はMySQLのリファレンスであるため、MySQLでも同等の対処方法で解決できると思われる。
この記事の対象者
- 再帰的なCTEで
Data too long for columnが出たときの対処方法を知りたい人
検証環境
MariaDB [sample]> select version();
+------------------------+
| version() |
+------------------------+
| 11.4.2-MariaDB-ubu2404 |
+------------------------+
現象
適当なテストデータを作ろうと思い、WITH RECURSIVE を使った以下のSQLを実行したが、想定した結果とは異なり、Data too long for column のエラーが起きてしまった。
実行したSQL
with recursive cte (n, name) as (
select 1, concat('p', 1)
union all
select n+1, concat('p', n+1)
from cte
where n < 10
)
select n, name from cte;
想定した結果
+----+------+
| n | name |
+----+------+
| 1 | p1 |
| 2 | p2 |
| 3 | p3 |
| 4 | p4 |
| 5 | p5 |
| 6 | p6 |
| 7 | p7 |
| 8 | p8 |
| 9 | p9 |
| 10 | p10 |
+----+------+
実際の結果
ERROR 1406 (22001): Data too long for column 'name' at row 10
なぜこのようなことになるのか
WITH RECURSIVE は非再帰的な部分と再帰的な部分に分けることができる。
with recursive cte (n, name) as (
-- ここは非再帰的な部分
select 1, concat('p', 1)
union all
-- ここは再帰的な部分
select n+1, concat('p', n+1)
from cte
where n < 10
)
select n, name from cte;
重要なのが WITH RECURSIVE の結果のカラムの型は非再帰的な部分の結果から推測される仕様となっていることだ。
そのため、concat('p', 1) は2文字分の文字列型とみなされる。
（この文字列の型がCHARなのかVARCHARなのかといった、具体的にどの型になるのかはリファレンスから見つけられず分からなかった。）
そして、再帰していくと、n = 9 のときに concat('p', 10) で3文字となってしまい、 Data too long for column が起きる。
つまり、再帰中に非再帰的な部分の文字列の長さよりも、文字列が長くなるとエラーが起きてしまう。
対処方法
非再帰的な部分でcastを使って文字列の幅を広げる。
今回のクエリでは3文字入れば問題ないため、以下のようなSQLに変更すればエラーは出なくなる。
修正後のSQL
with recursive cte (n, name) as (
select 1, cast(concat('p', 1) as char(3)) -- 非再帰的な部分でcastを使って文字列の幅を広げる
union all
select n+1, concat('p', n+1)
from cte
where n < 10
)
select n, name from cte;
実行結果
+------+------+
| n | name |
+------+------+
| 1 | p1 |
| 2 | p2 |
| 3 | p3 |
| 4 | p4 |
| 5 | p5 |
| 6 | p6 |
| 7 | p7 |
| 8 | p8 |
| 9 | p9 |
| 10 | p10 |
+------+------+
まとめ
-
WITH RECURSIVEの結果のカラムの型は非再帰的な部分の結果から推測される - 再帰していく際に非再帰的な部分の文字列の長さよりも、文字列が長くなるとエラーとなる
-
castを使って文字列の幅を広げることで対処できる
今回はWITH RECURSIVEを使った際に起きたData too long for columnへの対処方法をまとめた。
同じような現象に遭遇して困っている人がいれば、助けになればと思う。
それではまた。
TomoProg
参考資料