MySQLで文字列カラムの数値だけをキャストして更新する方法

MySQLで文字列カラムから数値をキャスト・更新するには？

MySQLではCEIL()関数とCAST()を組み合わせることで、文字列型（VARCHAR）のカラムに格納された値を数値に変換し、別のINT型カラムへ更新することができます。ここでは、実際にテーブルを作成しながら手順を解説します。

1. テーブルの作成

まず、最初のカラムをVARCHAR型としたテーブルを作成します。

mysql> create table DemoTable
    -> (
    -> Value varchar(20),
    -> UpdateValue int
    -> );
Query OK, 0 rows affected (1.08 sec)

2. レコードの挿入

INSERTコマンドを使って、数値と文字列が混在するデータを挿入します。

mysql> insert into DemoTable(Value) values('100');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable(Value) values('false');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable(Value) values('true');
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable(Value) values('1');
Query OK, 1 row affected (0.07 sec)

3. テーブルの内容を確認

SELECT文ですべてのレコードを表示してみましょう。

mysql> select *from DemoTable;

実行結果は以下の通りです。UpdateValueカラムはまだNULLのままです。

+-------+-------------+
| Value | UpdateValue |
+-------+-------------+
|   100 |        NULL |
| false |        NULL |
|  true |        NULL |
|     1 |        NULL |
+-------+-------------+
4 rows in set (0.00 sec)

4. 数値へのキャストと更新を実行

次のクエリを実行すると、Valueカラムの値をCASTで変換し、CEIL()関数によって整数値としてUpdateValueカラムに更新できます。文字列「false」や「true」はMySQLの暗黙的な型変換により0として扱われます。

mysql> update DemoTable
    -> set UpdateValue=ceil(cast(Value AS char(7)));
Query OK, 4 rows affected (0.18 sec)
Rows matched: 4 Changed: 4 Warnings: 0

5. 更新後の確認

再度テーブルのレコードを確認します。

mysql> select *from DemoTable;

実行結果は以下の通りです。数値として解釈できる値は正しくキャストされ、「false」「true」は0になっていることがわかります。

+-------+-------------+
| Value | UpdateValue |
+-------+-------------+
|   100 |         100 |
| false |           0 |
|  true |           0 |
|     1 |           1 |
+-------+-------------+
4 rows in set (0.00 sec)

まとめ

このように、CEIL(CAST(カラム名 AS CHAR))を使うことで、文字列カラム内の数値を簡単にINT型カラムへ反映できます。なお、より厳密に数値以外の文字列を除外したい場合は、REGEXPを用いた条件分岐や、MySQL 8.0以降で利用可能な検証方法と組み合わせることも検討するとよいでしょう。