SQLのconvertについてまとめています。
SQL convertの構文
convertは、convert（値,変換後のデータ型）という指定方法で、異なるデータ型に変換が可能です。以下は、employees（社員）テーブルから、社員番号、姓名にbirth_dateとbirth_dateをconvertで符号なし整数（unsigned）に変換したデータをselectするSQLです。
mysql>SELECT emp_no,
first_name,
last_name,
birth_date,
CONVERT( BIRTH_DATE, unsigned )
FROM employees limit 10;
+--------+------------+-----------+------------+---------------------------------+
| emp_no | first_name | last_name | birth_date | convert( birth_date, unsigned ) |
+--------+------------+-----------+------------+---------------------------------+
| 10001 | Georgi | Facello | 1953-09-02 | 19530902 |
| 10002 | Bezalel | Simmel | 1964-06-02 | 19640602 |
| 10003 | Parto | Bamford | 1959-12-03 | 19591203 |
| 10004 | Chirstian | Koblick | 1954-05-01 | 19540501 |
| 10005 | Kyoichi | Maliniak | 1955-01-21 | 19550121 |
| 10006 | Anneke | Preusig | 1953-04-20 | 19530420 |
| 10007 | Tzvetan | Zielinski | 1957-05-23 | 19570523 |
| 10008 | Saniya | Kalloufi | 1958-02-19 | 19580219 |
| 10009 | Sumant | Peac | 1952-04-19 | 19520419 |
| 10010 | Duangkaew | Piveteau | 1963-06-01 | 19630601 |
+--------+------------+-----------+------------+---------------------------------+
10 rows in set (0.00 sec)
castと同じ機能が利用可能です。
【関連記事】
▶SQL castはデータ型の明示的な変換 データベースによって、オプションに差異あり
SQL convertの使い方
convertは文字列の文字コード変換が可能
convert(値 using 文字コード)でキャラクターコードの変換が可能です。
mysql> select convert( 'abc' using utf8 ); +-----------------------------+ | convert( 'abc' using utf8 ) | +-----------------------------+ | abc | +-----------------------------+ 1 row in set, 1 warning (0.00 sec)
なお、変換した文字列は、charset()関数でキャラクターセットが確認できます。
/* ユーザ変数@aにabcを代入 */
mysql> set @a='abc';
Query OK, 0 rows affected (0.00 sec)
/* @aのキャラクタセットがlatin1 */であることを確認
mysql> select charset(@a);
+-------------+
| charset(@a) |
+-------------+
| latin1 |
+-------------+
1 row in set (0.00 sec)
/* convertを使って、utf8に変換した文字列を@aに代入 */
mysql> set @a=convert('abc' using utf8);
Query OK, 0 rows affected, 1 warning (0.00 sec)
/* @aのキャラクタセットが、utf8になっている */
mysql> select charset(@a);
+-------------+
| charset(@a) |
+-------------+
| utf8 |
+-------------+
1 row in set (0.00 sec)
【関連記事】
▶SQL convertは、値の型変換をおこなう 文字列のキャラクターセット変更が可能
convertで文字列変換後、likeであいまい検索
カラムを文字列に変換後、likeによるあいまい検索が可能になります。以下の例は、int型のemp_noを文字列に変換し、likeでemp_noに123を含む社員番号を抽出しています。
mysql>SELECT * FROM employees WHERE CONVERT(EMP_NO, Char(5)) LIKE '%123%' limit 10; +--------+------------+------------+-------------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-------------+--------+------------+ | 10123 | 1962-05-12 | Hinrich | Randi | M | 1993-01-15 | | 11123 | 1956-10-21 | Herb | Cooley | F | 1988-03-28 | | 11230 | 1956-12-28 | Yakichi | Tzvieli | M | 1990-12-06 | | 11231 | 1959-01-28 | Marit | Narwekar | F | 1988-04-07 | | 11232 | 1955-09-03 | Hidefumi | Crelier | M | 1988-02-02 | | 11233 | 1952-05-08 | Sanjit | Waschkowski | F | 1991-06-17 | | 11234 | 1961-06-17 | Tooru | Vingron | M | 1990-08-21 | | 11235 | 1964-11-13 | Larisa | Schueller | F | 1988-05-31 | | 11236 | 1963-01-27 | Marek | Orlowska | M | 1994-03-24 | | 11237 | 1964-03-21 | Brendon | Salinas | M | 1986-03-30 | +--------+------------+------------+-------------+--------+------------+ 10 rows in set (0.01 sec)
ただし、convert後の検索にはインデックスが使用されないため複雑な条件や大量データに対しては速度低下の原因になります。
【関連記事】
▶sql likeを使ったサンプル集 likeは本当に遅いのか？
まとめ
ポテパンダの一言メモ
- convertはcastと同様データ型の変換が可能
- convertのusing指定で文字コードの変換が可能
- convertで文字列に変換後はlikeによるあいまい検索が可能
