はじめに
MariaDBではSQL実行時に暗黙の型変換が行われる場合があります。
この記事では数値と文字列の暗黙の型変換についての挙動やなぜ意識する必要があるのかポイントをまとめます。
暗黙の型変換って何？
異なる型同士で計算を行う場合、型を同じに揃えないと計算できないですが、その変換を暗黙的に行ううことを暗黙の型変換と言います。
例えば、MariaDBでは数値と文字列を足し算した場合、明示的に型変換しなくても、暗黙的に数値として計算されます。
select 1 + '1';
+---------+
| 1 + '1' |
+---------+
| 2 |
+---------+
このようにMariaDBでは暗黙的に型変換を行なう場合があるため、以下ではどのようなパターンがあるのかまとめてみます。
動作環境
select version();
+------------------------+
| version() |
+------------------------+
| 11.4.2-MariaDB-ubu2404 |
+------------------------+
暗黙の型変換の例
実際にSQLで試しながら、いくつか暗黙の型変換の例を見てみます。
整数と小数
整数が小数に変換されて計算されます。
select 1 + 1.0;
+---------+
| 1 + 1.0 |
+---------+
| 2.0 |
+---------+
数値と文字列
文字列が数値に変換されて計算されます。
select 1 + '1';
+---------+
| 1 + '1' |
+---------+
| 2 |
+---------+
文字列側が小数である場合は小数に変換されて計算されます。
select 1 + '1.1';
+-----------+
| 1 + '1.1' |
+-----------+
| 2.1 |
+-----------+
ただし、小数部の値が0である場合も試してみたところ整数となりました。
整数と小数を計算したときは小数になりましたが、それとは挙動が違うようです。
select 1 + '1.0';
+-----------+
| 1 + '1.0' |
+-----------+
| 2 |
+-----------+
文字列が数値としてみなせない場合は0として扱われます。
select 1 + 'a';
+---------+
| 1 + 'a' |
+---------+
| 1 |
+---------+
また、先頭から数値とみなせる部分までを数値として変換してくれます。
-- この場合、'123a4' は 'a' が出てくるまでは数値とみなせるため、123 になる
select 1 + '123a4';
+-------------+
| 1 + '123a4' |
+-------------+
| 124 |
+-------------+
なぜ暗黙の型変換を意識する必要があるのか
ここまで説明してきましたが、データベース側がうまいこと型を変換してくれるのであれば、特に意識しなくても問題ないのでは？と思う方もいるかもしれません。
しかし、意識しておかないと問題になる場合があります。
それはインデックスを使うときです。
暗黙の型変換により、SQL自体は正常に動くのですが、インデックスを使うことができず、低速になってしまう場合があります。
低速になる例
実際に低速になってしまう例を見てみましょう。
なんらかの事情によりユーザのIDを文字列で定義しているテーブルを例にしてみます。
また、低速になることを確認するためにレコードも多めに（1,000万件）作っておきます。
create table users (
id varchar(8) not null primary key,
name varchar(20) not null
);
select count(*) from users;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
select * from users limit 10;
+----------+--------------+
| id | name |
+----------+--------------+
| 1 | name1 |
| 10 | name10 |
| 100 | name100 |
| 1000 | name1000 |
| 10000 | name10000 |
| 100000 | name100000 |
| 1000000 | name1000000 |
| 10000000 | name10000000 |
| 1000001 | name1000001 |
| 1000002 | name1000002 |
+----------+--------------+
このテーブルに対して、以下のSQLでユーザを抽出してみます。
select * from users where id = 100;
+-----+---------+
| id | name |
+-----+---------+
| 100 | name100 |
+-----+---------+
1 row in set (28.772 sec)
idカラムは文字列ですが、暗黙の型変換により文字列が数値に変換され、クエリが正しく機能していることがわかります。
しかし、実行時間が約28秒ほどかかっており、かなり遅いクエリになっているのがわかります。
実行計画も見てみましょう。
explain select * from users where id = 100;
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | users | ALL | PRIMARY | NULL | NULL | NULL | 7279110 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+---------+-------------+
type がALLになっていることからインデックスが使われておらず、テーブルフルスキャンが起きていることがわかります。
では、暗黙の型変換に頼らず、文字列で検索した場合どうなるのか試してみます。
select * from users where id = '100';
+-----+---------+
| id | name |
+-----+---------+
| 100 | name100 |
+-----+---------+
1 row in set (0.002 sec)
今度は0.002秒で結果が返ってきました。
暗黙の型変換を利用したときよりも約14,000倍くらい早くなっており、かなり高速化しました。
実行計画も見てみましょう。
explain select * from users where id = '100';
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 34 | const | 1 | |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
typeがconstになっており、PKを使った検索に変わりました。
rowsを見ても1行しか読み込んでいないため、とても高速になっています。
このように暗黙の型変換が行われることを意識しておかないと、インデックスが使われず低速になる場合があるため、注意が必要です。
まとめ
暗黙の型変換について知っておきたいことは以下です。
-
異なる型同士で計算や比較を行った場合、暗黙の型変換が起きる
数値と文字列で比較した場合は文字列が数値に型変換される -
暗黙の型変換が起きるとインデックスが使われず低速になる場合がある
例えば文字列型のカラムに対して、数値で比較した場合
暗黙の型変換を意識してインデックスが使われずスロークエリにならないように注意しましょう。
それではまた。
TomoProg
参考資料