アイスタイルAdvent Calender2021の7日目の記事です。
はじめに
こんにちは、DBAをやっているsaitokaです。
アイスタイルではSQL Serverをメインに担当しています。
最近、暗黙的型変換 (CONVERT_IMPLICIT) の罠にハマったので、振り返っていきたいと思います。
目次
暗黙的型変換 (CONVERT_IMPLICIT) ってなに？
異なるデータ型を使用して操作をする際に暗黙の型変換 (CONVERT_IMPLICIT) が発生することがあります。
違う型を比べたり、INSERT、UPDATEしようとしたときに、データベース側で自動的に型を合わせておいてくれる機能のことを言います。
例えば、smallint 型を int 型と比較する場合、比較を実行する前に、smallint 型から int 型に暗黙的に変換されます。
こんなことがありました
1. ある日質問が来ました
「SQL Serverの開発環境でクエリを実行するとエラーになってしまうんです。」
「ステージ環境では問題なく実行できるのですが、なぜでしょうか？各環境間での差異があるのでしょうか？」
USE testDB;
SELECT * FROM table1 WHERE ID_NO = 12345;
エラー内容
このエラーを見た瞬間に何かに気付いた方もいるかもしれませんが、このとき自分は、その何かに気が付くことができませんでした。
2. 調査を開始
開発環境とステージ環境とでデータベース設定の差異を確認
testDBのパラメーターや設定を確認しましたが、差異は見つかりませんでした。
もちろん原因はここではないので、差異は見つかるわけがありません。
シングルクォーテーションで囲うとクエリが通る
WHERE句の検索条件であるID_NOをシングルクォーテーションで囲うとクエリが通ることがわかりました。
USE testDB;
SELECT * FROM table1 WHERE ID_NO = '12345';
テーブルの定義を確認するとID_NO列は文字列型(VARCHAR)であることがわかり、ここでやっと暗黙的型変換というワードにたどり着きます。
しかし、なぜ暗黙的型変換が失敗しているのかが解決できませんでした。
神の声
暗黙的型変換が失敗する原因がわからない自分に先輩から神の声が届きます。
「エラーメッセージを見直してみてください。あと公式ドキュメントをよく読んでね。」
ここでエラーメッセージを見直してみます。
ここでやっと「tkg100671」という文字に気が付きます。
tkgは数値に変換できるわけがありませんね。
データを比較するとステージ環境には文字のデータが無いので、数値型(int)への暗黙的型変換が問題無く成功していることがわかりました。
かなり遠回りして解決に至りましたが、もっとエラー内容をよく見るべきでした。
暗黙的型変換の比較
もう少し暗黙的型変換を深堀りしてみました。
暗黙的型変換は、RDB毎に持つ型変換のルールに従って型が決定され、データベースソフトウェア毎に処理系依存のルールがあります。
今回はSQLServerとMySQLで暗黙的型変換が発生した際の結果の違いを比較してみました。
1. 基本動作で比較
以下の2つのSQLを実行してみます。SQL①が数値1と数値2を和算したものに対してSQL②は文字列1と数値2を和算しています。SQL ServerとMySQLそれぞれの結果を見てみます。
SQL①
SELECT 1+2;
SQL②
SELECT '1'+2;
実行結果
SQL Server
MySQL
mysql> SELECT 1+2;
+-----+
| 1+2 |
+-----+
| 3 |
+-----+
1 row in set (0.00 sec)
mysql> SELECT '1'+2;
+-------+
| '1'+2 |
+-------+
| 3 |
+-------+
1 row in set (0.00 sec)
SQLServerとMySQLの結果は同じでした。
SQL②は厳密には演算不可能なものですが、SQL Server、MySQL共に文字列1を暗黙的に型変換をしてくれたので、結果は数値の3が返ってきました。
2. 文字列に数字と文字が混合されている場合で比較
以下のSQLを実行して実行結果を見てみましょう。このSQLは、左右の両辺共に文字列で、数字と文字が混ざっています。
SQL
SELECT '1ko'+'2dondakeee';
実行結果
SQL Server
MySQL
mysql> SELECT '1ko'+'2dondakeee';
+--------------------+
| '1ko'+'2dondakeee' |
+--------------------+
| 3 |
+--------------------+
1 row in set, 2 warnings (0.00 sec)
今回はSQLServerとMySQLとで結果が変わりました。
SQL Serverでは暗黙的型変換はされず、文字列同士が結合されて文字列として返ってきました。
MySQLでは暗黙的型変換がされて数値の3が返ってきました。今回は両辺どちらも先頭が数字だったので数値として返すようにMySQLのルールで変換されました。
もう1つ別のSQLで比較
もう1つ別のSQLを実行してみます。左辺が文字列で右辺が数値です。左辺の文字列は数字と文字(カンマ)が交互に混ざっています。
SQL
SELECT '1,234,567' + 3;
実行結果
SQL Server
MySQL
mysql> SELECT '1,234,567' + 3;
+-----------------+
| '1,234,567' + 3 |
+-----------------+
| 4 |
+-----------------+
1 row in set, 1 warning (0.00 sec)
今回もSQLServerとMySQLとで結果が変わりました。
SQL Serverでは暗黙的型変換されましたが、文字（カンマ）を含めた、文字全てを型変換しようとして、失敗となりエラーが返ってきました。
MySQLでは暗黙的変換がされて数値の4が返ってきました。これは最初に出てきた数字以外の文字のところまでを数値変換するというMySQLのルールによるものです。
このルールは参考資料にリンクを掲載したMySQLの公式ドキュメントにも記載があります。
テーブルを作成して動作を比較
以下の構造と値のテーブルを用意しました。
mysql> SELECT * FROM test_my;
+------+---------+---------+---------------------+
| id | name | address | created_at |
+------+---------+---------+---------------------+
| 1 | saitoka | tokyo | 2021-12-03 01:05:33 |
| 2 | toyoday | chiba | 2021-12-03 01:06:55 |
+------+---------+---------+---------------------+
2 rows in set (0.00 sec)
テーブルに対して以下のSQLを実行します。WHERE句の検索条件を「name=’saito’+0」として左辺がname列には存在しない文字列で右辺が数値0です。
SQL
SELECT * FROM test_my WHERE name='saito'+0;
実行結果
SQL Server
MySQL
mysql> SELECT * FROM test_my WHERE name='saito'+0;
+------+---------+---------+---------------------+
| id | name | address | created_at |
+------+---------+---------+---------------------+
| 1 | saitoka | tokyo | 2021-12-03 01:05:33 |
| 2 | toyoday | chiba | 2021-12-03 01:06:55 |
+------+---------+---------+---------------------+
2 rows in set, 3 warnings (0.00 sec)
今回もSQLServerとMySQLとで結果が変わりました。
SQL Serverでは暗黙的型変換されましたが、文字を型変換しようとして、失敗となりエラーが返ってきました。データ型には優先順があり、データ型の異なる2つの列を和算すると、優先順位の低いデータ型を優先順位の高いデータ型に変換します。
今回のSQLは、数値が優先され、文字列が変換の対象となったということになります。
優先順位については、SQL Serverの公式ドキュメントを読んでみてください。
MySQLでは、一致する行が2件返ってきました。数値の和算である「+」の演算子の場合、その両辺は数値であることをMySQLは期待するので、左辺の文字が数値に暗黙的型変換がされます。
文字列の型変換は最初に出てきた数字以外の文字のところまでを数値変換するというMySQLのルールがあるので1文字目が「s」の「saito」はゼロと見なされ、「name=0」に変換されています。
もう1つ別のSQLで比較
テーブルに対してもう1つ別のSQLを実行します。今度はWHERE句の指定を「name=’saito’+’desu’」として両辺共がname列には存在しない文字列となっています。
SQL
SELECT * FROM test_my WHERE name='saito'+'desu';
実行結果
【SQL Server】
【MySQL】
mysql> SELECT * FROM test_my WHERE name='saito'+'desu';
+------+---------+---------+---------------------+
| id | name | address | created_at |
+------+---------+---------+---------------------+
| 1 | saitoka | tokyo | 2021-12-03 01:05:33 |
| 2 | toyoday | chiba | 2021-12-03 01:06:55 |
+------+---------+---------+---------------------+
2 rows in set, 4 warnings (0.01 sec)
今回もSQLServerとMySQLとで結果が変わりました。
SQL Serverでは暗黙的型変換されず、文字列が単純に結合されたので、「name=’saitodesu’」となり、一致する行は0件という結果となりました。
MySQLでは一致する行が2件返ってきました。こちらも同様で数値の和算である「+」の演算子の場合、その両辺は数値であることをMySQLは期待します。両辺とも文字列が数値に暗黙的型変換がされました。文字列の型変換は最初に出てきた数字以外の文字のところまでを数値変換するというMySQLのルールがあるので、1文字目が「s」の「saito」と「d」の「desu」は両方ともゼロと見なされ「name=0」に変換されています。これは以下のSQLからも確認できます。
mysql> SELECT 'saito'+'desu';
+----------------+
| 'saito'+'desu' |
+----------------+
| 0 |
+----------------+
1 row in set, 2 warnings (0.00 sec)
まとめ
- 暗黙的型変換は、違うデータ型を比べたり、INSERT、UPDATEをしようとしたときに、データベース側で自動的にデータ型を合わせてくれる機能です。
- データベース毎に型変換のルールが異なります。
- 自動に変換されて、予想とは異なる動作をする場合があります。
参考資料
SQL Server
データ型の変換 (データベース エンジン)
データ型の優先順位 (Transact-SQL)
MySQL
式評価での型変換
